Löschen von Text aus einer Zelle in einem Tabellenkalkulationsdokument

In diesem Thema wird gezeigt, wie Sie die Klassen im Open XML SDK für Office verwenden, um Text programmgesteuert aus einer Zelle in einem Tabellenkalkulationsdokument zu löschen.

Grundlegende Struktur eines SpreadsheetML-Dokuments

Die grundlegende Struktur eines SpreadsheetML-Dokuments besteht aus den Sheets and Sheet-Elementen, die auf Arbeitsblätter in der Arbeitsmappe verweisen. Es wird eine separate XML-Datei für jedes Arbeitsblatt erstellt. Beispiel: SpreadsheetML für eine Arbeitsmappe, die zwei Arbeitsblätter „MySheet1“ und „MySheet2“ enthält, befindet sich in der Datei „Workbook.xml“ und wird im nachstehenden Codebeispiel angezeigt.

    <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> 
    <workbook xmlns=https://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r="https://schemas.openxmlformats.org/officeDocument/2006/relationships">
        <sheets>
            <sheet name="MySheet1" sheetId="1" r:id="rId1" /> 
            <sheet name="MySheet2" sheetId="2" r:id="rId2" /> 
        </sheets>
    </workbook>

Die XML-Dateien des Arbeitsblatts enthalten ein oder mehrere Elemente auf Blockebene, z. B. sheetData stellt die Zelltabelle dar und enthält ein oder mehrere Row-Elemente . Eine Zeile enthält ein oder mehrere Cell-Elemente. Jede Zelle enthält ein CellValue-Element, das den Wert der Zelle darstellt. Beispiel: SpreadsheetML für das erste Arbeitsblatt in einer Arbeitsmappe, das nur den Wert „100“ in Zelle A1 aufweist, befindet sich in der Datei „Sheet1.xml“ und wird im nachstehenden Codebeispiel angezeigt.

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

Mit dem Open XML SDK können Sie Dokumentstrukturen und Inhalte erstellen, die stark typisierte Klassen verwenden, die SpreadsheetML-Elementen entsprechen. Diese Klassen sind im DocumentFormat.OpenXML.Spreadsheet-Namespace enthalten. Die folgende Tabelle enthält die Namen der Klassen, die den Elementen workbook, sheets, sheet, worksheet und sheetData entsprechen.

SpreadsheetML-Element	Open XML SDK-Klasse	Beschreibung
workbook	DocumentFormat.OpenXML.Spreadsheet.Workbook	Das Stammelement des Hauptdokumentteils.
sheets	DocumentFormat.OpenXML.Spreadsheet.Sheets	Der Container für die Strukturen auf Blockebene, wie z. B. "sheet", "fileVersion" und andere Elemente, die in der Spezifikation ISO/IEC 29500 angegeben sind.
sheet	DocumentFormat.OpenXml.Spreadsheet.Sheet	Ein Blatt, das auf eine Blattdefinitionsdatei zeigt.
worksheet	DocumentFormat.OpenXML.Spreadsheet. Worksheet	Eine Blattdefinitionsdatei, welche die Blattdaten enthält.
sheetData	DocumentFormat.OpenXML.Spreadsheet.SheetData	Die Zellentabelle, die mithilfe von Zeilen gruppiert wird.
row	DocumentFormat.OpenXml.Spreadsheet.Row	Eine Zeile in der Zellentabelle.
c	DocumentFormat.OpenXml.Spreadsheet.Cell	Eine Zelle in einer Zeile.
v	DocumentFormat.OpenXml.Spreadsheet.CellValue	Der Wert einer Zelle.

Funktionsweise des Beispielcodes

Im folgenden Codebeispiel löschen Sie Text aus einer Zelle in einem SpreadsheetDocument-Dokumentpaket . Anschließend überprüfen Sie, ob andere Zellen innerhalb des Tabellenkalkulationsdokuments weiterhin auf den aus der Zeile entfernten Text verweisen. Andernfalls entfernen Sie den Text mithilfe der Remove-Methode aus dem SharedStringTablePart-Objekt. Anschließend sauber Sie das SharedStringTablePart-Objekt hoch, indem Sie die RemoveSharedStringItem-Methode aufrufen.

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();
        worksheetPart.Worksheet.Save();
    }
}

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 sheet.
Public Sub DeleteTextFromCell(ByVal docName As String, ByVal sheetName As String, ByVal colName As String, ByVal rowIndex As UInteger)
    ' Open the document for editing.
    Dim document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)

    Using (document)
        Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)().Where(Function(s) s.Name = sheetName.ToString())
        If (sheets.Count = 0) Then
            ' The specified worksheet does not exist.
            Return
        End If

        Dim relationshipId As String = sheets.First.Id.Value
        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()
        worksheetPart.Worksheet.Save()

    End Using
End Sub

