Pivot Table Refresh does not retain order

Aspose.Cells Product Family
1

Hi

I currently generate excel workbooks from a template which has a pivot table which I've ordered by the row grand total. My Java code executes the lines below and the spreadsheet gets generated with the new data. However the ordering changes from the grand total to aplhabetical. Can you tell me if it's possible to retain this and if so, how?

pivotTable.refreshData();
pivotTable.calculateData();

Rgds

2

Hi Greg,

Thanks for your posting and considering Aspose.Cells for Java.

Please download and try the latest version: Aspose.Cells
for Java v8.0.0.2 and see if it fixes your issue.

If your issue still occurs, then please provide us your source file containing the pivot table. We will look into it and update you asap.

3
Hi Faiz,

That didn't work. I've attached the template. When the data is overridden and after I call refreshData() and calculateData() the order in the row column of the pivot table changes from the count to alpabetical.

Cheers
Greg
4

Hi Greg,

Thanks for your sample file and using Aspose.Cells.

I have tested your source file with the following code and did not notice any issue. I have attached the output xlsx file for your reference.

Could you provide your sample code and also highlight your issue in a screenshot? It will help us look into this issue more closely and we will update you asap.

Java


String filePath = “F:\Shak-Data-RW\Downloads\Template.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


PivotTable pivotTable = worksheet.getPivotTables().get(0);


pivotTable.refreshData();

pivotTable.calculateData();


workbook.save(filePath + “.out.xlsx”);

5

Thanks for your response.
The problem occurs if the source data is cleared and replaced with real data. So my java code reads data from a database somewhere and replaces the data that in the template that is used for the pivot table (second tab). When this data is replaced, I refresh the pivot table since the data is now new. The new file with the new data is loses the original ordering. To see what I mean, right click on the row and select more sort options. You’ll see that it’s sorted according to the count descending. If you overwrite the data in the template using java aspose, when you open the new sheet, that ordering has changed to alphabetical.

This seems similar to the filter issue highlighted here:
Retain filter information in Pivot table when the source table is cleared

Cheers

6

Hi Greg,

Thanks for your posting and using Aspose.Cells.

Could you please provide some simple code which should be using some dummy data and refreshing the pivot table and screenshot showing the difference in the source and output xlsx files? It will help us investigate this issue properly and we will log it in our database for a fix.

I refreshed the pivot table after inserting some data manually in Template.xlsx file and it was refreshed successfully but I was unable to find any different between the Template.xlsx and Template.xlsx.out.xlsx files. I have attached the files and screenshot for a reference.

Java


String filePath = “F:\Shak-Data-RW\Downloads\Template.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


PivotTable pivotTable = worksheet.getPivotTables().get(0);


pivotTable.refreshData();

pivotTable.calculateData();


workbook.save(filePath + “.out.xlsx”);

7

Hi Shak,


Unfortunately I cannot give you more code without putting a bigger slice of my code base which I cannot do as it contains sensitive information. My java code reads information from a database and replaces the dummy data in the template with this new info. After it does this, it then calls refresh. It is not inserted manually. It is done using code when the application runs. The problem is not that it isn’t sorted. The problem is that the sort has changed. The new sort is no longer by count of grand total. it is now alphabetical. So in your screenshot, you need to click on “More Sort Options”. There you’ll see there are 2 options to sort by. The Count by ID is what I want the new sheet to be sorted by and it is what I’ve set in the template.

However after the code runs, it changes to sort by Submitter. The other option in the drop down.
If you set up a template similar to mine and write java code that overwrites that data, you’ll see that the ordering changes.

Rgds
8

Hi Greg,

Thanks for your screenshot and using Aspose.Cells.

I am unable to find any difference in the source (template.xlsx) and output (template.xlsx.out.xlsx) files. When I check the More Sort Option settings, they both look same to me. I am attaching the screenshot for your reference.

