Excel> PDF

 

Download and install saveaspdf.exe

This section describes how to save an Excel 2007 workbook programmatically in the PDF and XPS file formats. There are five steps to this procedure:

1. Adding a reference to the Excel 12.0 Object Library.

2. ImportingExcel InteropAssembly namespace.
3. Creating an instance ofApplicationClassObject.
4. Declaring the appropriate variables.
5. Implementing the conversion code.

1. Adding a Reference to the Excel 12.0 Object Library

Begin by adding a reference to the Microsoft Excel 12.0 Object Library to the Visual Studio project.

To add a reference to the Excel 12.0 Object Library

1. Right-click the project inVisual Studio Solution Explorer.

2. SelectAdd Reference....

3. SelectCOMTab inAdd ReferenceDialog box.

4. Scroll down to the Microsoft Excel 12.0Object LibraryComponent and select it.

5. ClickOKTo add the reference.

Figure 1. Adding a Reference

2. Importing the Microsoft. Office. Interop. Excel Namespace

The next step is to import the Microsoft. Office. Interop. Excel namespace to the project.

To import the Microsoft. Office. Interop. Excel Assembly Namespace

1. For Visual Basic projects, right-click the project in the Visual Studio Solution Explorer and selectPropertiesMenu item.

2. On the Visual Basic Properties Page Dialog Box, selectReferencesTab.

3. Select the check box control next toMicrosoft. Office. Interop. ExcelEntry in the list of imported namespaces.

4. Close the Visual Basic Properties Page dialog box.

Figure 2. Import Namespace in Visual Basic

For Microsoft Visual C # projects add the following line to the top of the source file:

VBC # C ++ F # JScriptCopy

using Microsoft.Office.Interop.Excel;

3. Creating an Instance of the ApplicationClass Object

To work with the Excel object model create an instance of the top-levelApplicationClassObject and declare a variable to hold the reference to the workbook.

VBC # C ++ F # JScriptCopy

ApplicationClass excelApplication = new ApplicationClass();Workbook excelWorkBook = null;

4. Declaring the Appropriate Variables

You must declare variables for parameters that are passed to methods used in the conversion code. This modification makes the conversion code easier to read.

The variables are used withWorkbooks.OpenMethod.

TheparamSourceBookPathVariable specifies the path and filename of the Excel workbook to export as PDF or XPS.

You can useparamMissingVariable to call methods that accept optional parameters. Optional parameters are only optional when using Microsoft Visual Basic. You must specify a value for optional parameters when using C #. UsingType. MissingAs the value for an optional parameter signals to the method being called that the parameter is not being specified and that the method shocould use the parameter's default value.

VBC # C ++ F # JScriptCopy

string paramSourceBookPath = @"C:\Temp\Test.xlsx";object paramMissing = Type.Missing;

Use the following variables withWorkbook. ExportAsFixedFormatMethod.paramExportFormatVariable is important because it specifies the exported workbook's format.paramExportFormatVariable is of typeXlFixedFormatType, An enumerated type that has two values,XlTypePDFAndXlTypeXPS. The sample code shown setsparamExportFormatVariable toXlFixedFormatType.xlTypePDFValue to export a workbook to the PDF format.

To change the code to export a workbook in the XPS format the variable must be set toXlFixedFormatType.xlTypeXPSValue. For more information aboutExportAsFixedFormatMethod and the parameters it accepts, seeWorkbook. ExportAsFixedFormat Method.

VBC # C ++ F # JScriptCopy

string paramExportFilePath = @"C:\Temp\Test.pdf";XlFixedFormatType paramExportFormat = XlFixedFormatType.xlTypePDF;XlFixedFormatQuality paramExportQuality =     XlFixedFormatQuality.xlQualityStandard;bool paramOpenAfterPublish = false;bool paramIncludeDocProps = true;bool paramIgnorePrintAreas = true;object paramFromPage = Type.Missing;object paramToPage = Type.Missing;

5. Implementing the Conversion Code

The final step is to implement the conversion code.

To implement the conversion code

1. Add code to open the source workbook.

2. Export it to the specified format.

3. Quit Excel.

It is critical to have the Save as PDF add-in installed. If it is not, the call toWorkbook. ExportAsFixedFormatMethod generates an exception. To handle the exception, the conversion code is wrapped inTry…CatchBlock. code located in a finally block does two things: closes the Excel workbook and application objects, and releases references to the underlying Excel COM objects, allowing Excel to unload from memory. for more information about releasing COM objects when using managed code see Chapter 2: Basics of Office Interoperability (Part 2 of 3) from the book Microsoft. NET Development for Microsoft Office.

VBC # C ++ F # JScriptCopy

try{    // Open the source workbook.    excelWorkBook = excelApplication.Workbooks.Open(paramSourceBookPath,        paramMissing, paramMissing, paramMissing, paramMissing,        paramMissing, paramMissing, paramMissing, paramMissing,         paramMissing, paramMissing, paramMissing, paramMissing,         paramMissing, paramMissing);    // Save it in the target format.    if (excelWorkBook != null)        excelWorkBook.ExportAsFixedFormat(paramExportFormat,            paramExportFilePath, paramExportQuality,            paramIncludeDocProps, paramIgnorePrintAreas, paramFromPage,             paramToPage, paramOpenAfterPublish,             paramMissing);}catch (Exception ex){    // Respond to the error.}finally{    // Close the workbook object.    if (excelWorkBook != null)    {        excelWorkBook.Close(false, paramMissing, paramMissing);        excelWorkBook = null;    }    // Quit Excel and release the ApplicationClass object.    if (excelApplication != null)    {        excelApplication.Quit();        excelApplication = null;    }    GC.Collect();    GC.WaitForPendingFinalizers();    GC.Collect();    GC.WaitForPendingFinalizers();}