Share via

Calcular la suma de un rango de celdas en un documento de hoja de cálculo

  • Artículo

En este tema se muestra cómo usar las clases del SDK de Open XML para Office para calcular mediante programación la suma de un rango contiguo de celdas en un documento de hoja de cálculo.

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

Para comenzar, el código de ejemplo pasa al método un parámetro CalculateSumOfCellRange que representa la ruta de acceso completa al archivo SpreadsheetML de origen, un parámetro que representa el nombre de la hoja de cálculo que contiene las celdas, un parámetro que representa el nombre de la primera celda en el intervalo contiguo, un parámetro que representa el nombre de la última celda en el intervalo contiguo y un parámetro que representa el nombre de la celda donde desea ver el resultado.

A continuación, el código abre el archivo para su edición como un paquete de documentos SpreadsheetDocument para acceso de lectura y escritura y obtiene el objeto Worksheet especificado. Posteriormente, obtiene el índice de fila de la primera y de la última celda del intervalo contiguo mediante una llamada al método GetRowIndex. También obtiene el nombre de columna de la primera y de la última celda del intervalo contiguo mediante una llamada al método GetColumnName.

Para cada objeto Row dentro del intervalo contiguo, el código procesa una iteración de cada objeto Cell y determina si la columna de la celda se encuentra dentro del intervalo contiguo mediante una llamada al método CompareColumn. Si la celda se halla dentro del intervalo contiguo, el código agrega el valor de la celda a la suma. A continuación, obtiene el objeto SharedStringTablePart, si existe. Si no existe, crea uno mediante el método AddNewPart . Inserta el resultado en el objeto SharedStringTablePart mediante una llamada al método InsertSharedStringItem.

El código inserta una nueva celda para el resultado en la hoja de cálculo llamando al método InsertCellInWorksheet y estableciendo el valor de la celda. Para obtener más información, vea cómo insertar una celda en una hoja de cálculo y, a continuación, guardar la hoja de cálculo.

static void CalculateSumOfCellRange(string docName, string worksheetName, string firstCellName, string lastCellName, string resultCell)
{
    // Open the document for editing.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
    {
        IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
        string? firstId = sheets?.First().Id;
        if (sheets is null || firstId is null || sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(firstId);
        Worksheet worksheet = worksheetPart.Worksheet;

        // Get the row number and column name for the first and last cells in the range.
        uint firstRowNum = GetRowIndex(firstCellName);
        uint lastRowNum = GetRowIndex(lastCellName);
        string firstColumn = GetColumnName(firstCellName);
        string lastColumn = GetColumnName(lastCellName);

        double sum = 0;

        // Iterate through the cells within the range and add their values to the sum.
        foreach (Row row in worksheet.Descendants<Row>().Where(r => r.RowIndex is not null && r.RowIndex.Value >= firstRowNum && r.RowIndex.Value <= lastRowNum))
        {
            foreach (Cell cell in row)
            {
                if (cell.CellReference is not null && cell.CellReference.Value is not null)
                {
                    string columnName = GetColumnName(cell.CellReference.Value);
                    if (CompareColumn(columnName, firstColumn) >= 0 && CompareColumn(columnName, lastColumn) <= 0 && double.TryParse(cell.CellValue?.Text, out double num))
                    {
                        sum += num;
                    }
                }
            }
        }

        // Get the SharedStringTablePart and add the result to it.
        // If the SharedStringPart does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>();
        }

        // Insert the result into the SharedStringTablePart.
        int index = InsertSharedStringItem("Result: " + sum, shareStringPart);

        Cell result = InsertCellInWorksheet(GetColumnName(resultCell), GetRowIndex(resultCell), worksheetPart);

        // Set the value of the cell.
        result.CellValue = new CellValue(index.ToString());
        result.DataType = new EnumValue<CellValues>(CellValues.SharedString);

        worksheetPart.Worksheet.Save();
    }
}

Para obtener el índice de fila, el código pasa un parámetro que representa el nombre de la celda y crea una nueva expresión regular para que coincida con la parte del nombre de celda que corresponde al índice de fila. Para obtener más información sobre expresiones regulares, vea el tema sobre los elementos del lenguaje de expresiones regulares. Obtiene el índice de fila llamando al método Regex.Match y, a continuación, devuelve el índice de fila.

// Given a cell name, parses the specified cell to get the row index.
static uint GetRowIndex(string cellName)
{
    // Create a regular expression to match the row index portion the cell name.
    Regex regex = new Regex(@"\d+");
    Match match = regex.Match(cellName);

    return uint.Parse(match.Value);
}

Posteriormente, el código obtiene el nombre de columna; para ello, pasa un parámetro que representa el nombre de la celda y crea una nueva expresión regular para que coincida con la parte del nombre de celda que corresponde al nombre de columna. Esta expresión regular coincide con cualquier combinación de letras mayúsculas y minúsculas. Obtiene el nombre de columna llamando al método Regex.Match y, a continuación, devuelve el nombre de la columna.

