Вычислить сумму диапазона ячеек в документе электронной таблицы
В этом разделе показано, как использовать классы в пакете SDK Open XML для Office для программного вычисления суммы смежных диапазонов ячеек в документе электронной таблицы.
Базовая структура документа spreadsheetML
Базовая структура SpreadsheetML документа состоит из Sheets элементов и Sheet , ссылающихся на листы в книге. Для каждого листа создается отдельный XML-файл. Например, SpreadsheetML объект для , Workbook имеющий два листа с именами MySheet1 и MySheet2, находится в файле Workbook.xml и показан в следующем примере кода.
<?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>
XML-файлы листа содержат один или несколько элементов уровня блока, SheetData например представляет таблицу ячеек и содержит один или несколько Row элементов. Содержит row один или несколько Cell элементов. Каждая ячейка CellValue содержит элемент, представляющий значение ячейки. Например, SpreadsheetML для первого листа в книге, который имеет только значение 100 в ячейке A1, находится в файле Sheet1.xml и показан в следующем примере кода.
<?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>
С помощью пакета SDK Open XML можно создать структуру документа и содержимое, в котором используются строго типизированные классы, соответствующие SpreadsheetML элементам. Эти классы можно найти в DocumentFormat.OpenXML.Spreadsheet пространстве имен. В следующей таблице перечислены имена классов, которые соответствуют workbookэлементам , sheets, sheet, worksheetи sheetData .
| Элемент SpreadsheetML | Класс пакета SDK Open XML | Описание |
|---|---|---|
<workbook/> |
DocumentFormat.OpenXML.Spreadsheet.Workbook | Корневой элемент основной части документа. |
<sheets/> |
DocumentFormat.OpenXML.Spreadsheet.Sheets | Контейнер для структур уровня блока, таких как sheet, fileVersion и других, указанных в спецификации ISO/IEC 29500 . |
<sheet/> |
DocumentFormat.OpenXml.Spreadsheet.Sheet | Лист, указывающий на файл определения листа. |
<worksheet/> |
DocumentFormat.OpenXML.Spreadsheet. Worksheet | Файл определения листа, который содержит данные листа. |
<sheetData/> |
DocumentFormat.OpenXML.Spreadsheet.SheetData | Таблица ячеек, сгруппированных по строкам. |
<row/> |
DocumentFormat.OpenXml.Spreadsheet.Row | Строка в таблице ячеек. |
<c/> |
DocumentFormat.OpenXml.Spreadsheet.Cell | Ячейка в строке. |
<v/> |
DocumentFormat.OpenXml.Spreadsheet.CellValue | Значение ячейки. |
Механизм работы примера кода
Пример кода начинается с передачи в метод CalculateSumOfCellRange параметра, представляющего полный путь к исходному SpreadsheetML файлу, параметра, представляющего имя листа, содержащего ячейки, параметра, представляющего имя первой ячейки в непрерывном диапазоне, параметра, представляющего имя последней ячейки в непрерывном диапазоне. и параметр , представляющий имя ячейки, в которой нужно отобразить результат.
Затем код открывает файл для редактирования в виде SpreadsheetDocument пакета документа для доступа на чтение и запись. Код получает указанный Worksheet объект. Затем он получает индекс строки для первой и последней ячеек в непрерывном диапазоне GetRowIndex путем вызова метода . Он получает имя столбца для первой и последней ячеек в смежных диапазонах путем вызова GetColumnName метода .
Для каждого Row объекта в непрерывном диапазоне код выполняет итерацию по каждому Cell объекту и определяет, находится ли столбец ячейки в пределах смежных диапазонов, вызывая CompareColumn метод . Если ячейка находится в непрерывном диапазоне ячеек, код добавляет значение ячейки к сумме. Затем он получает объект , SharedStringTablePart если он существует. Если он не существует, он создает его с помощью AddNewPart метода . Он вставляет результат в объект путем SharedStringTablePart вызова InsertSharedStringItem метода .
Код вставляет новую ячейку для результата на лист путем вызова InsertCellInWorksheet метода и задает значение ячейки. Дополнительные сведения см. в статье Вставка ячейки в электронную таблицу.
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);
}
}
Чтобы получить индекс строки, код передает параметр, который представляет имя ячейки, и создает новое регулярное выражения для сопоставления части индекса строки из имени ячейки. Дополнительные сведения о регулярных выражениях см. в справочнике Элементы языка регулярных выражений. Он получает индекс строки путем вызова Match метода , а затем возвращает индекс строки.
// 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);
}
Затем код возвращает имя столбца путем передачи параметра, представляющего имя ячейки, и создает новое регулярное выражение для сопоставления имени столбца из имени ячейки. Регулярное выражение сопоставляет любое сочетание прописных и строчных букв. Он получает имя столбца путем вызова Match метода , а затем возвращает имя столбца.
// 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;
}
Для сравнения двух столбцов код передает два параметра, которые представляют сравниваемые столбцы. Если первый столбец длиннее второго, код возвращает значение 1. Если второй столбец длиннее первого, код возвращает значение -1. В противном случае он сравнивает значения столбцов с помощью Compare и возвращает результат.
// 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);
}
}
Чтобы вставить SharedStringItem, код передает параметр, представляющий текст для вставки в ячейку, и параметр, представляющий SharedStringTablePart объект электронной таблицы.
ShareStringTablePart Если объект не содержит объект , SharedStringTable он создает его. Если текст уже существует в объекте ShareStringTable , он возвращает индекс для SharedStringItem объекта, представляющего текст. Если текст не существует, создайте новый SharedStringItem объект, представляющий текст. Затем он возвращает индекс для SharedStringItem объекта, представляющего текст.
// 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;
}
Последний шаг — вставка ячейки в лист. Для этого код передает параметры, представляющие имя столбца и номер строки ячейки, а также параметр, представляющий лист с данной ячейкой. Если указанная строка не существует, код создает строку и добавляет ее на лист. Если указанный столбец существует, код находит ячейку, соответствующую строке в этом столбце, и возвращает ячейку. Если указанный столбец не существует, код создает его и вставляет в лист. Затем код определяет, где вставить ячейку в столбце, выполняя итерацию по элементам строки для поиска ячейки, которая расположена сразу после указанной строки. Эта строка сохраняется в переменной refCell . Он вставляет новую ячейку перед ячейкой, на которую ссылается с помощью refCellInsertBefore метода . Затем он возвращает новый 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);
return newCell;
}
}
Пример кода
Далее представлен полный пример кода на языках C# и 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;
}
}