まずはセルデータから - その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行目に相当する部分がもうちょっと複雑になります。
文字列をセットする場合のコードは次回、紹介予定です。