// Given a cell name, parses the specified cell to get the column name.
static string GetColumnName(string cellName)
{
    // Create a regular expression to match the column name portion of the cell name.
    Regex regex = new Regex("[A-Za-z]+");
    Match match = regex.Match(cellName);

    return match.Value;
}

Para comparar dos columnas, el código pasa dos parámetros que representan las columnas que se van a comparar. Si la primera columna es más larga que la segunda, devuelve 1. Si la segunda columna es más larga que la primera, devuelve -1. De lo contrario, compara los valores de las columnas mediante compare y devuelve el resultado.

// Given two columns, compares the columns.
static int CompareColumn(string column1, string column2)
{
    if (column1.Length > column2.Length)
    {
        return 1;
    }
    else if (column1.Length < column2.Length)
    {
        return -1;
    }
    else
    {
        return string.Compare(column1, column2, true);
    }
}

Para insertar SharedStringItem, el código pasa un parámetro que representa el texto que se insertará en la celda y un parámetro que representa el objeto SharedStringTablePart de la hoja de cálculo. Si el objeto ShareStringTablePart no contiene un objeto SharedStringTable , se crea uno. Si el texto ya existe en el objeto ShareStringTable , devuelve el índice del objeto SharedStringItem que representa el texto. Si el texto no existe, crea un nuevo objeto SharedStringItem que representará el texto. A continuación, devuelve el índice del objeto SharedStringItem que representa el texto.

// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
    // If the part does not contain a SharedStringTable, create it.
    if (shareStringPart.SharedStringTable is null)
    {
        shareStringPart.SharedStringTable = new SharedStringTable();
    }

    int i = 0;
    foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
    {
        if (item.InnerText == text)
        {
            // The text already exists in the part. Return its index.
            return i;
        }

        i++;
    }

    // The text does not exist in the part. Create the SharedStringItem.
    shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
    shareStringPart.SharedStringTable.Save();

    return i;
}

El último paso consiste en insertar una celda en la hoja de cálculo. Para ello, el código pasa parámetros que representan el nombre de columna y el número de fila de la celda, y un parámetro que representa la hoja de cálculo que contiene la celda. Si la fila especificada no existe, crea la fila y la anexa a la hoja de cálculo. Si la columna especificada existe, busca la celda que coincide con la fila de esa columna y devuelve la celda. Si la columna especificada no existe, crea la columna y la inserta en la hoja de cálculo. A continuación, determina dónde se insertará la nueva celda de la columna; para ello, procesa una iteración de los elementos de la fila para buscar la celda inmediatamente posterior a la fila especificada, en orden secuencial. Guarda esta fila en la variable refCell. Inserta la nueva celda antes de la celda a la que hace referencia refCell mediante el método InsertBefore . A continuación, devuelve el nuevo objeto Cell.

// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
// If the cell already exists, returns it. 
static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
    Worksheet worksheet = worksheetPart.Worksheet;
    SheetData sheetData = worksheet.GetFirstChild<SheetData>() ?? worksheet.AppendChild(new SheetData());
    string cellReference = columnName + rowIndex;

    // If the worksheet does not contain a row with the specified row index, insert one.
    Row row;
    if (sheetData.Elements<Row>().Where(r => r.RowIndex is not null && r.RowIndex == rowIndex).Count() != 0)
    {
        row = sheetData.Elements<Row>().Where(r => r.RowIndex is not null && r.RowIndex == rowIndex).First();
    }
    else
    {
        row = new Row() { RowIndex = rowIndex };
        sheetData.Append(row);
    }

    // If there is not a cell with the specified column name, insert one.  
    if (row.Elements<Cell>().Where(c => c.CellReference is not null && c.CellReference.Value == columnName + rowIndex).Count() > 0)
    {
        return row.Elements<Cell>().Where(c => c.CellReference is not null && c.CellReference.Value == cellReference).First();
    }
    else
    {
        // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
        Cell? refCell = null;

        foreach (Cell cell in row.Elements<Cell>())
        {
            if (string.Compare(cell.CellReference?.Value, cellReference, true) > 0)
            {
                refCell = cell;
                break;
            }
        }

        Cell newCell = new Cell() { CellReference = cellReference };
        row.InsertBefore(newCell, refCell);

        worksheet.Save();
        return newCell;
    }
}

Código de ejemplo

A continuación se incluye el código de ejemplo completo en C# y Visual Basic.

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


