IEnumerable<T> to Excel (2007) w/ Formatting

I'm looking for a good way to export an IEnumerable to Excel 2007 (.xlsb). The T is a known type, so reflection is not completely necessary for performance reasons. I'm using .xlsb (excel binary format) because the amount of data will be large for Excel.

The IEnumerable in question has approximately 2 million records. The IEnumerable is retrieved from an Access database (.mdb) then goes through some processing, then finally LINQ queries are wrote to genera开发者_开发技巧te a report structure for T. Though these records do not need to get sent to excel as one (nor could it); it will be sub-divided by a condition to which the largest record length will be roughly 1 million records.

I want to be able to convert the data to an Excel Pivot Table for easy viewing.

My initial idea was to convert the IEnumerable to a 2Darray [,] then push into an Excel range using COM interop.

public static object[,] To2DArray<T>(this IEnumerable<T> objectList)
    {
        Type t = typeof(T);
        PropertyInfo[] fields = t.GetProperties();

        object[,] my2DObject = new object[objectList.Count(), fields.Count()];

        int row = 0;

        foreach (var o in objectList)
        {
            int col = 0;
            foreach (var f in fields)
            {
                my2DObject[row, col] = f.GetValue(o, null) ?? string.Empty;
                col++;
            }
            row++;
        }

        return my2DObject;
    }

I then took that object[,] and did a "transaction split" as I called it which just split up the object[,] into smaller chunks such as I'd create a List then go through each one and send into Excel range using something similar to:

Excel.Range range = worksheet.get_Range(cell,cell);
range.Value2 = List<object[,]>[0]

I'd obviously loop the above but just for simplicity it would look like the above. This will work though, it takes an enormous amount of time to process, over 30minutes.

I've dabbled in outputting the IEnumerable to CSV though, it is not very efficient either; since it first requires the .csv file to be created, then open the .csv file using COM interop to do the excel pivot table formatting.

My question: Is there a better (preferred) way to do this? Should I force execution (toList()) before iteration?

Should I use a different mechanism to output/display the data? I'm open to any options to get a disconnected IEnumerable out to file in an efficient manner.

-I wouldn't be opposed to using something like SQL Express.

---

The main question will be where the bottleneck is. I'd have a look at the code in a profiler to see what part of the execution is taking a long time. It can also be worthwhile looking at your resource usage by running the process and seeing whether there is a shortage of CPU or Memory, or whether it's disk-locked. If you're getting sensible performance doing 2000 records at a time, then I suspect memory resources may be an issue - with the code you posted you're converting an IEnumerable (which can avoid loading a complete dataset into memory) into an entirely in-memory structure with potentially a million records - depending on the size and number of fields involved, this could easily become an issue. If the problem looks like the time to create the Excel file itself (which it doesn't immediately sound like it is in this case), then COM interop calls can add up, and some of the 3rd party Excel libraries aim to be much faster at writing Excel files, particularly with large numbers of records, so rather than necessarily use Excel Binary format and COM, I'd suggest looking at an Open Source library like EPPlus (http://epplus.codeplex.com/) and seeing what the performance difference is like.