Last active
September 24, 2018 09:16
-
-
Save mizuneko/fb258dbd44decf6af838a95ce4fbc4e4 to your computer and use it in GitHub Desktop.
[ClosedXMLでExcel帳票] Excelで帳票のテンプレートを作成しておくことで、Excel帳票を作成します。ヘッダやフッタはExcelに記載した${templatename}の箇所を置換し、明細は特定場所から行をコピーしながら貼り付けしています。 #ClosedXML
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment