Refresh Pivot Table with Apache POI

Question

I'm currently working on a Java application that uses a template excel file that contains a pivot table.

The template file also has a data sheet that seeds the pivot table. This data sheet is dynamically loaded in the java application through the Apache POI api.

When I open the excel file I must refresh the Pivot table manually to get the data loaded correctly.

Is there any way to refresh the Pivot table with the POI api so I don't have to manually do it?

Answer 1

The basic answer to this is no. POI is a document format reader and writer. Updating the Pivot table is an excel engine issue. Sure, another application could try to duplicate the Excel engine behavior here, but that is really going to get ugly. I recommend using Joel's workaround of accessing the excel COM objects over a webservice to get this kind of thing done.

Answer 2

This post says you can turn on an option on the pivot table that will cause it to refresh itself automatically every time the workbook is opened:

http://stackoverflow.com/questions/70947/how-can-i-refresh-all-the-pivot-tables-in-my-excel-workbook-with-a-macro

Hopefully this will still be true after you have used Apache POI to refresh or extend the data rows on which the pivot tables are based.