Could you please check the both files in MS-Excel (I used MS-Excel 2010) and let me know what I am missing?

Thanks for your patience and cooperation.

9
Hi Shak,

This is what I hope to see. However I'm not seeing this. Can you confirm your code does the following:

1) //Reads the template
Workbook workbook = new Workbook("Your template");

2) //Overwrites the data in the workbook
Update the data on the worksheet using java not manually

3) //Refreshes the pivot tables (pseudo code)

for (PivotTable pivotTable : workbook.getWorksheets().get(0).getPivotTables())
{
pivotTable.refreshData();
try
{
pivotTable.calculateData();
}
catch (Exception e)
{
throw new RuntimeException("Failed to calculate data for pivot table: " + pivotTable.getName(), e);
}

4)// Save the result
workbook.save(filepath + "out.xlsx");
Can you confirm that this is the pattern you follow to generate the file you attached?

If so, can you give me the code snippet you use, so I can see what I'm missing?

Rgds
Greg
10

Hi Greg,

Thanks for your posting and using Aspose.Cells.

I am not able to find any issue. I tested this issue with the following code. The code now inserts the data using Aspose.Cells in 4th row and then refresh the pivot table.

I have attached the source and output xlsx files for your reference.

Java


String filePath = “F:\Shak-Data-RW\Downloads\Template.xlsx”;


//Reads the template

Workbook workbook = new Workbook(filePath);


//Overwrites the data in the workbook

Worksheet dataSheet = workbook.getWorksheets().get(“Supervisees Submissions”);


dataSheet.getCells().get(“A4”).putValue(1);

dataSheet.getCells().get(“B4”).putValue(“No Data”);

dataSheet.getCells().get(“C4”).putValue(“No Data”);

dataSheet.getCells().get(“D4”).putValue(“No Data”);

dataSheet.getCells().get(“E4”).putValue(“No Data”);


dataSheet.getCells().get(“F4”).putValue(0);

dataSheet.getCells().get(“G4”).putValue(0);

dataSheet.getCells().get(“H4”).putValue(0);

dataSheet.getCells().get(“I4”).putValue(0);

dataSheet.getCells().get(“J4”).putValue(0);

dataSheet.getCells().get(“K4”).putValue(0);

dataSheet.getCells().get(“L4”).putValue(0);

dataSheet.getCells().get(“M4”).putValue(0);

dataSheet.getCells().get(“N4”).putValue(0);


dataSheet.getCells().get(“O4”).putValue(“No Data”);


dataSheet.getCells().get(“P4”).putValue(“00-01-1900 12:00:00 AM”, true);

dataSheet.getCells().get(“Q4”).putValue(“00-01-1900 12:00:00 AM”, true);


dataSheet.getCells().get(“R4”).setFormula("=($Q3-$P3)*1440");


dataSheet.getCells().get(“S4”).putValue(1);

dataSheet.getCells().get(“T4”).putValue(1);

dataSheet.getCells().get(“U4”).putValue(“No Data”);

dataSheet.getCells().get(“V4”).putValue(1);

dataSheet.getCells().get(“W4”).putValue(1);


//Refreshes the pivot tables (pseudo code)

PivotTableCollection pivotTables = workbook.getWorksheets().get(0).getPivotTables();


for (int i=0; i<pivotTables.getCount(); i++ )

{

PivotTable pivotTable = pivotTables.get(i);


pivotTable.refreshData();

try

{

pivotTable.calculateData();

}

catch (Exception e)

{

throw new RuntimeException("Failed to calculate data for pivot table: " + pivotTable.getName(), e);

}

}


// Save the result

workbook.save(filePath + “.out.xlsx”);



11

Hi Shak,


Thanks again for getting back to me. I have figured out what the difference is between your code and mine. In yours you are not overriding existing data, you’re merely adding to it. Can you try the snippet below? This should give you the results I’m seeing:

