Issue copying worksheet containing Pivot Table

Aspose.Cells Product Family
,
1

There is a really strange behaviour that I struggle to debug and understand. I am not able to make a clean copy of a worksheet containing a Pivot Table into the same workbook whenever I want. Fyi I am using the last Aspose.Cells 23.4.

  1. In the case that I open the existing workbook and only copy the source sheet (PIVOT3 containing a Pivot Table) into PIVOT3_copy_only.

  2. In the case that I open the workbook, creates full Pivot Table in PIVOT3, and then copy this source she into PIVOT3_copy_after_creation new sheet, it completely messes up. It means I created the source sheet content + copied it in the same run.

Here is the code I use for the first run (fails) :

      //loading workbook
	try {
		workbook = new Workbook(new FileInputStream(workbookPath));
	} catch (FileNotFoundException e) {
		Logger.error("pkg.ExcelInterpreterAsposeTest.loadWorkbook", e.getMessage(), e);
	} catch (Exception e) {
		Logger.error("pkg.ExcelInterpreterAsposeTest.loadWorkbook", e.getMessage(), e);
	}
	
	interpreter = new ExcelInterpreter(workbook);
    interpreter.createPivotFromTable("PolicyData", "tblPolicies",	"PIVOT3",	"49",	"1",	"P_PIVOT_3",	"5");
	interpreter.addPivotDataField("PIVOT3",	"P_PIVOT_3",	"Construction",	"-4112",	"# ### ### ### ##0");

	interpreter.addPivotCalculatedDataField("PIVOT3", "P_PIVOT_3", "CalculatedField1", "= Construction - BusinessType", "# ### ### ##0.#");
	interpreter.addPivotFieldsToSection("PIVOT3", "P_PIVOT_3", "3", "Flood,Expiry");
	interpreter.addPivotFieldsToSection("PIVOT3", "P_PIVOT_3", "1", "Region,Location");
	interpreter.addPivotFieldsToSection("PIVOT3", "P_PIVOT_3", "2", "State");
	interpreter.setPivotStyle("PIVOT3", "P_PIVOT_3", "PivotStyleMedium9");
	interpreter.copySheetFromToNotWorking("PIVOT3", "PIVOT3_copy_after_creation");

Second run (right copy) :

            //loading workbook
	try {
		workbook = new Workbook(new FileInputStream(workbookPath));
	} catch (FileNotFoundException e) {
		Logger.error("pkg.ExcelInterpreterAsposeTest.loadWorkbook", e.getMessage(), e);
	} catch (Exception e) {
		Logger.error("pkg.ExcelInterpreterAsposeTest.loadWorkbook", e.getMessage(), e);
	} 
    interpreter.copySheetFromToNotWorking("PIVOT3", "PIVOT3_copy_only");

I attach the result file sampledatainsurance.zip (191.5 KB)

And the class I use : ExcelInterpreter.zip (3.4 KB)

Please let me know what’s wrong and if it can be fixed. For the time being, I used a workaround to save temporary workbooks before copies worksheets, it is dirty but it works. Though I would like to get rid of this workaround.

Thank you

2

@GVA32156,

Thanks for the sample file.

I evaluated your ExcelInterpreter class a bit. There are some types, objects and other variables (e.g., EIGlobal, KeyChart, ExcelChart, etc.) in different methods which cannot not be resolved properly and I could not compile your code segment. Could you please provide a standalone sample console program to reproduce the issue, so we could analyze your issue precisely.

3

Hi @amjad.sahi,

Here is a proper test application for you, I removed some types not relevant for our tests.
TestAppPkg.zip (5.1 KB)

The excel output file from my previous post is still the same, by the way.
Please let me know

4

