Export (0) Print
Expand All

How to: Create a Spreadsheet Document by Providing a Filename

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;

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.

NoteNote

Select the appropriate SpreadsheetDocumentType and ensure that the persisted file has the correct, matching file name extension. If the SpreadsheetDocumentType does not match the file name extension, an error occurs when you open the file in Excel.

The following code example calls the Create method.

SpreadsheetDocument 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);

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

<x:workbook xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:x="http://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="http://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.

SpreadsheetML Element

Open XML SDK 2.0 Class

Description

workbook

DocumentFormat.OpenXml.Spreadsheet.Workbook

The root element for the main document part.

sheets

DocumentFormat.OpenXml.Spreadsheet.Sheets

The container for the block-level structures such as sheet, fileVersion, and others specified in the ISO/IEC 29500 specification.

sheet

DocumentFormat.OpenXml.Spreadsheet.Sheet

A sheet that points to a sheet definition file.

worksheet

DocumentFormat.OpenXml.Spreadsheet.Worksheet

A sheet definition file that contains the sheet data.

sheetData

DocumentFormat.OpenXml.Spreadsheet.SheetData

The cell table, grouped together by rows.

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.

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”)

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

Community Additions

ADD
Show:
© 2014 Microsoft