Im folgenden Codebeispiel überprüfen Sie, ob die durch den Spaltennamen und den Zeilenindex angegebene Zelle vorhanden ist. Wenn ja, gibt der Code die Zelle zurück. Andernfalls wird NULL zurückgegeben.

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.
Private Function GetSpreadsheetCell(ByVal worksheet As Worksheet, ByVal columnName As String, ByVal rowIndex As UInteger) As Cell
    Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)().Elements(Of Row)().Where(Function(r) r.RowIndex = rowIndex.ToString())
    If (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.ToString(), 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.First
End Function

Im folgenden Codebeispiel überprüfen Sie, ob andere Zellen im Tabellenkalkulationsdokument den vom shareStringId-Parameter angegebenen Text referenzieren. Falls nicht, entfernen Sie den Text aus dem SharedStringTablePart-Objekt. Hierzu übergeben Sie einen Parameter, der die ID des zu entfernenden Texts darstellt, und einen Parameter, der das SpreadsheetDocument-Dokumentpaket darstellt. Anschließend durchlaufen Sie die einzelnen Worksheet-Objekte und vergleichen den Inhalt der einzelnen Cell-Objekte mit der SharedString-ID. Wenn andere Zellen innerhalb des Tabellenkalkulationsdokuments weiterhin auf das SharedStringItem-Objekt verweisen, entfernen Sie das Element nicht aus dem SharedStringTablePart-Objekt . Wenn andere Zellen im Tabellenkalkulationsdokument das SharedStringItem-Objekt nicht mehr referenzieren, entfernen Sie es aus dem SharedStringTablePart-Objekt. Dann durchlaufen Sie die einzelnen Worksheet-Objekte und Cell-Objekte und aktualisieren die SharedString-Verweise. Abschließend speichern Sie das Arbeitsblatt und das SharedStringTable-Objekt .

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();
                        }
                    }
                }
                part.Worksheet.Save();
            }

            document.WorkbookPart.SharedStringTablePart?.SharedStringTable.Save();
        }
    }
}

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.
Private Sub RemoveSharedStringItem(ByVal shareStringId As Integer, ByVal document As SpreadsheetDocument)
    Dim remove As Boolean = True

    For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
        Dim worksheet As Worksheet = part.Worksheet
        For Each cell In worksheet.GetFirstChild(Of SheetData)().Descendants(Of Cell)()
            ' 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
            Exit Sub
        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 worksheet As Worksheet = part.Worksheet
                For Each cell In worksheet.GetFirstChild(Of SheetData)().Descendants(Of Cell)()
                    If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
                        Dim itemIndex As Integer = Integer.Parse(cell.CellValue.Text)
                        If itemIndex > shareStringId Then
                            cell.CellValue.Text = (itemIndex - 1).ToString()
                        End If
                    End If
                Next
                worksheet.Save()
            Next

            document.WorkbookPart.SharedStringTablePart.SharedStringTable.Save()
        End If
    End If
End Sub

Beispielcode

Nachstehend ist der vollständige Beispielcode in C# und Visual Basic aufgeführt.

C#

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

// 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();
        worksheetPart.Worksheet.Save();
    }
}

// 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();
                        }
                    }
                }
                part.Worksheet.Save();
            }

            document.WorkbookPart.SharedStringTablePart?.SharedStringTable.Save();
        }
    }
}

Visual Basic

Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet


Module MyModule

    Sub Main(args As String())
        DeleteTextFromCell(args(0), args(1), args(2), UInteger.Parse(args(3)))
    End Sub

    ' 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 sheet.
    Public Sub DeleteTextFromCell(ByVal docName As String, ByVal sheetName As String, ByVal colName As String, ByVal rowIndex As UInteger)
        ' Open the document for editing.
        Dim document As SpreadsheetDocument = SpreadsheetDocument.Open(docName, True)

        Using (document)
            Dim sheets As IEnumerable(Of Sheet) = document.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)().Where(Function(s) s.Name = sheetName.ToString())
            If (sheets.Count = 0) Then
                ' The specified worksheet does not exist.
                Return
            End If

            Dim relationshipId As String = sheets.First.Id.Value
            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()
            worksheetPart.Worksheet.Save()

        End Using
    End Sub

    ' Given a worksheet, a column name, and a row index, gets the cell at the specified column and row.
    Private Function GetSpreadsheetCell(ByVal worksheet As Worksheet, ByVal columnName As String, ByVal rowIndex As UInteger) As Cell
        Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)().Elements(Of Row)().Where(Function(r) r.RowIndex = rowIndex.ToString())
        If (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.ToString(), 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.First
    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.
    Private Sub RemoveSharedStringItem(ByVal shareStringId As Integer, ByVal document As SpreadsheetDocument)
        Dim remove As Boolean = True

        For Each part In document.WorkbookPart.GetPartsOfType(Of WorksheetPart)()
            Dim worksheet As Worksheet = part.Worksheet
            For Each cell In worksheet.GetFirstChild(Of SheetData)().Descendants(Of Cell)()
                ' 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
                Exit Sub
            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 worksheet As Worksheet = part.Worksheet
                    For Each cell In worksheet.GetFirstChild(Of SheetData)().Descendants(Of Cell)()
                        If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
                            Dim itemIndex As Integer = Integer.Parse(cell.CellValue.Text)
                            If itemIndex > shareStringId Then
                                cell.CellValue.Text = (itemIndex - 1).ToString()
                            End If
                        End If
                    Next
                    worksheet.Save()
                Next

                document.WorkbookPart.SharedStringTablePart.SharedStringTable.Save()
            End If
        End If
    End Sub
End Module

Siehe auch

Referenz zur Open XML SDK-Klassenbibliothek