Inserir texto numa célula num documento de folha de cálculo

Este tópico mostra como utilizar as classes no SDK Open XML para o Office para inserir texto numa célula numa nova folha de cálculo num documento de folha de cálculo programaticamente.

---

Estrutura básica de um documento de folha de cálculoML

A estrutura de documentos básica de um SpreadsheetML documento consiste nos Sheets elementos e Sheet , que referenciam as folhas de cálculo no livro. Um arquivo XML separado é criado para cada planilha. Por exemplo, o SpreadsheetML para um Workbook que tem duas folhas de cálculo com o nome MySheet1 e MySheet2 está localizado no ficheiro Workbook.xml e é apresentado no seguinte exemplo de código.

    <?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>

Os ficheiros XML da folha de cálculo contêm um ou mais elementos de nível de bloco, como SheetData representa a tabela de células e contém um ou mais Row elementos. A row contém um ou mais Cell elementos. Cada célula contém um CellValue elemento que representa o valor da célula. Por exemplo, o para a SpreadsheetML primeira folha de cálculo num livro, que tem apenas o valor 100 na célula A1, está localizado no ficheiro Sheet1.xml e é apresentado no seguinte exemplo de código.

    <?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>

Com o SDK Open XML, pode criar a estrutura do documento e o conteúdo que utiliza classes com tipos fortes que correspondem a SpreadsheetML elementos. Pode encontrar estas classes no DocumentFormat.OpenXML.Spreadsheet espaço de nomes. A tabela seguinte lista os nomes das classes que correspondem aos workbookelementos , sheets, sheet, worksheete sheetData .

Elemento SpreadsheetML	Abrir Classe SDK XML	Descrição
<workbook/>	DocumentFormat.OpenXML.Spreadsheet.Workbook	O elemento raiz para a parte do documento principal.
<sheets/>	DocumentFormat.OpenXML.Spreadsheet.Sheets	O contêiner para as estruturas de nível de bloco, como sheet, fileVersion e outras indicadas na especificação ISO/IEC 29500.
<sheet/>	DocumentFormat.OpenXml.Spreadsheet.Sheet	Uma planilha que aponta para um arquivo de definição de planilha.
<worksheet/>	DocumentFormat.OpenXML.Spreadsheet. Planilha	Um arquivo de definição de planilha que contém os dados de planilha.
<sheetData/>	DocumentFormat.OpenXML.Spreadsheet.SheetData	A tabela de células, agrupadas por linhas.
<row/>	DocumentFormat.OpenXml.Spreadsheet.Row	Uma linha na tabela de células.
<c/>	DocumentFormat.OpenXml.Spreadsheet.Cell	Uma célula em uma linha.
<v/>	DocumentFormat.OpenXml.Spreadsheet.CellValue	O valor de uma célula.

Como funciona o código de exemplo

Depois de abrir o SpreadsheetDocument documento para edição, o código insere um objeto em branco Worksheet num pacote de SpreadsheetDocument documento. Em seguida, insere um novo Cell objeto na nova folha de cálculo e insere o texto especificado nessa célula.

C#

// Given a document name and text, 
// inserts a new work sheet and writes the text to cell "A1" of the new worksheet.
static void InsertText(string docName, string text)
{
    // Open the document for editing.
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
    {
        WorkbookPart workbookPart = spreadSheet.WorkbookPart ?? spreadSheet.AddWorkbookPart();

        // Get the SharedStringTablePart. If it does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
        }

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

        // Insert a new worksheet.
        WorksheetPart worksheetPart = InsertWorksheet(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);
    }
}

Visual Basic

' Given a document name and text, 
' inserts a new work sheet and writes the text to cell "A1" of the new worksheet.
Sub InsertText(docName As String, text As String)
    ' Open the document for editing.
    Using spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        Dim workbookPart As WorkbookPart = If(spreadSheet.WorkbookPart, spreadSheet.AddWorkbookPart())

        ' Get the SharedStringTablePart. If it does not exist, create a new one.
        Dim shareStringPart As SharedStringTablePart
        If workbookPart.GetPartsOfType(Of SharedStringTablePart)().Count() > 0 Then
            shareStringPart = workbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
        Else
            shareStringPart = workbookPart.AddNewPart(Of SharedStringTablePart)()
        End If

        ' Insert the text into the SharedStringTablePart.
        Dim index As Integer = InsertSharedStringItem(text, shareStringPart)

        ' Insert a new worksheet.
        Dim worksheetPart As WorksheetPart = InsertWorksheet(workbookPart)

        ' Insert cell A1 into the new worksheet.
        Dim cell As Cell = InsertCellInWorksheet("A", 1, worksheetPart)

        ' Set the value of cell A1.
        cell.CellValue = New CellValue(index.ToString())
        cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
    End Using
