Edit

Open a spreadsheet document from a stream

  • Article

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

When to Open From a Stream

If you have an application, such as Microsoft SharePoint Foundation 2010, that works with documents by using stream input/output, and you want to use the Open XML SDK to work with one of the documents, this is designed to be easy to do. This is especially true if the document exists and you can open it using the Open XML SDK. However, suppose that the document is an open stream at the point in your code where you must use the SDK to work with it? That is the scenario for this topic. The sample method in the sample code accepts an open stream as a parameter and then adds text to the document behind the stream using the Open XML SDK.

The SpreadsheetDocument Object

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="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.

Generating the SpreadsheetML Markup to Add a Worksheet

When you have access to the body of the main document part, you add a worksheet by calling AddNewPart<T>(String, String) method to create a new WorksheetPart. The following code example adds the new WorksheetPart.


// Add a new worksheet.
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();

Sample Code

In this example, the OpenAndAddToSpreadsheetStream method can be used to open a spreadsheet document from an already open stream and append some text to it. The following is the complete sample code in both C# and Visual Basic.

var fileStream = File.Open(args[0], FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
OpenAndAddToSpreadsheetStream(fileStream);

Notice that the OpenAddAndAddToSpreadsheetStream method does not close the stream passed to it. The calling code must do that.

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

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

static void OpenAndAddToSpreadsheetStream(Stream stream)
{
    // Open a SpreadsheetDocument based on a stream.
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(stream, true);

    if (spreadsheetDocument is not null)
    {
        // Get or create the WorkbookPart
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart ?? spreadsheetDocument.AddWorkbookPart();

        
        // Add a new worksheet.
        WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        newWorksheetPart.Worksheet = new Worksheet(new SheetData());
        newWorksheetPart.Worksheet.Save();
        

        Workbook workbook = workbookPart.Workbook ?? new Workbook();

        if (workbookPart.Workbook is null)
        {
            workbookPart.Workbook = workbook;
        }

        Sheets sheets = workbook.GetFirstChild<Sheets>() ?? 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);
        workbookPart.Workbook.Save();

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

See also