Using LINQ to Query Excel Tables

Writing semantically aware LINQ queries against Excel tables

Brian Jones: Open XML Formats — Fri, 14 Nov 2008 22:34:45 GMT

I hope you all have been enjoying Zeyad's articles showing some of the powerful solutions you can build

re: Using LINQ to Query Excel Tables

Carlos LeBrilla — Tue, 18 Nov 2008 02:14:30 GMT

Is there anyway to use this with Excel Services?

re: Using LINQ to Query Excel Tables

EricWhite — Tue, 18 Nov 2008 17:38:58 GMT

Hi Carlos,

If you are writing a feature for SharePoint, you could use this approach to extract information from spreadsheets. Also, if you have spreadsheets in a document library, you could write a web service to retrieve the spreadsheets and use this code to query tables within the spreadsheets. Does this answer your questions?

Thanks, Eric

re: Using LINQ to Query Excel Tables

Richerd — Tue, 18 Nov 2008 20:26:37 GMT

Hi Eric,

I am building a B/S software to run a management work with VS2008Sp1 and SQL 2005. I use Linq in my project. My problem is, could I use linq to update the Datum in Excel. if so, I can use LinQ to SQL to retrive data, and Linq to Xml to write that data to excel.

Thanks in advance.

re: Using LINQ to Query Excel Tables

EricWhite — Tue, 18 Nov 2008 21:04:05 GMT

Hi Richard,

Yes, this is certainly possible. The easiest way is to have a 'template' spreadsheet that you copy and modify, inserting the results of your LINQ to SQL query. In short, you want to modify the worksheet part, and replace the x:sheetData element and its child x:Row elements with new elements that you construct from your query.

-Eric

re: Using LINQ to Query Excel Tables

Richerd — Wed, 19 Nov 2008 06:15:27 GMT

Hi Eric,

I am trying my best to find out a fast solution for the reporting part of my project. I have to export the data in to Excel. And I have realize that with automation Excel in the serverside(not a good solution), javascrip and gridview to out put the dataset from the Celint side. And now, I find that open XML maybe a better way to do that. Of course, I have write data to excel with openXml sdk. But I do not know how to manipulate the excel style(eg.the column with, border, and etc). Could you please provide some resource (eg. blog, article or website) for me to learn about that?

Do you have any suggestion for me?

What I need is a Stable and fast system to export data from SQL 2005 to excel. Thanks a lot!

re: Using LINQ to Query Excel Tables

Richerd — Wed, 19 Nov 2008 06:20:27 GMT

And does it the linq that makes my exporting system slow?

[Open XML] Utiliser le SDK 2.0 pour supprimer les commentaires Powerpoint 2007

Julien Chable — Wed, 19 Nov 2008 11:39:46 GMT

Dernier post de cette série sur la suppresion des commentaires dans les documents PowerPoint 2007 (PresentationML).

re: Using LINQ to Query Excel Tables

EricWhite — Wed, 19 Nov 2008 15:46:42 GMT

Hi Richard,

Exporting the data into Excel is certainly doable. I have a screen-cast that I need to record that shows how to do this, but basically, the gist is to find the sheetData element in the worksheet, and replace that element with a new one that contains appropriate child row elements. Take a look at this post: http://blogs.msdn.com/brian_jones/archive/2008/11/04/document-assembly-solution-for-spreadsheetml.aspx , and in general, look at the other posts by Zeyad Rajabi on Brian Jones's blog.

One approach for doing formatting - it is easiest to set up a spreadsheet with your desired formatting, and then modify the spreadsheet rather than generating the spreadsheet with formatting from scratch.

Also, keep OpenXmlDiff in mind - this has the capacity to teach you how to format - save a spreadsheet, change formatting slightly, save it again, and see the differences. This shows you the markup necessary to change formatting.

Regarding speed, the portion to write out the Open XML, or read the Open XML using either LINQ to XML or Open XML SDK V2 will, in general, be very fast.

