private bool RequireCustomerSubtotal(ArrearagesListSearch opt, ArrearagesList item, ArrearagesList itemBuf) => RequireCustomerSubtotal(opt) && itemBuf != null && (item == null || RequireDepartmentSubtotal(opt, item, itemBuf) || RequireStaffSubtotal(opt, item, itemBuf) || item.CustomerCode != itemBuf.CustomerCode);
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 bool RequireDepartmentSubtotal(ArrearagesListSearch opt, ArrearagesList item, ArrearagesList itemBuf) => RequireDepartmentSubtotal(opt) && itemBuf != null && (item == null || item.DepartmentCode != itemBuf.DepartmentCode);
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();
}
