Saving Workbooks to PDF and XPS Formats in Excel 2007

  • Article

Summary: This Visual How-To article illustrates how to use the Microsoft Excel 12.0 Object Library to access the Workbook.ExportAsFixedFormat method to convert an existing Excel workbook to the PDF or XPS format programmatically.

Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007

Joel Krist, Akona Systems

April 2007

Overview

The 2007 Microsoft Office system introduces multiple changes to the way developers can work with Excel 2007. Specifically, Microsoft Office Excel 2007 has support for the Microsoft Save as PDF Add-in for the 2007 Office release program, which allows an Excel workbook to export and save to the PDF format and XPS format. This is very useful for building Office business applications in which the data input and analysis is dynamic, but the application output is static. For example, a mortgage calculator application may require input and analysis using Excel's core features, but the final workbook is a PDF or XPS document.

 See It Thumbnail image of video file

Watch the Video

Length: 00:07:53| Size: 7.85 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the Code Sample

This section illustrates 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. Importing the Excel Interop assembly namespace.

  3. Creating an instance of the ApplicationClass object.

  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 in the Visual Studio Solution Explorer.

  2. Select Add Reference….

  3. Select the COM tab in the Add Reference dialog box.

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

  5. Click OK to add the reference.

Figure 1. Adding a Reference

Add Reference Dialog Box

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 select the Properties menu item.

  2. On the Visual Basic Properties Page Dialog Box, select the References tab.

  3. Select the check box control next to the Microsoft.Office.Interop.Excel entry in the list of imported namespaces.

  4. Close the Visual Basic Properties Page dialog box.

Figure 2. Import Namespace in Visual Basic

Importing Namespace

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

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-level ApplicationClass object and declare a variable to hold the reference to the workbook.

Dim excelApplication As ApplicationClass = New ApplicationClass()
Dim excelWorkbook As Workbook = Nothing

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 with the Workbooks.Open method.

The paramSourceBookPath variable specifies the path and filename of the Excel workbook to export as PDF or XPS.

You can use the paramMissing variable 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#. Using Type.Missing as the value for an optional parameter signals to the method being called that the parameter is not being specified and that the method should use the parameter's default value.

Dim paramSourceBookPath As String = "C:\Temp\Test.xlsx"

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

Use the following variables with the Workbook.ExportAsFixedFormat method. The paramExportFormat variable is important because it specifies the exported workbook's format. The paramExportFormat variable is of type XlFixedFormatType, an enumerated type that has two values, xlTypePDF and xlTypeXPS. The sample code shown sets the paramExportFormat variable to the XlFixedFormatType.xlTypePDF value 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 to the XlFixedFormatType.xlTypeXPS value. For more information about the ExportAsFixedFormat method and the parameters it accepts, see Workbook.ExportAsFixedFormat Method.

Dim paramExportFilePath As String = "C:\Temp\Test.pdf"
Dim paramExportFormat As XlFixedFormatType = _
    XlFixedFormatType.xlTypePDF
Dim paramExportQuality As XlFixedFormatQuality = _
    XlFixedFormatQuality.xlQualityStandard
Dim paramOpenAfterPublish As Boolean = False
Dim paramIncludeDocProps As Boolean = True
Dim paramIgnorePrintAreas As Boolean = True
Dim paramFromPage As Object = Type.Missing
Dim paramToPage As Object = Type.Missing

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 to the Workbook.ExportAsFixedFormat method generates an exception. To handle the exception, the conversion code is wrapped in a Try…Catch block. 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.

Try
    ' Open the source workbook.
    excelWorkbook = excelApplication.Workbooks.Open(paramSourceBookPath)

    ' Save it in the target format.
    If Not excelWorkbook Is Nothing Then
        excelWorkbook.ExportAsFixedFormat(paramExportFormat, _
            paramExportFilePath, paramExportQuality, _
            paramIncludeDocProps, paramIgnorePrintAreas, _
            paramFromPage, paramToPage, paramOpenAfterPublish)
    End If
 Catch ex As Exception
    ' Respond to the error.
Finally
    ' Close the workbook object.
    If Not excelWorkbook Is Nothing Then
        excelWorkbook.Close(False)
        excelWorkbook = Nothing
    End If

    ' Quit Excel and release the ApplicationClass object.
    If Not excelApplication Is Nothing Then
        excelApplication.Quit()
        excelApplication = Nothing
    End If

    GC.Collect()
    GC.WaitForPendingFinalizers()
    GC.Collect()
    GC.WaitForPendingFinalizers()
End Try

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();
}

Read It

There are a number of scenarios that require both dynamic and static views of the same spreadsheet where a developer may want to develop an Office business application. With the 2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS, you have access to key capabilities to save documents to both the PDF and XPS formats.

The key object that is used to save an Excel document in either format is the Workbook object. The Workbook object has a method called Workbook.ExportAsFixedFormat, which has a number of key parameters that it accepts to save the target workbook in the desired format. This article specifically explores how to use the Excel Workbooks.Open and Workbook.ExportAsFixedFormat methods to save an Excel 2007 workbook as a PDF or XPS document programmatically. These steps include:

  1. Adding a reference to the Excel 12.0 Object Library to the project. This sets the project to use of the Excel 12.0 Object Library and is the first step to building the Visual Studio project.

  2. Importing the Microsoft.Office.Interop.Excel namespace. This allows the code written to use the classes and types exposed as part of the Microsoft.Office.Interop.Excel namespace. This enables you to query the Excel object model and find the Workbook object and associated methods.

  3. Creating an instance of the Excel ApplicationClass Object. This is the top-most class in the Excel object model hierarchy and is the starting point for working with the other classes in the object model.

  4. Declaring variables to help with method calls. Although this is an optional step, declaring variables earlier helps with the actual execution code. Specifically, the parameters passed to methods used in the execution code are easier to read.

  5. Implementing the conversion code. This step shows code that you can use to open an existing workbook and save it as a PDF or XPS document. Note that there are many different parameters that are optional in this method call.

NoteNote

The example code shown in previously used the Type.Missing object to specify that an optional parameter was not provided and to use the default parameter value. To change the behavior to use something other than the defaults, specify the appropriate parameter types and values. For more information about the Excel Workbooks.Open and Workbook.ExportAsFixedFormat methods and the parameters they accept, see the Excel Object Model Reference.

Explore It