OutOfMemoryException while saving/creating/exporting excel sheet

Question

Develop environment :

OS - Windows 7 64bit
CPU - i5 460M
RAM - 8GB
.NET framework - 4.0
Excel-Interop - Microsoft Excel 14.0 Object Library

I'm using Excel-Interop to export excel file from DataGridView (dgv).

When I save more than about 150,000 rows

OutOfMemoryException

is thrown.

{
    object[,] valueObjArray = new object[rowCnt, colCnt];
    int rowCnt = dgv.Rows.Count;
    int colCnt = dgv.Columns.Count;

    for (int rowIndex = 0; rowIndex < rowCnt; rowIndex++)
    {
        for (int colIndex = 0; colIndex < colCnt; colIndex++)
        {
            valueObjArray[rowIndex, colIndex] = dgv[colIndex, rowIndex].Value;
        }
    }

    _workSheet.get_Range("A1", Convert.ToChar(colCnt + 64).ToString() + "1").Value2 = headerObjArray;                
    _workSheet.get_Range("A2", Convert.ToChar(colCnt + 64).ToString() + (rowCnt + 1).ToString()).Value2 = valueObjArray;
    _workSheet.get_Range("B2", "B" + (rowCnt+1).ToString()).NumberFormat = "yyyy-mm-dd hh:mm";

    _workBook.SaveAs(path);
}

This is best way to speed up that I known.

But, After monitoring RAM I think it causes memory increase. Exception is thrown when memory usage reached about 900Mb.

How do I catch this Exception?

I have saved 258927 rows with 15 columns lastly. Exported file size is 24.2MB and Memory usage is about 600Mb. — Tony Jang
– Tony Jang, Commented Feb 29, 2016 at 7:08
In the question your wrote that you have a problem with 150,00 rows. It is less than 258,927. — Michał Komorowski
– Michał Komorowski, Commented Feb 29, 2016 at 7:11
In which line did you receive OutOfMemoryException? In the line where an valueObjArray is created or when SaveAs method is called... — Michał Komorowski
– Michał Komorowski, Commented Feb 29, 2016 at 7:12
@MichałKomorowski _workSheet.get_Range("A2", Convert.ToChar(colCnt + 64).ToString() + (rowCnt + 1).ToString()).Value2 = valueObjArray; This line recived. And 258,927rows succeed but 270,000rows failed — Tony Jang
– Tony Jang, Commented Feb 29, 2016 at 7:28

Michał Komorowski · Accepted Answer · 2016-03-14 07:00:35Z

4

Try to do it in batches:

//We will call SaveAs method many times and we don't want to be asked
//if a file should be overwritten every time.
xlApp.DisplayAlerts = false

int rowCnt = dgv.Rows.Count;
int colCnt = dgv.Columns.Count;

int batchSize = 100000; //Try to experiment with other values
int currentRow = 0;

object[,] valueObjArray = new object[batchSize, colCnt];

_workSheet.get_Range("A1", Convert.ToChar(colCnt + 64).ToString() + "1").Value2 = headerObjArray;     

while (currentRow < rowCnt)
{
    for (int rowIndex = 0; rowIndex < batchSize && currentRow + rowIndex < rowCnt; rowIndex++)
    {
        for (int colIndex = 0; colIndex < colCnt; colIndex++)
        {
            valueObjArray[rowIndex, colIndex] =             
            dgv[colIndex, currentRow + rowIndex].Value;
        }
    }

    ws.get_Range("A2", Convert.ToChar(colCnt + 64).ToString() + (currentRow + batchSize + 1).ToString()).Value2 = valueObjArray;
    ws.get_Range("B2", "B" + (currentRow + batchSize + 1).ToString()).NumberFormat = "yyyy-mm-dd hh:mm";

    wb.SaveAs("a.xlsx");

    currentRow += batchSize;
}

I was able to save 1 million rows in this way. I tested it with fake data so some minor changes / fixes might be requried.

edited Mar 14, 2016 at 7:00

answered Feb 29, 2016 at 7:35

Michał Komorowski

6,2681 gold badge23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tony Jang Over a year ago

Thanks for your answer! It works fine. But, that I have to use batch means there is a limit of capacity. Do I know where is limit?

Michał Komorowski Over a year ago

Well it is difficult to give a precise limit, it depends on number of columns, the size of data in each cell, machine specification...

Ivaylo Over a year ago

This solution looks very promising :) Just one question. When switching to next batch I think you will need to change following line:

ws.get_Range("A2", Convert.ToChar(colCnt + 64).ToString() + (currentRow + batchSize + 1).ToString()).Value2 = valueObjArray;

Especially "A2" with a dynamic value that takes into consideration the batch value.

MahalingaM SubramaniyaM · Accepted Answer · 2022-04-27 06:47:33Z

Try do this....

I have large amount of data 2 lakhs and above. First i am using excel package and Microsoft.Office.Interop.Excel Local system is working fine , but iis(static ip address) hosting after (worksheet.cells/worksheet.range) OutOfMemoryeException error thorw. So i am using StreamWriter and write to .xls file. After .xls convert to .xlsx file and delete .xls file. Its working for me. My english is not good . please understand it.

  private void ExportxlFile(DataTable dt)
    {
        try
        {
            //open file
            //non english not support(ex: Bangla Language)"সালাউদ্দিন স্টোর";
            // StreamWriter wr = new StreamWriter(@"D:\TestBook.xls");

            //non english support(ex: Bangla Language)
            StreamWriter wr = new StreamWriter(@"D:\TestBook.xls", true, Encoding.Unicode); // Encoding.Unicode or Encoding.UTF32

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");
            }

            wr.WriteLine();

            //write rows to excel file
            for (int i = 0; i < (dt.Rows.Count); i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (dt.Rows[i][j] != null)
                    {
                        wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");
                    }
                    else
                    {
                        wr.Write("\t");
                    }
                }
                //go to next line
                wr.WriteLine();
            }
            //close file
            wr.Close();

            //xls to xlsx convertion
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(@"D:\TestBook.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            wb.SaveAs(@"D:\TestBook.xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            wb.Close();
            app.Quit();

            //delete xls file
            System.IO.File.Delete(@"D:\TestBook.xls");
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Collectives™ on Stack Overflow

OutOfMemoryException while saving/creating/exporting excel sheet

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related