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