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