从电子表格文档中的单元格中删除文本

本主题演示如何使用 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 单元格中删除文本。 然后，验证电子表格文档中的其他单元格是否仍引用从行中删除的文本，如果没有，则使用 Remove 方法删除对象中的SharedStringTablePart文本。 然后，通过调用 RemoveSharedStringItem 方法清理 SharedStringTablePart 对象。

C#

// Given a document, a worksheet name, a column name, and a one-based row index,
// deletes the text from the cell at the specified column and row on the specified worksheet.
static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)
{
    // Open the document for editing.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
    {
        IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook?.GetFirstChild<Sheets>()?.Elements<Sheet>()?.Where(s => s.Name is not null && s.Name == sheetName);
        if (sheets is null || sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return;
        }
        string? relationshipId = sheets.First()?.Id?.Value;

        if (relationshipId is null)
        {
            // The worksheet does not have a relationship ID.
            return;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(relationshipId);

        // Get the cell at the specified column and row.
        Cell? cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
        if (cell is null)
        {
            // The specified cell does not exist.
            return;
        }

        cell.Remove();
    }
}

Visual Basic

' Given a document, a worksheet name, a column name, and a one-based row index,
' deletes the text from the cell at the specified column and row on the specified worksheet.
Sub DeleteTextFromCell(docName As String, sheetName As String, colName As String, rowIndex As UInteger)
    ' Open the document for editing.
    Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart?.Workbook?.GetFirstChild(Of Sheets)()?.Elements(Of Sheet)()?.Where(Function(s) s.Name IsNot Nothing AndAlso s.Name = sheetName)
        If sheets Is Nothing OrElse sheets.Count() = 0 Then
            ' The specified worksheet does not exist.
            Return
        End If
        Dim relationshipId As String = sheets.First()?.Id?.Value

        If relationshipId Is Nothing Then
            ' The worksheet does not have a relationship ID.
            Return
        End If

        Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(relationshipId), WorksheetPart)

        ' Get the cell at the specified column and row.
        Dim cell As Cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex)
        If cell Is Nothing Then
            ' The specified cell does not exist.
            Return
        End If

        cell.Remove()
    End Using
End Sub

在下面的代码示例中，将验证通过列名称和行索引指定的单元格是否存在。 如果是这样，代码将返回 单元格;否则，它将返回 null。

C#

// Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
static Cell? GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
    IEnumerable<Row>? rows = worksheet.GetFirstChild<SheetData>()?.Elements<Row>().Where(r => r.RowIndex is not null && r.RowIndex == rowIndex);
    if (rows is null || rows.Count() == 0)
    {
        // A cell does not exist at the specified row.
        return null;
    }

    IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference?.Value, columnName + rowIndex, true) == 0);

    if (cells.Count() == 0)
    {
        // A cell does not exist at the specified column, in the specified row.
        return null;
    }

    return cells.FirstOrDefault();
}

Visual Basic

' Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
Function GetSpreadsheetCell(worksheet As Worksheet, columnName As String, rowIndex As UInteger) As Cell
    Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)()?.Elements(Of Row)().Where(Function(r) r.RowIndex IsNot Nothing AndAlso r.RowIndex.Equals(rowIndex))
    If rows Is Nothing OrElse rows.Count() = 0 Then
        ' A cell does not exist at the specified row.
        Return Nothing
    End If

    Dim cells As IEnumerable(Of Cell) = rows.First().Elements(Of Cell)().Where(Function(c) String.Compare(c.CellReference?.Value, columnName & rowIndex, True) = 0)

    If cells.Count() = 0 Then
        ' A cell does not exist at the specified column, in the specified row.
        Return Nothing
    End If

    Return cells.FirstOrDefault()
End Function

在下面的代码示例中，验证电子表格文档中的其他单元格是否引用 参数 shareStringId 指定的文本。 如果它们不引用文本，则将其从 对象中删除 SharedStringTablePart 。 为此，可以传递表示要删除的文本 ID 的参数和表示文档包的参数 SpreadsheetDocument 。 然后循环访问每个 Worksheet 对象，并将每个 Cell 对象的内容与共享字符串 ID 进行比较。 如果电子表格文档中的其他单元格仍引用对象 SharedStringItem ，则不会从 SharedStringTablePart 对象中删除该项。 如果电子表格文档中的其他单元格不再引用对象 SharedStringItem ，则从 SharedStringTablePart 对象中删除该项。 然后循环访问每个 Worksheet 对象和 Cell 对象并刷新共享字符串引用。

C#

// Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
// reference the specified SharedStringItem and removes the item.
static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document)
{
    bool remove = true;

    if (document.WorkbookPart is null)
    {
        return;
    }

    foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
    {
        var cells = part.Worksheet.GetFirstChild<SheetData>()?.Descendants<Cell>();

        if (cells is null)
        {
            continue;
        }

        foreach (var cell in cells)
        {
            // Verify if other cells in the document reference the item.
            if (cell.DataType is not null &&
                cell.DataType.Value == CellValues.SharedString &&
                cell.CellValue?.Text == shareStringId.ToString())
            {
                // Other cells in the document still reference the item. Do not remove the item.
                remove = false;
                break;
            }
        }

        if (!remove)
        {
            break;
        }
    }

    // Other cells in the document do not reference the item. Remove the item.
    if (remove)
    {
        SharedStringTablePart? shareStringTablePart = document.WorkbookPart.SharedStringTablePart;

        if (shareStringTablePart is null)
        {
            return;
        }

        SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);
        if (item is not null)
        {
            item.Remove();

            // Refresh all the shared string references.
            foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
            {
                var cells = part.Worksheet.GetFirstChild<SheetData>()?.Descendants<Cell>();

                if (cells is null)
                {
                    continue;
                }

                foreach (var cell in cells)
                {
                    if (cell.DataType is not null && cell.DataType.Value == CellValues.SharedString && int.TryParse(cell.CellValue?.Text, out int itemIndex))
                    {
                        if (itemIndex > shareStringId)
                        {
                            cell.CellValue.Text = (itemIndex - 1).ToString();
                        }
                    }
                }
            }
        }
    }
}

Visual Basic

' Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
' reference the specified SharedStringItem and removes the item.
Sub RemoveSharedStringItem(shareStringId As Integer, document As SpreadsheetDocument)
    Dim remove As Boolean = True

    If document.WorkbookPart Is Nothing Then
        Return
    End If

    For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
        Dim cells = part.Worksheet.GetFirstChild(Of SheetData)()?.Descendants(Of Cell)()

        If cells Is Nothing Then
            Continue For
        End If

        For Each cell In cells
            ' Verify if other cells in the document reference the item.
            If cell.DataType IsNot Nothing AndAlso
               cell.DataType.Value = CellValues.SharedString AndAlso
               cell.CellValue?.Text = shareStringId.ToString() Then
                ' Other cells in the document still reference the item. Do not remove the item.
                remove = False
                Exit For
            End If
        Next

        If Not remove Then
            Exit For
        End If
    Next

    ' Other cells in the document do not reference the item. Remove the item.
    If remove Then
        Dim shareStringTablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart

        If shareStringTablePart Is Nothing Then
            Return
        End If

        Dim item As SharedStringItem = shareStringTablePart.SharedStringTable.Elements(Of SharedStringItem)().ElementAt(shareStringId)
        If item IsNot Nothing Then
            item.Remove()

            ' Refresh all the shared string references.
            For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
                Dim cells = part.Worksheet.GetFirstChild(Of SheetData)()?.Descendants(Of Cell)()

                If cells Is Nothing Then
                    Continue For
                End If

                For Each cell In cells
                    Dim itemIndex As Integer

                    If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString AndAlso Integer.TryParse(cell.CellValue?.Text, itemIndex) Then
                        If itemIndex > shareStringId Then
                            cell.CellValue.Text = (itemIndex - 1).ToString()
                        End If
                    End If
                Next
            Next
        End If
    End If
End Sub

示例代码

以下是使用 C# 和 Visual Basic 编写的完整示例代码。

C#

static void DeleteTextFromCell(string docName, string sheetName, string colName, uint rowIndex)
{
    // Open the document for editing.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
    {
        IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook?.GetFirstChild<Sheets>()?.Elements<Sheet>()?.Where(s => s.Name is not null && s.Name == sheetName);
        if (sheets is null || sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return;
        }
        string? relationshipId = sheets.First()?.Id?.Value;

        if (relationshipId is null)
        {
            // The worksheet does not have a relationship ID.
            return;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(relationshipId);

        // Get the cell at the specified column and row.
        Cell? cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex);
        if (cell is null)
        {
            // The specified cell does not exist.
            return;
        }

        cell.Remove();
    }
}

// Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
static Cell? GetSpreadsheetCell(Worksheet worksheet, string columnName, uint rowIndex)
{
    IEnumerable<Row>? rows = worksheet.GetFirstChild<SheetData>()?.Elements<Row>().Where(r => r.RowIndex is not null && r.RowIndex == rowIndex);
    if (rows is null || rows.Count() == 0)
    {
        // A cell does not exist at the specified row.
        return null;
    }

    IEnumerable<Cell> cells = rows.First().Elements<Cell>().Where(c => string.Compare(c.CellReference?.Value, columnName + rowIndex, true) == 0);

    if (cells.Count() == 0)
    {
        // A cell does not exist at the specified column, in the specified row.
        return null;
    }

    return cells.FirstOrDefault();
}

// Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
// reference the specified SharedStringItem and removes the item.
static void RemoveSharedStringItem(int shareStringId, SpreadsheetDocument document)
{
    bool remove = true;

    if (document.WorkbookPart is null)
    {
        return;
    }

    foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
    {
        var cells = part.Worksheet.GetFirstChild<SheetData>()?.Descendants<Cell>();

        if (cells is null)
        {
            continue;
        }

        foreach (var cell in cells)
        {
            // Verify if other cells in the document reference the item.
            if (cell.DataType is not null &&
                cell.DataType.Value == CellValues.SharedString &&
                cell.CellValue?.Text == shareStringId.ToString())
            {
                // Other cells in the document still reference the item. Do not remove the item.
                remove = false;
                break;
            }
        }

        if (!remove)
        {
            break;
        }
    }

    // Other cells in the document do not reference the item. Remove the item.
    if (remove)
    {
        SharedStringTablePart? shareStringTablePart = document.WorkbookPart.SharedStringTablePart;

        if (shareStringTablePart is null)
        {
            return;
        }

        SharedStringItem item = shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);
        if (item is not null)
        {
            item.Remove();

            // Refresh all the shared string references.
            foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
            {
                var cells = part.Worksheet.GetFirstChild<SheetData>()?.Descendants<Cell>();

                if (cells is null)
                {
                    continue;
                }

                foreach (var cell in cells)
                {
                    if (cell.DataType is not null && cell.DataType.Value == CellValues.SharedString && int.TryParse(cell.CellValue?.Text, out int itemIndex))
                    {
                        if (itemIndex > shareStringId)
                        {
                            cell.CellValue.Text = (itemIndex - 1).ToString();
                        }
                    }
                }
            }
        }
    }
}

Visual Basic

Sub DeleteTextFromCell(docName As String, sheetName As String, colName As String, rowIndex As UInteger)
    ' Open the document for editing.
    Using document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)
        Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart?.Workbook?.GetFirstChild(Of Sheets)()?.Elements(Of Sheet)()?.Where(Function(s) s.Name IsNot Nothing AndAlso s.Name = sheetName)
        If sheets Is Nothing OrElse sheets.Count() = 0 Then
            ' The specified worksheet does not exist.
            Return
        End If
        Dim relationshipId As String = sheets.First()?.Id?.Value

        If relationshipId Is Nothing Then
            ' The worksheet does not have a relationship ID.
            Return
        End If

        Dim worksheetPart As WorksheetPart = CType(document.WorkbookPart.GetPartById(relationshipId), WorksheetPart)

        ' Get the cell at the specified column and row.
        Dim cell As Cell = GetSpreadsheetCell(worksheetPart.Worksheet, colName, rowIndex)
        If cell Is Nothing Then
            ' The specified cell does not exist.
            Return
        End If

        cell.Remove()
    End Using
End Sub

' Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
Function GetSpreadsheetCell(worksheet As Worksheet, columnName As String, rowIndex As UInteger) As Cell
    Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)()?.Elements(Of Row)().Where(Function(r) r.RowIndex IsNot Nothing AndAlso r.RowIndex.Equals(rowIndex))
    If rows Is Nothing OrElse rows.Count() = 0 Then
        ' A cell does not exist at the specified row.
        Return Nothing
    End If

    Dim cells As IEnumerable(Of Cell) = rows.First().Elements(Of Cell)().Where(Function(c) String.Compare(c.CellReference?.Value, columnName & rowIndex, True) = 0)

    If cells.Count() = 0 Then
        ' A cell does not exist at the specified column, in the specified row.
        Return Nothing
    End If

    Return cells.FirstOrDefault()
End Function

' Given a shared string ID and a SpreadsheetDocument, verifies that other cells in the document no longer 
' reference the specified SharedStringItem and removes the item.
Sub RemoveSharedStringItem(shareStringId As Integer, document As SpreadsheetDocument)
    Dim remove As Boolean = True

    If document.WorkbookPart Is Nothing Then
        Return
    End If

    For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
        Dim cells = part.Worksheet.GetFirstChild(Of SheetData)()?.Descendants(Of Cell)()

        If cells Is Nothing Then
            Continue For
        End If

        For Each cell In cells
            ' Verify if other cells in the document reference the item.
            If cell.DataType IsNot Nothing AndAlso
               cell.DataType.Value = CellValues.SharedString AndAlso
               cell.CellValue?.Text = shareStringId.ToString() Then
                ' Other cells in the document still reference the item. Do not remove the item.
                remove = False
                Exit For
            End If
        Next

        If Not remove Then
            Exit For
        End If
    Next

    ' Other cells in the document do not reference the item. Remove the item.
    If remove Then
        Dim shareStringTablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart

        If shareStringTablePart Is Nothing Then
            Return
        End If

        Dim item As SharedStringItem = shareStringTablePart.SharedStringTable.Elements(Of SharedStringItem)().ElementAt(shareStringId)
        If item IsNot Nothing Then
            item.Remove()

            ' Refresh all the shared string references.
            For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
                Dim cells = part.Worksheet.GetFirstChild(Of SheetData)()?.Descendants(Of Cell)()

                If cells Is Nothing Then
                    Continue For
                End If

                For Each cell In cells
                    Dim itemIndex As Integer

                    If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString AndAlso Integer.TryParse(cell.CellValue?.Text, itemIndex) Then
                        If itemIndex > shareStringId Then
                            cell.CellValue.Text = (itemIndex - 1).ToString()
                        End If
                    End If
                Next
            Next
        End If
    End If
End Sub

另请参阅

Open XML SDK 类库参考