static void CalculateSumOfCellRange(string docName, string worksheetName, string firstCellName, string lastCellName, string resultCell)
{
    // Open the document for editing.
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
    {
        IEnumerable<Sheet>? sheets = document.WorkbookPart?.Workbook.Descendants<Sheet>().Where(s => s.Name == worksheetName);
        string? firstId = sheets?.First().Id;
        if (sheets is null || firstId is null || sheets.Count() == 0)
        {
            // The specified worksheet does not exist.
            return;
        }

        WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart!.GetPartById(firstId);
        Worksheet worksheet = worksheetPart.Worksheet;

        // Get the row number and column name for the first and last cells in the range.
        uint firstRowNum = GetRowIndex(firstCellName);
        uint lastRowNum = GetRowIndex(lastCellName);
        string firstColumn = GetColumnName(firstCellName);
        string lastColumn = GetColumnName(lastCellName);

        double sum = 0;

        // Iterate through the cells within the range and add their values to the sum.
        foreach (Row row in worksheet.Descendants<Row>().Where(r => r.RowIndex is not null && r.RowIndex.Value >= firstRowNum && r.RowIndex.Value <= lastRowNum))
        {
            foreach (Cell cell in row)
            {
                if (cell.CellReference is not null && cell.CellReference.Value is not null)
                {
                    string columnName = GetColumnName(cell.CellReference.Value);
                    if (CompareColumn(columnName, firstColumn) >= 0 && CompareColumn(columnName, lastColumn) <= 0 && double.TryParse(cell.CellValue?.Text, out double num))
                    {
                        sum += num;
                    }
                }
            }
        }

        // Get the SharedStringTablePart and add the result to it.
        // If the SharedStringPart does not exist, create a new one.
        SharedStringTablePart shareStringPart;
        if (document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        {
            shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
        }
        else
        {
            shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>();
        }

        // Insert the result into the SharedStringTablePart.
        int index = InsertSharedStringItem("Result: " + sum, shareStringPart);

        Cell result = InsertCellInWorksheet(GetColumnName(resultCell), GetRowIndex(resultCell), worksheetPart);

        // Set the value of the cell.
        result.CellValue = new CellValue(index.ToString());
        result.DataType = new EnumValue<CellValues>(CellValues.SharedString);

        worksheetPart.Worksheet.Save();
    }
}

// Given a cell name, parses the specified cell to get the row index.
static uint GetRowIndex(string cellName)
{
    // Create a regular expression to match the row index portion the cell name.
    Regex regex = new Regex(@"\d+");
    Match match = regex.Match(cellName);

    return uint.Parse(match.Value);
}

// Given a cell name, parses the specified cell to get the column name.
static string GetColumnName(string cellName)
{
    // Create a regular expression to match the column name portion of the cell name.
    Regex regex = new Regex("[A-Za-z]+");
    Match match = regex.Match(cellName);

    return match.Value;
}

// Given two columns, compares the columns.
static int CompareColumn(string column1, string column2)
{
    if (column1.Length > column2.Length)
    {
        return 1;
    }
    else if (column1.Length < column2.Length)
    {
        return -1;
    }
    else
    {
        return string.Compare(column1, column2, true);
    }
}

// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text 
// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
    // If the part does not contain a SharedStringTable, create it.
    if (shareStringPart.SharedStringTable is null)
    {
        shareStringPart.SharedStringTable = new SharedStringTable();
    }

    int i = 0;
    foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
    {
        if (item.InnerText == text)
        {
            // The text already exists in the part. Return its index.
            return i;
        }

        i++;
    }

    // The text does not exist in the part. Create the SharedStringItem.
    shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
    shareStringPart.SharedStringTable.Save();

    return i;
}

// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet. 
// If the cell already exists, returns it. 
static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
    Worksheet worksheet = worksheetPart.Worksheet;
    SheetData sheetData = worksheet.GetFirstChild<SheetData>() ?? worksheet.AppendChild(new SheetData());
    string cellReference = columnName + rowIndex;

    // If the worksheet does not contain a row with the specified row index, insert one.
    Row row;
    if (sheetData.Elements<Row>().Where(r => r.RowIndex is not null && r.RowIndex == rowIndex).Count() != 0)
    {
        row = sheetData.Elements<Row>().Where(r => r.RowIndex is not null && r.RowIndex == rowIndex).First();
    }
    else
    {
        row = new Row() { RowIndex = rowIndex };
        sheetData.Append(row);
    }

    // If there is not a cell with the specified column name, insert one.  
    if (row.Elements<Cell>().Where(c => c.CellReference is not null && c.CellReference.Value == columnName + rowIndex).Count() > 0)
    {
        return row.Elements<Cell>().Where(c => c.CellReference is not null && c.CellReference.Value == cellReference).First();
    }
    else
    {
        // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
        Cell? refCell = null;

        foreach (Cell cell in row.Elements<Cell>())
        {
            if (string.Compare(cell.CellReference?.Value, cellReference, true) > 0)
            {
                refCell = cell;
                break;
            }
        }

        Cell newCell = new Cell() { CellReference = cellReference };
        row.InsertBefore(newCell, refCell);

        worksheet.Save();
        return newCell;
    }
}