dataSheet.getCells().get(“A1”).putValue(“Rubi ID”);
dataSheet.getCells().get(“B1”).putValue(“Submitter”);
dataSheet.getCells().get(“C1”).putValue(“Content Type”);
dataSheet.getCells().get(“D1”).putValue(“Desk”);
dataSheet.getCells().get(“E1”).putValue(“Canned Comment”);
dataSheet.getCells().get(“F1”).putValue(“MMC”);
dataSheet.getCells().get(“G1”).putValue(“Line Compliance”);
dataSheet.getCells().get(“H1”).putValue(“Executive Office”);
dataSheet.getCells().get(“I1”).putValue(“Legal”);
dataSheet.getCells().get(“J1”).putValue(“Tax”);
dataSheet.getCells().get(“K1”).putValue(“Accounting”);
dataSheet.getCells().get(“L1”).putValue(“Others”);
dataSheet.getCells().get(“M1”).putValue(“Sampled”);
dataSheet.getCells().get(“N1”).putValue(“Routed”);
dataSheet.getCells().get(“O1”).putValue(“Region”);
dataSheet.getCells().get(“P1”).putValue(“Started”);
dataSheet.getCells().get(“Q1”).putValue(“Submitted”);
dataSheet.getCells().get(“R1”).putValue(“Duration In Minutes”);
dataSheet.getCells().get(“S1”).putValue(“Supervisee”);
dataSheet.getCells().get(“T1”).putValue(“Has Comment”);
dataSheet.getCells().get(“U1”).putValue(“Quarter”);
dataSheet.getCells().get(“V1”).putValue(“Previous Quarter”);
dataSheet.getCells().get(“W1”).putValue(“Frequent Submitter”);

dataSheet.getCells().get(“A2”).putValue(1);
dataSheet.getCells().get(“B2”).putValue(“sub1”);
dataSheet.getCells().get(“C2”).putValue(“contentType1”);
dataSheet.getCells().get(“D2”).putValue(“desk1”);
dataSheet.getCells().get(“E2”).putValue(“cannedComment1”);
dataSheet.getCells().get(“F2”).putValue(1);
dataSheet.getCells().get(“G2”).putValue(1);
dataSheet.getCells().get(“H2”).putValue(1);
dataSheet.getCells().get(“I2”).putValue(1);
dataSheet.getCells().get(“J2”).putValue(1);
dataSheet.getCells().get(“K2”).putValue(1);
dataSheet.getCells().get(“L2”).putValue(1);
dataSheet.getCells().get(“M2”).putValue(1);
dataSheet.getCells().get(“N2”).putValue(0);
dataSheet.getCells().get(“O2”).putValue(“region1”);
dataSheet.getCells().get(“P2”).putValue(“00-01-1900 12:00:00 AM”);
dataSheet.getCells().get(“Q2”).putValue(“00-01-1900 12:00:00 AM”);
dataSheet.getCells().get(“R2”).putValue(0);
dataSheet.getCells().get(“S2”).putValue(1);
dataSheet.getCells().get(“T2”).putValue(1);
dataSheet.getCells().get(“U2”).putValue(“Q2”);
dataSheet.getCells().get(“V2”).putValue(1);
dataSheet.getCells().get(“W2”).putValue(1);

