Edit

Create a spreadsheet document by providing a file name

  • Article

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

Creating 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.

Note

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.

// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
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.

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

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.

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. The following code example creates a new worksheet, associates the worksheet, and appends the worksheet to the 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 = workbookPart.Workbook.AppendChild(new Sheets());

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

Sample Code

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

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

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 = workbookPart.Workbook.AppendChild(new Sheets());

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

    workbookPart.Workbook.Save();

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

See also