Populate Excel with data from LINQ to SQL query

Question

I am trying to send some data from a LINQ query in C# to an Excel speed sheet using OLE

I have a query like this:

Var data = from d in db.{MyTable}
           where d.Name = "Test"
           select d;

I have the Excel OLE object working fine, I just can't figure out how to populate the cells in Excel with the data from the LINQ query.

PS: I am using Excel 2003 if that makes any difference.

Can anyone help me with this?

Damien Damien · Accepted Answer · 2008-12-18T02:42:15

Sending individual OLE commands for each Excel cell is very slow so the key is to create an object array like this:

int noOfRows = data.Count - 1;
int noOfColumns = mydataclass.GetType().GetProperties().Count() - 1;
Object[noOfRows, noOfColumns] myArray;

Sending an object array allows you to send a mixture of data types e.g. string, integer, date etc. However, if you know that the data is all text for example, you can just send a string array.

The array needs to be populated from the query either by assigning each individual value or perhaps by using reflection to get the data object properties. Then you can send the array to Excel like this:

XLOLESheetobj.Range("A1","Z20").Value = myArray;

You can substitute Z20 with the number of columns -> Char + the number of rows -> string.

Populate Excel with data from LINQ to SQL query

3 Answers