Insert a new worksheet into a spreadsheet document

Article
01/18/2024

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

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 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="http://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, 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 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.

Sample Code

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

C#
Visual Basic

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

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

        Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>() ?? workbookPart.Workbook.AppendChild(new Sheets());
        string relationshipId = 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) ?? (uint)sheets.Elements<Sheet>().Count() + 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);
    }
}

InsertWorksheet(args[0]);

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

Module Program
    Sub Main(args As String())
    End Sub



    ' 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
End Module

Share via

Insert a new worksheet into a spreadsheet document

Basic structure of a spreadsheetML document

Sample Code

See also

Additional resources