Working with sheets (Open XML SDK) (2023)

  • Article
  • 9 minutes to read

This topic discusses the Open XML SDK 2.5Worksheet, Chartsheet, and DialogSheet classes and how they relate tothe Open XML File Format SpreadsheetML schema. For more informationabout the overall structure of the parts and elements that make up aSpreadsheetML document, see Structure of a SpreadsheetML document (Open XML SDK).

Sheets in SpreadsheetML

The following information from the ISO/IEC 29500specification introduces the sheet (<sheet>) element.

Sheets are the central structures within a workbook, and are where theuser does most of their spreadsheet work. The most common type of sheetis the worksheet, which is represented as a grid of cells. Worksheetcells can contain text, numbers, dates, and formulas. Cells can beformatted as well. Workbooks usually contain more than one sheet. To aidin the analysis of data and making informed decisions, spreadsheetapplications often implement features and objects which help calculate,sort, filter, organize, and graphically display information. Since thesefeatures are often connected very tightly with the spreadsheet grid,these are also included in the sheet definition on disk.

Other types of sheets include chart sheets and dialog sheets.

© ISO/IEC29500: 2008.

Open XML SDK 2.5 Worksheet Class

The Open XML SDK 2.5Worksheet classrepresents the worksheet (<worksheet>) element defined in the Open XML FileFormat schema for SpreadsheetML documents. Use the Worksheet class to manipulate individual <worksheet> elements in a SpreadsheetML document.

The following information from the ISO/IEC 29500 specificationintroduces the worksheet (<worksheet>) element.

An instance of this part type contains all the data, formulas, andcharacteristics associated with a given worksheet.

A package shall contain exactly one Worksheet part per worksheet

Specifically, the id attribute on the sheet element shall reference thedesired worksheet part.

The root element for a part of this content type shall be worksheet.

The following information from the ISO/IEC 29500 specificationintroduces the minimum worksheet scenario.

The smallest possible (blank) sheet is as follows:

<worksheet> <sheetData/></worksheet>

The empty sheetData collection represents an empty grid; this element isrequired. As defined in the schema, some optional sheet propertycollections can appear before sheetData, and some can appear after. Tosimplify the logic required to insert a new sheetData collection into anexisting (but empty) sheet, the sheetData collection is required, evenwhen empty.

© ISO/IEC29500: 2008.

A typical spreadsheet has at least one worksheet. The worksheet containsa table like structure for defining data, represented by the sheetData element. A sheet that contains datauses the worksheet element as the rootelement for defining worksheets. Inside a worksheet the data is split upinto three distinct sections. The first section contains optional sheetproperties. The second section contains the data, using the requiredsheetData element. The third section contains optional supportingfeatures such as sheet protection and filter information. To define anempty worksheet you only have to use the worksheet and sheetData elements. The sheetData element can be empty.

To create new values for the worksheet you define rows inside the sheetData element. These rows contain cells,which contain values. The row elementdefines a new row. Normally the first row in the sheetData is the first row in the visible sheet.Inside the row you create new cells using the <c> element. Values for cells can be provided bystoring a <v> element inside the cell.Usually the <v> element contains thecurrent value of the worksheet cell. If the value is a numeric value, itis stored directly in the <v> element inthe XML file. If the value is a string value, it is stored in a sharedstring table. For more information about using the shared string tableto store string values, see Working with the shared string table (Open XML SDK).

The following table lists the common Open XML SDK 2.5 classes used whenworking with the Worksheet class.

SpreadsheetML ElementOpen XML SDK 2.5 Class
sheetDataSheetData
rowRow
cCell
vCellValue

For more information about optional spreadsheet elements, such as sheetproperties and supporting sheet features, see the ISO/IEC 29500specification.

SheetData Class

The following information from the ISO/IEC 29500 specificationintroduces the sheet data (<sheetData>) element.

The cell table is the core structure of a worksheet. It consists of allthe text, numbers, and formulas in the grid.

© ISO/IEC29500: 2008.

Row Class

The following information from the ISO/IEC 29500 specificationintroduces the row (<row>) element.

The cells in the cell table are organized by row. Each row has an index(attribute r) so that empty rows need not be written out. Each rowindicates the number of cells defined for it, as well as their relativeposition in the sheet. In this example, the first row of data is row 2.

© ISO/IEC29500: 2008.

Cell Class

The following information from the ISO/IEC 29500 specificationintroduces the cell (<c>) element.

The cell itself is expressed by the c collection. Each cell indicatesits location in the grid using A1-style reference notation. A cell canalso indicate a style identifier (attribute s) and a data type(attribute t). The cell types include string, number, and Boolean. Inorder to optimize load/save operations, default data values are notwritten out.

© ISO/IEC29500: 2008.

CellValue Class

The following information from the ISO/IEC 29500 specificationintroduces the cell value (<v>) element.

Cells contain values, whether the values were directly entered (e.g.,cell A2 in our example has the value External Link:) or are the resultof a calculation (e.g., cell B3 in our example has the formula B2+1).

