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 básica de un SpreadsheetML documento consta de los Sheets elementos y Sheet , que hacen referencia a las hojas de cálculo del libro. Se crea un archivo XML independiente para cada hoja de cálculo. Por ejemplo, para SpreadsheetML un Workbook objeto que tiene dos hojas de cálculo denominados MySheet1 y MySheet2 se encuentra en el archivo Workbook.xml y se muestra en el ejemplo de código siguiente.

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

Los archivos XML de hoja de cálculo contienen uno o varios elementos de nivel de bloque, como SheetData representa la tabla de celdas y contiene uno o varios Row elementos. contiene row uno o varios Cell elementos. Cada celda contiene un CellValue elemento que representa el valor de la celda. Por ejemplo, para SpreadsheetML la primera hoja de cálculo de un libro, que solo tiene el valor 100 en la celda A1, se encuentra en el archivo Sheet1.xml y se muestra en el ejemplo de código siguiente.

    <?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 SpreadsheetML elementos. Puede encontrar estas clases en el espacio de DocumentFormat.OpenXML.Spreadsheet nombres. En la tabla siguiente se enumeran los nombres de clase de las clases correspondientes a los workbookelementos , sheets, sheet, worksheety sheetData .

Elemento de SpreadsheetML	Open XML SDK (clase)	Descripción
<workbook/>	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.
<worksheet/>	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 SpreadsheetDocument paquete de documento. 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 SharedStringTablePart objeto mediante el Remove método . A continuación, limpie el SharedStringTablePart objeto llamando al 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

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

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 shareStringId parámetro . Si no hacen referencia al texto, se quita del SharedStringTablePart objeto . Para ello, pase un parámetro que represente el identificador del texto que se va a quitar y un parámetro que representa el paquete de SpreadsheetDocument documento. A continuación, recorre en iteración cada Worksheet objeto y compara el contenido de cada Cell objeto con el identificador de cadena compartida. Si otras celdas del documento de hoja de cálculo siguen haciendo referencia al SharedStringItem objeto, no se quita el elemento del SharedStringTablePart objeto. Si otras celdas del documento de hoja de cálculo ya no hacen referencia al SharedStringItem objeto, se quita el elemento del SharedStringTablePart objeto . A continuación, recorre en iteración cada Worksheet objeto y Cell objeto y actualiza las referencias de cadena compartidas.

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 ejemplo

El siguiente es el ejemplo de código completo en C# y 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

Recursos adicionales

Referencia de la biblioteca de clases del SDK de Open XML