スプレッドシート ドキュメントでセルの範囲の合計を計算する
このトピックでは、Open XML SDK for Office のクラスを使用して、スプレッドシート ドキュメント内の連続するセル範囲の合計をプログラムで計算する方法について説明します。
spreadsheetML ドキュメントの基本的な構造
SpreadsheetMLドキュメントの基本的なドキュメント構造は、ブック内のワークシートを参照するSheets要素とSheet要素で構成されます。 ワークシートごとに、それぞれの XML ファイルが作成されます。 たとえば、MySheet1 と MySheet2 という名前の 2 つのワークシートがあるWorkbookのSpreadsheetMLは、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 1 つ以上のブロック レベル要素が含まれており、1 つ以上の Row 要素が含まれています。
rowには、1 つ以上のCell要素が含まれています。 各セルには、セルの値を表す CellValue 要素が含まれています。 たとえば、セル A1 の値が 100 しかないブックの最初のワークシートの SpreadsheetML は、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 名前空間にあります。 次の表に、 workbook、 sheets、 sheet、 worksheet、 sheetData の各要素に対応するクラスのクラス名を示します。
| 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 | セルの値。 |
サンプル コードの動作のしくみ
サンプル コードは、ソース SpreadsheetML ファイルへの完全なパスを表すパラメーターCalculateSumOfCellRangeメソッドに渡すことから始まります。セルを含むワークシートの名前を表すパラメーター、連続した範囲内の最初のセルの名前を表すパラメーター、連続した範囲内の最後のセルの名前を表すパラメーター、 と、結果を表示するセルの名前を表すパラメーター。
その後、コードは、読み取り/書き込みアクセス用の 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;
}
2 つの列を比較するため、比較する列を表す 2 つのパラメーターを渡します。 1 つ目の列が 2 つ目の列より長い場合は、1 を返します。 2 つ目の列が 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;
}
}