Reading half million records from Excel workbook in a VSTO project

Question

I am trying to build a simulation tool in Excel using VSTO and by creating a Visual Studio 2010 Office workbook project. One of the worksheets in this workbook will contain approximately half a million records. Ideally I would like to read all of the records use them in the simulation and then output back some statistics. So far I had OutOfMemory exceptions when I tried to get the whole range and then the cells out of it in one go. Does anyone have other ideas as to how I can read all of the data or suggestions in doing this?

This is my code:

Excel.Range range = Globals.shData.Range["A2:AX500000"];

Array values = (Array)range.Cells.Value;

Answer 1

How about fetching in batches, and assembling a slightly less memory heavy model in memory?

var firstRow = 2;
var lastRow = 500000;
var batchSize = 5000;
var batches = Enumerable
    .Range(0, (int)Math.Ceiling( (lastRow-firstRow) / (double)batchSize ))
    .Select(x => 
        string.Format(
            "A{0}:AX{1}",
            x * batchSize + firstRow,
            Math.Min((x+1) * batchSize + firstRow - 1, lastRow)))
    .Select(range => ((Array)Globals.shData.Range[range]).Cells.Value);

foreach(var batch in batches)
{
    foreach(var item in batch)
    {
        //reencode item into your own object collection.
    }
}