Why am I getting an Out of Memory Error doing ASP .NET Excel Interop?

Question

This was working..and I moved the disposal code to the finally block, and now it fails every time.

I have a test spreadsheet with 4 records, 6 columns long. Here is the code I'm using to bring it in. This is ASP .Net 3.5 on IIS 5 (my pc) and on IIS 6 (web server).

It blows up on the line right before the catch: "values = (object[,])range.Value2;" with the following error:

11/2/2009 8:47:43 AM :: Not enough storage is available to complete this operation. (Exception from HRESULT: 0x8007000E (E_OUTOFMEMORY))

Any ideas? Suggestions? I got most of this code off codeproject, so I have no idea if this is the correct way to work with Excel. Thanks for any help you can provide.

Here is my code:

Excel.ApplicationClass app = null;
Excel.Workbook book = null;
Excel.Worksheet sheet = null;
Excel.Range range = null;

object[,] values = null;

try
{
    // Configure Excel
    app = new Excel.ApplicationClass();
    app.Visible = false;
    app.ScreenUpdating = false;
    app.DisplayAlerts = false;

    // Open a new instance of excel with the uploaded file
    book = app.Workbooks.Open(path);

    // Get first worksheet in book
    sheet = (Excel.Worksheet)book.Worksheets[1];

    // Start with first cell on second row
    range = sheet.get_Range("A2", Missing.Value);

    // Get all cells to the right
    range = range.get_End(Excel.XlDirection.xlToRight);

    // Get all cells downwards
    range = range.get_End(Excel.XlDirection.xlDown);

    // Get address of bottom rightmost cell
    string downAddress = range.get_Address(false, false, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

    // Get complete range of data
    range = sheet.get_Range("A2", downAddress);

    // get 2d array of all data
    values = (object[,])range.Value2;
}
catch (Exception e)
{
    LoggingService.log(e.Message);
}
finally
{
    // Clean up
    range = null;
    sheet = null;

    if (book != null)
        book.Close(false, Missing.Value, Missing.Value);

    book = null;

    if (app != null)
        app.Quit();

    app = null;
}

return values;

Answer 1

I'm not sure if this is your issue or not, but it very well may be. You are not cleaning up your excel objects properly. They are unmanaged code and can be tricky to clean up. Finally should look something like this: And as the comments have noted working with excel from asp.net is not a good idea. This cleanup code is from a winform app:

 GC.Collect();
 GC.WaitForPendingFinalizers();


 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(range);
 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(sheet);
 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(book);

 WB.Close(false, Type.Missing, Type.Missing);

 Excel.Quit();
 System.Runtime.InteropServices.Marshal.FinalReleaseComObject(Excel);

EDIT

An alternative would be to use ado.net to open the workbook.

            DataTable dt = new DataTable();

            string connectionString;
            System.Data.OleDb.OleDbConnection excelConnection;
            System.Data.OleDb.OleDbDataAdapter da;
            DataTable dbSchema;
            string firstSheetName;
            string strSQL;

            connectionString = @"provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + @";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""";
            excelConnection = new System.Data.OleDb.OleDbConnection(connectionString);
            excelConnection.Open();
            dbSchema = excelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
            strSQL = "SELECT * FROM [" + firstSheetName + "]";
            da = new OleDbDataAdapter(strSQL, excelConnection);
            da.Fill(dt);

            da.Dispose();
            excelConnection.Close();
            excelConnection.Dispose();

Answer 2

Creating/destroying excel on every request will have absolutely terrible performance to matter what you do. In general running any Office app using automation is a nasty business for a lot of reasons (see here). The only way I got it to work is to have a single instance of the app (in my case Word) which is initialized once, and then requests are queued to this instance for processing

If you can stay away from the apps and parse the file yourself (using MS libraries, of just XML)

Answer 3

The error is probably exactly what it says, you are getting an out of memory error. Try to split the loading of the values array into several smaller chunks instead of getting the entire Range at one time. I tried your code out in C# and had no issues, but my spreadsheet I was loading was mostly empty.

I noticed that the Range was the entire spreadsheet though (from A2 to IV65536 or something). I'm not sure if that is intended.

One thing you could try using is sheet.UsedRange, that will cut down on the number of cells you are loading.

A couple additional small things that I have learned which you may find useful:

• Use Application instead of ApplicationClass
• Use Marshal.FinalReleaseComObject(range) (and also for sheet, book, app) otherwise you will have your EXCEL.EXE process sticking around.

Answer 4

You're going to run into a lot of trouble using interop from ASP.NET. Unless this is meant for some tiny in house application it would be advisable not to go forward with it.

Office Interop is not a programming API in the traditional sense - it's the Office macro system taken to its maximum, with the ability to work interprocess - for example an Excel macro could interact with Outlook.

Some consequences of using interop are:

• You are actually opening a full copy of the office application.
• Your actions are being executed by the application as if a user initiated them - that means instead of error messages being returned in code, they are displayed in the GUI.
• Your copy of the application only closes if you explicitly command it - and even then errors can prevent that from actually happening (the application may present a "do you want to save" dialog if you did not programmably tell Excel that the changes do not need to be saved). This usually results in many hidden copies of Excel being left running on the system - open task manager and see how many excel.exe processes are running.

All of this makes interop something to avoid for regular desktop application, and something that should only be used as a last resort for server applications, since a GUI popup requiring action or script that leaks process is murder on a server environment.

Some alternatives include:

• Using Microsoft Office 2007 XML based formats, so that you can write the XML files yourself.
• Using SpreadsheetGear.Net, which is a .NET binary Excel file reader/writer (you don't need Excel installed, as it is completely stand alone). SpreadsheetGear models itself after the Intertop interfaces to make conversion of older code easier.

Answer 5

Maybe you can try some third party excel components,for example SmartXLS for .net,it can read/write excel files without Excel installed.