End Sub

O código transmite um parâmetro que representa o texto a inserir na célula e um parâmetro que representa o SharedStringTablePart objeto da folha de cálculo. Se o ShareStringTablePart objeto não contiver um SharedStringTable objeto, o código cria um. Se o texto já existir no ShareStringTable objeto, o código devolve o índice do SharedStringItem objeto que representa o texto. Caso contrário, cria um novo SharedStringItem objeto que representa o texto.

O código seguinte verifica se o texto especificado existe no SharedStringTablePart objeto e adiciona o texto se não existir.

C#

// 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.
static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
    // If the part does not contain a SharedStringTable, create one.
    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)));

    return i;
}

Visual Basic

' 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.
Function InsertSharedStringItem(text As String, shareStringPart As SharedStringTablePart) As Integer
    ' If the part does not contain a SharedStringTable, create one.
    If shareStringPart.SharedStringTable Is Nothing Then
        shareStringPart.SharedStringTable = New SharedStringTable()
    End If

    Dim i As Integer = 0

    ' Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
    For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
        If item.InnerText = text Then
            Return i
        End If

        i += 1
    Next

    ' 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)))

    Return i
End Function

O código adiciona um novo WorksheetPart objeto ao WorkbookPart objeto com o AddNewPart método . Em seguida, adiciona um novo Worksheet objeto ao WorksheetPart objeto e obtém um ID exclusivo para a nova folha de cálculo ao selecionar o objeto máximo SheetId utilizado no documento da folha de cálculo e ao adicionar um para criar o novo ID da folha. Ele fornece um nome à planilha concatenando a palavra "Sheet" com a ID da planilha. Em seguida, acrescenta o novo Sheet objeto à Sheets coleção.

O código seguinte insere um novo Worksheet objeto ao adicionar um novo WorksheetPart objeto ao WorkbookPart objeto.

C#

// Given a WorkbookPart, inserts a new worksheet.
static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
    // Add a new worksheet part to the workbook.
    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 sheet.
    uint sheetId = 1;
    if (sheets.Elements<Sheet>().Count() > 0)
    {
        sheetId = sheets.Elements<Sheet>().Select<Sheet, uint>(s =>
        {
            if (s.SheetId is not null && s.SheetId.HasValue)
            {
                return s.SheetId.Value;
            }

            return 0;
        }).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);

    return newWorksheetPart;
}

Visual Basic

' Given a WorkbookPart, inserts a new worksheet.
Function InsertWorksheet(workbookPart As WorkbookPart) As WorksheetPart
    ' Add a new worksheet part to the workbook.
    Dim newWorksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
    newWorksheetPart.Worksheet = New Worksheet(New SheetData())

    Dim sheets As Sheets = If(workbookPart.Workbook.GetFirstChild(Of Sheets)(), workbookPart.Workbook.AppendChild(New Sheets()))
    Dim relationshipId As String = workbookPart.GetIdOfPart(newWorksheetPart)

    ' Get a unique ID for the new sheet.
    Dim sheetId As UInteger = 1
    If sheets.Elements(Of Sheet)().Count() > 0 Then
        sheetId = sheets.Elements(Of Sheet)().Select(Function(s)
                                                         If s.SheetId IsNot Nothing AndAlso s.SheetId.HasValue Then
                                                             Return s.SheetId.Value
                                                         End If

                                                         Return 0
                                                     End Function).Max() + 1
    End If

    Dim sheetName As String = "Sheet" & sheetId

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

    Return newWorksheetPart
End Function

Para inserir uma célula em uma planilha, o código determina onde inserir a nova célula na coluna iterando pelos elementos de linha para localizar a célula que vem logo após a linha especificada, em ordem sequencial. Guarda essa linha na refCell variável . Em seguida, insere a nova célula antes da célula referenciada com refCell o InsertBefore método .

No código seguinte, insira um novo Cell objeto num Worksheet objeto.

C#

// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
// If the cell already exists, returns it. 
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 is not null && r.RowIndex == rowIndex).Count() != 0)
    {
        row = sheetData!.Elements<Row>().Where(r => r.RowIndex is not null && 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 is not null && c.CellReference.Value == columnName + rowIndex).Count() > 0)
    {
        return row.Elements<Cell>().Where(c => c.CellReference is not null && 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);

        return newCell;
    }
}

Visual Basic

' Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
' If the cell already exists, returns it. 
Function InsertCellInWorksheet(columnName As String, rowIndex As UInteger, worksheetPart As WorksheetPart) As Cell
    Dim worksheet As Worksheet = worksheetPart.Worksheet
    Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
    Dim cellReference As String = columnName & rowIndex

    ' If the worksheet does not contain a row with the specified row index, insert one.
    Dim row As Row

    If sheetData.Elements(Of Row)().Where(Function(r) r.RowIndex IsNot Nothing AndAlso r.RowIndex.Equals(rowIndex)).Count() <> 0 Then
        row = sheetData.Elements(Of Row)().Where(Function(r) r.RowIndex IsNot Nothing AndAlso r.RowIndex.Equals(rowIndex)).First()
    Else
        row = New Row() With {
            .RowIndex = rowIndex
        }
        sheetData.Append(row)
    End If

    ' If there is not a cell with the specified column name, insert one.  
    If row.Elements(Of Cell)().Where(Function(c) c.CellReference IsNot Nothing AndAlso c.CellReference.Value = columnName & rowIndex).Count() > 0 Then
        Return row.Elements(Of Cell)().Where(Function(c) c.CellReference IsNot Nothing AndAlso c.CellReference.Value = cellReference).First()
    Else
        ' Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
        Dim refCell As Cell = Nothing

        For Each cell As Cell In row.Elements(Of Cell)()
            If String.Compare(cell.CellReference?.Value, cellReference, True) > 0 Then
                refCell = cell
                Exit For
            End If
        Next

        Dim newCell As New Cell() With {
            .CellReference = cellReference
        }
        row.InsertBefore(newCell, refCell)

        Return newCell
    End If
End Function

---

Código de exemplo

Este é o código de exemplo completo em C# e em Visual Basic.

C#

static void InsertText(string docName, string text)
{
    // Open the document for editing.
    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
    {
        WorkbookPart workbookPart = spreadSheet.WorkbookPart ?? spreadSheet.AddWorkbookPart();

        // Get the SharedStringTablePart. If it does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
        }

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

        // Insert a new worksheet.
        WorksheetPart worksheetPart = InsertWorksheet(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);
    }
}

// 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.
static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
    // If the part does not contain a SharedStringTable, create one.
    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)));

    return i;
}

// Given a WorkbookPart, inserts a new worksheet.
static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
{
    // Add a new worksheet part to the workbook.
    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 sheet.
    uint sheetId = 1;
    if (sheets.Elements<Sheet>().Count() > 0)
    {
        sheetId = sheets.Elements<Sheet>().Select<Sheet, uint>(s =>
        {
            if (s.SheetId is not null && s.SheetId.HasValue)
            {
                return s.SheetId.Value;
            }

            return 0;
        }).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);

    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. 
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 is not null && r.RowIndex == rowIndex).Count() != 0)
    {
        row = sheetData!.Elements<Row>().Where(r => r.RowIndex is not null && 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 is not null && c.CellReference.Value == columnName + rowIndex).Count() > 0)
    {
        return row.Elements<Cell>().Where(c => c.CellReference is not null && 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);

        return newCell;
    }
}

Visual Basic

