Table Formatting and Pivot Tables

FLX TMS FlexCel for VCL & FMX
1

I choose "Format as table" in Excel for a range of data to use it with a Pivot Table.

In a DELPHI APP I load this file with "Xls.Open('C:\temp\test.xlsx')",  then save it  with "xls.Save('C:\temp\test2.xlsx')"  under a different filename.

It seems to me that the table definition is not saved in the new file, but gets lost with "xls.Save().

Is there a way to preserve such a table definition as the Pivot Table depends on it.

Thanks
Stefan

2

Hi,

Sadly Tables aren't supported yet, they will not be saved. It is in our short term list to add support for them, but it isn't trivial. Tables was a feature introduced in Excel 2007 (only available in xlsx), and the engine didn't had support for them as it had for pivot tables (which exist in xls too). We need to do a lot of plumbing to add proper support (and to support stuff like when you insert a row the table gets bigger, or the new formulas in tables like "=SUBTOTAL(103,[Column4])". The formula parser currently understands the new syntax [column4] but it can't calculate it) 
3

Hi Adrian,

thank you for your quick help.

Is there any way to copy newly inserted data in a existing excel file and refresh a PivotTable accordingly.

I try a lot with code like this:

  Xls := TXlsFile.Create(true);
  Xls.Open('C:\temp\test.xlsx');
  si := xls.GetSheetIndex('sheet2');
  xls.activesheet := si;
  xls.InsertAndCopyRange(TXlsCellRange.Create(1, 1, 5, 1), 15, 1, 1, TFlxInsertMode.ShiftRangeDown, TRangeCopyMode.All);
  xls.Save('C:\temp\test2.xlsx');

The Data is copied properly, but the PivotTable is only refreshed partially.

Thanks again
Stefan

4

FlexCel doesn't refresh pivot tables, but you can set the pivot table to autorefresh when you open it in Excel.


I'll paste some docs from the pivot table demo (not yet available in Delphi, only in .NET, because delphi doesn't have reports yet):

Xls Templates: Excel saves a cache data for opening the pivot faster next time, and you can't use this on FlexCel because it is not implemented (FlexCel does not calculate the data on the pivot table). So, on the template, right-click the table, select "Don't save on open”, and deselect "Update on open". Note: FlexCel will make this automatically in xlsx templates, so you don't need to do it.

⦁ Excel by default does not delete old entries in the comboboxes inside the pivot table, no matter how much you refresh the pivot table. This means that you will see “...delete row...” inside those comboboxes, since those entries were there when you created the template and they won’t be deleted. You could get rid of them with a macro, but adding a macro to a sheet will add a security warning, so probably it is not worth. But there is other undocumented way: If you can get a copy of Excel 2007 to edit the template, you can set the “Pivot Table options”, “Data” tab, “Number of items to retain per field” and set it to “none”. The good thing about it is that once you change it, it will work also in Excel 2003, even when there is no way to change that option from Excel 2003 user interface.