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?

Answer 1

I assume you are not using OLE in a web scenario, because it will eventually fail.

If you just need raw data, you can dump to a tab-delimited textfile:

var lines = data.Select(d => d.Name + '\t' + d.AnotherProperty + ...);

Answer 2

Your best option is using the Excel XML format. I did a blog post on how to achieve that: http://www.aaron-powell.com/blog.aspx?id=1237

You're other option would be looking at the LINQ provider for Excel - http://www.codeplex.com/xlslinq.

Answer 3

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.