@GVA32156,
I have modified the sample code as follows, and we can obtain the correct results. Please refer to the attachment. out.zip (205.5 KB)

		//1st usease 
		interpreter = new ExcelInterpreter(workbook);
		//Change the position of the newly created pivot table so that it does not overlap with the existing pivot table position
	    interpreter.createPivotFromTable("PolicyData", "tblPolicies",	"PIVOT3",	"2",	"1",	"P_PIVOT_3_test",	"5");
		interpreter.addPivotFieldsToSection("PIVOT3", "P_PIVOT_3_test", "3", "Flood,Expiry");
		interpreter.addPivotFieldsToSection("PIVOT3", "P_PIVOT_3_test", "1", "Region,Location");
		interpreter.addPivotFieldsToSection("PIVOT3", "P_PIVOT_3_test", "2", "State");
		
		interpreter.addPivotDataField("PIVOT3",	"P_PIVOT_3_test",	"Construction",	"-4112",	"# ### ### ### ##0");

		interpreter.addPivotCalculatedDataField("PIVOT3", "P_PIVOT_3_test", "CalculatedField1", "= Construction - BusinessType", "# ### ### ##0.#");

		interpreter.setPivotStyle("PIVOT3", "P_PIVOT_3_test", "PivotStyleMedium9");
		interpreter.copySheetFromToNotWorking("PIVOT3", "PIVOT3_copy_after_creation_test");

Can you try it?

5

First, the filters fields are not copied properly in your P_PIVOT_3_test object. Or they are not recognized by Excel as you can see. It means the copy is not clean.
filterFieldsUnrecognized.JPG (19.1 KB)

Secondly, what you propose is not my use case. I create PIVOT3 sheet with a pivot table, apply additions/modifications/formatting on the Pivot object, then I copy this sheet into a new one. And I expect to get the exact same content.

If I apply your modification of the Pivot position to my use case, it does not change anything.

  1. Deletion of PIVOT3 and PIVOT3_copy_after_creation tabs (real runtime conditions because they are not existing yet), then run of this code :

     //1st usease 
 interpreter = new ExcelInterpreter(workbook);
 //Change the position of the newly created pivot table so that it does not overlap with the existing pivot table position
 interpreter.createPivotFromTable("PolicyData", "tblPolicies",	"PIVOT3",	"2",	"1",	"P_PIVOT_3",	"5");
 interpreter.addPivotDataField("PIVOT3",	"P_PIVOT_3",	"Construction",	"-4112",	"# ### ### ### ##0");
 interpreter.addPivotCalculatedDataField("PIVOT3", "P_PIVOT_3", "CalculatedField1", "= Construction - BusinessType", "# ### ### ##0.#");
 interpreter.addPivotFieldsToSection("PIVOT3", "P_PIVOT_3", "3", "Flood,Expiry");
 interpreter.addPivotFieldsToSection("PIVOT3", "P_PIVOT_3", "1", "Region,Location");
 interpreter.addPivotFieldsToSection("PIVOT3", "P_PIVOT_3", "2", "State");
 interpreter.setPivotStyle("PIVOT3", "P_PIVOT_3", "PivotStyleMedium9");
 interpreter.copySheetFromToNotWorking("PIVOT3", "PIVOT3_copy_after_creation");

-> still the exact same problem.

  1. Then I remove PIVOT3_copy_only tab (real runtime conditions) and I run this code for a second run :

     //2nd usecase (different/second runtime)
 interpreter.copySheetFromToNotWorking("PIVOT3", "PIVOT3_copy_only");

-> magically works exactly as expected (full proper copy of the PIVOT3 sheet keeping the Pivot structure and format)
sampledatainsurance.zip (212.9 KB)

This is the observation I do. Though I would like to get rid of doing several intermediate saving file operations at runtime
Is there a solution for this ?

6

@GVA32156

We will evaluate it further and get back to you soon.

7

@GVA32156
We can reproduce your issue, the newly created pivot table cannot be copied correctly when copying the worksheet.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-45386

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

8

@GVA32156,

We are pleased into inform you that your issue (logged earlier as “CELLSJAVA-45386”) has been resolved. The fix will be included in an upcoming release (Aspose.Cells v23.6) that we plan to release in the first half of June 2023. You will be notified when the next version is released.

9

The issues you have found earlier (filed as CELLSJAVA-45386) have been fixed in Aspose.Cells for Java 23.6.