-Eric

re: Using LINQ to Query Excel Tables

Richerd — Wed, 19 Nov 2008 15:59:10 GMT

Hi Eric,

I really appreciate your help. I will learn more throug the resources which you and your firend provided.

Thanks again.

[Open XML] Les liens de la semaine 17/11/08 et autres infos en vrac

Julien Chable — Fri, 21 Nov 2008 16:39:08 GMT

Comme à l'accoutumé, voici une brochettes de liens de la semaine sur Open XML. Posts techniques en vrac

Joining LINQ to SQL and LINQ to Excel

Eric White's Blog — Thu, 04 Dec 2008 01:54:07 GMT

By combining LINQ to SQL with LINQ to Objects in this fashion, you can write some pretty simple C# code that uses LINQ to join data across two separate, disparate data sources.

re: Using LINQ to Query Excel Tables

Constantinos Leftheris — Mon, 22 Dec 2008 16:59:38 GMT

Is there any chance in the future, of providing additional functionality to the given example for Tables so that after manipulating the table contents in memory you can actualy save the results back? Your post was a great help for me, thanks alot!!

re: Using LINQ to Query Excel Tables

EricWhite — Tue, 23 Dec 2008 16:07:09 GMT

Hi Constantinos, I do have plans to show some code to update tables. This would be valuable, I think.

-Eric

re: Using LINQ to Query Excel Tables

Paul — Sat, 27 Dec 2008 03:12:40 GMT

Nice post Eric. Have you tried out the open source Linq to Excel project (<a href="http://code.google.com/p/linqtoexcel/">http://code.google.com/p/linqtoexcel/</a>) to use linq queries against Excel? It uses OleDb and takes care of everything in the background. All you have to do is declare a simple class with properties that map to the column names in the excel sheet. Here's an example:

IExcelRepository<Customer> repo = new ExcelRepository<Customer>();

var londonCustomers = from c in repo.Worksheet

where c.City == "London"

select c;

foreach (Customer customer in londonCustomers)

Console.WriteLine(customer.ToString());

Links for January 9th 2008: LINQ for Office Developers, Office and Open XML Poster Downloads

Erika Ehrli — Fri, 09 Jan 2009 22:04:25 GMT

Here is a list on links that I want to share with you. LINQ for Office Developers Some Office solutions

re: Using LINQ to Query Excel Tables

Deepesh — Sun, 15 Mar 2009 18:59:48 GMT

Hi Eric,

You post are good and book even better, but it has been a struggle for me most of the time because i have to painfully reconstruct everything from c# to VB, could you be kind enough to give VB option to all your code?

Thanks a lot

Deepesh

re: Using LINQ to Query Excel Tables

EricWhite — Mon, 16 Mar 2009 21:31:16 GMT

Hi Deepesh,

I agree, having VB samples would be great. I'll do this when possible. :)

-Eric

re: Using LINQ to Query Excel Tables

Mickel — Sun, 10 May 2009 20:15:41 GMT

Hi Eric,

This helped me ALOT and I am very grateful for all your high-quality posts :)

re: Using LINQ to Query Excel Tables

Bernard Darnton — Wed, 13 May 2009 07:02:09 GMT

Thanks for this post - I've found it very useful.

One problem I've run into is addressing table columns where columns have been inserted after the rest of the table has been created. The ids are no longer in order and the TableRow[columnName] method no longer works.

You get this in table1.xml:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

</autoFilter>

</tableColumns>

</table>

Because the column ids are out of order, the line in the TableRow class that gets the cell gets the wrong result:

string columnAddress = (startRefs[0].ColumnAddressToIndex() + tc.Id - 1).IndexToColumnAddress();

re: Using LINQ to Query Excel Tables

Bernard Darnton — Wed, 13 May 2009 07:39:49 GMT

The solution I've used for the problem above is to add a COlumnIndex property to the TableColumn class:

public int ColumnIndex { get; set; }

Set it using the ElementsBeforeSelf method:

