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

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

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

C#

using Microsoft.Office.Interop.Excel;

3. Creating an Instance of the ApplicationClass Object

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

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

4. Declaring the Appropriate Variables

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

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

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

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

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.

Note:

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.

• 2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS

• Excel Developer Portal

• Excel 2007 Resource Center

• Office Developer Center

• Understanding Optional Parameters in COM Interop on MSDN

• Blog: Microsoft Excel 2007

• Excel Workbook.ExportAsFixedFormat documentation on MSDN