3 out of 7 rated this helpful - Rate this topic

How to: Insert Text into a Cell in a Spreadsheet Document

Office 2010

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

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

Copy

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

Copy

Imports System.Linq
Imports DocumentFormat.OpenXml
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()()()() overload 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.

Copy

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

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.

The 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

Copy

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
<workbook xmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r="http://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

Copy

<?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 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 SpreadsheetDocument document for editing, the code inserts a blank Worksheet object into a SpreadsheetDocument document package. Then, inserts a new Cell object into the new worksheet and inserts the specified text into that cell.

Copy

// Given a document name and text, 
// inserts a new worksheet and writes the text to cell "A1" of the new worksheet.
public static void InsertText(string docName, string text)
{
    // Open the document for editing.
    Imports (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
    {
        // Get the SharedStringTablePart. If it does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
        }

        // Insert the text into the SharedStringTablePart.
        int index = InsertSharedStringItem(text, shareStringPart);

        // Insert a new worksheet.
        WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);

        // Insert cell A1 into the new worksheet.
        Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);

        // Set the value of cell A1.
        cell.CellValue = new CellValue(index.ToString());
        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

        // Save the new worksheet.
        worksheetPart.Worksheet.Save();
    }
}

The code passes in a parameter that represents the text to insert into the cell and a parameter that represents the SharedStringTablePart object for the spreadsheet. If the ShareStringTablePart object does not contain a SharedStringTable object, the code creates one. If the text already exists in the ShareStringTable object, the code returns the index for the SharedStringItem object that represents the text. Otherwise, it creates a new SharedStringItem object that represents the text.

The following code verifies if the specified text exists in the SharedStringTablePart object and add the text if it does not exist.

Copy

// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
    // If the part does not contain a SharedStringTable, create one.
    if (shareStringPart.SharedStringTable == null)
    {
        shareStringPart.SharedStringTable = new SharedStringTable();
    }

    int i = 0;

    // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
    foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
    {
        if (item.InnerText == text)
        {
            return i;
        }

        i++;
    }

    // The text does not exist in the part. Create the SharedStringItem and return its index.
    shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
    shareStringPart.SharedStringTable.Save();

    return i;
}

The code adds a new WorksheetPart object to the WorkbookPart object by using the AddNewPart method. It then adds a new Worksheet object to the WorksheetPart object, and 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. It then appends the new Sheet object to the Sheets collection.

The following code inserts a new Worksheet object by adding a new WorksheetPart object to the WorkbookPart object.

Copy

// Given a WorkbookPart, inserts a new worksheet.
private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
    // Add a new worksheet part to the workbook.
    WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    newWorksheetPart.Worksheet = new Worksheet(new SheetData());
    newWorksheetPart.Worksheet.Save();

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

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

    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();

    return newWorksheetPart;
}

To insert a cell into a worksheet, the code determines where to insert the new cell in the column by iterating through the row elements to find the cell that comes directly after the specified row, in sequential order. It saves that row in the refCell variable. It then inserts the new cell before the cell referenced by refCell using the InsertBefore method.

In the following code, insert a new Cell object into a Worksheet object.

Copy

// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
// If the cell already exists, returns it. 
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
    Worksheet worksheet = worksheetPart.Worksheet;
    SheetData sheetData = worksheet.GetFirstChild<SheetData>();
    string cellReference = columnName + rowIndex;

    // If the worksheet does not contain a row with the specified row index, insert one.
    Row row;
    if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
    {
        row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
    }
    else
    {
        row = new Row() { RowIndex = rowIndex };
        sheetData.Append(row);
    }

    // If there is not a cell with the specified column name, insert one.  
    if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
    {
        return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
    }
    else
    {
        // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
        Cell refCell = null;
        foreach (Cell cell in row.Elements<Cell>())
        {
            if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
            {
                refCell = cell;
                break;
            }
        }

        Cell newCell = new Cell() { CellReference = cellReference };
        row.InsertBefore(newCell, refCell);

        worksheet.Save();
        return newCell;
    }
}

Sample Code

The following code sample is used to insert a new worksheet and write the text to the cell "A1" of the new worksheet for a specific spreadsheet document named “Sheet8.xlsx.” To call the InsertText method you can use the following code as an example.

Copy

InsertText(@"C:\Users\Public\Documents\Sheet8.xlsx", "Inserted Text");

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

Copy

