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);
        }
예제 #2
0
        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);
        }
예제 #4
0
        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);
        }
예제 #7
0
        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();
        }
예제 #8
0
        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);
        }