Open XML SDKをつかって、セルの表示形式を設定します。
「セルの書式設定 - その1 」でとりあげた、セルデータの表示形式をOpen XML SDKを使って編集します。
編集対象のワークブック「Book1.xlsx」のSheet1シートは、あらかじめ以下のような入力がされているものとします。
定義済みの表示形式をセット
#
セルA1に定義済みの表示形式「 #,##0.00 」をセットすることを考えます。この表示形式のnumFmtIdは4です。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text.RegularExpressions;
namespace OpenXMLSDK_Sample4 {
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 = "A1";
const uint CELL_NUMBER_FORMAT_ID = 4; //#,##0.00
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);
Cell cell = GetCell(sheet_part, CELL_ADDRESS);
//現在のs属性から現在のxfを取得
CellFormat current_xf;
IEnumerable<CellFormat> cell_formats = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements<CellFormat>();
if (cell.StyleIndex == null) {
current_xf = new CellFormat();
current_xf.NumberFormatId = new UInt32Value(CELL_NUMBER_FORMAT_ID);
bookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(current_xf);
cell.StyleIndex = new UInt32Value((uint)bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1);
} else {
current_xf = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt((int)cell.StyleIndex.Value) as CellFormat;
if(current_xf.NumberFormatId != CELL_NUMBER_FORMAT_ID) {
current_xf = current_xf.CloneNode(true) as CellFormat;
current_xf.NumberFormatId = new UInt32Value(CELL_NUMBER_FORMAT_ID);
bookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(current_xf);
cell.StyleIndex = new UInt32Value((uint)bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1);
}
}
//保存
sheet_part.Worksheet.Save();
}
//ファイルを開く
System.Diagnostics.Process.Start(FILE);
}
private static Cell GetCell(WorksheetPart sheet_part, string cell_address) {
//行の特定
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);
}
}
return cell;
}
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart) {
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null) {
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>()) {
//if (item.InnerText == text) {
if (item.Text != null && item.Text.InnerText == text) {
return i;
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save(); //Flush the changes.
return i;
}
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://learn.microsoft.com/ja-jp/office/open-xml/spreadsheet/how-to-get-a-column-heading-in-a-spreadsheet
// 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://learn.microsoft.com/ja-jp/office/open-xml/spreadsheet/how-to-get-a-column-heading-in-a-spreadsheet
public static string GetColumnName(string cellName) {
Match match = Regex.Match(cellName, "[A-Za-z]+");
return match.Value;
}
}
}
|
上記のコードのうち、セルの表示形式を設定するのは25行目～42行目の部分(ハイライトしています)です。
それ以外の部分は以前の記事「まずはセルデータから - その3 」で紹介した部分と同一です。
以下、25行目～42行目の抜粋です。
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
//現在のs属性から現在のxfを取得
CellFormat current_xf;
IEnumerable<CellFormat> cell_formats = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements<CellFormat>();
if (cell.StyleIndex == null) {
current_xf = new CellFormat();
current_xf.NumberFormatId = new UInt32Value(CELL_NUMBER_FORMAT_ID);
bookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(current_xf);
cell.StyleIndex = new UInt32Value((uint)bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1);
} else {
current_xf = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt((int)cell.StyleIndex.Value) as CellFormat;
if(current_xf.NumberFormatId != CELL_NUMBER_FORMAT_ID) {
current_xf = current_xf.CloneNode(true) as CellFormat;
current_xf.NumberFormatId = new UInt32Value(CELL_NUMBER_FORMAT_ID);
bookPart.WorkbookStylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(current_xf);
cell.StyleIndex = new UInt32Value((uint)bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1);
}
}
|
29行目でcellにスタイルの適用があるかどうかをチェックします。スタイルの指定がなければ、新たにxf要素を作成してNumberFormatIDを4にセットし、CellFormats末尾に追加します。
cellにスタイルの適用がある場合はそのxf要素を取得します。
36行目で現在のxf要素のNumberFormatIDがCELL_NUMBER_FORMAT_ID(==4)に一致しないことをチェックしていますが、このプログラムを複数回実行した場合に不要なxfを39行目で追加してしまわないようにするための処置です。
不要なxfがたくさんあったからといってファイル(C:\Users\Public\Documents\Book1.xlsx)を破損することはありませんが、実行するたびにファイルサイズが大きくなるのは嫌だなぁ…と🙂
37行目では変数current_xfをそのCloneで置き換えています。
今回の処理対象ファイル(Book1.xlsx)に限ってはこの行は不要ですが、実務に用いる場合は必要になります。
Excelを使ってファイルを編集する場合、セルをコピーして作業をしますよね？このとき、セルのスタイルも一緒にコピーされます。
37行目でCloneせずに38行目でNumberFormatIDを書き換えてしまうと、同じスタイルが適用されている(=同じxfを参照している)セルについても表示形式が変わってしまうことになってしまいます。
