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(); }