dataSheet.getCells().get(“A3”).putValue(2);
dataSheet.getCells().get(“B3”).putValue(“sub2”);
dataSheet.getCells().get(“C3”).putValue(“contentType2”);
dataSheet.getCells().get(“D3”).putValue(“desk2”);
dataSheet.getCells().get(“E3”).putValue(“cannedComment2”);
dataSheet.getCells().get(“F3”).putValue(1);
dataSheet.getCells().get(“G3”).putValue(1);
dataSheet.getCells().get(“H3”).putValue(1);
dataSheet.getCells().get(“I3”).putValue(1);
dataSheet.getCells().get(“J3”).putValue(1);
dataSheet.getCells().get(“K3”).putValue(1);
dataSheet.getCells().get(“L3”).putValue(1);
dataSheet.getCells().get(“M3”).putValue(0);
dataSheet.getCells().get(“N3”).putValue(1);
dataSheet.getCells().get(“O3”).putValue(“region2”);
dataSheet.getCells().get(“P3”).putValue(“00-01-1900 12:00:00 AM”);
dataSheet.getCells().get(“Q3”).putValue(“00-01-1900 12:00:00 AM”);
dataSheet.getCells().get(“R3”).putValue(0);
dataSheet.getCells().get(“S3”).putValue(1);
dataSheet.getCells().get(“T3”).putValue(1);
dataSheet.getCells().get(“U3”).putValue(“Q2”);
dataSheet.getCells().get(“V3”).putValue(1);
dataSheet.getCells().get(“W3”).putValue(1);

Cheers
12

Hi Greg,

Thanks for your sample code and using Aspose.Cells.

I have looked into this issue and found that you are changing the headings. e.g

"Duration In Minutes" should be “Duration (in minutes)”, “Previous Quarter” should be “Is Previous Quarter” and “Frequent Submitter” should be “Is Frequent Submitter”.

Once, I made the above changes, then issue mentioned by you did not happen. Please see the following full sample code for your reference. Please see the commented and added lines.

I have attached the template.xlsx (it has no data now, data is inserted with the code) and template.xlsx.out.xlsx for you to view.

Java
String filePath = “F:\Shak-Data-RW\Downloads\Template.xlsx”;

//Reads the template
Workbook workbook = new Workbook(filePath);

//Overwrites the data in the workbook
Worksheet dataSheet = workbook.getWorksheets().get(“Supervisees Submissions”);

dataSheet.getCells().get(“A1”).putValue(“Rubi ID”);
dataSheet.getCells().get(“B1”).putValue(“Submitter”);
dataSheet.getCells().get(“C1”).putValue(“Content Type”);
dataSheet.getCells().get(“D1”).putValue(“Desk”);
dataSheet.getCells().get(“E1”).putValue(“Canned Comment”);
dataSheet.getCells().get(“F1”).putValue(“MMC”);
dataSheet.getCells().get(“G1”).putValue(“Line Compliance”);
dataSheet.getCells().get(“H1”).putValue(“Executive Office”);
dataSheet.getCells().get(“I1”).putValue(“Legal”);
dataSheet.getCells().get(“J1”).putValue(“Tax”);
dataSheet.getCells().get(“K1”).putValue(“Accounting”);
dataSheet.getCells().get(“L1”).putValue(“Others”);
dataSheet.getCells().get(“M1”).putValue(“Sampled”);
dataSheet.getCells().get(“N1”).putValue(“Routed”);
dataSheet.getCells().get(“O1”).putValue(“Region”);
dataSheet.getCells().get(“P1”).putValue(“Started”);
dataSheet.getCells().get(“Q1”).putValue(“Submitted”);
// dataSheet.getCells().get(“R1”).putValue(“Duration In Minutes”);
dataSheet.getCells().get(“R1”).putValue(“Duration (in minutes)”);
dataSheet.getCells().get(“S1”).putValue(“Supervisee”);
dataSheet.getCells().get(“T1”).putValue(“Has Comment”);
dataSheet.getCells().get(“U1”).putValue(“Quarter”);
// dataSheet.getCells().get(“V1”).putValue(“Previous Quarter”);
dataSheet.getCells().get(“V1”).putValue(“Is Previous Quarter”);
// dataSheet.getCells().get(“W1”).putValue(“Frequent Submitter”);
dataSheet.getCells().get(“W1”).putValue(“Is Frequent Submitter”);

