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);
}
/// <summary><see cref="SheetData"/>に 数字を設定</summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="rowIndex"></param>
/// <param name="columnName"></param>
/// <param name="value"></param>
/// <param name="styleIndex"></param>
public static void SetNumber <T>(this SheetData data, uint rowIndex, string columnName, T?value, uint?styleIndex = null) where T : struct
{
if (value.HasValue)
{
data.SetNumber(rowIndex, columnName, value.Value, styleIndex);
}
else
{
var cell = data.GetCell(rowIndex, columnName);
if (styleIndex.HasValue)
{
cell.StyleIndex = styleIndex.Value;
}
}
}
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, cfidxSCJ);
data.SetNumber(rowIndex, "D", item.CurrentMonthSales, cfidxSCJ);
data.SetNumber(rowIndex, "E", item.CurrentMonthReceipt, cfidxSCJ);
data.SetNumber(rowIndex, "F", item.CurrentMonthMatching, cfidxSCJ);
data.SetNumber(rowIndex, "G", item.CurrentMonthRemain, cfidxSCJ);
data.SetNumber(rowIndex, "H", item.MonthlyRemain0, cfidxSCJ);
data.SetNumber(rowIndex, "I", item.MonthlyRemain1, cfidxSCJ);
data.SetNumber(rowIndex, "J", item.MonthlyRemain2, cfidxSCJ);
data.SetNumber(rowIndex, "K", item.MonthlyRemain3, cfidxSK);
}
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);
}
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();
}