RefreshData results in removal of pivot tables on workbook open

Aspose.Cells Product Family
1

Hi,

I’ve been attempting to write an automated Excel report for a customer, using Aspose.Cells but have had some issues with the pivot tables in the report. The program (built in Visual Basic) pastes data sets into worksheets, updates named ranges to include the newly inserted rows, and then refreshes the pivot tables throughout the workbook that use the data. However, if I call the PivotTable’s RefreshData method, Excel encounters unreadable content upon opening the workbook and removes all of the pivot tables. Neglecting to use the RefreshData method results in a beautifully functioning report, but the pivots must be updated by hand (which unfortunately is not acceptable to my company’s customers).

Thanks for any and all help,
Oren

2

Hi,

Kindly provide us your sample Visual Basic project replicating the problem. We will investigate the issue and update you asap.

Please also try our latest version: Aspose.Cells
for .NET v6.0.1.3 and provide us your feedback.

3

Alright, sorry for the long response; I’m working on this stuff outside of my normal responsibilities to help out another department. So, if the Excel or VB looks fishy, it’s probably due to the fact that I’m not that familiar with it. After developing this simple solution, I did not see the same exception that I was encountering in the customer’s report code. I believe this might be the difference int he Excel templates as the VB code generating the reports (or at least my trimmed down, debugging only version) is nearly identical to this example. I should point out that we have to provide these reports in Excel 2000 format due to our customer’s needs and the complexity of the original template file. If the problem is not being caused by wacky Excel in our template, I’d guess it’s because we’re using Excel 2000. But, I’ll leave that for you guys to decide.

Thanks for all the help,
Oren

4

Hi,

We could not run the sample project because of the sql database.

Please post your sample file with the source data of the pivottable in the workbook.

5

I believe sending a workbook with the data already in the file wouldn’t present the problem accurately. Our basic algorithm is as follows:

1. Query the database to find appropriate data for the report.
2. Import the data into the appropriate data Worksheet.
3. Recreate the named range for that Worksheet to include the newly imported rows.
4. Refresh pivot tables whose source is the newly updated named range.

By following this algorithm, the Aspose.Cells RefreshData() call fails on an IndexOutOfRange exception. If I switch our template to include all possible Excel rows for the spreadsheet and skip step 3, I get the pivot table behavior mentioned in the original post. Is there any way I could help with the SQL database setup? The backup I sent in the zip is of a very small database used to illustrate the behavior. All that should be required is a SQL Express install and a restoration from that .bak file. If this is not possible, I’ll try to find another accurate test that doesn’t conflict with our data privacy policies.

Oren

6

Hi,

Thanks for your feedback.

If you could remove your extra code and simplify it to replicate the problem, than it would be better. There should not be any database dependency while running the code.

However, I have logged this issue and we will investigate the problem and update you asap.

This issue has been logged as CELLSNET-29690.

7

Alright, I’ve modified the code to import rows from an array of strings but the same behavior is not encountered. I still can’t get the pivot table to refresh but there’s no complaint about indices after updating the named range.

8

Hi,

Thanks for your feedback and the files.

I have added your comment and forwarded your files. We will update you asap.

9

Hi,

Please use this sample code to refresh file data on opening:

table.RefreshDataOnOpeningFile = True

10

Hi,

Is there any way around using this approach within the Aspose.Cells functionality? I’ve already tried taking this path but my project manager says it’s impractical as some reports have 100+ pivot tables. Thank you for the help!

Oren

11
Hi,

We can give a property to Refresh All pivottables in MS Excel2007 but unfortunately there is no such flag/attribute in MS Excel2003, so, if you use MS Excel2003, please write a loop in your code to set RefreshDataOnOpeningFile attribute accordingly.

Thanks for your understanding!