public override Stream BuildToStream(params object[] args)
{
//Проверяем аргументы
if (args != null &&
(args.Count() > 2 ||
args.Where(a => a != null).Any(a => !(a is DateTime))))
{
throw new ArgumentException("SalesReport.BuildToStream: invalid arguments");
}
var sDate = args != null && args.Length >= 1 && args[0] != null
? (args[0] as DateTime?).Value : DateTime.MinValue;
var eDate = args != null && args.Length >= 2 && args[1] != null
? (args[1] as DateTime?).Value : DateTime.MaxValue;
if (sDate > eDate)
{
var tmpDate = eDate; eDate = sDate; sDate = tmpDate;
}
var result = new MemoryStream();
//создаем объект отчета
var report = new ExcelReport();
//создаем вкладку
var excelsheet = report.AddDocument("Продажи");
//Задаем ширины колонок
excelsheet.SetColumnsWidth(10, 25, 10, 40, 10, 10);
//Шапка таблицы
var rHead = new ExcelRow();
rHead.SetStyle(1, true);
rHead.Append(new ExcelCell("OrderId"),
new ExcelCell("OrderDate"),
new ExcelCell("ProductID"),
new ExcelCell("ProductName"),
new ExcelCell("Quantity"),
new ExcelCell("UnitPrice"),
new ExcelCell("CalcField"));
excelsheet.Append(rHead);
//Построение тела таблицы
var cx = 1;
NorthwindContext.Order
.Where(o => o.OrderDate.HasValue && o.OrderDate.Value >= sDate && o.OrderDate.Value <= eDate)
.SelectMany(o => o.OrderDetail)
.ToList()
.ForEach(o => {
cx++;
excelsheet.Append(new ExcelRow(new ExcelCell(o.OrderID, ExcelCellType.Number),
new ExcelCell(o.Order.OrderDate, ExcelCellType.Date),
new ExcelCell(o.ProductID, ExcelCellType.Number),
new ExcelCell(o.Product.Name),
new ExcelCell(o.Quantity, ExcelCellType.Number),
new ExcelCell(o.UnitPrice, ExcelCellType.Number),
new ExcelCell($"E{cx}*F{cx}", ExcelCellType.Formula)));
});
report.Save(result);
return(result);
}
/// <summary>
/// Deploys the style to row.
/// </summary>
/// <param name="ex_row">The ex row.</param>
/// <param name="in_row">The in row.</param>
/// <param name="ws">The ws.</param>
/// <param name="metaSet">The meta set.</param>
public void DeployStyleToRow(ExcelRow ex_row, DataRow in_row, ExcelWorksheet ws, tableStyleRowSetter metaSet = null)
{
DataRowInReportTypeEnum style = DataRowInReportTypeEnum.data;
if (extraRowStyles.ContainsKey(in_row))
{
style = extraRowStyles[in_row];
}
bool isEven = ((in_row.Table.Rows.IndexOf(in_row) % 2) > 0);
Boolean isLegend = in_row.Table.TableName == LEGENDTABLE_NAME;
var baseStyle = styleSet.rowStyles[style];
var rowsMetaSet = this.GetRowMetaSet();
if (metaSet != null)
{
rowsMetaSet = metaSet;
}
var response = rowsMetaSet.evaluate(in_row, this, baseStyle);
if (response != null)
{
if (response.style != null)
{
baseStyle = response.style;
}
}
if (baseStyle == null)
{
baseStyle = styleSet.rowStyles[style];
}
foreach (string s in response.notes)
{
Console.WriteLine(s);
}
ex_row.SetStyle(baseStyle, isEven);
switch (style)
{
case DataRowInReportTypeEnum.columnCaption:
if (!isLegend)
{
foreach (var cpair in categories)
{
foreach (selectZone zone in categories.categoryZones[cpair.Key])
{
var sl = ws.getExcelRange(ex_row, zone);
sl.Style.Fill.PatternType = ExcelFillStyle.Solid;
var col = categories.categoryColors[cpair.Key]; //.First().DefaultBackground(System.Drawing.Color.Gray);
sl.Style.Fill.BackgroundColor.SetColor(col);
sl.Style.Fill.BackgroundColor.Tint = new decimal(0.2);
}
}
}
break;
case DataRowInReportTypeEnum.mergedHeaderTitle:
ws.Cells[ex_row.Row, 1, ex_row.Row, in_row.Table.Columns.Count].Merge = true;
break;
case DataRowInReportTypeEnum.mergedHeaderInfo:
ws.Cells[ex_row.Row, 1, ex_row.Row, in_row.Table.Columns.Count].Merge = true;
break;
case DataRowInReportTypeEnum.mergedFooterInfo:
ws.Cells[ex_row.Row, 1, ex_row.Row, in_row.Table.Columns.Count].Merge = true;
break;
case DataRowInReportTypeEnum.mergedHorizontally:
selectZone z = new selectZone(0, 0, Columns.Count - 1, 0);
var s2l = ws.getExcelRange(ex_row, z);
s2l.Merge = true;
break;
case DataRowInReportTypeEnum.mergedCategoryHeader:
double tn = 0.2;
int hlip = -1;
foreach (var cpair in categories)
{
if (hlip == -1)
{
tn = 0.3;
}
else
{
tn = 0.6;
}
foreach (selectZone zone in categories.categoryZones[cpair.Key])
{
var sl = ws.getExcelRange(ex_row, zone);
sl.Merge = true;
sl.Value = cpair.Key.ToUpper();
sl.Style.Fill.PatternType = ExcelFillStyle.Solid;
var col = categories.categoryColors[cpair.Key]; //.First().DefaultBackground(System.Drawing.Color.Gray);
sl.Style.Fill.BackgroundColor.SetColor(col);
sl.Style.Fill.BackgroundColor.Tint = new decimal(tn);
}
hlip = -hlip;
}
isEven = !isEven;
break;
case DataRowInReportTypeEnum.data:
//ex_row.Style.Font.Size = 9;
foreach (DataColumn dc in in_row.Table.Columns)
{
string format = dc.GetFormatForExcel();
var aligment = dc.GetAligment();
ws.Cells[ex_row.Row, dc.Ordinal + 1].Style.HorizontalAlignment = aligment.GetHorizontalAlignment();
if (!format.isNullOrEmpty())
{
ws.Cells[ex_row.Row, dc.Ordinal + 1].Style.Numberformat.Format = format;
}
//if (dc.GetValueType().isNumber())
//{
// ws.Cells[ex_row.Row, dc.Ordinal + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
//}
//else if (dc.GetValueType().IsEnum)
//{
// ws.Cells[ex_row.Row, dc.Ordinal + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//}
//else if (dc.GetValueType().isBoolean())
//{
// ws.Cells[ex_row.Row, dc.Ordinal + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//}
//else
//{
// ws.Cells[ex_row.Row, dc.Ordinal + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
//}
}
break;
}
if (!isLegend)
{
if (ex_row.Row == (RowStart + RowsWithMaxAggregation))
{
ex_row.Style.Border.Bottom.Style = ExcelBorderStyle.Dotted;
ex_row.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Red);
}
}
}