I have a large number of Excel files being "reloaded" with data
programmatically using SQL Server 2000 DTS Packages which uses a server-side
installation of Excel to open the files with VBA and reload the data to
update pivot tables in the files.
Set appXL = CreateObject("Excel.Application")
appXL.Visible = False
I want to use Aspose.Cells on the server instead of Excel, but can I in some
way do this without having to create new Excel files from scratch, I just
want to use Aspose.Cells instead of the Excel application.
You can force Excel refresh the PivotCache when Opening the file with MS Excel. Please check "Refresh on open" option of the "PivotTable Options".Please check the wiki Creating a Simple PivotTable.
“Refresh on open” is unfortunately not an option for me as the file has to be loaded with data from the beginning, it will take to long to refresh the data every time a user opens the file.
We are reviewing Aspose.Cells and would like to know if this function has been added. The templates will be opened via Aspose server side and will need to refresh the pivottable data from and MSAS OLAP cube. They will not be opened in Excel by the end user, rather posted to a sharepoint site as a PDF.
Well, I am afraid there is no such feature available at the
moment. The component does not support to manipulate the Pivot Tables
in the template files. Aspose.Cells creates/formulates pivot data only
and it forces MS Excel to create a pivot table when opening the file
into MS Excel. It actually implements the refresh on open
option/setting for MS Excel, so, when you open the generated file(by
Aspose.Cells) into MS Excel, the pivot table is then created.
Just
to update you further, we are currently working on reading pivot tables
in template files, hopefully the feature would be completed in Q1 2010.