// 根据 Merged ListView 和指定的 Key 列创建报表
// parameters:
// nKeyColumn 用于 Key 的列 index 值。例如 MERGED_COLUMN_SELLER
// strKeyCaption Key 列的列标题。例如“渠道”
public static void BuildMergedReport(
IEnumerable <ListViewItem> items,
int nKeyColumn,
string strKeyCaption,
IXLWorksheet sheet)
{
List <KeyStatisLine> results = //this.listView_merged.Items
items
.Cast <ListViewItem>()
.GroupBy(p => ListViewUtil.GetItemText(p, nKeyColumn))
.Select(cl => new KeyStatisLine
{
Key = ListViewUtil.GetItemText(cl.First(), nKeyColumn),
OrderCopies = cl.Sum(o => Convert.ToInt32(ListViewUtil.GetItemText(o, MERGED_COLUMN_COPY))),
OrderPrice = ConcatPrice(cl, MERGED_COLUMN_PRICE),
}).ToList();
int line = 0;
// 栏目标题行
{
int column = 0;
// Seller
IXLCell start = PrintOrderForm.WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
strKeyCaption);
// OrderCopies
WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
"订购套数");
// OrderPrice
IXLCell end = WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
"订购价");
IXLRange range = sheet.Range(start, end);
range.Style.Font.Bold = true;
range.Style.Fill.BackgroundColor = XLColor.LightGray;
range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
line++;
}
// 内容行
foreach (var item in results)
{
int column = 0;
// Seller
WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
item.Key);
// OrderCopies
WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
item.OrderCopies);
// OrderPrice
WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
item.OrderPrice);
// text.Append($"i={i} Seller='{item.Seller}' OrderCopies={item.OrderCopies} OrderPrice={item.OrderPrice}\r\n");
line++;
}
}
// 根据 Origin ListView 和指定的 Key 列创建报表
// parameters:
// nKeyColumn 用于 Key 的列 index 值。例如 ORIGIN_COLUMN_SOURCE
// strKeyCaption Key 列的列标题。例如“经费来源”
public static void BuildOriginReport(
bool bSeries,
IEnumerable <ListViewItem> items,
string strKeyName,
string strKeyCaption,
IXLWorksheet sheet)
{
List <int> column_max_chars = new List <int>();
// 先变换为 LineInfo 数组
// LineInfo 可以放在固定面板区用 PropertyGrid 界面显示
List <PrintOrderForm.LineInfo> lines = new List <PrintOrderForm.LineInfo>();
int i = 0;
foreach (ListViewItem item in items)
{
var current = PrintOrderForm.LineInfo.Build(item, $"原始视图第 {(i + 1)} 行");
lines.Add(current.Adjust());
i++;
}
List <KeyStatisLine> results =
lines
.Where(o => bSeries || string.IsNullOrEmpty(o.State) == false)
.GroupBy(p => (string)GetPropertyValue(p, strKeyName))
.Select(cl => new KeyStatisLine
{
Key = (string)GetPropertyValue(cl.First(), strKeyName),
OrderIssueCount = cl.Where(a => a.Copy?.OldCopy?.Copy > 0)
.Sum(o => Convert.ToInt32(o.IssueCount)),
AcceptIssueCount = cl.Where(a => a.Copy?.NewCopy?.Copy > 0)
.Sum(o => Convert.ToInt32(o.IssueCount)),
OrderCopies = cl.Sum(o => Convert.ToInt32(o.Copy.OldCopy.Copy)),
OrderPrice = ConcatLinePrice(cl, "TotalPrice"),
AcceptCopies = cl.Sum(o => Convert.ToInt32(o.Copy.NewCopy.Copy)),
AcceptPrice = ConcatLinePrice(cl, "AcceptTotalPrice"),
OrderBiblioCount = cl.Where(a => a.Copy?.OldCopy?.Copy > 0)
.GroupBy(p => p.BiblioRecPath).LongCount(),
// cl.GroupBy(p => p.BiblioRecPath).LongCount(),
AcceptBiblioCount = cl.Where(a => a.Copy?.NewCopy?.Copy > 0)
.GroupBy(p => p.BiblioRecPath).LongCount(),
OrderFixedPrice = ConcatLinePrice(cl, "OrderTotalFixedPrice"), // 可能要乘以套数
OrderDiscount = cl.Where(a => a.Copy?.OldCopy?.Copy > 0)
.DefaultIfEmpty(new PrintOrderForm.LineInfo()).Average(o => o.OrderDiscount).ToString(),
AcceptFixedPrice = ConcatLinePrice(cl, "AcceptTotalFixedPrice"), // 可能要乘以套数
AcceptDiscount = cl.Where(a => a.Copy?.NewCopy?.Copy > 0)
.DefaultIfEmpty(new PrintOrderForm.LineInfo()).Average(o => o.AcceptDiscount).ToString(),
}).ToList();
int line = 0;
// 栏目标题行
string[] titles = new string[] {
strKeyCaption,
"订购种数", "*订购期数",
"订购套数", "订购价",
"订购码洋", "平均订购折扣",
"验收种数", "*验收期数",
"验收套数", "验收价",
"验收码洋", "平均验收折扣",
};
{
IXLCell start = null;
IXLCell end = null;
SetCellInfo info = new SetCellInfo
{
ColumnMaxChars = column_max_chars,
Line = line,
Column = 0,
Sheet = sheet,
};
foreach (string title in titles)
{
if (title.StartsWith("*") && bSeries == false)
{
continue;
}
string text = title.StartsWith("*") ? title.Substring(1) : title;
IXLCell cell = info.SetCellText(text);
#if NO
SetMaxChars(ref column_max_chars,
TABLE_LEFT_BLANK_COLUMS + column,
text.Length);
IXLCell cell = PrintOrderForm.WriteExcelCell(
sheet,
TABLE_TOP_BLANK_LINES + line,
TABLE_LEFT_BLANK_COLUMS + column++,
text);
#endif
if (start == null)
{
start = cell;
}
end = cell;
}
IXLRange range = sheet.Range(start, end);
range.Style.Font.Bold = true;
range.Style.Fill.BackgroundColor = XLColor.LightGray;
range.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
line++;
}
// 内容行
foreach (var item in results)
{
SetCellInfo info = new SetCellInfo
{
ColumnMaxChars = column_max_chars,
Line = line,
Column = 0,
Sheet = sheet,
};
// Seller
info.SetCellText(item.Key);
// OrderBiblioCount
info.SetCellText(item.OrderBiblioCount);
if (bSeries)
{
// OrderIssueCount
info.SetCellText(item.OrderIssueCount);
}
// OrderCopies
info.SetCellText(item.OrderCopies);
// OrderPrice
info.SetCellText(item.OrderPrice);
// OrderFixedPrice
info.SetCellText(item.OrderFixedPrice);
// OrderDiscount
info.SetCellText(item.OrderDiscount);
// AcceptBiblioCount
info.SetCellText(item.AcceptBiblioCount);
if (bSeries)
{
// AcceptIssueCount
info.SetCellText(item.AcceptIssueCount);
}
// AcceptCopies
info.SetCellText(item.AcceptCopies);
// AcceptPrice
info.SetCellText(item.AcceptPrice);
// AcceptFixedPrice
info.SetCellText(item.AcceptFixedPrice);
// AcceptDiscount
info.SetCellText(item.AcceptDiscount);
// text.Append($"i={i} Seller='{item.Seller}' OrderCopies={item.OrderCopies} OrderPrice={item.OrderPrice}\r\n");
line++;
}
AdjectColumnWidth(sheet, column_max_chars);
}