Refresh Pivot table functionality

Aspose.Cells Product Family
1
Is this feature available in latest version?
2

Hi,


Thank you for considering Aspose.
Can you please elaborate your question a little bit more? Also share the platform (Java / .NET) you are using.
By the way, Aspose.Cells API in .NET and JAVA supports to refresh the Pivot Table programmatically. We can provide you further assistance in this regard so feel free to write back.
3

Hello and thank you for your support,

I have an existing .xls spreadsheet which contains a sheet named data.

I use an sql query to pull the latest data into this spreadsheet..

private void LoadDataWorkSheet1(Aspose.Cells.Worksheet dataWorkSheet)
{
// Retrieve Report1 Data

//DataTable reportDataTable = RetrieveTestData1();
System.Data.DataTable reportDataTable = RetrieveDataReport("ODS_DB.dbo.MYSTOREDPROCEDURE");

ImportDataColumn(dataWorkSheet, reportDataTable);
}

Once I have "reloaded" the data into the spreedsheet.. I attempt to refresh the pivot tables that are dependent upon the "reloaded" data.. The following code..

private void RefreshAllData(Workbook workbook)
{
foreach (Aspose.Cells.Worksheet activeWorkSheet in workbook.Worksheets)
{
if (activeWorkSheet.PivotTables.Count > 0)
{
foreach (Aspose.Cells.Pivot.PivotTable pivotTable in activeWorkSheet.PivotTables)
{
pivotTable.RefreshData();
pivotTable.CalculateData();
}
}
}
}

Finally I save the updated file.

workbook.Save(saveFileName);

Everything appears to work as expected.. Great.. But when I go try to open the generated file.. The file now appears corupt and Excel repairs the pivot tables saying that they contain errors.

The only detailed message I get is the following.

Microsoft Office Excel File Repair Log

Errors were detected in file 'C:\Documents and Settings\o919265\Desktop\test.xls'
The following is a list of repairs:

Repairs were made to PivotTable report 'PivotTable1' on '[test.xls]Firm_Location_Pivot'.
Repairs were made to PivotTable report 'PivotTable2' on '[test.xls]Firm_Agent_Pivot'.
Repairs were made to PivotTable report 'PivotTable4' on '[test.xls]DQ_FirmLead_Pivot'.

I am using the latest version of aspose downloaded off the site this morning.

Please let me know if you need to examine the orignial spreadsheet but no errors exist before running the refresh / calculate methods. So I have to believe this is not working as expected.

Thanks for your support!! Benjamin Pirih

4

Also!! I can do the pivot refresh using the micrsoft.interop.execel without issue. using the following code..

This would indicate that an issue exists with the current aspose method and not the underlying spreadsheet pivot table formulas..

I cannot use this on our production server and is one of reason we purchased Aspose..

Thanks Again for your support!! Benjamin Pirih

public void RefreshAllData(string saveFileName)
{
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook ObjWorkBook;

ObjWorkBook = ObjExcel.Workbooks.Open(saveFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

ObjWorkBook.RefreshAll();

ObjWorkBook.Save();
ObjWorkBook.Close(false, saveFileName, null);

}

5

Hi,


Thank you for sharing your source code.
Yes, we need your sample input Excel file as well to re-produce this problem. Because we tried a similar but simple scenario on our end and it seems to work fine with latest fix version of Aspose.Cells for .NET v6.0.0.1[attached].
6

I sent you an email with our template… Thanks!

7

Hi Benjamin,


Thanks for the sample spreadsheets.
I have logged an investigation for your said issue in our Bug Tracking System under Ticket ID CELLSNET-28751 and attached your provided spreadsheet to this ticket. Soon we will update you in this regard.
8

Thank you for your support. Do you have a timeline on when this issue will be corrected? We have a project that is pending this? Thanks Again!!

9

Hi,


I am afraid, your issue is not sorted out yet. Once we have any update, we will update you here.

Thanks for being patient!
10
Hi,

Please try the attached version and use the following sample code:

foreach (Aspose.Cells.Pivot.PivotTable pivotTable in activeWorkSheet.PivotTables)
{
pivotTable.RefreshDataOnOpeningFile = true;
pivotTable.RefreshData();
//pivotTable.CalculateData();
}
If you still find the issue and it does not work as expected, please give your complete sample code which is related to: "ImportDataColumn(dataWorkSheet, reportDataTable);" with your sample file which should have the data in the worksheet and not blank to reproduce the issue on our end. Preferably you may create a sample console application, zip it and post it here for your issue.

thank you.
11

The issues you have found earlier (filed as 28751) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.