Open XML SDK を利用してExcelのセルの背景色を設定するコードを紹介します。
概要
こちらの記事でExcelシートのセルに値を設定するコードを紹介しました。
この記事では、セルへの値の設定とExcelシートのセルの背景色を設定するコードを紹介します。
事前準備
プロジェクトを作成しOpen XML SDKをインストールします。インストール手順は
こちらの記事を参照してください。
プログラム
UI
下図のフォームを作成します。ボタンが4つ配置されていますが、今回のプログラムで利用するボタンは1つです。
コード
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace ExcelApp
{
public partial class Form_DataOutput : Form
{
List<DataItem> dataList = new List<DataItem>();
class DataItem
{
public int id;
public string name;
public string code;
public int price;
}
public Form_DataOutput()
{
InitializeComponent();
}
private void button4_Click(object sender, EventArgs e)
{
dataList.Clear();
dataList.Add(new DataItem() { id = 1, name = "ぺんぎんクッキー", code = "PCD-001", price = 180 });
dataList.Add(new DataItem() { id = 2, name = "あひるサブレ", code = "PCD-002", price = 120 });
dataList.Add(new DataItem() { id = 3, name = "らくだキャラメル", code = "PCD-003", price = 40 });
dataList.Add(new DataItem() { id = 4, name = "しろくまアイス", code = "PCA-001", price = 230 });
dataList.Add(new DataItem() { id = 5, name = "だちょうカステラ", code = "PCD-004", price = 90 });
//シート作成
SpreadsheetDocument doc = SpreadsheetDocument.Create("c:\\data\\test-ex.xlsx", SpreadsheetDocumentType.Workbook);
WorkbookPart wbpart = doc.AddWorkbookPart();
wbpart.Workbook = new Workbook();
WorksheetPart wspart = wbpart.AddNewPart<WorksheetPart>();
wspart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Sheet sheet = new Sheet();
sheet.Id = doc.WorkbookPart.GetIdOfPart(wspart);
sheet.SheetId = 1;
sheet.Name = "商品";
sheets.Append(sheet);
//Style
WorkbookStylesPart stylesPart = wbpart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
stylesPart.Stylesheet.Save();
//Font Style
stylesPart.Stylesheet.Fonts = new Fonts();
stylesPart.Stylesheet.Fonts.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Font());
stylesPart.Stylesheet.Fonts.Count = 1;
//Fill Style
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 } });
PatternFill solidFill = new PatternFill() { PatternType = PatternValues.Solid };
solidFill.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FF89daff") };
solidFill.BackgroundColor = new BackgroundColor { Indexed = 64 };
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = solidFill });
stylesPart.Stylesheet.Fills.Count = 3;
//Border Style
stylesPart.Stylesheet.Borders = new Borders();
stylesPart.Stylesheet.Borders.AppendChild(new Border());
stylesPart.Stylesheet.Borders.Count = 1;
//Cell Style
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
stylesPart.Stylesheet.CellStyleFormats.Count = 1;
stylesPart.Stylesheet.CellFormats = new CellFormats();
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
CellFormat cellFormat = new CellFormat();
cellFormat.FormatId = 0;
cellFormat.FontId = 0;
cellFormat.ApplyFont = true;
cellFormat.BorderId = 0;
cellFormat.FillId = 2;
cellFormat.ApplyFill = true;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
stylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(cellFormat);
stylesPart.Stylesheet.CellFormats.Count = 2;
stylesPart.Stylesheet.Save();
//データ挿入
Row row;
UInt32Value row_index = 1;
foreach (DataItem di in dataList) {
row = new Row() { RowIndex = row_index };
wspart.Worksheet.GetFirstChild<SheetData>().Append(row);
Cell refCell = null;
Cell newCell;
newCell = new Cell() { CellReference = "A" + row_index.ToString() };
newCell.CellValue = new CellValue(di.id.ToString());
newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
row.InsertAfter(newCell, refCell);
refCell = newCell;
//
newCell = new Cell() { CellReference = "B" + row_index.ToString() };
newCell.CellValue = new CellValue(di.name);
newCell.DataType = new EnumValue<CellValues>(CellValues.String);
row.InsertAfter(newCell, refCell);
refCell = newCell;
//
newCell = new Cell() { CellReference = "C" + row_index.ToString() };
newCell.CellValue = new CellValue(di.code);
newCell.DataType = new EnumValue<CellValues>(CellValues.String);
//newCell.StyleIndex = new UInt32Value(stylesPart.Stylesheet.CellFormats.Count - 1);
newCell.StyleIndex = new UInt32Value((uint)1);
row.InsertAfter(newCell, refCell);
refCell = newCell;
//
newCell = new Cell() { CellReference = "D" + row_index.ToString() };
newCell.CellValue = new CellValue(di.price.ToString());
newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
row.InsertAfter(newCell, refCell);
refCell = newCell;
row_index++;
}
wbpart.Workbook.Save();
doc.Close();
MessageBox.Show("Excel Bookを作成しました。");
}
}
}
解説
Open XMLを利用してファイルの出力をする処理に関しては
こちらの記事を参照してください。
SpreadsheetDocumentの構造は下図になっています。WorkbookStylesPartを利用する場合は、この構造のインスタンスオブジェクトを作成する必要があります。
今回は背景色を水色に設定するスタイルを追加するため、FillsオブジェクトにPatternFillオブジェクトを一つ追加します。FillsのPatterFillが3つになるため、FillsオブジェクトのCountプロパティに3を設定します。
//Fill Style
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 } });
PatternFill solidFill = new PatternFill() { PatternType = PatternValues.Solid };
solidFill.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FF89daff") };
solidFill.BackgroundColor = new BackgroundColor { Indexed = 64 };
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = solidFill });
stylesPart.Stylesheet.Fills.Count = 3;
新しいCellFormatオブジェクトを追加します。CellFormatオブジェクトのFillIdに先ほど作成した背景を水色で塗りつぶすFill オブジェクトのインデックス番号を設定します。
(3つ目に追加していますので、インデックス番号は2になります。)
//Cell Style
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
stylesPart.Stylesheet.CellStyleFormats.Count = 1;
stylesPart.Stylesheet.CellFormats = new CellFormats();
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
CellFormat cellFormat = new CellFormat();
cellFormat.FormatId = 0;
cellFormat.FontId = 0;
cellFormat.ApplyFont = true;
cellFormat.BorderId = 0;
cellFormat.FillId = 2;
cellFormat.ApplyFill = true;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
stylesPart.Stylesheet.CellFormats.AppendChild<CellFormat>(cellFormat);
stylesPart.Stylesheet.CellFormats.Count = 2;
stylesPart.Stylesheet.Save();
RowオブジェクトにCellオブジェクトを追加する際に、StyleIndexプロパティにCellFormatsオブジェクト内の CellFormat のインデックス番号を設定します。
(FillIdに2を設定した塗りつぶしスタイルのCellFormatオブジェクトはデフォルトの空のセルスタイルの次、2つ目に追加していますので、インデックス番号は1になります。)
newCell = new Cell() { CellReference = "C" + row_index.ToString() };
newCell.CellValue = new CellValue(di.code);
newCell.DataType = new EnumValue<CellValues>(CellValues.String);
newCell.StyleIndex = new UInt32Value((uint)1);
row.InsertAfter(newCell, refCell);
refCell = newCell;
実行結果
プロジェクトを実行します。下図のウィンドウが表示されます。
コードを実装したボタン(今回の例では[Data Out4]ボタン)をクリックします。処理が完了するとメッセージボックスが表示されます。
出力先のディレクトリにExcelワークシートのファイルが出力されています。
作成されたExcelワークシートのファイルを開きます。下図の画面が表示されます。C列のセルの背景色が水色に設定されていることが確認できます。
セルの背景色を設定できました。
参考:何も設定を入れない場合
何も設定を入れない場合にWorkbookStylesPartのインスタンスオブジェクトを作成するコードは以下になります。
private void button5_Click(object sender, EventArgs e)
{
dataList.Clear();
dataList.Add(new DataItem() { id = 1, name = "ぺんぎんクッキー", code = "PCD-001", price = 180 });
dataList.Add(new DataItem() { id = 2, name = "あひるサブレ", code = "PCD-002", price = 120 });
dataList.Add(new DataItem() { id = 3, name = "らくだキャラメル", code = "PCD-003", price = 40 });
dataList.Add(new DataItem() { id = 4, name = "しろくまアイス", code = "PCA-001", price = 230 });
dataList.Add(new DataItem() { id = 5, name = "だちょうカステラ", code = "PCD-004", price = 90 });
//シート作成
SpreadsheetDocument doc = SpreadsheetDocument.Create("c:\\data\\test-ex.xlsx", SpreadsheetDocumentType.Workbook);
WorkbookPart wbpart = doc.AddWorkbookPart();
wbpart.Workbook = new Workbook();
WorksheetPart wspart = wbpart.AddNewPart<WorksheetPart>();
wspart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Sheet sheet = new Sheet();
sheet.Id = doc.WorkbookPart.GetIdOfPart(wspart);
sheet.SheetId = 1;
sheet.Name = "商品";
sheets.Append(sheet);
//Style
WorkbookStylesPart stylesPart = wbpart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
stylesPart.Stylesheet.Save();
//Font Style
stylesPart.Stylesheet.Fonts = new Fonts();
stylesPart.Stylesheet.Fonts.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Font());
stylesPart.Stylesheet.Fonts.Count = 1;
//Fill Style
stylesPart.Stylesheet.Fills = new Fills();
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } });
stylesPart.Stylesheet.Fills.Count = 1;
//Border Style
stylesPart.Stylesheet.Borders = new Borders();
stylesPart.Stylesheet.Borders.AppendChild(new Border());
stylesPart.Stylesheet.Borders.Count = 1;
//Cell Style
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
stylesPart.Stylesheet.CellStyleFormats.Count = 1;
stylesPart.Stylesheet.CellFormats = new CellFormats();
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
stylesPart.Stylesheet.CellFormats.Count = 1;
stylesPart.Stylesheet.Save();
//データ挿入
Row row;
UInt32Value row_index = 1;
foreach (DataItem di in dataList) {
row = new Row() { RowIndex = row_index };
wspart.Worksheet.GetFirstChild<SheetData>().Append(row);
Cell refCell = null;
Cell newCell;
newCell = new Cell() { CellReference = "A" + row_index.ToString() };
newCell.CellValue = new CellValue(di.id.ToString());
newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
row.InsertAfter(newCell, refCell);
refCell = newCell;
//
newCell = new Cell() { CellReference = "B" + row_index.ToString() };
newCell.CellValue = new CellValue(di.name);
newCell.DataType = new EnumValue<CellValues>(CellValues.String);
row.InsertAfter(newCell, refCell);
refCell = newCell;
//
newCell = new Cell() { CellReference = "C" + row_index.ToString() };
newCell.CellValue = new CellValue(di.code);
newCell.DataType = new EnumValue<CellValues>(CellValues.String);
row.InsertAfter(newCell, refCell);
refCell = newCell;
//
newCell = new Cell() { CellReference = "D" + row_index.ToString() };
newCell.CellValue = new CellValue(di.price.ToString());
newCell.DataType = new EnumValue<CellValues>(CellValues.Number);
row.InsertAfter(newCell, refCell);
refCell = newCell;
//row.InsertBefore(
row_index++;
}
wbpart.Workbook.Save();
doc.Close();
MessageBox.Show("Excel Bookを作成しました。");
}
著者
iPentecのメインプログラマー
C#, ASP.NET の開発がメイン、少し前まではDelphiを愛用
最終更新日: 2020-10-18
作成日: 2020-10-16