new TableColumn(this)

{

Id = (int)c.Attribute("id"),

Name = (string)c.Attribute("name"),

FormatId = (int?)c.Attribute("dataDxfId"),

QueryTableFieldId = (int?)c.Attribute("queryTableFieldId"),

UniqueName = (string)c.Attribute("uniqueName"),

ColumnIndex = c.ElementsBeforeSelf().Count()

}

And then fix the cell reference in the TableRow[columnName] method:

string columnAddress = (startRefs[0].ColumnAddressToIndex() + tc.ColumnIndex).IndexToColumnAddress();

Hope that's useful for someone.

re: Using LINQ to Query Excel Tables

Minh Le — Sun, 26 Jul 2009 07:32:18 GMT

Hi,

I just use Open XML Lib to query data from Excel file. However, I have one problem with this approach. The problem is that I can not handle the black cell. For ex: I have a table with 14 columns. The first row is the header, it contains column's name. The remaining rows are the data. In the range of data, there's some cell are blank. When I query this data, instead of getting 14 columns for row, I only get 13 columns and the data is incorrect due to lack of order.

Onw work around for me right now is replace the blank cell with the null pattern defined by myself, and later I replace the pattern with space. But I find it is tricky.

Do you know a better solution?

Regards,

Minh.

re: Using LINQ to Query Excel Tables

sanvid — Fri, 07 Aug 2009 13:45:35 GMT

hi what my requirement is client will be able to uplad the multiple CSV uaually 5 to 6 which is user define and he wants to generate another csv file with a use of query builder. As per generated query a new file is generated. Can you please help me to fire a query with multiple tables in csv file. is it possible with above given solution by you.

re: Using LINQ to Query Excel Tables

sanvid — Fri, 07 Aug 2009 13:45:46 GMT

re: Using LINQ to Query Excel Tables

sanvid — Fri, 07 Aug 2009 13:45:58 GMT

hi what my requirement is client will be able to upload the multiple CSV usually 5 to 6 which is user define and he wants to generate another csv file with a use of query builder. As per generated query a new file is generated. Can you please help me to fire a query with multiple tables in csv file. is it possible with above given solution by you.

re: Using LINQ to Query Excel Tables

gopal — Wed, 12 Aug 2009 15:36:07 GMT

how to convert following string in to linq query as in my project its comming dynamically.

"from i in spreadsheet.Table("Inventory").TableRows()

where (int)i["Qty"] > 2

select i;"

re: Using LINQ to Query Excel Tables

EricWhite — Wed, 12 Aug 2009 21:27:26 GMT

Hi Gopal,

Take a look at this post:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

-Eric

re: Using LINQ to Query Excel Tables

samir — Mon, 02 Nov 2009 13:05:06 GMT

I have two spreadsheets: one is a list of job numbers and descriptions and the other is a time sheet. Any ideas on how I could get the job numbers as a drop -down list in the time sheet file?

Thanks.

re: Using LINQ to Query Excel Tables

EricWhite — Thu, 05 Nov 2009 08:42:33 GMT

Hi Samir, I'm not a word-automation expert. This is a good question. I'll ask one of the experts in my office.

-Eric

re: Using LINQ to Query Excel Tables

EricWhite — Fri, 06 Nov 2009 05:19:05 GMT

Hi Samir, I have a few questions. Are you looking for a solution in Visual Studio? Do you want the combo box on a toolbar? Which version of Excel are you using? Feel free to contact me directly via the email button above, and I'll be happy to help get you the answers you need.

Thanks, Eric

re: Using LINQ to Query Excel Tables

EricWhite — Sat, 07 Nov 2009 04:25:56 GMT

Hi Samir, I have perhaps a solution for you. I have a shared Add-in project that populates a combo box on the Ribbon in one Excel workbook with a range of data from another workbook. If you'd like to contact me directly via the EMAIL button on my blog, I'd be happy to send it to you (and anyone else who wants it).

-Eric