CODEWITHSUNDEEP
c#.netdata-structures
Cannon
Cannon

Reputation: 2783

OutofMemory Exception Object Array Size

I am trying to capture a spreadsheet data in to a 2D array. I am using VSTO.

int rc = 1048576;
int cc = 1638;

string[,] arr = new string[rc, cc];

The last line throws Out of Memory exception. I would like to show message telling the user only 'X' elements can be captured.

Checked MSDN and there is a row count limit mentioned of 16,777,216. No Column count limitation for datatable. Cant find limit either for 2D array.

My issue is not with WHY the exception. What I am looking for is if you are doing VSTO development, and had to capture a worksheet in a DataTable to perform In-Memory joins etc, you will need to do this: 

string[,] arr = new string[rc, cc]; 
Microsoft.Office.Interop.Excel.Range selection 
arr = selection.Value as string[,];

and then copy the data from that array to datatable. Now what will be the ideal limit for number of elements a user should select. So I can set that rowcount/columncount lmits and display message when selection exceeds this criteria.

Upvotes: 0

Views: 1819

Answers (2)

Martin Meeser
Martin Meeser

Reputation: 2956

Aleks, your are asking for a limit of your memory allocation. Let me suggest to change your point of view and limit your input either

  • 1.1 by using only the user selected cells
  • 1.2 by using only the indeed used cells within your xl.worksheet

Additionally to your question, let me advise to mind the difference of Range.Value and Range.Value2: Range.Value contains the user input e.g. a formula like =SUM(A1:B10), while Range.Value2 contains the result of that formula e.g. 10.

1.1 Using only the user selected cells
As stated here use Range userSelectedRange = Application.Selection;

1.2 Using only the indeed used cells within your xl.worksheet
Use the Worksheet.Cells.SpecialCells property.
Maybe Worksheet.UsedRange is even better.

A code example:

int lastUsedRowIndex = xlWorksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
int lastUsedColIndex = xlWorksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Column;
string rangeString = string.Format("A0:{0}{1}", SomeIndexToExcelColConverterFunc(lastUsedColIndex), lastUsedRowIndex));
Xl.Range range = xlWorksheet.Range[rangeString];
object[,] objectMatrix = range.Value2 as object[,];
Marshal.ReleaseComObject(range);    
// convert to string here if needed

Upvotes: 0

Darin Dimitrov
Darin Dimitrov

Reputation: 1038770

Let's do the math. You are trying to allocate a 2D string array with 1048576 * 1638 = 1717567488 elements. The size of string reference on x64 is 8 bytes => a total of 1717567488 * 8 = 13740539904 bytes. Which is about 13 GB of continuous memory space. Maximum size for single allocation for CLR is 2GB, so you are getting OutOfMemoryException as such single block can't be allocated.

Note that such amount of strings even when all are 1-2 characters long will take 30GB for string values in addition to references. What else than an OutOfMemoryException did you expect to get?

Upvotes: 16

Related Questions