OutOfMemoryException in Filling Excel

Question

I want to fill an Excel file to one million records but I get an outofmemory exception error.

This is my code

private void FillingExcellSheet(IEnumerable<object> sooratHesabList, string path, int i, int excelFilePartsNumber, string source,
            ExcelData item, int itemsCount, string itemId)
{
    int Max_Num_SEFAOT = CalculateMaximum_Numbers_SaveExcel_File_AOTime(sooratHesabList.Count());
    var dest = SetExcelFilePath(i, excelFilePartsNumber, path, item);
    CopyExcellFileFromSourceToDestination(source, dest);
    IWorkbook workbook = null;
    IWorksheet worksheet = null;
    IRange cells = null;

    SaveLogQueryResultCount(item, itemsCount, i);
    SaveLogTemplateFilePartCopiedInsertingRows(excelFilePartsNumber, i, item);

    for (int j = 0; j < Max_Num_SEFAOT; j++)
    {
        workbook = Factory.GetWorkbook(dest, System.Globalization.CultureInfo.CurrentCulture);
        worksheet = workbook.Worksheets[SooratHesabName];
        cells = worksheet.Cells;
   
        FillingExcellSheet(cells, sooratHesabList.Skip(Maximum_Numbers_SaveExcel_File_AOTime * j).Take(Maximum_Numbers_SaveExcel_File_AOTime), item,j,excelFilePartsNumber);
        workbook.Save();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        workbook.Close(); 
    }
    SaveLogTemplateFilePartSaved(excelFilePartsNumber, i, item);
}

private void FillingExcellSheet(IRange cells, IEnumerable<object> sooratHesabList, ExcelData item, int j, int excelFilePartsNumber)
{
    if (excelFilePartsNumber > 1 && j == 0)
        item.RowNumber = 2;

    foreach (IEnumerable<object> row in sooratHesabList)
    {
        var colNumber = 0;
        cells[item.RowNumber, colNumber, item.RowNumber, 48].Copy(cells[(item.RowNumber) + 1, colNumber, (item.RowNumber) + 1, 48]);//ToDo: check style of template file
        foreach (var columnValue in row)
        {
            cells[item.RowNumber, colNumber].Value = columnValue?.ToString();
            colNumber++;
        }
        item.RowNumber++;
    }
}

I think excel can only have `1,048,576 rows by 16,384 columns` or perhaps your object that is holding the data is running out of space. https://stackoverflow.com/questions/4312169/net-object-size-limit#:~:text=3%20Answers&text=answer%20was%20accepted%E2%80%A6-,.,limit%20of%20a%20single%20object. — traveler3468, Aug 25 '20 at 07:36
Total number of rows on a worksheet are 1,048,576 rows. Maybe you are reaching the limit. — D A, Aug 25 '20 at 07:36
Why are you putting 1 million records in excel, it sounds like you need a database — TheGeneral, Aug 25 '20 at 07:53
Why not simply generate a CSV then? What exacltly are trying to achieve. Excel is not about million of something. Also `GC` calls are useless here. Also be sure that the app targets x64 architecture. — aepot, Aug 25 '20 at 08:03
To make GC's life easier, move `IWorkbook workbook` and other interfaces declaration inside the loop. You have no reasons to keep it outside. — aepot, Aug 25 '20 at 08:18
This line of code : `cells[item.RowNumber, colNumber, item.RowNumber, 48]` looks strange, because `Cells[]` as far as I know accepts row and column but you pass four arguments, what `IRange` is it? Are you using some external library? What version of Excel is you code targeting currently? — Daniel Dušek, Aug 25 '20 at 09:15
But how is that possible, it accepts four arguments? Is IRange and Excel range or are you using some third party lib? — Daniel Dušek, Aug 25 '20 at 10:00
To copy just format : https://stackoverflow.com/questions/18542864/copy-format-from-one-row-to-another-using-c-sharp — Daniel Dušek, Aug 25 '20 at 10:04

OutOfMemoryException in Filling Excel

0 Answers0