/// <summary> /// Create a Calc file from the HTML /// </summary> public static ExcelPackage HTMLToCalc(HtmlDocument html) { ExcelPackage pck = new ExcelPackage(); ExcelWorksheet worksheet = pck.Workbook.Worksheets.Add("Data Export"); // write the column headings var elements = HTMLTemplateProcessor.SelectNodes(html.DocumentNode, "//div[@id='column_headings']/div"); int colCounter = 1; int rowCounter = 3; foreach (var element in elements) { worksheet.Cells[rowCounter, colCounter].Value = element.InnerText; worksheet.Cells[rowCounter, colCounter].Style.Font.Bold = true; colCounter++; } rowCounter += 2; var rows = HTMLTemplateProcessor.SelectNodes(html.DocumentNode, "//div[@id='content']//div[contains(@class, 'row')]"); foreach (var row in rows) { colCounter = 1; elements = HTMLTemplateProcessor.SelectNodes(row, ".//div[contains(@class, 'col-')]"); foreach (var element in elements) { string value = element.InnerText; if (value == " ") { value = String.Empty; } if (element.HasClass("currency")) { TVariant v = new TVariant(value); worksheet.Cells[rowCounter, colCounter].Value = v.ToDecimal(); } else if (element.HasClass("date")) { TVariant v = new TVariant(value); worksheet.Cells[rowCounter, colCounter].Value = v.ToDate(); worksheet.Cells[rowCounter, colCounter].Style.Numberformat.Format = "dd/mm/yyyy"; } else { worksheet.Cells[rowCounter, colCounter].Value = value; } if (element.InnerHtml.Contains("<strong>")) { worksheet.Cells[rowCounter, colCounter].Style.Font.Bold = true; } colCounter++; } rowCounter++; } worksheet.Cells.AutoFitColumns(); return(pck); }
/// <summary> /// Create an Excel file from the HTML /// </summary> public static XSSFWorkbook HTMLToCalc(HtmlDocument html) { XSSFWorkbook xssWorkbook = new XSSFWorkbook(); IRow wsrow = null; ICell wscell = null; ISheet worksheet = xssWorkbook.CreateSheet("Data Export"); ICellStyle wsstyle_bold = xssWorkbook.CreateCellStyle(); IFont wsfont = wsstyle_bold.GetFont(xssWorkbook); wsfont.IsBold = true; wsstyle_bold.SetFont(wsfont); ICellStyle wsstyle_dateformat = xssWorkbook.CreateCellStyle(); ICreationHelper createHelper = xssWorkbook.GetCreationHelper(); wsstyle_dateformat.DataFormat = createHelper.CreateDataFormat().GetFormat("dd/mm/yyyy"); // write the column headings var elements = HTMLTemplateProcessor.SelectNodes(html.DocumentNode, "//div[@id='column_headings']/div"); int colCounter = 1; int rowCounter = 3; wsrow = worksheet.CreateRow(rowCounter); foreach (var element in elements) { wscell = wsrow.CreateCell(colCounter); wscell.SetCellValue(element.InnerText); wscell.CellStyle = wsstyle_bold; colCounter++; } rowCounter += 2; var rows = HTMLTemplateProcessor.SelectNodes(html.DocumentNode, "//div[@id='content']//div[contains(@class, 'row')]"); foreach (var row in rows) { wsrow = worksheet.CreateRow(rowCounter); colCounter = 1; elements = HTMLTemplateProcessor.SelectNodes(row, ".//div[contains(@class, 'col-')]"); foreach (var element in elements) { wscell = wsrow.CreateCell(colCounter); string value = element.InnerText; if (value == " ") { value = String.Empty; } if (element.HasClass("currency")) { TVariant v = new TVariant(value); wscell.SetCellValue((double)v.ToDecimal()); } else if (element.HasClass("date")) { TVariant v = new TVariant(value); wscell.SetCellValue(v.ToDate()); wscell.CellStyle = wsstyle_dateformat; } else { wscell.SetCellValue(value); } if (element.InnerHtml.Contains("<strong>")) { wscell.CellStyle = wsstyle_bold; } colCounter++; } rowCounter++; } for (int colIndex = 1; colIndex < colCounter; colIndex++) { worksheet.AutoSizeColumn(colIndex); } return(xssWorkbook); }