Sub InsertText(docName As String, text As String)
    ' Open the document for editing.
    Using spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        Dim workbookPart As WorkbookPart = If(spreadSheet.WorkbookPart, spreadSheet.AddWorkbookPart())

        ' Get the SharedStringTablePart. If it does not exist, create a new one.
        Dim shareStringPart As SharedStringTablePart
        If workbookPart.GetPartsOfType(Of SharedStringTablePart)().Count() > 0 Then
            shareStringPart = workbookPart.GetPartsOfType(Of SharedStringTablePart)().First()
        Else
            shareStringPart = workbookPart.AddNewPart(Of SharedStringTablePart)()
        End If

        ' Insert the text into the SharedStringTablePart.
        Dim index As Integer = InsertSharedStringItem(text, shareStringPart)

        ' Insert a new worksheet.
        Dim worksheetPart As WorksheetPart = InsertWorksheet(workbookPart)

        ' Insert cell A1 into the new worksheet.
        Dim cell As Cell = InsertCellInWorksheet("A", 1, worksheetPart)

        ' Set the value of cell A1.
        cell.CellValue = New CellValue(index.ToString())
        cell.DataType = New EnumValue(Of CellValues)(CellValues.SharedString)
    End Using
End Sub

' 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.
Function InsertSharedStringItem(text As String, shareStringPart As SharedStringTablePart) As Integer
    ' If the part does not contain a SharedStringTable, create one.
    If shareStringPart.SharedStringTable Is Nothing Then
        shareStringPart.SharedStringTable = New SharedStringTable()
    End If

    Dim i As Integer = 0

    ' Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
    For Each item As SharedStringItem In shareStringPart.SharedStringTable.Elements(Of SharedStringItem)()
        If item.InnerText = text Then
            Return i
        End If

        i += 1
    Next

    ' 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)))

    Return i
End Function

' Given a WorkbookPart, inserts a new worksheet.
Function InsertWorksheet(workbookPart As WorkbookPart) As WorksheetPart
    ' Add a new worksheet part to the workbook.
    Dim newWorksheetPart As WorksheetPart = workbookPart.AddNewPart(Of WorksheetPart)()
    newWorksheetPart.Worksheet = New Worksheet(New SheetData())

    Dim sheets As Sheets = If(workbookPart.Workbook.GetFirstChild(Of Sheets)(), workbookPart.Workbook.AppendChild(New Sheets()))
    Dim relationshipId As String = workbookPart.GetIdOfPart(newWorksheetPart)

    ' Get a unique ID for the new sheet.
    Dim sheetId As UInteger = 1
    If sheets.Elements(Of Sheet)().Count() > 0 Then
        sheetId = sheets.Elements(Of Sheet)().Select(Function(s)
                                                         If s.SheetId IsNot Nothing AndAlso s.SheetId.HasValue Then
                                                             Return s.SheetId.Value
                                                         End If

                                                         Return 0
                                                     End Function).Max() + 1
    End If

    Dim sheetName As String = "Sheet" & sheetId

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

    Return newWorksheetPart
End Function


' Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
' If the cell already exists, returns it. 
Function InsertCellInWorksheet(columnName As String, rowIndex As UInteger, worksheetPart As WorksheetPart) As Cell
    Dim worksheet As Worksheet = worksheetPart.Worksheet
    Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
    Dim cellReference As String = columnName & rowIndex

    ' If the worksheet does not contain a row with the specified row index, insert one.
    Dim row As Row

    If sheetData.Elements(Of Row)().Where(Function(r) r.RowIndex IsNot Nothing AndAlso r.RowIndex.Equals(rowIndex)).Count() <> 0 Then
        row = sheetData.Elements(Of Row)().Where(Function(r) r.RowIndex IsNot Nothing AndAlso r.RowIndex.Equals(rowIndex)).First()
    Else
        row = New Row() With {
            .RowIndex = rowIndex
        }
        sheetData.Append(row)
    End If

    ' If there is not a cell with the specified column name, insert one.  
    If row.Elements(Of Cell)().Where(Function(c) c.CellReference IsNot Nothing AndAlso c.CellReference.Value = columnName & rowIndex).Count() > 0 Then
        Return row.Elements(Of Cell)().Where(Function(c) c.CellReference IsNot Nothing AndAlso c.CellReference.Value = cellReference).First()
    Else
        ' Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
        Dim refCell As Cell = Nothing

        For Each cell As Cell In row.Elements(Of Cell)()
            If String.Compare(cell.CellReference?.Value, cellReference, True) > 0 Then
                refCell = cell
                Exit For
            End If
        Next

        Dim newCell As New Cell() With {
            .CellReference = cellReference
        }
        row.InsertBefore(newCell, refCell)

        Return newCell
    End If
End Function

Confira também

Open XML SDK class library reference (Referência da biblioteca de classes SDK Open XML)