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()); }