Video: How Do I: Use LINQ Against Excel Ranges?

Presenter:Steve Hansen

In this video, Steve Hansen demonstrates how you can use LINQ to write and execute queries against a list of values from Excel.


The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0
By my name is Steve Hansen with officer let com is produced on a use link with ranges in Excel and to demonstrate bickering a new project using Visual Studio 2008 Beta two and a type in an existing document benefits him down and soul of the about link link stands for language integrated query is a query languages and net 3 5 and will the cool things about link is that you can apply to in memory information not his information from external sources if applied to any IE innumerable based information source another cool thing about link is that you can join data together so for example Miss workbook
Minute 1
I got a one simple list of products different types of fruits and then another one that shows sales information and you can see in these lists one of the common pieces of information is product ID so this example on them so you two things won a simple example of how to use link and then another example of how you can join these two tables together for a start noticed that a guy named ranges so ago that product named range and I ve also got a sales main range in order to launch this information for the example program about
Minute 2
Items can put a button on a document surface and first thing am going to do is import the link namespace and others create a procedure to run my sample code and from my button click event handler I ll call the run example procedure in a way that I m going to use this as I m going to read the information from
Minute 3
Its list into separate lives and that I m going to query the lists were to make this easier going to create a couple of classes that represent the types of lists I liked it like a great soul have one class that represents the product another class that represents a sale and this can put some public verbals in these classes to hold information
Minute 4
But as an a method fridge class to vote the items from the range and Excel into lives of classes so have a private function to load sales and then another function to load products will come back to these and
Minute 5
Estimate in my main future and have one variable for products is going to be a list of products and can equal the result of the load products procedure same thing for sales so to load the sales and had to declare a list and declare a range verbal clearance sale variable
Minute 6
Will do is set a reference to the range of sales in the worksheet and rather than reference to all of these sales what I d like to do is resize that Raines site arranged as to the first item in really the first column and limited to is just walked down the list of sales until I find an empty cell at that point I know I ve finished reading all the sale items into my list and I will exit the procedure song and set a reference to the sales named range humans is resize it to range that is one row by one column
Minute 7
Note that this is a resizing the sales named range is just resizing the reference that I originally got by setting a reference to the entire sales range from the owl going to all be walking through each row in the range than using the
Minute 8
Offset method web is that it be each property of my sale by first referring to the range and then using you offset method to go one column over the store and to go to columns over to retrieve the amount they need to move down one row before moving on back to the top of the loop oh before do that need to add the sale item to my list of sales than at the end of going to does return my list
Minute 9
Tofu products and menus in the exact same methodology as I did for loading sales so refer to the to the list of products instead
Minute 10
So that a reloading the list from my named ranges and Excel worksheet snow that got to be main example method here and plus as well so the first thing I do is a shown example of a simple link query
Minute 11
So these queries are very similar to running SQL but there are few differences of the similar but little bit different from the biggest thing is that must ve started starting with your select statement to an about select statement and you begin with a from statement and notes as I type a query to get IntelliSense from menus as is basically think it is like an alias for table from us and sales which is my list here
Minute 12
And I m going to do use a where clause Ramon is greater than 40 minutes select the items spent at a select of the items was for them in range G3 making is a for each to iterate over the items I want to iterate over the product sales
Minute 13
19 sale at a relationship until a sincere making is the offset method to just move over column for the other items so that is a simple example and I ll add to this system in it but before do we build it and run it to make sure everything works so far Arafat to do more
Minute 14
Thing if not to move down one row in my loop okay and they don t sell this might be helpful to people that don t have detailed knowledge of the Excel object model or home you might do different things and Excel still need to query a lot of information certainly different ways that you could go about doing the same thing notice however that this was a pretty simple query you could have a much more complicated where clauses and you can do filtering and sorting
Minute 15
And even aggregate functions so it s very flexible or language for doing that type of work without doing all sorts of different things and Excel to figure out the same the same thing but another cool thing you can do is really joining to different regions of data someone add to the example here and I ll go in and put a pic of Ranger to 25 for guests to shuttle example here of joining two ranges stellar and displaying the results so this was then start the same
Minute 16
Septum going to join the sales table to the products table knows I get IntelliSense insights into my house and get the product ID and it equals the product ID from the product table knowing his November cause here to wear a store equals a fun am going to select the product name for my products table 1 with the store name
Minute 17
And in the amount of sale
Minute 18
Are really we suddenly noticed that the information that comes from both of the tables so I can bring in the product name that includes a studio and how to use link with ranges and Excel thanks crossing a great day

Click here to go to original video page