在电子表格文档中的单元格中插入文本

本主题演示如何使用 Open XML SDK for Office 中的类以编程方式将文本插入电子表格文档中新工作表中的单元格中。

---

spreadsheetML 文档的基本结构

文档的基本文档结构SpreadsheetML由 和 Sheet 元素组成Sheets，这些元素引用工作簿中的工作表。 将为每张工作表创建单独的 XML 文件。 例如， SpreadsheetML 具有两个 Workbook 工作表 MySheet1 和 MySheet2 的 位于 Workbook.xml 文件中，并在以下代码示例中显示。

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

工作表 XML 文件包含一个或多个块级元素，如 SheetData 表示单元格表，并包含一个或多个 Row 元素。 包含 row 一个或多个 Cell 元素。 每个单元格都包含一个 CellValue 表示单元格值的元素。 例如， SpreadsheetML 工作簿中第一个工作表的 （在单元格 A1 中只有值 100） 位于 Sheet1.xml 文件中，并显示在以下代码示例中。

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

使用 Open XML SDK，可以创建使用与元素对应的 SpreadsheetML 强类型类的文档结构和内容。 可以在 命名空间中找到 DocumentFormat.OpenXML.Spreadsheet 这些类。 下表列出了对应于 、、sheets、 worksheetsheet和 sheetData 元素的类的workbook类名。

SpreadsheetML 元素	Open XML SDK 类	说明
<workbook/>	DocumentFormat.OpenXML.Spreadsheet.Workbook	主文档部件的根元素。
<sheets/>	DocumentFormat.OpenXML.Spreadsheet.Sheets	块级结构（如工作表、文件版本和 ISO/IEC 29500 规范中指定的其他项）的容器。
<sheet/>	DocumentFormat.OpenXml.Spreadsheet.Sheet	指向工作表定义文件的工作表。
<worksheet/>	DocumentFormat.OpenXML.Spreadsheet。 Worksheet	包含工作表数据的工作表定义文件。
<sheetData/>	DocumentFormat.OpenXML.Spreadsheet.SheetData	按行分组在一起的单元格表。
<row/>	DocumentFormat.OpenXml.Spreadsheet.Row	单元格表中的行。
<c/>	DocumentFormat.OpenXml.Spreadsheet.Cell	行中的单元格。
<v/>	DocumentFormat.OpenXml.Spreadsheet.CellValue	单元格的值。

示例代码的工作方式

打开 SpreadsheetDocument 文档进行编辑后，代码会将空白 Worksheet 对象 SpreadsheetDocument 插入文档包。 然后，在新工作表中插入一个新 Cell 对象，并将指定的文本插入到该单元格中。

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

代码传入一个参数，该参数表示要插入单元格中的文本，以及一个表示 SharedStringTablePart 电子表格对象的参数。 ShareStringTablePart如果对象不包含 SharedStringTable 对象，则代码将创建一个对象。 如果对象中 ShareStringTable 已存在文本，则代码返回表示文本的 SharedStringItem 对象的索引。 否则，它将创建一个表示文本的新 SharedStringItem 对象。

以下代码验证对象中 SharedStringTablePart 是否存在指定的文本，如果不存在，则添加该文本。

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

代码使用 AddNewPart 方法将WorkbookPart新WorksheetPart对象添加到 对象。 然后，它会向 WorksheetPart 对象添加一个新Worksheet对象，并通过选择电子表格文档中使用的最大SheetId对象并添加一个对象来创建新的工作表 ID 来获取新工作表的唯一 ID。 它通过将"Sheet"一词和工作表 ID 连接在一起来指定工作表的名称。 然后，它将新 Sheet 对象追加到集合中 Sheets 。

下面的代码通过向 对象添加新对象来WorkbookPart插入Worksheet新WorksheetPart对象。

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

为了将单元格插入到工作表中，代码通过按顺序循环访问行元素以查找紧跟在指定行后面的单元格来确定要将新单元格插入到列中的位置。 它将该行保存在 变量中 refCell 。 然后，它将新单元格插入到使用 InsertBefore 方法引用的refCell单元格之前。

在以下代码中，将新 Cell 对象插入到 对象中 Worksheet 。

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# 和 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

另请参阅

Open XML SDK 类库参考