How to: Insert a New Worksheet into a Spreadsheet Document

Article
07/30/2014

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

In this article
Getting a SpreadsheetDocument Object
Basic Structure of a SpreadsheetML Document
How the Sample Code Works
Sample Code

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

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

using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

Imports System.Linq
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 open and work with an Excel document, you create an instance of the SpreadsheetDocument class from the document. After you create the instance from the document, you can then obtain access to the main workbook part that contains the worksheets. The text in the document is represented in the package as XML using SpreadsheetML markup.

To create the class instance from the document that you call one of the Open() methods. Several are provided, each with a different signature. The sample code in this topic uses the Open(String, Boolean) method with a signature that requires two parameters. The first parameter takes a full path string that represents the document that you want to open. The second parameter is either true or false and represents whether you want the file to be opened for editing. Any changes that you make to the document will not be saved if this parameter is false.

The code that calls the Open method is shown in the following using statement.

// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true)) 
{
    // Insert other code here.
}

' Open the document for editing.
Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
Using (spreadSheet)
    ' Insert other code here.
End Using

The using statement provides a recommended alternative to the typical .Open, .Save, .Close sequence. It ensures that the Dispose method (internal method used by the Open XML SDK to clean up resources) is automatically called when the closing brace is reached. The block that follows the using statement establishes a scope for the object that is created or named in the using statement, in this case spreadSheet.

Basic Structure of a SpreadsheetML Document

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. For example, the SpreadsheetML for a workbook that has two worksheets name MySheet1 and MySheet2 is located in the Workbook.xml file and is shown in the following code example.

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
<workbook xmlns=https://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r="https://schemas.openxmlformats.org/officeDocument/2006/relationships">
    <sheets>
        <sheet name="MySheet1" sheetId="1" r:id="rId1" /> 
        <sheet name="MySheet2" sheetId="2" r:id="rId2" /> 
    </sheets>
</workbook>

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 value of the cell. For example, the SpreadsheetML for the first worksheet in a workbook, that only has the value 100 in cell A1, is located in the Sheet1.xml file and is shown in the following code example.

<?xml version="1.0" encoding="UTF-8" ?> 
<worksheet xmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <sheetData>
        <row r="1">
            <c r="A1">
                <v>100</v> 
            </c>
        </row>
    </sheetData>
</worksheet>

Using the Open XML SDK 2.0, you can create document structure and content that uses 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.
row	DocumentFormat.OpenXml.Spreadsheet.Row	A row in the cell table.
c	DocumentFormat.OpenXml.Spreadsheet.Cell	A cell in a row.
v	DocumentFormat.OpenXml.Spreadsheet.CellValue	The value of a cell.

How the Sample Code Works

After opening the document for editing as a SpreadsheetDocument document package, the code adds a new WorksheetPart object to the WorkbookPart object using the AddNewPart method. It then adds a new Worksheet object to the WorksheetPart object.

// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = 
    spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());

Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
string relationshipId = 
    spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart);

' Add a blank WorksheetPart.
Dim newWorksheetPart As WorksheetPart = spreadSheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
newWorksheetPart.Worksheet = New Worksheet(New SheetData())
' newWorksheetPart.Worksheet.Save()

Dim sheets As Sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()
Dim relationshipId As String = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart)

The code then gets a unique ID for the new worksheet by selecting the maximum SheetId object used within the spreadsheet document and adding one to create the new sheet ID. It gives the worksheet a name by concatenating the word "Sheet" with the sheet ID and appends the new sheet to the sheets collection.

// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
    sheetId = 
        sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}

// Give the new worksheet a name.
string sheetName = "Sheet" + sheetId;

// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() 
{ Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);

' Get a unique ID for the new worksheet.
Dim sheetId As UInteger = 1
If (sheets.Elements(Of Sheet).Count > 0) Then
    sheetId = sheets.Elements(Of Sheet).Select(Function(s) s.SheetId.Value).Max + 1
End If

' Give the new worksheet a name.
Dim sheetName As String = ("Sheet" + sheetId.ToString())

' Append the new worksheet and associate it with the workbook.
Dim sheet As Sheet = New Sheet
sheet.Id = relationshipId
sheet.SheetId = sheetId
sheet.Name = sheetName
sheets.Append(sheet)

Sample Code

In the following code, insert a blank Worksheet object by adding a blank WorksheetPart object, generating a unique ID for the WorksheetPart object, and registering the WorksheetPart object in the WorkbookPart object contained in a SpreadsheetDocument document package. To call the method InsertWorksheet, you can use the following code, which inserts a worksheet in a file names "Sheet7.xslx," as an example.

string docName = @"C:\Users\Public\Documents\Sheet7.xlsx";
InsertWorksheet(docName);

Dim docName As String = "C:\Users\Public\Documents\Sheet7.xlsx"
InsertWorksheet(docName)

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

// Given a document name, inserts a new worksheet.
public static void InsertWorksheet(string docName)
{
    // Open the document for editing.
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
    {
        // Add a blank WorksheetPart.
        WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
        newWorksheetPart.Worksheet = new Worksheet(new SheetData());

        Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
        string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart);

        // Get a unique ID for the new worksheet.
        uint sheetId = 1;
        if (sheets.Elements<Sheet>().Count() > 0)
        {
            sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
        }

        // Give the new worksheet a name.
        string sheetName = "Sheet" + sheetId;

        // Append the new worksheet and associate it with the workbook.
        Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
        sheets.Append(sheet);
    }
}

' Given a document name, inserts a new worksheet.
Public Sub InsertWorksheet(ByVal docName As String)
    ' Open the document for editing.
    Dim spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)

    Using (spreadSheet)
        ' Add a blank WorksheetPart.
        Dim newWorksheetPart As WorksheetPart = spreadSheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
        newWorksheetPart.Worksheet = New Worksheet(New SheetData())
        ' newWorksheetPart.Worksheet.Save()

        Dim sheets As Sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)()
        Dim relationshipId As String = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart)

        ' Get a unique ID for the new worksheet.
        Dim sheetId As UInteger = 1
        If (sheets.Elements(Of Sheet).Count > 0) Then
            sheetId = sheets.Elements(Of Sheet).Select(Function(s) s.SheetId.Value).Max + 1
        End If

        ' Give the new worksheet a name.
        Dim sheetName As String = ("Sheet" + sheetId.ToString())

        ' Append the new worksheet and associate it with the workbook.
        Dim sheet As Sheet = New Sheet
        sheet.Id = relationshipId
        sheet.SheetId = sheetId
        sheet.Name = sheetName
        sheets.Append(sheet)
    End Using
End Sub

Partager via

How to: Insert a New Worksheet into a Spreadsheet Document

Getting a SpreadsheetDocument Object

Basic Structure of a SpreadsheetML Document

How the Sample Code Works

Sample Code

See Also

Reference

Other Resources

Ressources supplémentaires