String values in a cell are not stored in the cell table unless they arethe result of a calculation. Therefore, instead of seeing External Link:as the content of the cell's v node, instead you see a zero-based indexinto the shared string table where that string is stored uniquely. Thisis done to optimize load/save performance and to reduce duplication ofinformation. To determine whether the 0 in v is a number or an index toa string, the cell's data type must be examined. When the data typeindicates string, then it is an index and not a numeric value.

© ISO/IEC29500: 2008.

Open XML SDK Code Example

The following code example creates a spreadsheet document with thespecified file name and instantiates a Worksheet class, and then adds a row and adds acell to the cell table at position A1. Then the value of the cell in A1is set equal to the numeric value 100.

 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); // Get the sheetData cell table. SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>(); // Add a row to the cell table. Row row; row = new Row() { RowIndex = 1 }; sheetData.Append(row); // In the new row, find the column location to insert a cell in A1. Cell refCell = null; foreach (Cell cell in row.Elements<Cell>()) { if (string.Compare(cell.CellReference.Value, "A1", true) > 0) { refCell = cell; break; } } // Add the cell to the cell table at A1. Cell newCell = new Cell() { CellReference = "A1" }; row.InsertBefore(newCell, refCell); // Set the cell value to be a numeric value of 100. newCell.CellValue = new CellValue("100"); newCell.DataType = new EnumValue<CellValues>(CellValues.Number); // Close the document. spreadsheetDocument.Close(); }
 Public Sub CreateSpreadsheetWorkbookWithNumValue(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 New Sheet() With {.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), .SheetId = 1, .Name = "mySheet"} sheets.Append(sheet) ' Get the sheetData cell table. Dim sheetData As SheetData = worksheetPart.Worksheet.GetFirstChild(Of SheetData)() ' Add a row to the cell table. Dim row As Row row = New Row() With {.RowIndex = 1} sheetData.Append(row) ' In the new row, find the column location to insert a cell in A1. Dim refCell As Cell = Nothing For Each cell As Cell In row.Elements(Of Cell)() If String.Compare(cell.CellReference.Value, "A1", True) > 0 Then refCell = cell Exit For End If Next ' Add the cell to the cell table at A1. Dim newCell As New Cell() With {.CellReference = "A1"} row.InsertBefore(newCell, refCell) ' Set the cell value to be a numeric value of 100. newCell.CellValue = New CellValue("100") newCell.DataType = New EnumValue(Of CellValues)(CellValues.Number) ' Close the document. SpreadsheetDocument.Close() End Sub

Generated SpreadsheetML

When the Open XML SDK code is run, the following XML is written to theSpreadsheetML document referenced in the code. To view this XML, openthe "sheet.xml" file in the "worksheets" folder of the .zip file.

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

The Open XML SDK Chartsheet Class

The following information from the ISO/IEC 29500 specificationintroduces the chartsheet (<chartsheet>) element.

An instance of this part type represents a chart that is stored in itsown sheet.

A package is permitted to contain zero or more Chartsheet parts.

Example: sheet1.xml refers to a drawing that is the target of arelationship in the Chartsheet part's relationship item:

<chartsheet xmlns:r="…" …> <sheetViews> <sheetView scale="64"/> </sheetViews\> <drawing r:id="rId1"></chartsheet>

© ISO/IEC29500: 2008.

The following table lists the common Open XML SDK 2.5 classes used whenworking with the Chartsheet class.

SpreadsheetML ElementOpen XML SDK 2.5 Class
drawingDrawing

Drawing Class

The following information from the ISO/IEC 29500 specificationintroduces the drawings (<wsDr>) element.

An instance of this part type contains the presentation and layoutinformation for one or more drawing elements that are present on thisworksheet.

A package is permitted to contain one or more Drawings parts, and eachsuch part shall be the target of an explicit relationship from aWorksheet part (§12.3.24), or a Chartsheet part (§12.3.2). There shallbe only one Drawings part per worksheet or chartsheet.

© ISO/IEC29500: 2008.

Open XML SDK 2.5 Dialogsheet Class

The following information from the ISO/IEC 29500 specificationintroduces the dialogsheet (<dialogsheet>) element.

An instance of this part type contains information about a legacy customdialog box for a user form.

A package is permitted to contain one or more Dialogsheet parts

The root element for a part of this content type shall be dialogsheet.

Example: sheet1.xml contains the following:

<dialogsheet xmlns:r="…" …> <sheetPr> <pageSetUpPr/> </sheetPr> <sheetViews> … </sheetViews> … <legacyDrawing r:id="rId1"/></dialogsheet>

© ISO/IEC29500: 2008.

Top Articles
Latest Posts
Article information

Author: Otha Schamberger

Last Updated: 12/07/2022

Views: 5629

Rating: 4.4 / 5 (55 voted)

Reviews: 86% of readers found this page helpful

Author information

Name: Otha Schamberger

Birthday: 1999-08-15

Address: Suite 490 606 Hammes Ferry, Carterhaven, IL 62290

Phone: +8557035444877

Job: Forward IT Agent

Hobby: Fishing, Flying, Jewelry making, Digital arts, Sand art, Parkour, tabletop games

Introduction: My name is Otha Schamberger, I am a vast, good, healthy, cheerful, energetic, gorgeous, magnificent person who loves writing and wants to share my knowledge and understanding with you.