Cellの書式を設定する(OpenXML編)

著者：杉山貴規

公開日：2020/02/28

最終更新日：2020/02/27

カテゴリー：技術情報

タグ：Excel

クローバーフィールドの杉山です。

久しぶりのブログ記事です。
年末、年度末は環境屋さんの繫忙期で、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();
        }
    }
}

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

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

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で書き直したいと思います。