/// <summary>
/// Build Excel file
/// </summary>
/// <param name="items">product</param>
/// <param name="title"></param>
/// <returns></returns>
public static string Build(List <Ttn> items, string title = "Без заголовка")
{
var workbook = new Workbook();
PropertiesBuilder.Apply(workbook);
StylesBuilder.Apply(workbook);
// create first worksheet
var worksheet = new Worksheet("Лист 1");
worksheet.AddCellWithStyle(0, 0, title, StylesBuilder.TitleStyle, 6U);
// adding headers
worksheet.AddCellWithStyle(1, 0, "ТТН № ", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 1, "Дата", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 2, "Время", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 3, "Продукция", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 4, "Кол", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 5, "Цена", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 6, "Сумма", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 7, "Модель", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 8, "Автобаза", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 9, "Гос. №", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 10, "Водитель", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 11, "Адрес", StylesBuilder.Headerstyle);
var row = 2;
foreach (var item in items)
{
worksheet.AddCell(row, 0, item.Num);
worksheet.AddCell(row, 1, item.Dat.ToString());
worksheet.AddCell(row, 2, item.Tm);
worksheet.AddCell(row, 3, item.Good);
worksheet.AddCell(row, 4, item.Kol);
worksheet.AddCell(row, 5, item.Price);
worksheet.AddCell(row, 6, item.Sm);
worksheet.AddCell(row, 7, item.Amodel);
worksheet.AddCell(row, 8, item.Ab);
worksheet.AddCell(row, 9, item.Gn);
worksheet.AddCell(row, 10, item.Driv);
worksheet.AddCell(row, 11, item.Adres);
row++;
}
// appending footer with formulas
//worksheet.AddCellWithStyle(row, 0, string.Empty, StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyle(row, 1, string.Empty, StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyle(row, 2, string.Empty, StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyle(row, 3, string.Empty, StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyle(row, 4, string.Empty, StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyleAndFormula(row, 5, 0, "=AVERAGE(R[-" + (row - 1) + "]C:R[-1]C)", StylesBuilder.SummaryStyle);
//worksheet.AddCellWithStyleAndFormula(row, 6, 0, "=SUM(R[-" + (row - 1) + "]C:R[-1]C)", StylesBuilder.SummaryStyle);
workbook.AddWorksheet(worksheet);
return(workbook.ExportToXML());
}
public static string Build(List <Ttn> items, string title = "Без заголовка")
{
var workbook = new Workbook();
PropertiesBuilder.Apply(workbook);
StylesBuilder.Apply(workbook);
// create first worksheet
var worksheet = new Worksheet("Лист 1");
worksheet.AddCellWithStyle(0, 0, title, StylesBuilder.TitleStyle, 6U);
// adding headers
worksheet.AddCellWithStyle(1, 0, "Номер заказа", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 1, "Дата заказа", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 2, "Артикул товара", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 3, "Наименование товара", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 4, "Количество", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 5, "Цена", StylesBuilder.Headerstyle);
worksheet.AddCellWithStyle(1, 6, "Сумма", StylesBuilder.Headerstyle);
var row = 2;
foreach (var item in items)
{
worksheet.AddCell(row, 0, item.Id);
worksheet.AddCell(row, 1, item.OrderDate.ToString());
worksheet.AddCell(row, 2, item.ProductId);
worksheet.AddCell(row, 3, item.ProductName);
worksheet.AddCell(row, 4, item.Quantity);
worksheet.AddCell(row, 5, item.UnitPrice);
worksheet.AddCellWithFormula(row, 6, 0, "=RC[-2]*RC[-1]");
row++;
}
// appending footer with formulas
worksheet.AddCellWithStyle(row, 0, string.Empty, StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyle(row, 1, string.Empty, StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyle(row, 2, string.Empty, StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyle(row, 3, string.Empty, StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyle(row, 4, string.Empty, StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyleAndFormula(row, 5, 0, "=AVERAGE(R[-" + (row - 1) + "]C:R[-1]C)", StylesBuilder.SummaryStyle);
worksheet.AddCellWithStyleAndFormula(row, 6, 0, "=SUM(R[-" + (row - 1) + "]C:R[-1]C)", StylesBuilder.SummaryStyle);
workbook.AddWorksheet(worksheet);
return(workbook.ExportToXML());
}
