计算电子表格文档中的单元格区域的总和
本主题演示如何使用 Open XML SDK for Office 中的类以编程方式计算电子表格文档中连续单元格区域的总和。
spreadsheetML 文档的基本结构
文档的基本文档结构SpreadsheetML由 和 Sheet 元素组成Sheets，这些元素引用工作簿中的工作表。 将为每张工作表创建单独的 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 工作簿中第一个工作表的 （在单元格 A1 中只有值 100） 位于 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>
使用 Open XML SDK，可以创建使用与元素对应的 SpreadsheetML 强类型类的文档结构和内容。 可以在 命名空间中找到 DocumentFormat.OpenXML.Spreadsheet 这些类。 下表列出了对应于 、、sheets、 worksheetsheet和 sheetData 元素的类的workbook类名。
| SpreadsheetML 元素 | Open XML SDK 类 | 说明 |
|---|---|---|
<workbook/> |
DocumentFormat.OpenXML.Spreadsheet.Workbook | 主文档部件的根元素。 |
<sheets/> |
DocumentFormat.OpenXML.Spreadsheet.Sheets | 块级结构（如工作表、文件版本和 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 方法创建一个。 它通过调用 InsertSharedStringItem 方法将SharedStringTablePart结果插入 对象。
代码通过调用 方法将结果的新单元格插入工作表中， 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 。 它将新单元格插入到使用 InsertBefore 方法引用的refCell单元格之前。 然后，它返回新 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;
}
}