/// <summary> /// save a generic CSV string to an Excel file /// </summary> public static bool CSV2ExcelStream(string ACSVData, MemoryStream AStream, string ASeparator = ",", string ATableName = "data") { try { ExcelPackage pck = new ExcelPackage(); ExcelWorksheet worksheet = pck.Workbook.Worksheets.Add(ATableName); Int32 rowCounter = 1; Int16 colCounter = 1; // we don't have headers for the columns List <String> Lines = ACSVData.Split(Environment.NewLine).ToList(); Int32 LineCounter = 0; while (LineCounter < Lines.Count) { string line = Lines[LineCounter]; while (line.Trim().Length > 0) { string value = StringHelper.GetNextCSV(ref line, Lines, ref LineCounter, ASeparator); TVariant v = new TVariant(value); if (v.TypeVariant == eVariantTypes.eDecimal) { worksheet.Cells[rowCounter, colCounter].Value = v.ToDecimal(); } else if (v.TypeVariant == eVariantTypes.eInteger) { worksheet.Cells[rowCounter, colCounter].Value = v.ToInt32(); } else if (v.TypeVariant == eVariantTypes.eDateTime) { worksheet.Cells[rowCounter, colCounter].Value = v.ToDate(); } else { worksheet.Cells[rowCounter, colCounter].Value = value; } colCounter++; } LineCounter++; rowCounter++; colCounter = 1; } pck.SaveAs(AStream); return(true); } catch (Exception e) { TLogging.Log(e.ToString()); return(false); } }
/// <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> /// save a generic CSV string to an Excel file /// </summary> public static bool CSV2ExcelStream(string ACSVData, MemoryStream AStream, string ASeparator = ",", string ATableName = "data") { try { XSSFWorkbook workbook = new XSSFWorkbook(); ISheet worksheet = workbook.CreateSheet(ATableName); IRow wsrow = null; ICell wscell = null; Int32 rowCounter = 1; Int16 colCounter = 1; // we don't have headers for the columns List <String> Lines = ACSVData.Split(Environment.NewLine).ToList(); Int32 LineCounter = 0; while (LineCounter < Lines.Count) { wsrow = worksheet.CreateRow(rowCounter); string line = Lines[LineCounter]; while (line.Trim().Length > 0) { wscell = wsrow.CreateCell(colCounter); string value = StringHelper.GetNextCSV(ref line, Lines, ref LineCounter, ASeparator); TVariant v = new TVariant(value); if (v.TypeVariant == eVariantTypes.eDecimal) { wscell.SetCellValue((double)v.ToDecimal()); } else if (v.TypeVariant == eVariantTypes.eInteger) { wscell.SetCellValue(v.ToInt32()); } else if (v.TypeVariant == eVariantTypes.eDateTime) { wscell.SetCellValue(v.ToDate()); } else { wscell.SetCellValue(value); } colCounter++; } LineCounter++; rowCounter++; colCounter = 1; } workbook.Write(AStream); return(true); } catch (Exception e) { TLogging.Log(e.ToString()); return(false); } }
/// <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); }