Excel ファイルの読み書き 2020年6月17日記
０．初めに
Excel ファイルの読み書き
C# から Excel ファイルを読みだすために利用できるライブラリに ClosedXML というライブラリがあります。ここでは、このライブラリを使用して、さらに簡便に Excel ファイルを読み書きするためのクラスを紹介します。
ここで紹介するクラスの特徴は、以下になります。
- Excel ファイルの読み書きには、ClosedXML ライブラリを使用しています
このライブラリの制限として、操作できる Excel ファイルの拡張子は xlsx に限定されます - Excel の読み書きは、単純な文字列としてだけ操作します
ただし、 ClosedXML ライブラリは豊富な機能を持っていますので、Excel ファイルの機能を十分に利用することができます
ClosedXML ライブラリを使用するには、 Visual Studio の NuGet パッケージの機能を利用して、必要なライブラリをダウンロード・組み込んでください。私が利用したライブラリのバージョンは 0.94.2.0 となっていました。
ここで紹介しているライブラリの読み書きには、Csv ファイルの読み書きと同様に、標準クラスの DataTable を使用しています。これは、さらに大きなライブラリから、これらのクラスを利用しているため、 メソッドのインターフェースを共通にしてあります。いずれ、そのさらに大きなライブラリの紹介も行っていこうと思います。
１．解決方法
ExcelFile クラス
それでは、 CSV ファイルを読み書きするためのクラス ExcelFile のソースを以下に示します。かなり長いプログラムですが、多くのコメントを残してあります。おおよその意味は読み取れるものと思います。
1 public class ExcelFile
2 {
/// <summary>
/// DataRow の列名と Excel のセルとの関係を定義したリスト
/// DataRow が５項目からなり、その列名が "One","Two","Three","Four","Five"であり
/// 対応する Excel のセルが、A10, B10, C10, D10, E10 である場合には、次のリストとなる
/// 読込み時のリスト
/// {{ "One", "A10" }, { "Two", "B10" }, { "Three", "C10" }, { "Four", "D10" }, { "Five", "E10" }}
/// この意味は、"A10" のセルの値を "One" の列名の項目に格納する、以下同様
/// 書出し時のリスト
/// {{ "A10", "One" }, { "B10", "Two" }, { "C10", "Three" }, { "D10", "Four" }, { "E10", "Five" }}
/// この意味は、"One" の列名の値を "A10" のセルに格納する、以下同様
///
/// 読込み時の処理
/// 上記のセルの値を読出して、DataRow に格納する
/// 以下 A11, B11, C11, D11, E11 の様に行番号を１つ増やして処理を繰り返す
/// AXX が空欄の場合に、読込み処理を中止する
/// 中止の判定方法を変更したい場合は、 IsEndCell() メソッドをオーバーライドする
/// （例：AXX が文字列"END"の場合や、全てのセルが空欄の場合など）
///
/// 書出し時の処理
/// 先頭の DataRow の値を対応するセルに書込む
/// 次の DataRow の値を、セルの行番号を１つ増やして処理を繰り返す
/// 最後の DataRow を書き出して処理を中止する
///
/// デフォルトのリストを作成するメソッドが用意されている
/// 読込み用のメソッド
/// MakeReadColumns()
/// 使用方法は、メソッドのコメントを参照せよ
///
/// 書出し用のメソッド
/// MakeWriteColumns()
/// 使用方法は、メソッドのコメントを参照せよ
/// </summary>
3 public List<KeyValuePair<String, String>> Columns
4 {
5 get; set;
6 }
/// <summary>
/// 読み取り時に何番目のシートを読み取るか（書出し時には使用しない）
/// デフォルトは１（最初のシート）
/// </summary>
7 public int Sheet
8 {
9 get; set;
10 }
/// <summary>
/// Excel のファイル名
/// </summary>
11 public String Path
12 {
13 get; set;
14 }
/// <summary>
/// 書出し時の書込みモードを指定する（読み取り時には使用しない）
/// 上書きモードの場合（IsAppend == false）こちらがデフォルトの動作
/// Path が指定する Excel ファイルを新規に作成し、先頭のシートに書込む
/// シートの総数は１増える
/// 追加モードの場合（IsAppend == true）
/// Path が指定する Excel ファイルが存在していること
/// 新規のシートを IsFirst に応じて追加し、そのシートに書込む
/// シートの総数は１増える
/// </summary>
15 public bool IsAppend
16 {
17 get; set;
18 }
/// <summary>
/// 書込みモードが追加モードの場合のみ有効となる
/// IsFirst == true の場合、先頭にシートを追加する
/// IsFirst == false の場合、最後にシートを追加する（デフォルト値）
/// 途中に挿入するモードはサポートしていない
/// </summary>
19 public bool IsFirst
20 {
21 get; set;
22 }
/// <summary>
/// 書込み時に新規に作成するシートの名前
/// デフォルトは「出力結果」
/// 同じ名前が存在する場合、「出力結果-(x)」の書式で x が 1 からの連番でユニークとなる名前を探す
/// </summary>
23 public string SheetName
24 {
25 get; set;
26 }
/// <summary>
/// 処理結果
/// </summary>
27 public String Result
28 {
29 get; set;
30 }
31 public ExcelFile(String name = "")
32 {
33 internalClear();
34 Path = name;
35 }
36 public virtual void Clear()
37 {
38 internalClear();
39 }
/// <summary>
/// Excel ファイルからデータテーブルに読み取る
/// </summary>
40 public virtual bool Read(DataTable table)
41 {
42 try
43 {
44 if (Path.Length == 0)
45 throw new FileNotFoundException();
46 if (table.Columns.Count < 1)
47 throw new Exception("テーブルのスキーマが指定されていません。");
48 List<KeyValuePair<String, String>> columns = Columns;
49 if (columns == null || columns.Count == 0)
50 throw new Exception("テーブルのコラムとExcelファイルの関連が指定されていません。");
51 return Read(columns, table);
52 }
53 catch (Exception ex)
54 {
55 Result = ex.Message;
56 return false;
57 }
58 }
/// <summary>
/// データテーブルの内容を Excel ファイルに書き出す
/// </summary>
59 public virtual bool Write(DataTable table)
60 {
61 try
62 {
63 if (Path.Length == 0)
64 throw new FileNotFoundException();
65 if (IsAppend == true && !File.Exists(Path))
66 throw new FileNotFoundException(Path);
67 if (table.Columns.Count < 1)
68 throw new Exception("テーブルのスキーマが指定されていません。");
69 List<KeyValuePair<String, String>> columns = Columns;
70 if (columns == null || columns.Count == 0)
71 throw new Exception("テーブルのコラムとExcelファイルの関連が指定されていません。");
72 return Write(table, columns);
73 }
74 catch (Exception ex)
75 {
76 Result = ex.Message;
77 return false;
78 }
79 }
/// <summary>
/// データテーブルの内容を Excel ファイルに書き出す
/// テーブルと Excel のセルとの対応に従って書き出す
/// </summary>
/// <param name="columns">テーブルと Excel のセルとの関係</param>
80 protected virtual bool Write(DataTable table, List<KeyValuePair<String, String>> columns)
81 {
82 try
83 {
84 XLWorkbook book;
85 IXLWorksheet sheet;
86 if (!IsAppend) // 上書きモード
87 {
88 book = new XLWorkbook();
89 sheet = book.Worksheets.Add(SheetName);
90 }
91 else // 追加モード
92 {
93 book = new XLWorkbook(Path);
94 string name = uniqName(book, SheetName); // ユニークなシート名を探す
95 sheet = book.Worksheets.Add(name, IsFirst ? 1 : book.Worksheets.Count + 1);
96 }
97 int offset = 0;
98 foreach (DataRow row in table.Rows)
99 {
100 if (!Write(row, sheet, offset, columns))
101 return false;
102 ++offset;
103 }
104 if (!IsAppend)
105 {
106 book.SaveAs(Path);
107 }
108 else
109 {
110 book.Save();
111 }
112 return true;
113 }
114 catch (Exception ex)
115 {
116 Result = ex.Message;
117 return false;
118 }
119 }
/// <summary>
/// データロウの内容を Excel シートに書き出す
/// セルの名前が A10 である場合、列名は A , 行番号は (10 + offset) となる
/// </summary>
/// <param name="sheet">Excel シート</param>
/// <param name="offset">基準となるセルの列番号からのオフセット</param>
/// <param name="columns">データロウの列名とセルとの関係</param>
/// <param name="row">データを格納しているデータロウ</param>
120 protected virtual bool Write(DataRow row, IXLWorksheet sheet, int offset, List<KeyValuePair<String, String>> columns)
121 {
122 try
123 {
124 int at;
125 String col, val;
126 foreach (var c in columns)
127 {
128 if (!ParseCell(c.Key, out at, out col))
129 throw new Exception(String.Format("セルの書式が不正です。[{0}]", c.Key));
130 val = row[c.Value] == DBNull.Value ? "" : (String)row[c.Value];
131 sheet.Cell(at + offset, col).Value = val;
132 }
133 return true;
134 }
135 catch (Exception ex)
136 {
137 Result = ex.Message;
138 return false;
139 }
140 }
/// <summary>
/// Excel のデータを DataTable に読み込む
/// セルとデータテーブルの対応は第一引数を使用する
/// </summary>
/// <param name="columns">対応リスト</param>
/// <param name="table">読込み用データテーブル</param>
141 protected virtual bool Read(List<KeyValuePair<String, String>> columns, DataTable table)
142 {
143 if (! File.Exists(Path))
144 {
145 Result = String.Format("ファイルが存在しません。[{0}]", Path);
146 return false;
147 }
148 using (var book = new XLWorkbook(Path))
149 {
150 var sheet = book.Worksheets.Worksheet(Sheet);
151 DataRow row;
152 int offset = 0;
153 while (true)
154 {
155 if (IsEndCell(sheet, offset, columns[0].Value))
156 break;
157 row = table.NewRow();
158 if (!Read(sheet, offset, columns, row))
159 return false;
160 table.Rows.Add(row);
161 ++offset;
162 }
163 }
164 return true;
165 }
/// <summary>
/// Excell のシートから DataRow に読込む
/// セルとデータテーブルの対応は第一引数（オフセット）と第二引数を使用する
/// </summary>
/// <param name="sheet">Excel のシート</param>
/// <param name="offset">第三引数に加えるオフセット</param>
/// <param name="columns">対応リスト</param>
/// <param name="row">格納用の DataRow</param>
166 protected virtual bool Read(IXLWorksheet sheet, int offset, List<KeyValuePair<String, String>> columns, DataRow row)
167 {
168 try
169 {
170 int at;
171 String cell, val;
172 foreach (var c in columns)
173 {
174 if (!ParseCell(c.Value, out at, out cell))
175 throw new Exception(String.Format("セルの書式が不正です。{0}", c.Key + ":" + c.Value));
176 val = getRowCellValue(sheet, at + offset, cell);
177 row[c.Key] = val;
178 }
179 return true;
180 }
181 catch (Exception ex)
182 {
183 Result = String.Format("Excel の読出しに失敗しました。\r\n[{0}] [{1}]", Path, ex.Message);
184 return false;
185 }
186 }
/// <summary>
/// 指定したセル以降には、有効なセルがないかどうかを返す
/// セルの名前が A10 である場合、セルの列名は A, 行番号は（10 + offset）となる
/// 該当するセルが空欄の場合、それ以降のセルは無効なセルと判断する
/// </summary>
/// <param name="sheet">Excel シート</param>
/// <param name="offset">セルの行番号に対するオフセット</param>
/// <param name="column">セルの名前</param>
187 protected virtual bool IsEndCell(IXLWorksheet sheet, int offset, String column)
188 {
189 String val = getCellValue(sheet, offset, column);
190 return String.IsNullOrEmpty(val);
191 }
/// <summary>
/// Excel の列名のリストを返す
/// count == 3, start == "A" の場合、{ "A", "B", "C" } を返す
/// count == 4, start == "AB" の場合、{ "AB", "AC", "AD", "AE" } を返す
/// </summary>
/// <param name="count">必要な列数</param>
/// <param name="start">最初の列名</param>
192 public static List MakeExcelColums(int count, string start = "")
193 {
194 string alpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
195 int cnt = 0, all = 0;
196 string txt;
197 bool isOk = string.IsNullOrEmpty(start) ? true : false;
198 List<string> columns = new List<string>();
199 foreach (var ch in alpha)
200 {
201 ++all;
202 txt = new string(ch, 1);
203 if (!isOk)
204 {
205 if (txt.Equals(start))
206 isOk = true;
207 else
208 continue;
209 }
210 columns.Add(txt);
211 ++cnt;
212 if (cnt >= count)
213 return columns;
214 }
215 char[] char2 = new char[2];
216 foreach (var ch1 in alpha)
217 {
218 char2[0] = ch1;
219 foreach (var ch2 in alpha)
220 {
221 ++all;
222 char2[1] = ch2;
223 txt = new string(char2);
224 if (!isOk)
225 {
226 if (txt.Equals(start))
227 isOk = true;
228 else
229 continue;
230 }
231 columns.Add(txt);
232 ++cnt;
233 if (cnt >= count)
234 return columns;
235 }
236 }
237 int max = 16384;
238 char[] char3 = new char[3];
239 foreach (var ch1 in alpha)
240 {
241 char3[0] = ch1;
242 foreach (var ch2 in alpha)
243 {
244 char3[1] = ch2;
245 foreach (var ch3 in alpha)
246 {
247 ++all;
248 char3[2] = ch3;
249 txt = new string(char3);
250 if (!isOk)
251 {
252 if (txt.Equals(start))
253 isOk = true;
254 else
255 continue;
256 }
257 columns.Add(txt);
258 ++cnt;
259 if (cnt >= count)
260 return columns;
261 if (all >= max)
262 return null;
263 }
264 }
265 }
266 return columns;
267 }
/// <summary>
/// Excel のセル名を列名と行番号に分割する
/// </summary>
/// <param name="value">セル名</param>
/// <param name="at">行番号</param>
/// <param name="col">列名</param>
268 public static bool ParseCell(String value, out int at, out String col)
269 {
270 at = ParseRow(value);
271 col = ParseColumn(value);
272 return at >= 0 && !String.IsNullOrEmpty(col);
273 }
/// <summary>
/// Excel のセル名のうち、行番号に該当する部分の値を返す
/// セル名が A10 の場合、10 を返す
/// </summary>
/// <param name="cell"></param>
/// <returns>無効なセル名の場合、-1 を返す</returns>
274 public static int ParseRow(String cell)
275 {
276 String nums = "0123456789";
277 int index = cell.IndexOfAny(nums.ToCharArray());
278 if (index <= 0)
279 return -1;
280 int val = -1;
281 try
282 {
283 val = int.Parse(cell.Substring(index));
284 return val;
285 }
286 catch (Exception)
287 {
288 return -1;
289 }
290 }
/// <summary>
/// Excel のセル名のうち、列名に該当する部分の文字列を返す
/// セル名が A10 の場合、A を返す
/// </summary>
/// <param name="cell"></param>
/// <returns>無効なセル名の場合、空文字列を返す</returns>
291 public static string ParseColumn(String cell)
292 {
293 String nums = "0123456789";
294 int index = cell.IndexOfAny(nums.ToCharArray());
295 if (index <= 0)
296 return String.Empty;
297 return cell.Substring(0, index);
298 }
/// <summary>
/// 読込み用の DataRow と Excell のセルの対応リストを返す
/// リストの要素数は、table のコラム数分
/// 先頭のセルは、引数の cell
/// それ以降のセルは、次の列名と同じ行番号を自動的に作成する
/// table のコラム名が { "One", "Two", "Three" } の３つで、 cell が "A1" の場合、
/// {{ "One", "A1" }, { "Two", "B1" }, { "Three", "C1" }} となるリストを返す
/// </summary>
/// <param name="table"></param>
/// <param name="cell"></param>
299 public static List<KeyValuePair<string, string>> MakeReadColumns(DataTable table, string cell)
300 {
301 if (table.Columns.Count == 0 || string.IsNullOrEmpty(cell))
302 return null;
303 return MakePairColumns(table, cell, true);
304 }
/// <summary>
/// 書出し用の DataRow と Excell のセルの対応リストを返す
/// リストの要素数は、table のコラム数分
/// 先頭のセルは、引数の cell
/// それ以降のセルは、次の列名と同じ行番号を自動的に作成する
/// table のコラム名が { "One", "Two", "Three" } の３つで、 cell が "A1" の場合、
/// {{ "A1", "One" }, { "B1", "Two" }, { "C1", "Three" }} となるリストを返す
/// </summary>
/// <param name="table"></param>
/// <param name="cell"></param>
305 public static List<KeyValuePair<string, string>> MakeWriteColumns(DataTable table, string cell)
306 {
307 if (table.Columns.Count == 0 || string.IsNullOrEmpty(cell))
308 return null;
309 return MakePairColumns(table, cell, false);
310 }
311 private static List<KeyValuePair<string, string>> MakePairColumns(DataTable table, string cell, bool isRead)
312 {
313 int at = 0;
314 string alpha = string.Empty;
315 if (!ExcelFile.ParseCell(cell, out at, out alpha))
316 return null;
317 List<string> names = ExcelFile.MakeExcelColums(table.Columns.Count, alpha);
318 if (names == null)
319 return null;
320 int index = 0;
321 string atTxt = at.ToString();
322 List<KeyValuePair<string, string>> columns = new List<KeyValuePair>string, string>>();
323 foreach (DataColumn col in table.Columns)
324 {
325 columns.Add(isRead ?
326 new KeyValuePair<string, string>(col.ColumnName, names[index] + atTxt) : // 読込み用
327 new KeyValuePair<string, string>(names[index] + atTxt, col.ColumnName)); // 書出し用
328 ++index;
329 }
330 return columns;
331 }
/// <summary>
/// Excel シートとオフセットとセル名（例：A10）を指定して、セルの値を文字列として返す
/// 上記のセル名の場合、セルの列名は A であり、行番号は、(10 + offset)となる
/// </summary>
332 private String getCellValue(IXLWorksheet sheet, int offset, String column)
333 {
334 int at;
335 String col;
// セル名を列名と行番号に分解する
336 if (!ExcelFile.ParseCell(column, out at, out col))
337 return null;
338 at += offset;
339 return getRowCellValue(sheet, at, col);
340 }
// 行番号（１～）と列名（Ａ～）を指定して、セルの値を文字列として返す
341 private String getRowCellValue(IXLWorksheet sheet, int at, String value)
342 {
343 var cell = sheet.Cell(at, value);
345 return cell.Value.ToString();
346 }
347 protected String uniqName(XLWorkbook book, String name)
348 {
349 bool same = book.Worksheets.Any((s) => (s.Name).Equals(name));
350 if (!same)
351 return name;
353 String newName;
354 for (int i = 1; ; ++i)
355 {
356 newName = name + "(" + i + ")";
357 same = book.Worksheets.Any((s) => (s.Name).Equals(newName));
358 if (!same)
359 return newName;
360 }
361 }
362 private void internalClear()
363 {
364 Columns = null;
365 Path = string.Empty;
366 IsAppend = false;
367 IsFirst = false;
368 Sheet = 1;
368 SheetName = "出力結果";
369 Path = string.Empty;
370 Result = String.Empty;
371 }
372 }
３．サンプル
読みだしのサンプル
次のような内容の Excel ファイルを読み込むとします。
B2, C2, D2 の各セルに１つ目のデータがあり、B3, C3, D3 の各セルに２つ目のデータがあります。
読み込む先の DataRow には、次のようなコラム名が付けてあります。
この状態で、以下のようなプログラムにより、Excel の内容を読み取ることができます。
1 DataTable table = new DataTable();
2 ExcelFile excel = new ExcelFile("ファイルのパス名");
3 excel.Columns = new List<KeyValuePair<String, String>> {
4 new KeyValuePair<string, string>("1", "B2"),
5 new KeyValuePair<string, string>("2", "C2"),
6 new KeyValuePair<string, string>("3", "D2")};
7 excel.Read(table);
| １行目 | 読み込む DataTable のインスタンスを作成します コラムの作成部分は省略していますが、３つのコラムを作成してください |
| ４行目 | Excel の B2 セルの内容を、 "1" のコラムに読みこみます |
| ５～６行目 | C2 セルの内容を "2" のコラムに、D2 セルの内容を "3" のコラムに読みこみます |
| ７行目 | Columns のリストに従って、順次セルを読みだします |
読みだした結果は次のようになります。
最初の DataRow には、 Excel の１つ目のデータが、次の DataRow には、 Excel の２つ目のデータが読みだされます。
Excel の２つ目のデータは、列名が同じで行番号が１つ増えたセルから読みだします。この場合は B3, C3, D3 のセルから読みだしますし、３つ目のデータは B4, C4, D4 となります。読みだしを終了するのはいつでしょうか？
それは、１行の先頭のセル Bxx （ここで、xx は数字です）セルが空欄だった時になります。この例題では、B4 のセルが空欄ですので、この時点で終了します。
この終了条件は変更することができます。このクラスから派生したクラスを定義してください。そのクラスで、 IsEndCeｌl() メソッドを再定義することで、終了条件を変更することができます。読みだしを終了したい場合に、 true を返すようにしてください。
このサンプルでは、 Excel のセルと DataTable のコラムの関係を１行分すべて定義しています。通常の使い方では、読みだしを開始するセルを１つだけ定義すれば、読みだすセルの列数は DataTable の列と同じ数だけ読みだし、 次は先頭の次のセルを読みだすと思います。そして、先頭の行が空欄になった場合に読みだしを終了するのが普通でしょう。そのための方法も用意されています。以下のようにしてください。
1 DataTable table = new DataTable();
2 ExcelFile excel = new ExcelFile("ファイルのパス名");
3 excel.Columns = ExcelFile.MakeReadColumns(table, "B2");
4 excel.Read(table);
MakeReadCoulmns() メソッドを呼び出すことで、先頭のセルの番号を指定するだけで、必要なリストを作成することができます。
書き込みのサンプル
こんどは、書き込みのサンプルです。DataTable に格納されているデータを Excel ファイルに書き出すことになります。
次のような DataRow を想定してください。
次のようなプログラムで Excel に書き込むことができます。
1 DataTable table = new DataTable();
2 ExcelFile excel = new ExcelFile("ファイルのパス名");
3 excel.Columns = new List<KeyValuePair<String, String>> {
4 new KeyValuePair<string, string>("B2", "1"),
5 new KeyValuePair<string, string>("C2", "2"),
6 new KeyValuePair<string, string>("C2", "3")};
7 excel.Write(table);
| １行目 | 書き込み用の DataTable のインスタンスを作成します DataRow の作成は省略しています |
| ４～６行目 | DataRow のコラムとセルの対応を定義しています．キーと値の順番が読み込み時とは逆になっています |
| ７行目 | Excel に書き出します |
書き込み時には、読みだし時とは違う考慮が必要になります。
- 既存の Excel ファイルに書き込むのか、新規に Excel ファイルを作成するのか
- 書き込むシートはどのシートを使用するのか
こうした動作を指定することができます。Sheet, IsAppend, IsFirst, SheetName の各プロパティを確認してください。