// Given a document name and text, 
 // inserts a new work sheet and writes the text to cell "A1" of the new worksheet.

 public static void InsertText(string docName, string text)
{
    // Open the document for editing.
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
    {
        // Get the SharedStringTablePart. If it does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
        }

        // Insert the text into the SharedStringTablePart.
        int index = InsertSharedStringItem(text, shareStringPart);

        // Insert a new worksheet.
        WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);

        // Insert cell A1 into the new worksheet.
        Cell cell = InsertCellInWorksheet("A", 1, worksheetPart);

        // Set the value of cell A1.
        cell.CellValue = new CellValue(index.ToString());
        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

        // Save the new worksheet.
        worksheetPart.Worksheet.Save();
    }
}

        // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
        // and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            // If the part does not contain a SharedStringTable, create one.
            if (shareStringPart.SharedStringTable == null)
            {
                shareStringPart.SharedStringTable = new SharedStringTable();
            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                {
                    return i;
                }

                i++;
            }

            // The text does not exist in the part. Create the SharedStringItem and return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }

        // Given a WorkbookPart, inserts a new worksheet.
        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

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

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

            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();

            return newWorksheetPart;
        }

        // Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
        // If the cell already exists, returns it. 
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);

                worksheet.Save();
                return newCell;
            }
        }

Reference

Class Library Reference

Other Resources

Language-Integrated Query (LINQ)

Lambda Expressions

Lambda Expressions (C# Programming Guide)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Content Add

FAQ

Over 26 columns fix

Change $0 $0if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)$0 $0to$0 $0if (cell.CellReference.Value == cellReference)$0 $0I assume the compare finds columns where "B1" exists as the compare for AB1 finds B1. Therefore updating the comparison should resolve the issue$0 $0

History

4/21/2012
James Prout

Column reference compare fails for more than 26 columns (function: InsertCellInWorksheet)

dsafasdfs

History

5/18/2011
Mr.Hunt

Maybe InsertSharedStringItem should be avoided in a lot of cases

I did some unscientific testing with writing 6500 rows (with the same string of text repeated) to Excel.
Using just a straight myCell.DataType = CellValues.String; and then CellValue myValue = new CellValue(text); ... the entire process took about 1 second, and the file was 346kB.
Using InsertSharedStringItem the file was 335kB, but it took about 200 seconds!

Maybe I'm not understanding InsertSharedStringItem's purpose.

History

10/22/2010
Hameron

RE: Column comparison fails

I agree with @fuzzfoot, but the comparison in the example will still fail if more than 52 columns. What worked for me is to also compare the length of the string in column name.

I changed the same part as @fuzzfoot to:

foreach (Cell cell in row.Elements<Cell>()) {
string thisCellColName = cell.CellReference.Value.Replace(row.RowIndex.ToString(), "");
if (thisCellColName.Length >= columnName.Length && string.Compare(thisCellColName, columnName, true) > 0) { refCell = cell; break; } }

History

8/23/2010
Boris Gomiunik

9/16/2010
Thomas Lee

Column reference comparison fails if more than 26 columns

I believe there is an error in the InsertCellInWorksheet method. It works fine as long as you don't use more than 26 columns, but then the string comparison fails, inserting A2 after AA2, essentially corrupting the worksheet.

Try changing

// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
    {
        refCell = cell;
        break;
    }
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);

// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
string c1 = cell.CellReference.Value.Replace(rowIndex.ToString(), "");
    if (c1.Length == 1) c1 = " " + c1;
    string c2 = cellReference.Replace(rowIndex.ToString(), "");
    if (c2.Length == 1) c2 = " " + c2;
    if (string.Compare(c1, c2, true) > 0)
    {
        refCell = cell;
        break;
    }
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);

I expect there is a more elegant solution, but this worked well enough for my purposes.

History

8/3/2010
PJPatterson

@mickey12345

No, you don't want to do that, as other cells could also refer to this string in the SHARED string table.
You want to check whether the new string is already in the shared string table and if yes, reuse it (by index).
Or you add a new string.

History

7/12/2010
JensLondon

may be wrong

I think this method is wrong.
for example, we want to insert text ("mickey") in cell ("A1"), and in cell A1 is exist another value text "Mouse". So "Mouse" in sharedstringtable, and we must change "mouse" to "mickey" in sharedstringtable.

History

6/25/2010
Mickey12345