How to: Create a Spreadsheet Document by Providing a Filename

Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | Word 2010

In this article
Getting a SpreadsheetDocument Object
Basic Structure of a SpreadsheetML Document
Generating the SpreadsheetML Markup
Sample Code

This topic shows how to use the classes in the Open XML SDK 2.0 for Microsoft Office to programmatically create a spreadsheet document.

The following assembly directives are required to compile the code in this topic.

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Getting a SpreadsheetDocument Object

In the Open XML SDK, the SpreadsheetDocument class represents an Excel document package. To create an Excel document, create an instance of the SpreadsheetDocument class and populate it with parts. At a minimum, the document must have a workbook part that serves as a container for the document, and at least one worksheet part. The text is represented in the package as XML using SpreadsheetML markup.

To create the class instance, call the Create(Package, SpreadsheetDocumentType) method. Several Create methods are provided, each with a different signature. The sample code in this topic uses the Create method with a signature that requires two parameters. The first parameter, package, takes a full path string that represents the document that you want to create. The second parameter, type, is a member of the SpreadsheetDocumentType enumeration. This parameter represents the document type. For example, there are different members of the SpreadsheetDocumentType enumeration for add-ins, templates, workbooks, and macro-enabled templates and workbooks.

The following code example calls the Create method.

SpreadsheetDocument spreadsheetDocument = 
SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

Dim spreadsheetDocument As SpreadsheetDocument = _
SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)

When you have created the Excel document package, you can add parts to it. To add the workbook part you call the AddWorkbookPart() method of the SpreadsheetDocument class. A workbook part must have at least one worksheet. To add a worksheet, create a new Sheet. When you create a new Sheet, associate the Sheet with the Workbook by passing the Id, SheetId and Name parameters. Use the GetIdOfPart(OpenXmlPart) method to get the Id of the Sheet. Then add the new sheet to the Sheet collection by calling the Append([]) method of the Sheets class. The following code example creates a new worksheet, associates the worksheet, and appends the worksheet to the workbook.

Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
sheets.Append(sheet);

Dim sheet As New Sheet() With {.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), .SheetId = 1, .Name = "mySheet"}
sheets.Append(sheet)

Basic Structure of a SpreadsheetML Document

The following code example is the SpreadsheetML markup for the workbook that the sample code creates.

<x:workbook xmlns:r="https://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x="https://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:sheets>
    <x:sheet name="mySheet" sheetId="1" r:id="R47fd958b504b4526" />
  </x:sheets>
</x:workbook>

The basic document structure of a SpreadsheetML document consists of the Sheets and Sheet elements, which reference the worksheets in the workbook. A separate XML file is created for each worksheet. The worksheet XML files contain one or more block level elements such as SheetData. sheetData represents the cell table and contains one or more Row elements. A row contains one or more Cell elements. Each cell contains a CellValue element that represents the cell value. The following code example is the SpreadsheetML markup for the worksheet created by the sample code.

<x:worksheet xmlns:x="https://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:sheetData />
</x:worksheet>

Using the Open XML SDK 2.0, you can create document structure and content by using strongly-typed classes that correspond to SpreadsheetML elements. You can find these classes in the DocumentFormat.OpenXml.Spreadsheet namespace. The following table lists the class names of the classes that correspond to the workbook, sheets, sheet, worksheet, and sheetData elements.

Generating the SpreadsheetML Markup

To create the basic document structure using the Open XML SDK, instantiate the Workbook class, assign it to the WorkbookPart property of the main document part, and then add instances of the WorksheetPart, Worksheet, and Sheet classes. This is shown in the sample code and generates the required SpreadsheetML markup.

Sample Code

The CreateSpreadsheetWorkbook method shown here can be used to create a basic Excel document, a workbook with one sheet named "mySheet". To call it in your program, you can use the following code example that creates a file named "Sheet2.xlsx" in the public documents folder.

CreateSpreadsheetWorkbook(@"c:\Users\Public\Documents\Sheet2.xlsx")

CreateSpreadsheetWorkbook("c:\Users\Public\Documents\Sheet2.xlsx")

Notice that the file name extension, .xlsx, matches the type of file specified by the SpreadsheetDocumentType.Workbook parameter in the call to the Create method.

Following is the complete sample code in both C# and Visual Basic.

public static void CreateSpreadsheetWorkbook(string filepath)
{
    // Create a spreadsheet document by supplying the filepath.
    // By default, AutoSave = true, Editable = true, and Type = xlsx.
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
        Create(filepath, SpreadsheetDocumentType.Workbook);

    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());

    // Add Sheets to the Workbook.
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
        AppendChild<Sheets>(new Sheets());

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.
        GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
    sheets.Append(sheet);

    workbookpart.Workbook.Save();

    // Close the document.
    spreadsheetDocument.Close();
}

Public Sub CreateSpreadsheetWorkbook(ByVal filepath As String)
    ' Create a spreadsheet document by supplying the filepath.
    ' By default, AutoSave = true, Editable = true, and Type = xlsx.
    Dim spreadsheetDocument As SpreadsheetDocument = _
spreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)

    ' Add a WorkbookPart to the document.
    Dim workbookpart As WorkbookPart = spreadsheetDocument.AddWorkbookPart
    workbookpart.Workbook = New Workbook

    ' Add a WorksheetPart to the WorkbookPart.
    Dim worksheetPart As WorksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
    worksheetPart.Worksheet = New Worksheet(New SheetData())

    ' Add Sheets to the Workbook.
    Dim sheets As Sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())

    ' Append a new worksheet and associate it with the workbook.
    Dim sheet As Sheet = New Sheet
    sheet.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart)
    sheet.SheetId = 1
    sheet.Name = "mySheet"

    sheets.Append(sheet)

    workbookpart.Workbook.Save()

    ' Close the document.
    spreadsheetDocument.Close()
End Sub

See Also

Reference

Class Library Reference