Converting Excel Spreadsheets to PDF

////////////////////////////////////////////////////////////////////////////////////////////////////
// This example was designed for using in Delphi 7 or higher.
//
// 1. Microsoft Excel 97 or above should be installed and activated on your PC.
//
// 2. Universal Document Converter 5.2 or above should be installed, too.
//
// 3. Before using this example, please read this article from Microsoft Excel 2003 knowledge base:
//    http://support.microsoft.com/kb/320369/en-us/
//    A workaround for this issue is available in this example.
//
// 4. Add "Universal Document Converter Type Library" and "Microsoft Excel XX.0 Object Library" type libraries to the project.
//    XX is the Microsoft Office version installed on your computer.
//
//    Delphi 7:
//      Use the Project | Import Type Library menu.
//    Delphi 2006 or latter:
//      Use the Component | Import Component menu.
//
//    Clear the "Generate Component Wrapper" checkbox and click the "Create Unit" button (Delphi 7) or
//    select the "Create Unit" option (Delphi 2006 or latter).
//
// 5. Notice that the number of Microsoft Excel's method parameters may depend on the Office version.
//
////////////////////////////////////////////////////////////////////////////////////////////////////

program ExcelToPDF;

{$APPTYPE CONSOLE}

{$DEFINE LATE_BINDING}

uses
  SysUtils,
  Variants,
  Windows,  
  Dialogs,
  ActiveX,
  UDC_TLB,
  Excel_TLB;

  procedure PrintExcelToPDF(ExcelFilePath: string);
  var
    objUDC: IUDC;
    Printer: IUDCPrinter;
    Profile: IProfile;
    ExcelApp: ExcelApplication;
    Workbook: ExcelWorkbook;
    Worksheet: ExcelWorksheet;
    PageSetup: Excel_TLB.PageSetup;
    ReadOnly: OleVariant;
    Missing: OleVariant;
    Preview: OleVariant;
    SaveChanges: OleVariant;
    {$IFNDEF LATE_BINDING}
    LocaleID: Integer;
    {$ENDIF}
  begin
    //Create a UDC object and get its interfaces
    objUDC := CoAPIWrapper.Create;
    Printer := objUDC.get_Printers('Universal Document Converter');
    Profile := Printer.Profile;

    //Use Universal Document Converter API to change settings of converterd document
    Profile.PageSetup.ResolutionX := 150;
    Profile.PageSetup.ResolutionY := 150;

    Profile.FileFormat.ActualFormat := FMT_PDF;

    Profile.FileFormat.PDF.ColorSpace := CS_TRUECOLOR;
    Profile.FileFormat.PDF.Multipage := MM_MULTI;

    Profile.OutputLocation.Mode := LM_PREDEFINED;
    Profile.OutputLocation.FolderPath := 'c:\UDC Output Files';
    Profile.OutputLocation.FileName := '&[DocName(0)] -- &[Date(0)] -- &[Time(0)].&[ImageType]';
    Profile.OutputLocation.OverwriteExistingFile := False;

    Profile.PostProcessing.Mode := PP_OPEN_FOLDER;

    //Create a Excel's Application object
    ExcelApp := CoExcelApplication.Create;

    ReadOnly := True;
    Missing := Variants.EmptyParam; //This will be passed when ever we don’t want to pass value

    //If you run an English version of Excel on a computer with the regional settings are configured for a non-English language, you must pass the correct locale ID.
    //Open the document from a file
    {$IFDEF LATE_BINDING}
    Workbook := IDispatch(OleVariant(ExcelApp).Workbooks.Open(FileName := ExcelFilePath, ReadOnly := ReadOnly)) as ExcelWorkbook;
    {$ELSE}
    LocaleID := $0409; //set en-US locale or call GetThreadLocale
    Workbook := ExcelApp.Workbooks.Open(ExcelFilePath, Missing, ReadOnly, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, LocaleID);
    {$ENDIF}

    //Change active worksheet settings and print it
    Worksheet := Workbook.ActiveSheet as ExcelWorksheet;
    PageSetup := Worksheet.PageSetup;

    PageSetup.Orientation := xlLandscape;

    Preview := False;
    {$IFDEF LATE_BINDING}
    OleVariant(Worksheet).PrintOut(Preview := Preview, ActivePrinter := 'Universal Document Converter');
    {$ELSE}
    Worksheet.PrintOut(Missing, Missing, Missing, Preview, 'Universal Document Converter', Missing, Missing, Missing, LocaleID);
    {$ENDIF}

    //Close the spreadsheet without saving changes
    SaveChanges := False;
    {$IFDEF LATE_BINDING}
    OleVariant(Workbook).Close(SaveChanges := SaveChanges);
    {$ELSE}
    Workbook.Close(SaveChanges, Missing, Missing, LocaleID);
    {$ENDIF}

    //Close Microsoft Excel
    ExcelApp.Quit();
  end;

var
  TestFilePath: string;
begin
  TestFilePath := ExtractFilePath(ParamStr(0)) + 'TestFile.xls';
  try
    CoInitialize(nil);
    try
      PrintExcelToPDF(TestFilePath);
    finally
      CoUninitialize;
    end;
  except
    on E: Exception do
      MessageDlg(E.ClassName + ' : ' + E.Message, mtError, [mbOK], 0);
  end;
end.