public ExcelResult Export()
{
string result = string.Empty;
Workbook wb = new Workbook();
// properties
wb.Properties.Author = "Grapart";
wb.Properties.Created = DateTime.Today;
wb.Properties.LastAutor = "Grapart";
wb.Properties.Version = "14";
// options sheets
wb.ExcelWorkbook.ActiveSheet = 1;
wb.ExcelWorkbook.DisplayInkNotes = false;
wb.ExcelWorkbook.FirstVisibleSheet = 1;
wb.ExcelWorkbook.ProtectStructure = false;
wb.ExcelWorkbook.WindowHeight = 800;
wb.ExcelWorkbook.WindowTopX = 0;
wb.ExcelWorkbook.WindowTopY = 0;
wb.ExcelWorkbook.WindowWidth = 600;
// create style s1 for header
Style s1 = new Style("s1");
s1.Font.Bold = true;
s1.Font.Italic = true;
s1.Font.Color = "#21610B";
wb.AddStyle(s1);
// create style s2 for header
Style s2 = new Style("s2");
s2.Font.Bold = true;
s2.Font.Italic = true;
s2.Borders.Add(new Border());
s2.Font.Color = "#0000FF";
wb.AddStyle(s2);
//s3
Style s3 = new Style("s3");
s3.Font.Bold = true;
s3.Font.Italic = true;
s3.Borders.Add(new Border());
s3.Font.Color = "#FF0000";
wb.AddStyle(s3);
// First sheet
Worksheet ws3 = new Worksheet("Лист 1");
//adding headers
string summary = "Згенеровано і розв'язано 5 задач різних розмірностей.";
ws3.AddCellWithStyle(0, 0, summary, s3.ID, 10, 0);
summary = "Граф представляється списком ребер. І і ІІ - вершини ребра.";
ws3.AddCellWithStyle(1, 0, summary, s3.ID, 10, 0);
summary = "Значення true у колонці алгоритму - ребро входить у розріз за результатом розбиття відповідним алгоритмом.";
ws3.AddCellWithStyle(2, 0, summary, s3.ID, 10, 0);
int totalRows = 4;
// appending rows with data
foreach (var table in resultTables)
{
ws3.AddCellWithStyle(totalRows, 0, "I", s1.ID);
ws3.AddCellWithStyle(totalRows, 1, "II", s1.ID);
ws3.AddCellWithStyle(totalRows, 2, "Вага", s1.ID);
ws3.AddCellWithStyle(totalRows, 3, "ФМ", s1.ID);
ws3.AddCellWithStyle(totalRows, 4, "Бджолиний", s1.ID);
for (int i = 0; i < table.Partition.Count; i++)
{
ws3.AddCell(totalRows + 1, 0, table.Partition[i].first_vertex);
ws3.AddCell(totalRows + 1, 1, table.Partition[i].second_vertex);
ws3.AddCell(totalRows + 1, 2, table.Partition[i].weight);
ws3.AddCell(totalRows + 1, 3, Convert.ToString(table.Partition[i].fmPartition));
ws3.AddCell(totalRows + 1, 4, Convert.ToString(table.Partition[i].beePartition));
totalRows++;
}
ws3.AddCellWithStyle(totalRows + 1, 0, "Розріз:", s2.ID);
//ws3.AddCellWithStyle(totalRows+1, 0, "ФМ: ", s2.ID);
ws3.AddCell(totalRows + 1, 1, "");
ws3.AddCell(totalRows + 1, 2, "");
ws3.AddCellWithStyle(totalRows + 1, 3, table.fm_cutWeight, s2.ID);
//totalRows++;
//totalRows++;
//ws3.AddCellWithStyle(totalRows+1, 0, "Бджолиний: ", s2.ID);
ws3.AddCellWithStyle(totalRows + 1, 4, table.bee_cutWeight, s2.ID);
totalRows++;
totalRows++;
totalRows++;
}
wb.AddWorksheet(ws3);
// generate xml
string workstring = wb.ExportToXML();
// Send to user file
return(new ExcelResult("ExpResult.xls", workstring));
}
/// <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());
}
