[ClosedXMLでExcel帳票] Excelで帳票のテンプレートを作成しておくことで、Excel帳票を作成します。ヘッダやフッタはExcelに記載した${templatename}の箇所を置換し、明細は特定場所から行をコピーしながら貼り付けしています。 #ClosedXML
using ClosedXML.Excel;
using System.Data;
using System.IO;
namespace ExcelReport
{
public class Program
{
static readonly int firstDtilRow = 16;
static void Main(string[] args)
{
string tmppath = @".\template.xlsx";
string outpath = @".\Test.xlsx";
if (!File.Exists(tmppath))
{
throw new FileNotFoundException("テンプレートファイルが存在しません。");
}
if (File.Exists(outpath))
{
File.Delete(outpath);
}
File.Copy(tmppath, outpath);
var book = new XLWorkbook(outpath);
var tmpsheet = book.Worksheet(1);
// ヘッダ部データの取得
DataTable dh = SampleData.SampleDataHeader();
// ヘッダ毎にシートを作成する
for (int i = 1; i <= dh.Rows.Count; i++)
{
if (i > 1)
{
tmpsheet.CopyTo("Sheet" + (i).ToString(), i);
}
}
for (int i = 0; i < dh.Rows.Count; i++)
{
var sheet = book.Worksheet(i + 1);
// ヘッダデータのセット
foreach (var row in sheet.RangeUsed().Rows())
{
SetData("datetime", row, dh.Rows[i]);
SetData("comp", row, dh.Rows[i]);
SetData("dept", row, dh.Rows[i]);
SetData("name", row, dh.Rows[i]);
SetData("postalcode", row, dh.Rows[i]);
SetData("address1", row, dh.Rows[i]);
SetData("address2", row, dh.Rows[i]);
SetData("mycomp", row, dh.Rows[i]);
SetData("mydept", row, dh.Rows[i]);
SetData("myuser", row, dh.Rows[i]);
SetData("mypostalcode", row, dh.Rows[i]);
SetData("myaddress1", row, dh.Rows[i]);
SetData("myaddress2", row, dh.Rows[i]);
SetData("myphone", row, dh.Rows[i]);
SetData("deliperiod", row, dh.Rows[i]);
SetData("val", row, dh.Rows[i]);
}
DataTable dd = SampleData.SampleDataDetail(dh.Rows[i]);
if (dd.Rows.Count > 1)
{
sheet.Row(firstDtilRow).InsertRowsBelow(dd.Rows.Count - 1);
for (int j = 1; j < dd.Rows.Count; j++)
{
// 行をコピーすると書式等が消えるため、再設定
sheet.Row(firstDtilRow + j).Height = 15;
sheet.Range(firstDtilRow + j, 2, firstDtilRow + j, 9).Merge(false);
sheet.Range(firstDtilRow + j, 10, firstDtilRow + j, 23).Merge(false);
sheet.Range(firstDtilRow + j, 24, firstDtilRow + j, 30).Merge(false);
sheet.Range(firstDtilRow + j, 31, firstDtilRow + j, 33).Merge(false);
sheet.Range(firstDtilRow + j, 34, firstDtilRow + j, 40).Merge(false);
sheet.Range(firstDtilRow + j, 41, firstDtilRow + j, 47).Merge(false);
}
}
// 明細データのセット
InsertData(sheet, dd.Rows, firstDtilRow);
}
book.Save();
}
/// <summary>
/// offset位置からの明細データのセット
/// </summary>
/// <param name="sheet"></param>
/// <param name="rows"></param>
/// <param name="offset"></param>
private static void InsertData(IXLWorksheet sheet, DataRowCollection rows, int offset)
{
for (int i = 0; i < rows.Count; i++)
{
sheet.Cell(offset + i, 2).Value = rows[i]["prodcode"].ToString();
sheet.Cell(offset + i, 10).Value = rows[i]["prodname"].ToString();
sheet.Cell(offset + i, 24).Value = rows[i]["qt"];
sheet.Cell(offset + i, 31).Value = rows[i]["unit"].ToString();
sheet.Cell(offset + i, 34).Value = rows[i]["price"];
sheet.Cell(offset + i, 41).Value = rows[i]["amount"];
}
}
/// <summary>
/// テンプレートの書式(${templateName})に値をセットする
/// </summary>
/// <param name="templateName"></param>
/// <param name="row"></param>
/// <param name="dr"></param>
private static void SetData(string templateName, IXLRangeRow row, DataRow dr)
{
string searchName = "${" + templateName + "}";
row.Search(searchName).Value = dr[templateName].ToString();
}
}
}