How to: Create a Spreadsheet Document by Providing a Filename
Last modified: October 14, 2010
Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | Word 2010
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.
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.
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.
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.
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.
Open XML SDK 2.0 Class
The root element for the main document part.
The container for the block-level structures such as sheet, fileVersion, and others specified in the ISO/IEC 29500 specification.
A sheet that points to a sheet definition file.
A sheet definition file that contains the sheet data.
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.
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 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