Cellの書式を設定する(OpenXML編)
クローバーフィールドの杉山です。
久しぶりのブログ記事です。
年末、年度末は環境屋さんの繫忙期で、Cellの書式をOpenXMLで定義するための調査時間が取れませんでした。
別に謹慎していた訳ではありません。
さて、OpenXMLで書式を設定するために様々なサイトの記事を参考にさせていただきました。
出来た不細工なコードは下記の通りです。
OpenXMLではOffice Open XML形式のファイルを意識してコードを書く必要があります。
この辺りはClosedXML, NPO, EPPlusなどでは、意識していなかった部分ですが、これがまた面倒くさいものでした。
ちなみに下記のコードをビルドして実行すると書式の情報が不足しているらしく、Excelで書式の不足部分を追加してくれます。
不足部分を調査してコードに追加することを断念しました。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Globalization;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace ExcelOpenXML
{
class Program
{
static void Main(string[] args)
{
String filepath = @"C:\output\OpenXML.xlsx";
// ブック(book)の追加
SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// スタイルシートの追加
WorkbookStylesPart stylesPart = workbookpart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
// フォント定義
stylesPart.Stylesheet.Fonts = new Fonts();
stylesPart.Stylesheet.Fonts.Count = 1;
stylesPart.Stylesheet.Fonts.AppendChild(new Font());
// 塗りつぶしの定義
stylesPart.Stylesheet.Fills = new Fills();
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } });
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } });
// ボーダー定義
stylesPart.Stylesheet.Borders = new Borders();
stylesPart.Stylesheet.Borders.Count = 1;
stylesPart.Stylesheet.Borders.AppendChild(new Border());
// セルスタイルフォーマット定義
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.Count = 1;
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
// セルフォーマット定義を追加
stylesPart.Stylesheet.CellFormats = new CellFormats();
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
// シート(sheet)の追加
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
// 行(row)の追加
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
Row row = new Row() { RowIndex = 1 };
sheetData.Append(row);
// セル(cell)の追加
Cell refCell = null;
Cell newCell = new Cell()
{
CellReference = "A1",
DataType = CellValues.String,
CellValue = new CellValue("Hello Excel!!")
};
row.InsertBefore(newCell, refCell);
newCell = new Cell()
{
CellReference = "B1",
DataType = CellValues.String,
CellValue = new CellValue("0123456789")
};
row.InsertBefore(newCell, refCell);
row = new Row() { RowIndex = 2 };
sheetData.Append(row);
// スタイルシートにセルフォーマット定義を追加
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat()
{
FormatId = 0,
NumberFormatId = 4,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
});
stylesPart.Stylesheet.CellFormats.Count
= new UInt32Value((uint)stylesPart.Stylesheet.CellFormats.Count());
newCell = new Cell()
{
CellReference = "A2",
DataType = CellValues.Number,
CellValue = new CellValue("1234"),
StyleIndex = new UInt32Value(stylesPart.Stylesheet.CellFormats.Count - 1)
};
row.InsertBefore(newCell, refCell);
newCell = new Cell()
{
CellReference = "B2",
DataType = CellValues.Number,
CellValue = new CellValue("2345")
};
row.InsertBefore(newCell, refCell);
row = new Row() { RowIndex = 3 };
sheetData.Append(row);
newCell = new Cell()
{
CellReference = "A3",
DataType = CellValues.Number,
CellValue = new CellValue("2019/9/1")
};
row.InsertBefore(newCell, refCell);
// スタイルシートにセルフォーマット定義を追加
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat()
{
FormatId = 0,
NumberFormatId = 14,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
});
stylesPart.Stylesheet.CellFormats.Count
= new UInt32Value((uint)stylesPart.Stylesheet.CellFormats.Count());
newCell = new Cell()
{
CellReference = "B3",
DataType = CellValues.Number,
CellValue = new CellValue(new DateTime(2020,2,25).ToOADate().ToString(CultureInfo.InvariantCulture)),
StyleIndex = new UInt32Value(stylesPart.Stylesheet.CellFormats.Count - 1)
};
row.InsertBefore(newCell, refCell);
row = new Row() { RowIndex = 4 };
sheetData.Append(row);
newCell = new Cell()
{
CellReference = "A4",
DataType = CellValues.Number,
CellValue = new CellValue("1:2:3")
};
row.InsertBefore(newCell, refCell);
// スタイルシートに新しいセルフォーマット定義を追加する
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat()
{
FormatId = 0,
NumberFormatId = 21,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U,
ApplyNumberFormat = BooleanValue.FromBoolean(true),
});
stylesPart.Stylesheet.CellFormats.Count
= new UInt32Value((uint)stylesPart.Stylesheet.CellFormats.Count());
newCell = new Cell()
{
CellReference = "B4",
DataType = CellValues.Number,
CellValue = new CellValue(new DateTime(2020, 2, 26,22,10,12).ToOADate().ToString(CultureInfo.InvariantCulture)),
StyleIndex = new UInt32Value(stylesPart.Stylesheet.CellFormats.Count - 1)
};
row.InsertBefore(newCell, refCell);
// ブックの保存
workbookpart.Workbook.Save();
// ドキュメントオフジェクトを閉じる
spreadsheetDocument.Close();
}
}
}
ちなみに実行結果は下記のようになります。
セルA2に対して、”#,##0.00″の書式を適用しています。
また、B3, C3に対して日付形式の書式を適用しています。
これで当初予定のライブラリを確認しました。
実際に利用するのであれば、CloseXMLが一番使い良い印象でした。
実はNPOIで書きかけのプログラムがあったのですが、CloseXMLで書き直したいと思います。