CalculateData method not working on certain excel files

Aspose.Cells Product Family
,
1

Hello,

we are evaluating Aspose Cells for use in our application and delivery pipeline. As part of that, we are updating existing Excel Pivot files with new layout and Aspose seemed best fit for this purpose as the API is really easy to understand. However we ran into some issues where the layout is not reflected in the resulting pivot.

Input is an Excel file with pivot table and populated pivot cache (but no connected data source, so all the data is in pivot cache only - thus we don’t and can’t call refreshData() method).

We open it in Aspose and call code snippet like this:

` Workbook asposeWB = new Workbook(name);
Worksheet sheetWB = asposeWB.getWorksheets().get(worksheetName);
PivotTable pivotTable = sheetWB.getPivotTables().get(0);

    pivotTable.setRowGrand(false);
    pivotTable.setColumnGrand(true);

    pivotTable.addFieldToArea(PivotFieldType.ROW, "Color");
    pivotTable.addFieldToArea(PivotFieldType.COLUMN, "Animal");
    pivotTable.addFieldToArea(PivotFieldType.DATA, "Weight");
    pivotTable.addFieldToArea(PivotFieldType.PAGE, "Fur");

    pivotTable.calculateData();

    asposeWB.save(name);`

In some cases, this works fine and the resulting pivot reflects the new layout (added fields). In other cases I can see the newly added fields in the PivotTable fields wizard, however the pivot table itself has the old layout and data.

Once I change the pivot fields in the wizard in any way, the pivot refreshes as usual, but I can’t simulate this manual action by using Aspose cells “calculateData()” as expected. Without it, we are not able to use Aspose in our application.

Can you please help us figure out why most Excel files don’t work for us and how to fix this?

Best regards,
Jan B.

2

@jbures,

If you are saving to Excel file format, then you may skip calculating or refreshing pivot table via code and let MS Excel do the refresh when the output file is opened into MS Excel. If you are saving to image or PDF format, then it is compulsory to refresh pivot table data once you have edited the pivot table elements and its source data.

If you still have any confusion or issue, kindly paste a sample Java program (without compile time errors) and zip and attach template (input) file to reproduce the issue on our end, we will check it soon.

3

Hello Amjad,

we are saving to Excel file format.

As I said in the original post, we cannot refresh the data as the connection is to an external MDB file that is disconnected and we use the pivot cache as the “data store”. Refresh on opening does not solve the issue as once again, the pivot can’t “refresh”, only recalcuate.

It works for some excel files, but not others (those are provided by the end user so might differ in some details).

I will try to get together a template and simple compilable version to make it more understandable.

Best,
Jan B.

4

@jbures,

Ok, we are looking forward to your simulation java code and sample file to reproduce the issue on our end.

5

Hi Amjad,

we figured it out in the end while preparing the simulation code + Excel - if the original pivot has External data source as its source, the calculateData() method did not recalculate the pivot from cache (while manual user action did)

Pivots with “table” data source worked fine and recalculated the pivot table from pivot cache.

We ended up adding code that “severs” the external data connection and all is working fine, but you might want to look into the cause of this anyway.

Best,
Jan B.

6

@jbures,

Good to know that everything is working fine now. Please note, currently, Aspose.Cells does not support to calculate pivot data where the pivot table is created with data model as source.

As requested earlier, we appreciate if you could prepare a sample to simulate the issue, so we could look into it precisely to consequently figure it out soon.