Printing Multi-Sheet Workbook Programmatically

FLX TMS FlexCel for VCL & FMX
1

Since Excel by default only prints the active worksheet, then using e.g. ShellExecute to print a multi-sheet file will only print that one active sheet. The solution that I have developed thus far essentially does this (in broad terms):


CurrentSheet := 1;
repeat  
  load up file into Txlsfile
  find next visible sheet starting at current sheet
  if a sheet was found then
     save file (so activesheet is now set to current sheet)
     print the file (using e.g. shellexecute)
     increment CurrentSheet
until no more visible sheets found.

(One  might be able to speed it up a little by saving the current sheet only to a new temporary file and printing that, but it's more or less the same).

Is there a better way?
2

Hi,
Is there a reason you are printing with Excel and not FlexCel?

You could print natively from FlexCel with FlexCelPrintDocument (see the demo TMSSoftware\FlexCelVCLNT\Demo\Delphi\Modules\25.Printing and Exporting\20.CustomPreview for an example on how it is done).

Basically, you can do:


var
  p: TFlexCelPrintDocument;
begin
  p := TFlexCelPrintDocument.Create(xls);
  try
    p.BeginPrint;
    p.PrintAllVisibleSheets(false);
    p.EndPrint;
  finally
    p.Free;
  end;
end;



If you need to do it with Excel and ShellExecute, I'll be honest: I am not really sure if there is a better solution. Maybe using OLE Automation instead of ShellExecute?

My OLE is a little rusty, but I've tried this code and it seems to print all sheets:


program Project34;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, ActiveX, ComObj;

procedure PrintAllSheets(const filename: string);
var
  App, Workbook : Variant;
begin
  App := CreateOleObject('Excel.Application');
  App.Visible := false;
  App.DisplayAlerts := false;
  try
    // Get a new workbook
    Workbook := App.Workbooks.Open(filename);
    Workbook.PrintOut();
  finally
    App.Quit;
  end;
end;

begin
  CoInitialize(nil);
  PrintAllSheets('r:\multisheet.xlsx');
  CoUninitialize;
end.



You can specify other parameters to printout too, like the printer where to print:
https://msdn.microsoft.com/en-us/library/office/ff196840(v=office.15).aspx

But I would use OLE only if for some reason you can't print directly from FlexCel (for example you have stuff that FlexCel can't print). FlexCel printing is going to be more reliable, and doesn't require the user to have Excel installed.

3

Thanks Adrian. I just wasn't aware of that functionality being available in Flexcel. That looks great. And I certainly don't want to go back to OLE automation, if I can possibly avoid it.