まずはセルデータから - その3
- 詳細
- 公開日:2015年08月24日(月)00:00
前置きが長くなってしまいました。前回、前々回の内容を踏まえて、Open XML SDKを使ってセルにデータをセットするプログラムを紹介します。
using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System; using System.Linq; using System.Text.RegularExpressions; namespace OpenXMLSDK_Sample1 { class Program { static void Main(string[] args) { const string FILE = @"C:\Users\Public\Documents\Book1.xlsx"; const string SHEET_NAME = "Sheet1"; const string CELL_ADDRESS = "A6"; const int VALUE = 123456; using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(FILE, true)) { WorkbookPart bookPart = excelDoc.WorkbookPart; int sheet_index = GetWorksheetIndex(bookPart, SHEET_NAME); WorksheetPart sheet_part = GetWorksheetPart(bookPart, sheet_index); //行の特定 uint rowIndex = GetRowIndex(CELL_ADDRESS); Row row = sheet_part.Worksheet.Descendants<Row>().FirstOrDefault(r => r.RowIndex == rowIndex); if (row == null) { //行がない場合は行を新規作成する row = new Row() { RowIndex = new UInt32Value(rowIndex) }; SheetData sh_data = sheet_part.Worksheet.Descendants<SheetData>().First(); //場所を特定して行を挿入(行は行番号で昇順に並ばないとだめ) Row rRow = sh_data.Elements<Row>().LastOrDefault(r => r.RowIndex < rowIndex); if (rRow == null) { sh_data.InsertAt(row, 0); } else { sh_data.InsertAfter(row, rRow); } } uint colIndex = GetColumnIndex(CELL_ADDRESS); //セルを特定 Cell cell = row.Elements<Cell>().FirstOrDefault(c => c.CellReference.Value == CELL_ADDRESS); if (cell == null) { cell = new Cell() { CellReference = new StringValue(CELL_ADDRESS) }; //cellは昇順に書き込む必要がある Cell rCell = row.Elements<Cell>().LastOrDefault(c => GetColumnIndex(c.CellReference) < colIndex); if (rCell == null) { row.InsertAt(cell, 0); } else { row.InsertAfter(cell, rCell); } } //値をセット(セットする値が文字列ではない場合) cell.CellValue = new CellValue(VALUE.ToString()); //保存 sheet_part.Worksheet.Save(); } //ファイルを開く System.Diagnostics.Process.Start(FILE); } static int GetWorksheetIndex(WorkbookPart book_part, string sheet_name) { Sheets sheets = book_part.Workbook.GetFirstChild<Sheets>(); //シートのエレメントの順番(0スタート) int sheet_index = 0; foreach (Sheet sh in sheets) { if (sh.Name == sheet_name) break; sheet_index++; } return sheet_index; } static WorksheetPart GetWorksheetPart(WorkbookPart book_part, int index) { Sheets sheets = book_part.Workbook.GetFirstChild<Sheets>(); if (index >= sheets.Count()) { return null; } string target_relationshipID = sheets.Descendants<Sheet>().ElementAt(index).Id; return (WorksheetPart)book_part.GetPartById(target_relationshipID); } public static uint GetColumnIndex(string cellName) { string column_name = GetColumnName(cellName).ToUpper(); const string colChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; uint column_index = 0; for (int i = column_name.Length; i > 0; i--) { column_index += (uint)((colChars.IndexOf(column_name.Substring(column_name.Length - i, 1)) + 1) * Math.Pow(26, i - 1)); } return column_index; } //https://msdn.microsoft.com/ja-jp/library/office/Cc822064.aspx // Given a cell name, parses the specified cell to get the row index. public 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); } //https://msdn.microsoft.com/ja-jp/library/office/Cc822064.aspx public static string GetColumnName(string cellName) { Match match = Regex.Match(cellName, "[A-Za-z]+"); return match.Value; } } }
準備
当然といえば当然ですが、OpenXML SDKを使えるようにします。(Nugetで検索して、インストールするのが簡単です。)
それから、参照設定に「WindowsBase」を追加します。
コードの説明
上記のコードにおいて、GetRowIndex、GetColumnName の二つのメソッドはMSDN (https://msdn.microsoft.com/ja-jp/library/office/Cc822064.aspx) から拝借してきました。
上記のコードは「C:\Users\Public\Documents\Book1.xlsx のSheet1のセルA6に123456をセットする」コードです。
16行目で、C:\Users\Public\Documents\Book1.xlsx を開き、17行目でそのWorkbookパーツ(/xl/workbook.xml)を特定します。
つづいて、20行目でWorksheetパーツ(/xl/worksheets/sheet1.xml)を特定します。
using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(FILE, true)) { WorkbookPart bookPart = excelDoc.WorkbookPart; int sheet_index = GetWorksheetIndex(bookPart, SHEET_NAME); WorksheetPart sheet_part = GetWorksheetPart(bookPart, sheet_index);
ここまでが前回の内容(シート名を手がかりに、/xl/workbook.xml ファイルからリレーションシップIDを取得する。取得したリレーションシップIDをキーに/xl/_res/workbook.xml.rels ファイルからその定義が記述されているWorksheetパーツファイルを特定する。)に相当します。
つづいて、23行目から25行目までで、セルアドレスの行番号を手がかりに行(r要素)を特定します。
26行目から39行目は行が存在しない場合の処理です。行が存在しない場合(注目する行にデータを持つセルがかつて存在したことがない場合は、その行は存在しません)、新たに行を作成し、SheetDataの子要素としてこれを挿入します。
//行の特定 uint rowIndex = GetRowIndex(CELL_ADDRESS); Row row = sheet_part.Worksheet.Descendants<Row>().FirstOrDefault(r => r.RowIndex == rowIndex); if (row == null) { //行がない場合は行を新規作成する row = new Row() { RowIndex = new UInt32Value(rowIndex) }; SheetData sh_data = sheet_part.Worksheet.Descendants<SheetData>().First(); //場所を特定して行を挿入(行は行番号で昇順に並ばないとだめ) Row rRow = sh_data.Elements<Row>().LastOrDefault(r => r.RowIndex < rowIndex); if (rRow == null) { sh_data.InsertAt(row, 0); } else { sh_data.InsertAfter(row, rRow); } }
行を特定できたら、今度はセル(c要素)を特定します(41行目から54行目まで)。行の場合と同じく、セルが存在しない場合にはセルを作成し、行に挿入します。
uint colIndex = GetColumnIndex(CELL_ADDRESS); //セルを特定 Cell cell = row.Elements<Cell>().FirstOrDefault(c => c.CellReference.Value == CELL_ADDRESS); if (cell == null) { cell = new Cell() { CellReference = new StringValue(CELL_ADDRESS) }; //cellは昇順に書き込む必要がある Cell rCell = row.Elements<Cell>().LastOrDefault(c => GetColumnIndex(c.CellReference) < colIndex); if (rCell == null) { row.InsertAt(cell, 0); } else { row.InsertAfter(cell, rCell); } }
ここまででセルを特定できました。あとは57行目でセルのv要素をセットし、60行目でシートへの編集内容を保存します。
//値をセット(セットする値が文字列ではない場合) cell.CellValue = new CellValue(VALUE.ToString()); //保存 sheet_part.Worksheet.Save();
このコードを実行すると、64行目でC:\Users\Public\Documents\Book1.xlsxを開きます。セルA6に正しく「123456」がセットされていることを確認してください。
GetColumnIndexの実装がちょっとわかりづらいかもしれませんが、Excelの列名は26進数とみなせるので、列名の末尾から一文字ずつ切り出し、桁の重みをかけながら加算して10進数に変換し、列番号(0スタートです)を求めています。
で、文字列をセットするには...
上記のコードはセルの値に数値をセットする例だったので、セルを特定したらあとは57行目「cell.CellValue = new CellValue(VALUE.ToString());」だけでセルデータの編集が終わります。
文字列をセットする場合には、(前々回で確認したように)SharedStringも同時に編集する必要があるので、57行目に相当する部分がもうちょっと複雑になります。
文字列をセットする場合のコードは次回、紹介予定です。