dataSheet.getCells().get(“A2”).putValue(1);
dataSheet.getCells().get(“B2”).putValue(“sub1”);
dataSheet.getCells().get(“C2”).putValue(“contentType1”);
dataSheet.getCells().get(“D2”).putValue(“desk1”);
dataSheet.getCells().get(“E2”).putValue(“cannedComment1”);
dataSheet.getCells().get(“F2”).putValue(1);
dataSheet.getCells().get(“G2”).putValue(1);
dataSheet.getCells().get(“H2”).putValue(1);
dataSheet.getCells().get(“I2”).putValue(1);
dataSheet.getCells().get(“J2”).putValue(1);
dataSheet.getCells().get(“K2”).putValue(1);
dataSheet.getCells().get(“L2”).putValue(1);
dataSheet.getCells().get(“M2”).putValue(1);
dataSheet.getCells().get(“N2”).putValue(0);
dataSheet.getCells().get(“O2”).putValue(“region1”);
dataSheet.getCells().get(“P2”).putValue(“00-01-1900 12:00:00 AM”);
dataSheet.getCells().get(“Q2”).putValue(“00-01-1900 12:00:00 AM”);
dataSheet.getCells().get(“R2”).putValue(0);
dataSheet.getCells().get(“S2”).putValue(1);
dataSheet.getCells().get(“T2”).putValue(1);
dataSheet.getCells().get(“U2”).putValue(“Q2”);
dataSheet.getCells().get(“V2”).putValue(1);
dataSheet.getCells().get(“W2”).putValue(1);

dataSheet.getCells().get(“A3”).putValue(2);
dataSheet.getCells().get(“B3”).putValue(“sub2”);
dataSheet.getCells().get(“C3”).putValue(“contentType2”);
dataSheet.getCells().get(“D3”).putValue(“desk2”);
dataSheet.getCells().get(“E3”).putValue(“cannedComment2”);
dataSheet.getCells().get(“F3”).putValue(1);
dataSheet.getCells().get(“G3”).putValue(1);
dataSheet.getCells().get(“H3”).putValue(1);
dataSheet.getCells().get(“I3”).putValue(1);
dataSheet.getCells().get(“J3”).putValue(1);
dataSheet.getCells().get(“K3”).putValue(1);
dataSheet.getCells().get(“L3”).putValue(1);
dataSheet.getCells().get(“M3”).putValue(0);
dataSheet.getCells().get(“N3”).putValue(1);
dataSheet.getCells().get(“O3”).putValue(“region2”);
dataSheet.getCells().get(“P3”).putValue(“00-01-1900 12:00:00 AM”);
dataSheet.getCells().get(“Q3”).putValue(“00-01-1900 12:00:00 AM”);
dataSheet.getCells().get(“R3”).putValue(0);
dataSheet.getCells().get(“S3”).putValue(1);
dataSheet.getCells().get(“T3”).putValue(1);
dataSheet.getCells().get(“U3”).putValue(“Q2”);
dataSheet.getCells().get(“V3”).putValue(1);
dataSheet.getCells().get(“W3”).putValue(1);


//Refreshes the pivot tables (pseudo code)
PivotTableCollection pivotTables = workbook.getWorksheets().get(0).getPivotTables();

for (int i=0; i<pivotTables.getCount(); i++ )
{
PivotTable pivotTable = pivotTables.get(i);

pivotTable.refreshData();
try
{
pivotTable.calculateData();
}
catch (Exception e)
{
throw new RuntimeException("Failed to calculate data for pivot table: " + pivotTable.getName(), e);
}
}

// Save the result
workbook.save(filePath + “.out.xlsx”);

13

Thanks for spotting my error Shak. I now see the order as I expect it to be. However this has uncovered another issue.

