はじめに
『C#コードレシピ集』では掲載できなかったOpen XMLを使ってExcelを操作するレシピを5回に分けて紹介しています。
- OpenXMLでExcelファイルを操作しよう (1) - Excelファイルを新規作成したい
- OpenXMLでExcelファイルを操作しよう (2) - セルに値を設定したい
- OpenXMLでExcelファイルを操作しよう (3) - セルの値を読み込みたい
- OpenXMLでExcelファイルを操作しよう (4) - 全てのセルの値を取得したい ← 今ここ
- OpenXMLでExcelファイルを操作しよう (5) - セルに書式を設定したい
コードレシピの環境は、C#9.0 + .NET 5 のコンソールアプリケーションです。
なお、『C#コードレシピ集』では、NPOIを使ってエクセルファイルを操作するレシピを掲載しています。
定義するメソッド
前回は指定したセルの値を読み取るコードを示しましたが、今回はシート内の全てのセルの値を読み取るコードを示します。
前回同様、MyExcelBookクラスにこれらのメソッドを追加定義します。定義するのは以下の2つのメソッドです。
| メソッド | 機能 |
|---|---|
| GetAllCellValues | シート内の全てのセルを値を取得する |
| ToCellInfo | GetAllCellValuesの下請けメソッド |
追加で定義するクラス
OpenXmlのCellクラスをそのまま利用しても良いのですが、ここでは必要最小限の情報を持ったCellInfoクラス(ここではrecordを利用)を定義します。
public record CellInfo
{
public int Row { get; set; }
public string Col { get; set; }
public string Value { get; set; }
}
MyExcelBook クラス
では、MyExcelBookクラスを示します。コードが長くなるので、今回利用しないコードは省略します。
using System;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
sealed class MyExcelBook : IDisposable
{
private MyExcelBook() { }
public void Dispose() => _document?.Dispose();
private SpreadsheetDocument _document;
private WorkbookPart _workbookpart;
private WorksheetPart _worksheetPart;
private Sheets _sheets;
public void SelectSheet(string sheetName)
{
var theSheet = _workbookpart.Workbook.Descendants<Sheet>().
Where(s => s.Name == sheetName).FirstOrDefault();
if (theSheet == null)
throw new ArgumentException("sheetName");
_worksheetPart = (WorksheetPart)(_workbookpart.GetPartById(theSheet.Id));
}
public static MyExcelBook Open(string filePath)
{
var obj = new MyExcelBook();
obj._document = SpreadsheetDocument.Open(filePath, false);
obj._workbookpart = obj._document.WorkbookPart;
return obj;
}
public string GetCellValue(Cell cell)
{
if (cell == null)
return null;
if (cell.CellFormula != null)
return cell.CellValue.Text;
var value = cell.InnerText;
if (cell.DataType == null)
return value;
switch (cell.DataType.Value)
{
case CellValues.SharedString: // 文字列
// 単一の SharedStringTablePart への参照を取得する必要がある
var stringTable = _workbookpart
.GetPartsOfType<SharedStringTablePart>()
.FirstOrDefault();
if (stringTable != null)
{
// valueはindexを表している。SharedStringTableから要素を取得
var element = stringTable.SharedStringTable
.ElementAt(int.Parse(value));
// そのInnterTextがセルの値
value = element.InnerText;
}
break;
case CellValues.Boolean: // ブール値
value = (value != "0").ToString().ToLower();
break;
}
return value;
}
// 以降が追加したメンバー
public IEnumerable<CellInfo[]> GetAllCellValues()
{
if (_worksheetPart == null)
{
Console.WriteLine("WorksheetPart Not Found !!");
yield break;
}
var ws = _worksheetPart.Worksheet;
foreach (var row in ws.Descendants<Row>())
{
var list = new List<CellInfo>();
// 注意:値が空のセルがあると列挙から除外される。
// これに対応するには、cell.CellReferenceの値を見て
// セルの位置を確認する必要がある。
foreach (Cell cell in row)
{
list.Add(ToCellInfo(cell));
}
yield return list.ToArray();
}
}
private CellInfo ToCellInfo(Cell cell)
{
return new CellInfo {
Col = Regex.Match(cell.CellReference.Value, @"^[A-Z]+").Value,
Row = int.Parse(Regex.Match(cell.CellReference.Value, @"[0-9]+$").Value),
Value = GetCellValue(cell)
};
}
// 以下省略 （前回までに定義したメソッドの一部は省略）
}
MyExcelBookクラスを利用し、全てのセルの値を取り出す
前述のMyExcelBookクラスを利用し、全てのセルの値を取り出し、コンソールに出力するコードを示します。
foreach (var row in xls.GetAllCellValues())
{
foreach (var cell in row)
{
Console.Write($"{cell.Col}{cell.Row}: {cell.Value}\t");
}
Console.WriteLine();
}
実行例
下記のエクセルファイルを入力ファイルとします。
C4は、=SUM(C1:C3), D4は、=AVERAGE(D1:D3)の関数が設定されています。
以下が実行結果です。
A1: A1 B1: B1 C1: 123 D1: 10.1
A2: A2 B2: B2 C2: 234 D2: 20.2
A3: A3 B3: B3 C3: 345 D3: 30.3
A4: Totle C4: 702 D4: 20.2