PivotTable not kept

FLX TMS FlexCel for VCL & FMX
1
Hello,

I have an Excel sheet with a pivot table.

With Excel, when I copy the sheet to another sheet, the pivot table is retained.

With Flexcel (InsertAndCopySheets), the PivotTable is not preserved.

How can I keep the pivot tables set up in the sheet?

Excel sample file (test.xlsx) is available at:
http://dl.free.fr/getfile.pl?file=/TWSFr8QW

Here is an example:


Uses FlexCel.XlsAdapter, VCL.FlexCel.Core;

procedure TForm1.Button1Click(Sender: TObject);
var
  ExcelFile: TExcelFile;
begin
  ExcelFile:= TXlsFile.Create(true);
  try
    if FileOpenDialog1.Execute then
    begin
      ExcelFile.Open(FileOpenDialog1.FileName);
      ExcelFile.InsertAndCopySheets(1, ExcelFile.SheetCount+1, 1);
      ExcelFile.Save(ExtractFilePath(FileOpenDialog1.FileName)
                     + ExtractFileName(FileOpenDialog1.FileName)
                     + '_Save'
                     + ExtractFileExt(FileOpenDialog1.FileName));
    end;
  finally
    ExcelFile.Free;
  end;
end;
2

Hi,

Indeed FlexCel isn't currently copying the pivot tables across sheets in xlsx. This is a feature that we can add, but I wonder how useful it really would be. The reason it isn't implemented yet is indeed because the feature as implemented by Excel is kind of useless (And we would have to implement it the same way Excel does)

Let me explain: In your example, you have a data range for the pivot table of:
'F2'!$A$1000:$J$12298

Now, if you copy this page in Excel, you might expect the new range to be:
'F2 (copy)'!$A$1000:$J$12298
but it will still be
'F2'!$A$1000:$J$12298

So the new pivot table in the new sheet will point to the old data. And we have to implement it the same way, because we need to do the same Excel does. 

I've done some tests here and I think it won't be complicated to implement, but I should ask again: Would this have any use?  Note that sadly we can't support for the time being changing the pivot table datasource from the api, so this means all pivot tables will point to the same data.
3
Thank you for your explanation, I'll see if the behavior is annoying FLEXCEL.

Indeed the source data does not change, it is not useful but I was expecting to have the same Excel.

This can be interpreted as a bug.
4

For what it is worth we have implemented this now the same as Excel. So the pivot table will be copied, but the data source will remain the same. (same as in Excel)


Next version will have this feature, if you need it urgently email me at adrian@tmssoftware.com and I'll send you a fixed version.

Regards,
   Adrian.