Export a C# List of Lists to Excel

Question

Using C#, is there a direct way to export a List of Lists (i.e., List<List<T>>) to Excel 2003?

I am parsing out large text files and exporting to Excel. Writing one cell at a time creates way too much overhead. I chose to use List<T> so that I would not have to worry about specifying the number of rows or columns.

Currently, I wait until end of file, then put the contents of my List<List<object>> into a 2-dimensional array. Then the array can be set as the value of an Excel.Range object. It works, but it seems like I should be able to take my List of Lists, without worrying about the number of rows or columns, and just dump it into a worksheet from A1 to wherever.

Here's a snippet of the code I'd like to replace or improve on:

object oOpt = System.Reflection.Missing.Value; //for optional arguments
Excel.Application oXL = new Excel.Application();
Excel.Workbooks oWBs = oXL.Workbooks;
Excel._Workbook oWB = oWBs.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel._Worksheet oSheet = (Excel._Worksheet)oWB.ActiveSheet;

int numberOfRows = outputRows.Count;
int numberOfColumns = int.MinValue;

//outputRows is a List<List<object>>
foreach (List<object> outputColumns in outputRows)
{
        if (numberOfColumns < outputColumns.Count)
        { numberOfColumns = outputColumns.Count; }
}

Excel.Range oRng = oSheet.get_Range("A1", oSheet.Cells[numberOfRows,numberOfColumns]);

object[,] outputArray = new object[numberOfRows,numberOfColumns];

for (int row = 0; row < numberOfRows; row++)
{
        for (int col = 0; col < outputRows[row].Count; col++)
        {
                outputArray[row, col] = outputRows[row][col];
        }
}

oRng.set_Value(oOpt, outputArray);

oXL.Visible = true;
oXL.UserControl = true;

This works, but I'd rather use the List directly to Excel than having the intermediary step of creating an array just for the sake of Excel. Any ideas?

Answer 1

Strategically, you are doing it correctly. As Joe says, it is massively faster to execute cell value assignments by passing an entire array of values in one shot rather than by looping through the cells one by one.

Excel is COM based and so operates with Excel via the .NET interop. The interop is ignorant of generics, unfortunately, so you cannot pass it a List<T> or the like. A two dimensional array really is the only way to go.

That said, there are a few ways to clean up your code to make it a bit more manageable. Here are some thoughts:

(1) If you are using .NET 3.0, you can use LINQ to shorten your code from:

int numberOfColumns = int.MinValue;

foreach (List<object> outputColumns in outputRows)
{
        if (numberOfColumns < outputColumns.Count)
        { numberOfColumns = outputColumns.Count; }
}

to a single line:

int numberOfColumns = outputRows.Max(list => list.Count);

(2) Don't use the _Worksheet or _Workbook interfaces. Make use of Worksheet or Workbook instead. See here for a discussion: Excel interop: _Worksheet or Worksheet?.

(3) Consider making use of the Range.Resize method, which comes through as Range.get_Resize in C#. This is a toss-up though -- I actually like the way you are setting your range size. But it's something that I thought that you might want to know about. For example, your line here:

Excel.Range oRng = oSheet.get_Range("A1", oSheet.Cells[numberOfRows,numberOfColumns]);

Could be changed to:

Excel.Range oRng = 
    oSheet.get_Range("A1", Type.Missing)
        .get_Resize(numberOfRows, numberOfColumns);

(4) You do not have to set the Application.UserControl to true. Making Excel visible to the user is enough. The UserControl property is not doing what you think it does. (See the help files here) If you want to control whether the user can control Excel or not, you should utilze Worksheet protection, or you could set Application.Interactive = false if you want to lock out your users. (Rarely a good idea.) But if you want to allow the user to use Excel, then simply making it visible is enough.

Overall, with these in mind, I think that your code could look something like this:

object oOpt = System.Reflection.Missing.Value; //for optional arguments
Excel.Application oXL = new Excel.Application();
Excel.Workbooks oWBs = oXL.Workbooks;
Excel.Workbook oWB = oWBs.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet oSheet = (Excel.Worksheet)oWB.ActiveSheet;

//outputRows is a List<List<object>>
int numberOfRows = outputRows.Count;
int numberOfColumns = outputRows.Max(list => list.Count);

Excel.Range oRng = 
    oSheet.get_Range("A1", oOpt)
        .get_Resize(numberOfRows, numberOfColumns);

object[,] outputArray = new object[numberOfRows, numberOfColumns];

for (int row = 0; row < numberOfRows; row++)
{
    for (int col = 0; col < outputRows[row].Count; col++)
    {
        outputArray[row, col] = outputRows[row][col];
    }
}

oRng.set_Value(oOpt, outputArray);

oXL.Visible = true;

Hope this helps...

Mike

Answer 2

It's much faster to pass a two-dimensional array to Excel than to update cells one at a time.

Create a 2-dimensional array of objects with values from your list of lists, redimension the Excel range to the dimensions of your array, and then call range.set_Value, passing your two-dimensional array.