Eliminar texto de una celda de un documento de hoja de cálculo

En este tema se muestra cómo usar las clases del SDK de Open XML para Office para eliminar texto de una celda de un documento de hoja de cálculo mediante programación.

Estructura básica de un documento spreadsheetML

La estructura de documento básica de un documento SpreadsheetML se compone de elementos Sheets y Sheet, que hacen referencia a las hojas de cálculo en el libro. Se crea un archivo XML independiente para cada hoja de cálculo. Por ejemplo, SpreadsheetML para un Workbook que tiene dos hojas de cálculo MySheet1 y MySheet2 se encuentra en el archivo Workbook.xml y se muestra en el siguiente ejemplo de código.

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

Los archivos XML de hoja de cálculo contienen uno o varios elementos de nivel de bloque, como sheetData , que representa la tabla de celdas y contiene uno o varios elementos Row . Un elemento row contiene uno o más elementos Cell. Cada celda contiene un elemento CellValue que representa el valor de la celda. Por ejemplo, el SpreadsheetML para la primera hoja de cálculo del libro, que solo tiene el valor 100 en la celda A1, se encuentra en el archivo Sheet1.xml y se muestra en el siguiente ejemplo 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>

Con el SDK de Open XML, puede crear la estructura de documentos y el contenido que usa clases fuertemente tipadas que corresponden a elementos SpreadsheetML . Puede encontrar estas clases en el espacio de nombres DocumentFormat.OpenXML.Spreadsheet. En la tabla siguiente se enumeran los nombres de las clases que corresponden a los elementos workbook, sheets, sheet, worksheet y sheetData.

Elemento de SpreadsheetML	Open XML SDK (clase)	Descripción
libro de trabajo	DocumentFormat.OpenXML.Spreadsheet.Workbook	El elemento raíz del elemento de documento principal.
sheets	DocumentFormat.OpenXML.Spreadsheet.Sheets	El contenedor de las estructuras del nivel de bloque, como sheet, fileVersion y otras que se detallan en la especificación ISO/IEC 29500.
sheet	DocumentFormat.OpenXml.Spreadsheet.Sheet	Una hoja que apunta a un archivo de definición de hoja.
hoja de cálculo	DocumentFormat.OpenXML.Spreadsheet. Worksheet	Un archivo de definición de hoja que contiene los datos de la hoja.
sheetData	DocumentFormat.OpenXML.Spreadsheet.SheetData	La tabla de celdas agrupadas por filas.
row	DocumentFormat.OpenXml.Spreadsheet.Row	Una fila en una tabla de celdas.
c	DocumentFormat.OpenXml.Spreadsheet.Cell	Una celda en una fila.
v	DocumentFormat.OpenXml.Spreadsheet.CellValue	El valor de una celda.

Funcionamiento del código de ejemplo

En el ejemplo de código siguiente, se elimina texto de una celda de un paquete de documentos SpreadsheetDocument . A continuación, compruebe si otras celdas del documento de hoja de cálculo siguen haciendo referencia al texto quitado de la fila y, si no lo hacen, quita el texto del objeto SharedStringTablePart mediante el método Remove . A continuación, limpie el objeto SharedStringTablePart llamando al método RemoveSharedStringItem .

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

En el siguiente ejemplo de código, se comprueba que la celda especificada por el nombre de columna y el índice de fila existe. Si es así, el código devuelve la celda; de lo contrario, devuelve 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.
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

En el siguiente ejemplo de código, se comprueba si otras celdas del documento de hoja de cálculo hacen referencia al texto especificado por el parámetro shareStringId. Si no lo hacen, quítelas del objeto SharedStringTablePart. Para ello, pase un parámetro que represente el identificador del texto que se va a quitar y un parámetro que represente el paquete de documentos SpreadsheetDocument. A continuación, realice iteraciones por todos los objetos Worksheet y compare el contenido de cada objeto Cell con el identificador de cadena compartida. Si otras celdas del documento de hoja de cálculo siguen haciendo referencia al objeto SharedStringItem , no se quita el elemento del objeto SharedStringTablePart . Si otras celdas del documento de hoja de cálculo ya no continúan haciendo referencia al objeto SharedStringItem, quite el elemento del objeto SharedStringTablePart. A continuación, realice iteraciones por todos los objetos Worksheet y Cell, y actualice las referencias de cadena compartida. Por último, guarde la hoja de cálculo y el objeto SharedStringTable .

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

Código de ejemplo

El siguiente es el ejemplo de código completo en C# y Visual Basic.

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

Consulte también

Referencia de la biblioteca de clases del SDK de Open XML