EPPlus Array dimensions exceeded supported range. System.OutOfMemoryException

Question

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

score 0 · Accepted Answer · answered Apr 20 '17 at 10:48

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

EPPlus Array dimensions exceeded supported range. System.OutOfMemoryException

1 Answers1