Ok so I am trying to load a CSVStream into an ExcelPackage (I am using EPPlus).
It always fails at line 221482, no matter what option I choose. I am running on x64 and I have in my app.config...
The error given is the one from the title :(
public ExcelPackage ExcelPackageFromCsvStream(Stream csvStream)
{
var excelPackage = new ExcelPackage();
var workSheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
var csvFormat = new ExcelTextFormat
{
Delimiter = ',',
TextQualifier = '"',
DataTypes = new[] { eDataTypes.String }
};
using (var sr = new StreamReader(csvStream))
{
int i = 1;
foreach (var line in sr.ReadLines("\r\n"))
{
workSheet.Cells["A" + i].LoadFromText(line, csvFormat);
i++;
}
}
return excelPackage;
}
Resolved it by creating multiple ExcelPackages and also I've read the stream in batches (e.g. 200k lines at once)
public List<ExcelPackage> ExcelPackagesFromCsvStream(Stream csvStream, int batchSize)
{
var excelPackages = new List<ExcelPackage>();
int currentPackage = -1; // so that first package will have the index 0
var csvFormat = new ExcelTextFormat
{
Delimiter = ',',
TextQualifier = '"',
DataTypes = new[] {eDataTypes.String}
};
using (var sr = new StreamReader(csvStream))
{
int index = 1;
foreach (var line in sr.ReadLines("\r\n"))
{
if ((index - 1) % batchSize == 0)
{
var excelPackage = new ExcelPackage();
excelPackage.Workbook.Worksheets.Add("Sheet1");
excelPackages.Add(excelPackage);
currentPackage++;
index = 1;
}
excelPackages[currentPackage].Workbook.Worksheets.First().Cells["A" + index].LoadFromText(line, csvFormat);
index++;
}
}
return excelPackages;
}