Calcular la suma de un rango de celdas en 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 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 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
El código de ejemplo comienza pasando al método CalculateSumOfCellRange un parámetro que representa la ruta de acceso completa al archivo de origen SpreadsheetML , 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 del rango contiguo, un parámetro que representa el nombre de la última celda del rango contiguo, y un parámetro que representa el nombre de la celda donde desea que se muestre el resultado.
A continuación, el código abre el archivo para su edición como un SpreadsheetDocument paquete de documentos para el acceso de lectura y escritura; el código obtiene el objeto especificado Worksheet . A continuación, obtiene el índice de la fila de la primera y la última celda del rango contiguo llamando al GetRowIndex método . Obtiene el nombre de la columna de la primera y la última celda del rango contiguo llamando al GetColumnName método .
Para cada Row objeto dentro del intervalo contiguo, el código recorre en iteración cada Cell objeto y determina si la columna de la celda está dentro del intervalo contiguo llamando al CompareColumn método . 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 SharedStringTablePart objeto si existe. Si no existe, crea uno mediante el AddNewPart método . Inserta el resultado en el SharedStringTablePart objeto llamando al InsertSharedStringItem método .
El código inserta una nueva celda para el resultado en la hoja de cálculo llamando al InsertCellInWorksheet método y establece el valor de la celda. Para obtener más información, vea cómo insertar una celda en una 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);
}
}
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 Match método 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 Match método y, a continuación, devuelve el nombre de 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 , SharedStringItemel código pasa un parámetro que representa el texto que se va a insertar en la celda y un parámetro que representa el SharedStringTablePart objeto de la hoja de cálculo. Si el ShareStringTablePart objeto no contiene un SharedStringTable objeto, crea uno. Si el texto ya existe en el ShareStringTable objeto , devuelve el índice del SharedStringItem objeto que representa el texto. Si el texto no existe, cree un nuevo SharedStringItem objeto que represente el texto. A continuación, devuelve el índice del SharedStringItem objeto 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)));
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 refCell variable . Inserta la nueva celda antes de la celda a la que se hace referencia mediante refCell el InsertBefore método . A continuación, devuelve el nuevo Cell objeto.
// 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);
return newCell;
}
}
Código de ejemplo
A continuación se incluye el código de ejemplo completo en C# y Visual Basic.
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);
}
}
// 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)));
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);
return newCell;
}
}