Deleting sheets from report/Excel file

FLX TMS FlexCel for VCL & FMX
1

Another question that bothers me:

How can I delete the last sheet programatically?
Say, I have to generate a report, and at a certain poiint, I
decide, that a sheet shall be deleted from the report/excel file.
I can't find the answer. Is it possible with using TFlexCelReport alone?
Or must I use TFlexCelImport after generating the Report, when yes, how is it done?

Thank you!

2

In the current report engine sadly this isn't possible. (in 5.0 there is a <#delete sheet> tag)


You can either save the report to a memorystream and open it with FlexCelImport, or use the event AfterGenerateWorkbook in FlexCelReport.

AfterGenerateWorkbook doesn't provide you a FlexCelImport component, it provides you a TExcelFile object instead, but it is mostly equivalent. (again, we made this much nicer in 5.0, I don't see the time to have it finished)

you should do something like:

procedure TForm16.FlexCelReport1AfterGenerateWorkbook(Sender: TObject;
  const ExcelApp: TExcelFile);
begin
  ExcelApp.DeleteSheet(1);
end;


3

Thank you,

but sadly I get an exception reading 'Can't delete a sheet on a file containing macros' with the AfterGenerateWorkbook
method..

4

There are no macros inside the whole template..

5

Sadly we can't reliabl delete sheets when there are macros, because they might get corrupt. 

On those cases, the only option is to set the sheet visibility to "VeryHidden", so it will still be there, but normal users won't see it.

Now, macros are detected very reliably. If it says there are macros, then there are. Sometimes excel keeps some remains of them, but they are still there. 
If the template was the one you send me earlier this morning, then it had one macro: Sub Zählen()
If it is another, here are some ideas to remove unneeded macros:

1) First of all, try opening the file in Excel, pressing Alt-F11, and removing anything there that you can find.
 2) If it still complains, you might try installing the preview of FlexCel 5, and running the following code:
program Project17;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  VCL.FlexCel.Core,
  FlexCel.XlsAdapter;

var
  Xls: TExcelFile;
begin
  Xls := TXlsFile.Create('r:\GGJBericht_Auflistung_Template.xls', true);
  Xls.RemoveMacros;
  Xls.Save('r:\newtest.xls');
end.

I tried this with the file you sent me and it worked fine.

3)If you prefer not to install the 5 preview, but you have visual studio and don't matter installing FlexCel .net, there is a tool there "Remove macros" that basically does the same as the code above.

4) If none of the options work, again, you can send me the file and I can send you the file without macros.


6

Thank you, yes you're right now everything works!

With regards,
greetings from Germany