前回は
で、さまざまな発注先のデータがつまったExcelファイルを発注先ごとに分割するアプリをつくりました。今回は発注書のテンプレをつかって発注先ごとにExcelファイルを送ることができるようにします。
問題のテンプレですが、これをつかいます。
これだと17行から29行までを差し替えればこれをもとに発注書をつくれそうです。
まずこのテンプレートを実行ファイルがあるフォルダにコピーしておきます。ファイル名は「発注書_タテ型.xlsx」にします。
[開始]ボタンをクリックしたときの処理は前回とほとんど同じです。処理をする前にtemplateFilePathが存在するかどうか確認しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
public partial class Form1 : Form { // フィールド変数 string templateFilePath = Application.StartupPath + "\\発注書_タテ型.xlsx"; private void button1_Click(object sender, EventArgs e) { if(!File.Exists(templateFilePath)) { return; } OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "Exelファイル|*.xlsx"; dialog.DefaultExt = "xlsx"; if(dialog.ShowDialog() == DialogResult.OK) { string filePath = dialog.FileName; List<Data> datas = GetDatasFromExel(filePath); var query = datas.GroupBy(x => x.Supplier); string outputFolderPath = OutputFolderPath(filePath); foreach(IGrouping<string, Data> group in query) { OutputFiles(outputFolderPath, group); } } dialog.Dispose(); MessageBox.Show("処理は完了しました"); } } |
OutputFilesメソッドを変更しました。テンプレートのファイルのパスを第一引数、新しく作成するファイルを第二引数にして、自作メソッド OutputFile(string templateFilePath, string newFilePath, IGrouping<string, Data> group)メソッドを呼び出しています。
|
1 2 3 4 5 6 7 8 |
public partial class Form1 : Form { void OutputFiles(string outputFolderPath, IGrouping<string, Data> group) { string ExcelFilePath = outputFolderPath + "\\" + group.Key + ".xlsx"; OutputFile(templateFilePath, ExcelFilePath, group); } } |
OutputFile(string templateFilePath, string newFilePath, IGrouping<string, Data> group)メソッドでは、テンプレートのファイルの前半部分をコピーして、そのあと発注する商品の情報を書き込み、最後にテンプレートファイルの後半部分をコピーしています。
各セルのスタイルを取得して新しく生成する発注書のセルに適用しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
public partial class Form1 : Form { IXLStyle XlStyleItem = null; // 発注書の項目名のスタイル IXLStyle XlStyleInt = null; // 発注数量のスタイル IXLStyle XlStyleString = null; // 商品名のスタイル IXLStyle XlStyleYen = null; // 金額のスタイル void OutputFile(string templateFilePath, string newFilePath, IGrouping<string, Data> group) { if(!File.Exists(templateFilePath)) { return; } using(IXLWorkbook workbook1 = new XLWorkbook(templateFilePath)) { IXLWorksheet worksheet1 = workbook1.Worksheet("発注書"); // Excelファイルを作る using(var newWorkbook = new XLWorkbook()) { // ワークシートを追加する IXLWorksheet newWorksheet1 = newWorkbook.Worksheets.Add("発注書"); newWorksheet1.ColumnWidth = 5; newWorkbook.SaveAs(newFilePath); } XlStyleItem = worksheet1.Cell("B17").Style; // 項目名 XlStyleInt = worksheet1.Cell("J18").Style; // 数量 XlStyleString = worksheet1.Cell("B18").Style; // 発注品のスタイル XlStyleYen = worksheet1.Cell("O18").Style; // 金額のスタイル using(var workbook2 = new XLWorkbook(newFilePath)) { IXLWorksheet newWorksheet1 = workbook2.Worksheet("発注書"); newWorksheet1.ColumnWidth = 5; // テンプレの前半をコピー IXLRange range = worksheet1.Range("A1:Q16"); IXLCell toCell = newWorksheet1.Cell("A1"); range.CopyTo(toCell); // セルに値や数式をセット（後述） SetColomHeder(newWorksheet1, 17); //SetColomHeder(newWorksheet1, 17); int start = 18; // 発注する内容をセット（後述） int end = SetOrderList(newWorksheet1, start, group) + start-1; newWorksheet1.Range(17, 1, end, 17).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); newWorksheet1.Range(end + 2, 10, end + 4, 17).Style .Border.SetTopBorder(XLBorderStyleValues.Thin) .Border.SetBottomBorder(XLBorderStyleValues.Thin) .Border.SetLeftBorder(XLBorderStyleValues.Thin) .Border.SetRightBorder(XLBorderStyleValues.Thin); // 小計をセット（後述） string subtotalCell = SetSubtotal(newWorksheet1, start, end); // 消費税をセット（後述） string consumptionTaxCell = SetConsumptionTax(newWorksheet1, start, end); // 合計をセット（後述） string totalCell = SetTotal(newWorksheet1, end, subtotalCell, consumptionTaxCell); // テンプレのフッターをコピー range = worksheet1.Range("A36:Q40"); toCell = GetCellFooterStart(newWorksheet1, end); range.CopyTo(toCell); // テンプレに値をセット（発注先と金額） newWorksheet1.Cell(3, "A").Value = group.Key; newWorksheet1.Cell(15, "D").SetFormulaA1(totalCell); newWorksheet1.Cell(15, "D").Style.NumberFormat.SetFormat("\#,##0"); newWorksheet1.ShowGridLines = false; workbook2.SaveAs(newFilePath); } } } } |
SetColomHeder(IXLWorksheet newWorksheet, int row)メソッドは発注書の項目（摘要、数量、単価、金額）をセットするためのメソッドです。スタイルはテンプレートのセル”B17″を使用しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
public partial class Form1 : Form { void SetColomHeder(IXLWorksheet newWorksheet, int row) { newWorksheet.Cell(row, "A").Value = "No."; newWorksheet.Cell(row, "A").Style = XlStyleItem; newWorksheet.Cell(row, "B").Value = "商品名"; newWorksheet.Cell(row, "B").Style = XlStyleItem; newWorksheet.Range(row, 2, 17, 9).Merge(); newWorksheet.Cell(row, "J").Value = "単価"; newWorksheet.Cell(row, "J").Style = XlStyleItem; newWorksheet.Range(row, 10, 17, 12).Merge(); newWorksheet.Cell(row, "M").Value = "数量"; newWorksheet.Cell(row, "M").Style = XlStyleItem; newWorksheet.Range(row, 13, 17, 14).Merge(); newWorksheet.Cell(row, "O").Value = "金額"; newWorksheet.Cell(row, "O").Style = XlStyleItem; newWorksheet.Range(row, 15, 17, 17).Merge(); } } |
SetOrderList(IXLWorksheet newWorksheet, int firstRow, IGrouping<string, Data> group)メソッドは発注する商品の情報をセットするためのメソッドです。
セルは、BからI、JからL、MからN、OからQを統合しています。またスタイルも文字か数字か、数字でも金額なにかどうかで変更しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
public partial class Form1 : Form { int SetOrderList(IXLWorksheet newWorksheet, int firstRow, IGrouping<string, Data> group) { int index = 1; int row = firstRow; foreach(Data data in group) { newWorksheet.Cell(row, "A").Value = index; newWorksheet.Cell(row, "A").Style = XlStyleInt; newWorksheet.Cell(row, "B").Value = data.Product; newWorksheet.Cell(row, "B").Style = XlStyleString; newWorksheet.Range(row, 2, row, 9).Merge(); // 単価 newWorksheet.Cell(row, "J").Value = data.Unitprice; newWorksheet.Cell(row, "J").Style = XlStyleYen; newWorksheet.Range(row, 10, row, 12).Merge(); // 数量 newWorksheet.Cell(row, "M").Value = data.Quantity; newWorksheet.Cell(row, "M").Style = XlStyleInt; newWorksheet.Range(row, 13, row, 14).Merge(); // 金額 newWorksheet.Cell(row, "O").FormulaA1 = String.Format("= J{0}*M{0}", row); newWorksheet.Cell(row, "O").Style = XlStyleYen; newWorksheet.Range(row, 15, row, 17).Merge(); index++; row++; } return index - 1; } } |
SetSubtotal(IXLWorksheet newWorksheet, int rowOrderListStart, int rowOrderListEnd)メソッドは小計（税なし）を表示するためのものです。またSetConsumptionTax(IXLWorksheet newWorksheet, int rowOrderListStart, int rowOrderListEnd)とSetTotal(IXLWorksheet newWorksheet, int rowOrderListEnd, string Subtotal, string ConsumptionTax)は消費税と合計金額をセットするためのものです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
public partial class Form1 : Form { string SetSubtotal(IXLWorksheet newWorksheet, int rowOrderListStart, int rowOrderListEnd) { newWorksheet.Cell(rowOrderListEnd + 2, "J").Value = "小計"; newWorksheet.Range(rowOrderListEnd + 2, 10, rowOrderListEnd + 2, 12).Merge(); newWorksheet.Cell(rowOrderListEnd + 2, "M").FormulaA1 = String.Format("=SUM(O{0}:O{1})", rowOrderListStart, rowOrderListEnd); newWorksheet.Cell(rowOrderListEnd + 2, "J").Style = XlStyleItem; newWorksheet.Cell(rowOrderListEnd + 2, "M").Style = XlStyleYen; newWorksheet.Range(rowOrderListEnd + 2, 13, rowOrderListEnd + 2, 17).Merge(); return "M" + (rowOrderListEnd + 2).ToString(); } string SetConsumptionTax(IXLWorksheet newWorksheet, int rowOrderListStart, int rowOrderListEnd) { newWorksheet.Cell(rowOrderListEnd + 3, "J").Value = "消費税"; newWorksheet.Range(rowOrderListEnd + 3, 10, rowOrderListEnd + 3, 12).Merge(); newWorksheet.Cell(rowOrderListEnd + 3, "J").Style = XlStyleItem; newWorksheet.Cell(rowOrderListEnd + 3, "M").SetFormulaA1(String.Format("=SUM(O{0}:O{1})*0.1", rowOrderListStart, rowOrderListEnd)); newWorksheet.Cell(rowOrderListEnd + 3, "M").Style = XlStyleYen; newWorksheet.Range(rowOrderListEnd + 3, 13, rowOrderListEnd + 3, 17).Merge(); return "M" + (rowOrderListEnd + 3).ToString(); } string SetTotal(IXLWorksheet newWorksheet, int rowOrderListEnd, string Subtotal, string ConsumptionTax) { newWorksheet.Cell(rowOrderListEnd + 4, "J").Value = "合計"; newWorksheet.Range(rowOrderListEnd + 4, 10, rowOrderListEnd + 4, 12).Merge(); newWorksheet.Cell(rowOrderListEnd + 4, "J").Style = XlStyleItem; newWorksheet.Cell(rowOrderListEnd + 4, "M").SetFormulaA1(String.Format("={0} + {1}", Subtotal, ConsumptionTax)); newWorksheet.Cell(rowOrderListEnd + 4, "M").Style = XlStyleYen; newWorksheet.Range(rowOrderListEnd + 4, 13, rowOrderListEnd + 4, 17).Merge(); return "M" + (rowOrderListEnd + 4).ToString(); } } |
以上をセットしたら備考欄をセットすれば完成です。
GetCellFooterStart(IXLWorksheet newWorksheet, int rowOrderListEnd)メソッドは備考欄をどこへセットするかを求めるメソッドです。
|
1 2 3 4 5 6 7 |
public partial class Form1 : Form { IXLCell GetCellFooterStart(IXLWorksheet newWorksheet, int rowOrderListEnd) { return newWorksheet.Cell(rowOrderListEnd + 6, "A"); } } |
これが終わったらそれ以外にも必要なデータをセルにセットしてしまえば完成です。
|
1 2 3 4 5 6 |
newWorksheet1.Cell(3, "A").Value = group.Key; // 発注先の会社名をセット newWorksheet1.Cell(15, "D").SetFormulaA1(totalCell); // 合計金額をセット newWorksheet1.Cell(15, "D").Style.NumberFormat.SetFormat("\#,##0"); newWorksheet1.ShowGridLines = false; // 罫線は表示しない workbook2.SaveAs(newFilePath); // 保存 |