It appears that although the order is kept, the filter I have in the template to ignore blank rows stops working correctly showing blanks and wrongly filtering any new rows out (sub2 in the example I'm sending).

This also seems to be the case in the files you sent me.

I've attached my template and the results. Below is the code snippet.

Please note that the template should have data initially. I want to be able to override it successfully while keeping all filters, ordering etc:

dataSheet.getCells().get("A1").putValue("Rubi ID");
dataSheet.getCells().get("B1").putValue("Submitter");
dataSheet.getCells().get("C1").putValue("Content Type");
dataSheet.getCells().get("D1").putValue("Desk");
dataSheet.getCells().get("E1").putValue("Canned Comment");
dataSheet.getCells().get("F1").putValue("MMC");
dataSheet.getCells().get("G1").putValue("Line Compliance");
dataSheet.getCells().get("H1").putValue("Executive Office");
dataSheet.getCells().get("I1").putValue("Legal");
dataSheet.getCells().get("J1").putValue("Tax");
dataSheet.getCells().get("K1").putValue("Accounting");
dataSheet.getCells().get("L1").putValue("Others");
dataSheet.getCells().get("M1").putValue("Sampled");
dataSheet.getCells().get("N1").putValue("Routed");
dataSheet.getCells().get("O1").putValue("Region");
dataSheet.getCells().get("P1").putValue("Started");
dataSheet.getCells().get("Q1").putValue("Submitted");
dataSheet.getCells().get("R1").putValue("Duration (in minutes)");
dataSheet.getCells().get("S1").putValue("Supervisee");
dataSheet.getCells().get("T1").putValue("Has Comment");
dataSheet.getCells().get("U1").putValue("Quarter");
dataSheet.getCells().get("V1").putValue("Is Previous Quarter");
dataSheet.getCells().get("W1").putValue("Is Frequent Submitter");

dataSheet.getCells().get("A2").putValue(1);
dataSheet.getCells().get("B2").putValue("sub1");
dataSheet.getCells().get("C2").putValue("contentType1");
dataSheet.getCells().get("D2").putValue("desk1");
dataSheet.getCells().get("E2").putValue("cannedComment1");
dataSheet.getCells().get("F2").putValue(1);
dataSheet.getCells().get("G2").putValue(1);
dataSheet.getCells().get("H2").putValue(1);
dataSheet.getCells().get("I2").putValue(1);
dataSheet.getCells().get("J2").putValue(1);
dataSheet.getCells().get("K2").putValue(1);
dataSheet.getCells().get("L2").putValue(1);
dataSheet.getCells().get("M2").putValue(1);
dataSheet.getCells().get("N2").putValue(0);
dataSheet.getCells().get("O2").putValue("region1");
dataSheet.getCells().get("P2").putValue("00-01-1900 12:00:00 AM");
dataSheet.getCells().get("Q2").putValue("00-01-1900 12:00:00 AM");
dataSheet.getCells().get("R2").putValue(0);
dataSheet.getCells().get("S2").putValue(1);
dataSheet.getCells().get("T2").putValue(1);
dataSheet.getCells().get("U2").putValue("Q2");
dataSheet.getCells().get("V2").putValue(1);
dataSheet.getCells().get("W2").putValue(1);

dataSheet.getCells().get("A3").putValue(2);
dataSheet.getCells().get("B3").putValue("sub2");
dataSheet.getCells().get("C3").putValue("contentType2");
dataSheet.getCells().get("D3").putValue("desk2");
dataSheet.getCells().get("E3").putValue("cannedComment2");
dataSheet.getCells().get("F3").putValue(1);
dataSheet.getCells().get("G3").putValue(1);
dataSheet.getCells().get("H3").putValue(1);
dataSheet.getCells().get("I3").putValue(1);
dataSheet.getCells().get("J3").putValue(1);
dataSheet.getCells().get("K3").putValue(1);
dataSheet.getCells().get("L3").putValue(1);
dataSheet.getCells().get("M3").putValue(0);
dataSheet.getCells().get("N3").putValue(1);
dataSheet.getCells().get("O3").putValue("region2");
dataSheet.getCells().get("P3").putValue("00-01-1900 12:00:00 AM");
dataSheet.getCells().get("Q3").putValue("00-01-1900 12:00:00 AM");
dataSheet.getCells().get("R3").putValue(0);
dataSheet.getCells().get("S3").putValue(1);
dataSheet.getCells().get("T3").putValue(1);
dataSheet.getCells().get("U3").putValue("Q2");
dataSheet.getCells().get("V3").putValue(1);
dataSheet.getCells().get("W3").putValue(1);

14

Hi there,


Please call pivotTable.setHasBlankRows(false) before refreshing and re-calculating the Pivot Table. I have checked on my end, the above line removes the blank rows from the Pivot Table.
15

Hi,

Thanks for your posting and using Aspose.Cells for Java.

We were able to reproduce this issue. When pivot table is refreshed manually using MS-Excel, filter is retained and blank rows are not shown. But when it is refreshed with Aspose.Cells, filter is not retained and blank rows are displayed.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40799.

I have attached the screenshot illustrating this issue for a reference.

Java


String filePath = “F:\Shak-Data-RW\Downloads\sample.xlsx”;


//Reads the template

Workbook workbook = new Workbook(filePath);


//Refreshes the pivot tables (pseudo code)

PivotTableCollection pivotTables = workbook.getWorksheets().get(0).getPivotTables();


for (int i=0; i<pivotTables.getCount(); i++ )

{

PivotTable pivotTable = pivotTables.get(i);


pivotTable.refreshData();

try

{

pivotTable.calculateData();

}

catch (Exception e)

{

throw new RuntimeException("Failed to calculate data for pivot table: " + pivotTable.getName(), e);

}

}


// Save the result

workbook.save(filePath + “.out.xlsx”);

16

Thanks for confirming. I think this is similar to the issue raised here:

Retain filter information in Pivot table when the source table is cleared

One thing that I noticed in my example was that if I override the columns and with new ones (as long as the columns I override are not in the pivot table filters e.g. override Duration (in minutes) with Duration In Minutes), the filter was kept but the order is reset.

Hope that helps.

Cheers

17

Hi Greg,

Thanks for your posting and using Aspose.Cells.

It seems, your issue is related with the issue mentioned (CELLSNET-41734) by you, so we have linked your issue CELLSJAVA-40799 with this issue as Related issue.

Besides, we have logged your comments relating to retaining filter when the column names are overridden. Hopefully, it will help us resolve this issue.

Once, we will have some fix or update for you, we will let you know asap.

18

Hi Greg,

Thanks for using Aspose.Cells.

We have evaluated this issue further and have the following update for you.

In Ms-Excel:

1. In Ms-Excel, in the file named sample.xlsx, before you refresh the PivotTable, the PivotField named “Submitter” contains two items(“No Data”,”blank”).the data is remained when the original data source of PivotTable is deleted.

2. In Ms-Excel, when you refresh the PivotTable, the current data source of PivotTable will supply three items(“sub1”,”sub2”,”blank”) to the PivotField named “Submitter”. After removing the repeated items, the PivotField named “submitter” will contains four items(“No Data”,”blank” ,“sub1”,”sub2”).

3. But the original filter index is not changed, so the item named “No Data” is selected. So the PivotTable contains no data.


In Aspose.Cells:

1. When you refresh the PivotTable, the two original items(“No Data”,”blank”) which the PivotField named “Submitter” contains are deleted, the current data source will supply three items (“sub1”,”sub2”,”blank”) to the PivotField named “Submitter”.

2. The original filter index is not changed,so the second item named “sub2” is not selected. The items(“sub1”,”blank”) are selected.


Currently, we cannot support it for now, we will support it later.

19

@newtoaspose,

This is to inform you that your issue (logged as “CELLSJAVA-40799”) has been resolved. The fix will be included in the upcoming release (Aspose.Cells v23.11) that we plan to release in the first half of November. You will be notified when the new version is released.

20

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