Eliminar texto de uma 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 eliminar texto de uma célula 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

No exemplo de código seguinte, elimina texto de uma célula num pacote de SpreadsheetDocument documentos. Em seguida, verifique se outras células no documento da folha de cálculo ainda referenciam o texto removido da linha e, se não o fizerem, remova o SharedStringTablePart texto do objeto com o Remove método . Em seguida, limpo o SharedStringTablePart objeto ao chamar o RemoveSharedStringItem método .

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

No exemplo de código seguinte, verifique se a célula especificada pelo nome da coluna e pelo índice de linhas existe. Em caso afirmativo, o código devolve a célula; caso contrário, devolve 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

No exemplo de código seguinte, verifique se outras células no documento de folha de cálculo referenciam o texto especificado pelo shareStringId parâmetro . Se não referenciarem o texto, remova-o do SharedStringTablePart objeto. Pode fazê-lo ao transmitir um parâmetro que representa o ID do texto a remover e um parâmetro que representa o pacote de SpreadsheetDocument documentos. Em seguida, itera cada Worksheet objeto e compara os conteúdos de cada Cell objeto com o ID da cadeia partilhada. Se outras células no documento de folha de cálculo ainda referenciar o SharedStringItem objeto, não removerá o item do SharedStringTablePart objeto. Se outras células no documento da folha de cálculo já não referenciar o SharedStringItem objeto, remova o item do SharedStringTablePart objeto. Em seguida, itera cada Worksheet objeto e Cell objeto e atualiza as referências de cadeia partilhada.

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ódigo de exemplo

A seguir está um exemplo de código completo em C# e 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

Confira também

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