private void SetDetail(SheetData data, ArrearagesList item, ref uint rowIndex, List <uint> indices)
{
rowIndex++;
data.SetNumber(rowIndex, "A", item.Id, cfidxDA1);
data.SetText(rowIndex, "B", item.CustomerCode, cfidxDBI1);
data.SetText(rowIndex, "C", item.Tel, cfidxDBI1);
data.SetDate(rowIndex, "D", item.BilledAt, cfidxDDate1);
data.SetDate(rowIndex, "E", item.ClosingAt, cfidxDDate1);
data.SetDate(rowIndex, "F", item.OriginalDueAt, cfidxDDate1);
data.SetNumber(rowIndex, "G", item.RemainAmount, cfidxDVal1);
data.SetText(rowIndex, "H", item.InvoiceCode, cfidxDBI1);
data.SetText(rowIndex, "I", item.DepartmentCode, cfidxDBI1);
data.SetText(rowIndex, "J", item.StaffCode, cfidxDJ1);
indices.Add(rowIndex);
rowIndex++;
data.SetStyle(rowIndex, "A", cfidxDA2);
data.SetText(rowIndex, "B", item.CustomerName, cfidxDBI2);
data.SetText(rowIndex, "C", item.CustomerNote, cfidxDBI2);
data.SetDate(rowIndex, "D", item.SalesAt, cfidxDDate2);
data.SetDate(rowIndex, "E", item.DueAt, cfidxDDate2);
data.SetNumber(rowIndex, "F", item.ArrearagesDayCount, cfidxDDay2);
data.SetStyle(rowIndex, "G", cfidxDBI2);
data.SetText(rowIndex, "H", item.Note1, cfidxDBI2);
data.SetText(rowIndex, "I", item.DepartmentName, cfidxDBI2);
data.SetText(rowIndex, "J", item.StaffName, cfidxDJ2);
}
private void SetDetail(SheetData data, BillingAgingListSearch option, BillingAgingList item, ref uint rowIndex)
{
rowIndex++;
var isChildCustomer = item.ParentCustomerFlag != 1 && item.ParentCustomerId.HasValue && option.ConsiderCustomerGroup;
var isParentCustomer = item.ParentCustomerFlag == 1 && option.ConsiderCustomerGroup;
var customerInfo = string.Concat(
isChildCustomer ? " " : "",
DisplayCustomerCode ? $"{item.CustomerCode} " : "",
item.CustomerName);
data.SetText(rowIndex, "A", customerInfo, cfidxDA);
var caption = isParentCustomer ? "小計" : "";
data.SetText(rowIndex, "B", caption, cfidxDB);
data.SetNumber(rowIndex, "C", item.LastMonthRemain, cfidxDCJ);
data.SetNumber(rowIndex, "D", item.CurrentMonthSales, cfidxDCJ);
data.SetNumber(rowIndex, "E", item.CurrentMonthReceipt, cfidxDCJ);
data.SetNumber(rowIndex, "F", item.CurrentMonthMatching, cfidxDCJ);
data.SetNumber(rowIndex, "G", item.CurrentMonthRemain, cfidxDCJ);
data.SetNumber(rowIndex, "H", item.MonthlyRemain0, cfidxDCJ);
data.SetNumber(rowIndex, "I", item.MonthlyRemain1, cfidxDCJ);
data.SetNumber(rowIndex, "J", item.MonthlyRemain2, cfidxDCJ);
data.SetNumber(rowIndex, "K", item.MonthlyRemain3, cfidxDK);
}
private void SetSubtotal(SheetData data, string caption, decimal amount, ref uint rowIndex, List <uint> indices, List <uint> subtotalIndices)
{
rowIndex++;
// 合計行
data.SetText(rowIndex, "A", caption);
data.SetNumber(rowIndex, "G", amount, cfidxSG1);
data.SetStyle(GetCellReference(rowIndex, "A", columnNameEnd: "E"), cfidxS1);
data.SetStyle(rowIndex, "F", cfidxSF1);
data.SetStyle(rowIndex, "H", cfidxSH1);
data.SetStyle(GetCellReference(rowIndex, "I", columnNameEnd: "J"), cfidxS1);
indices.Add(rowIndex);
subtotalIndices.Add(rowIndex);
rowIndex++;
data.SetStyle(GetCellReference(rowIndex, "A", columnNameEnd: "E"), cfidxS2);
data.SetStyle(rowIndex, "F", cfidxSF2);
data.SetStyle(rowIndex, "G", cfidxSG2);
data.SetStyle(rowIndex, "H", cfidxSH2);
data.SetStyle(GetCellReference(rowIndex, "I", columnNameEnd: "J"), cfidxS2);
}
private void SetSubtotal(SheetData data, BillingAgingListSearch opt, BillingAgingList item, ref uint rowIndex)
{
rowIndex++;
// 合計行
var caption
= item.RecordType == 1 ? $"{item.StaffCode} {item.StaffName} 計"
: item.RecordType == 2 ? $"{item.DepartmentCode} {item.DepartmentName} 計"
: item.RecordType == 3 ? (UseForeignCurrency ? "通貨計" : "総合計")
: string.Empty;
data.SetText(rowIndex, "A", caption, cfidxSA);
data.SetStyle(rowIndex, "B", cfidxSB);
data.SetNumber(rowIndex, "C", item.LastMonthRemain, cfidxSCI);
data.SetNumber(rowIndex, "D", item.CurrentMonthSales, cfidxSCI);
data.SetNumber(rowIndex, "E", item.CurrentMonthMatching, cfidxSCI);
data.SetNumber(rowIndex, "F", item.CurrentMonthRemain, cfidxSCI);
data.SetNumber(rowIndex, "G", item.MonthlyRemain0, cfidxSCI);
data.SetNumber(rowIndex, "H", item.MonthlyRemain1, cfidxSCI);
data.SetNumber(rowIndex, "I", item.MonthlyRemain2, cfidxSCI);
data.SetNumber(rowIndex, "J", item.MonthlyRemain3, cfidxSJ);
}
public void Process(SpreadsheetDocument document)
{
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var workbook = (workbookPart.Workbook = new Workbook());
var worksheet = (worksheetPart.Worksheet = new Worksheet());
var bookstylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
var title = "滞留明細一覧";
var sheets = workbook.AppendChild(new Sheets());
var sheet = new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = title,
};
sheets.Append(sheet);
var decNumFmt = Precision == 0 ? 38 : 164;
var stylesheet = bookstylesPart.InitizalizeStylesheet(
initializeNumberingFormats: () => InitializeNumeringFormats(Precision),
initializeFonts: () => InitializeFonts(),
initializeFills: () => InitializeFills(),
initializeBorders: () => InitializeBorders(),
initializeCellFormats: () => InitializeCellFormats(decNumFmt)
);
var columns = new Columns();
columns.Append(new[] {
new Column {
Min = 1, Max = 1, Width = 10.28, CustomWidth = true
},
new Column {
Min = 2, Max = 2, Width = 26.57, CustomWidth = true
},
new Column {
Min = 3, Max = 3, Width = 17.57, CustomWidth = true
},
new Column {
Min = 4, Max = 6, Width = 10, CustomWidth = true
},
new Column {
Min = 7, Max = 7, Width = 14.42, CustomWidth = true
},
new Column {
Min = 8, Max = 8, Width = 19.28, CustomWidth = true
},
new Column {
Min = 9, Max = 9, Width = 25, CustomWidth = true
},
new Column {
Min = 10, Max = 10, Width = 17.57, CustomWidth = true
},
});
worksheet.Append(columns);
var data = new SheetData();
worksheet.Append(data);
if (!string.IsNullOrEmpty(Password))
{
worksheet.Append(new SheetProtection {
Password = Password.ToHash(),
Sheet = true,
Scenarios = true,
});
}
var rowIndex = 1u;
var indices = new List <uint>();
var subtotalIndices = new List <uint>();
var rowbreakIndices = new List <uint>();
data.SetText(rowIndex, "A", title);
data.SetStyle(GetCellReference(rowIndex, "A", columnNameEnd: "J"), cfidxTitle);
rowIndex++;
SetColumnCaptin(data, ref rowIndex, indices);
var endIndex = rowIndex;
var dueAtAmount = 0M;
var customerAmount = 0M;
var staffAmount = 0M;
var departmentAmount = 0M;
var totalAmount = 0M;
ArrearagesList itemBuf = null;
foreach (var item in Items)
{
var requireDueAtSubtotal = RequireDueAtSubtotal(Option, item, itemBuf);
var requireCustomerSubtotal = RequireCustomerSubtotal(Option, item, itemBuf);
var requireStaffSubtotal = RequireStaffSubtotal(Option, item, itemBuf);
var requireDepertmentSubtotal = RequireDepartmentSubtotal(Option, item, itemBuf);
if (requireDueAtSubtotal)
{
SetSubtotal(data, "予定日計", dueAtAmount, ref rowIndex, indices, subtotalIndices);
dueAtAmount = 0M;
}
if (requireCustomerSubtotal)
{
SetSubtotal(data, "得意先計", customerAmount, ref rowIndex, indices, subtotalIndices);
customerAmount = 0M;
}
if (requireStaffSubtotal)
{
SetSubtotal(data, "担当者計", staffAmount, ref rowIndex, indices, subtotalIndices);
staffAmount = 0M;
}
if (requireDepertmentSubtotal)
{
SetSubtotal(data, "請求部門計", departmentAmount, ref rowIndex, indices, subtotalIndices);
departmentAmount = 0M;
}
if (requireCustomerSubtotal || requireCustomerSubtotal || requireStaffSubtotal || requireDepertmentSubtotal)
{
rowbreakIndices.Add(rowIndex);
}
SetDetail(data, item, ref rowIndex, indices);
dueAtAmount += item.RemainAmount;
customerAmount += item.RemainAmount;
staffAmount += item.RemainAmount;
departmentAmount += item.RemainAmount;
totalAmount += item.RemainAmount;
itemBuf = item;
}
if (RequireDueAtSubtotal(Option, null, itemBuf))
{
SetSubtotal(data, "予定日計", dueAtAmount, ref rowIndex, indices, subtotalIndices);
}
if (RequireCustomerSubtotal(Option, null, itemBuf))
{
SetSubtotal(data, "得意先計", customerAmount, ref rowIndex, indices, subtotalIndices);
}
if (RequireStaffSubtotal(Option, null, itemBuf))
{
SetSubtotal(data, "担当者計", staffAmount, ref rowIndex, indices, subtotalIndices);
}
if (RequireDepartmentSubtotal(Option, null, itemBuf))
{
SetSubtotal(data, "請求部門計", departmentAmount, ref rowIndex, indices, subtotalIndices);
}
SetSubtotal(data, " 総合計", totalAmount, ref rowIndex, indices, subtotalIndices);
var mergeCells = new MergeCells();
mergeCells.Append(Concat(
GetNextRowMergeCells("A", indices.Except(subtotalIndices)),
GetNextRowMergeCells("G", indices),
subtotalIndices.Select(x => GetMergeCell(x, "A", x + 1, "F"))
));
worksheet.Append(mergeCells);
worksheet.Append(GetPageSetup());
var companyInfo = $"{Company.Code}：{Company.Name}";
var outputInfo = $"出力日時：{DateTime.Today:yyyy年MM月dd日}";
worksheet.Append(GetHeaderFooter(leftHeader: companyInfo, rightHeader: outputInfo));
if (rowbreakIndices.Any())
{
worksheet.Append(GetRowBreaks(rowbreakIndices));
}
var definedNames = (workbook.DefinedNames = new DefinedNames());
definedNames.Append(sheet.GetPrintTitlesDefine(1u, endIndex));
workbook.Save();
}
private void SetColumnCaptin(SheetData data, ref uint rowIndex, List <uint> indices)
{
rowIndex++;
data.SetText(rowIndex, "A", "ID", cfidxHA1);
data.SetText(rowIndex, "B", "得意先コード", cfidxHBI1);
data.SetText(rowIndex, "C", "得意先電話番号", cfidxHBI1);
data.SetText(rowIndex, "D", "請求日", cfidxHBI1);
data.SetText(rowIndex, "E", "請求締日", cfidxHBI1);
data.SetText(rowIndex, "F", "当初予定日", cfidxHBI1);
data.SetText(rowIndex, "G", "回収予定額", cfidxHBI1);
data.SetText(rowIndex, "H", "請求書番号", cfidxHBI1);
data.SetText(rowIndex, "I", "請求部門コード", cfidxHBI1);
data.SetText(rowIndex, "J", "担当者コード", cfidxHJ1);
indices.Add(rowIndex);
rowIndex++;
data.SetStyle(rowIndex, "A", cfidxHA2);
data.SetText(rowIndex, "B", "得意先名", cfidxHBI2);
data.SetText(rowIndex, "C", "得意先備考", cfidxHBI2);
data.SetText(rowIndex, "D", "売上日", cfidxHBI2);
data.SetText(rowIndex, "E", "入金予定日", cfidxHBI2);
data.SetText(rowIndex, "F", "滞留日数", cfidxHBI2);
data.SetStyle(rowIndex, "G", cfidxHBI2);
data.SetText(rowIndex, "H", Note1, cfidxHBI2);
data.SetText(rowIndex, "I", "請求部門名", cfidxHBI2);
data.SetText(rowIndex, "J", "担当者名", cfidxHJ2);
}
public void Process(SpreadsheetDocument document)
{
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var workbook = (workbookPart.Workbook = new Workbook());
var worksheet = (worksheetPart.Worksheet = new Worksheet());
var bookstylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
var title = "滞留明細一覧";
var sheets = workbook.AppendChild(new Sheets());
var sheet = new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = title,
};
sheets.Append(sheet);
var decNumFmt = Precision == 0 ? 38 : 164;
var stylesheet = bookstylesPart.InitizalizeStylesheet(
initializeNumberingFormats: () => InitializeNumeringFormats(Precision),
initializeFonts: () => InitializeFonts(),
initializeFills: () => InitializeFills(),
initializeBorders: () => InitializeBorders(),
initializeCellFormats: () => InitializeCellFormats(decNumFmt)
);
var columns = new Columns();
columns.Append(new[] {
new Column {
Min = 1, Max = 1, Width = 41.14, CustomWidth = true
},
new Column {
Min = 2, Max = 2, Width = 30, CustomWidth = true
},
new Column {
Min = 3, Max = 3, Width = 42.14, CustomWidth = true
},
new Column {
Min = 4, Max = 4, Width = 43.85, CustomWidth = true
},
});
worksheet.Append(columns);
var data = new SheetData();
worksheet.Append(data);
if (!string.IsNullOrEmpty(Password))
{
worksheet.Append(new SheetProtection {
Password = Password.ToHash(),
Sheet = true,
Scenarios = true,
});
}
var rowIndex = 1u;
var bIndices = new List <uint>();
data.SetText(rowIndex, "A", title);
data.SetStyle("A1:D1", cfidxTitle);
rowIndex++;
rowIndex++;
data.SetText(rowIndex, "A", "得意先コード", cfidxHA1);
data.SetText(rowIndex, "B", "回収予定額", cfidxHBC1);
data.SetText(rowIndex, "C", "請求部門コード", cfidxHBC1);
data.SetText(rowIndex, "D", "担当者コード", cfidxHD1);
bIndices.Add(rowIndex);
rowIndex++;
data.SetText(rowIndex, "A", "得意先名", cfidxHA2);
data.SetStyle(rowIndex, "B", cfidxHBC2);
data.SetText(rowIndex, "C", "請求部門名", cfidxHBC2);
data.SetText(rowIndex, "D", "担当者名", cfidxHD2);
var endIndex = rowIndex;
var totalAmount = 0M;
foreach (var item in Items)
{
rowIndex++;
data.SetText(rowIndex, "A", item.CustomerCode, cfidxDA1);
data.SetNumber(rowIndex, "B", item.RemainAmount, cfidxDB1);
data.SetText(rowIndex, "C", item.DepartmentCode, cfidxDC1);
data.SetText(rowIndex, "D", item.StaffCode, cfidxDD1);
bIndices.Add(rowIndex);
totalAmount += item.RemainAmount;
rowIndex++;
data.SetText(rowIndex, "A", item.CustomerName, cfidxDA2);
data.SetStyle(rowIndex, "B", cfidxDBC2);
data.SetText(rowIndex, "C", item.DepartmentName, cfidxDBC2);
data.SetText(rowIndex, "D", item.StaffName, cfidxDD2);
}
rowIndex++;
// 合計行
data.SetText(rowIndex, "A", " 総合計", cfidxSA1);
data.SetNumber(rowIndex, "B", totalAmount, cfidxSB1);
data.SetStyle(rowIndex, "C", cfidxSC1);
data.SetStyle(rowIndex, "D", cfidxSD1);
bIndices.Add(rowIndex);
var totalCaptionIndex = rowIndex;
rowIndex++;
data.SetStyle(rowIndex, "A", cfidxSA2);
data.SetStyle(rowIndex, "B", cfidxSB2);
data.SetStyle(rowIndex, "C", cfidxSC2);
data.SetStyle(rowIndex, "D", cfidxSD2);
var mergeCells = new MergeCells();
mergeCells.Append(Concat(
GetNextRowMergeCells("A", new[] { totalCaptionIndex }),
GetNextRowMergeCells("B", bIndices)
));
worksheet.Append(mergeCells);
worksheet.Append(GetPageSetup());
var companyInfo = $"{Company.Code}：{Company.Name}";
var outputInfo = $"出力日時：{DateTime.Today:yyyy年MM月dd日}";
worksheet.Append(GetHeaderFooter(leftHeader: companyInfo, rightHeader: outputInfo));
var definedNames = (workbook.DefinedNames = new DefinedNames());
definedNames.Append(sheet.GetPrintTitlesDefine(1u, endIndex));
workbook.Save();
}
private void SetTitle(SheetData data, BillingAgingListSearch option, BillingAgingList item, ref uint rowIndex)
{
rowIndex++;
data.SetText(rowIndex, "A", "請求残高年齢表");
data.SetStyle(GetCellReference(rowIndex, "A", columnNameEnd: "I"), cfidxTitle);
if (option.RequireDepartmentSubtotal)
{
rowIndex++;
data.SetText(rowIndex, "A", $"請求部門コード：{item.DepartmentCode} {item.DepartmentName}");
}
if (option.RequireStaffSubtotal)
{
rowIndex++;
data.SetText(rowIndex, "A", $"担当者コード ：{item.StaffCode} {item.StaffName}");
}
rowIndex++;
rowIndex++;
data.SetText(rowIndex, "A", "得意先", cfidxHA);
data.SetStyle(rowIndex, "B", cfidxHB);
data.SetText(rowIndex, "C", "前月請求残", cfidxHCI);
data.SetText(rowIndex, "D", "当月売上高", cfidxHCI);
data.SetText(rowIndex, "E", "当月消込", cfidxHCI);
data.SetText(rowIndex, "F", "当月請求残", cfidxHCI);
data.SetText(rowIndex, "G", option.MonthlyRemain0, cfidxHCI);
data.SetText(rowIndex, "H", option.MonthlyRemain1, cfidxHCI);
data.SetText(rowIndex, "I", option.MonthlyRemain2, cfidxHCI);
data.SetText(rowIndex, "J", option.MonthlyRemain3, cfidxHJ);
}