Sunday, August 4, 2013

Excel programming - OutOfMemoryException when setting value to a cell

I ran into OutOfMemory exception when setting a value to a cell in Excel.
Here is a simplified example in C#.

using Excel = Microsoft.Office.Interop.Excel;  //Excel PIA 12.0
//...

Excel.Application excelApp = new Excel.Application();
Excel.Workbook wb = excelApp.Workbooks.Add(Type.Missing);

Excel.Worksheet ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;

string val = "-" + "".PadRight(8192, ' ');
ws.Cells[1, 1] = val;

//....

Running this code throws OutOfMemoryException with the following message:

   Not enough storage is available to complete this operation. 
   (Exception from HRESULT: 0x8007000E (E_OUTOFMEMORY))

When tried to set the same value to a cell manually in Excel, I got more detail message.

   Formula is too long. Formulas may not exceed 8192 characters. 

So it makes more sense. When text begins with -, Excel consider it as Excel Formula.
In Excel 2007 - 2010, the max size of formula is 8192 (In Excel 2003, it is 1024).

So workaround might be to replace the first character with something else or prepend something in front of the text.