Getting data from Excel the fast way, using LINQ

You may also like...

14 Responses

  1. Als de data al in de DataTable zit dan zie ik eigenlijk niet echt de meerwaarde van LINQ niet.

    Maar dat kan aan mij liggen.

    foreach DataRow dbrow in dataTable.Rows
    Console.WriteLine (dbrow(“ClientName”));

    of iets dergelijks.

  2. Great example, but I think you have a small error in this line:

    OleDbDataAdapter dataAdapter = new OleDbDataAdapter(“SELECT * FROM [Sheet1$]“, strConn);

    I think “strConn” should be “connectionString”.

    Thanks again,

    pt

  3. Niels Olsen says:

    How to get data the other way around? I mean from SQL Server using Linq to Excel. I have tried using a web page to publish the result of the Linq query, and then import using Excel’s import from web page. It works but the performance is poor. Is there a way to “convert” the Linq query to a ODBC connection to avoid all the ASCII conversions?

  4. Dennis van der Stelt says:

    @Niels : Could you be a little more specific, because I don’t really understand what you’re doing.

    If you want to exchange OLEDB for ODBC, I wouldn’t recommend that, because normally (I said : normally :) OLEDB is always faster.

    But I don’t understand from where to where you’re importing and exporting stuff. Are you getting info from Excel into a dataset or vice-versa?

  5. Excellent, been looking for an example of this!

  6. Andy S. says:

    This example was very helpful. Here is my VB.NET version:

    ‘ Imports System.Data.OleDb
    Dim filename As String = “C:myfile.xls”

    Dim connectionString As String = String.Format(“Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;”, filename)

    Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter(“SELECT * FROM [Sheet1$]“, connectionString)

    Dim myDataSet As DataSet = New DataSet()

    dataAdapter.Fill(myDataSet, “ExcelInfo”)

    Dim dataTable As DataTable = myDataSet.Tables(“ExcelInfo”)

    Dim query = From r In dataTable.AsEnumerable() _
    Select New With _
    { _
    .RelationNr = r.Field(Of Double)(“RelationNumber”), _
    .ClientName = r.Field(Of String)(“ClientName”) _
    }

    For Each item In query
    Console.WriteLine(item.ClientName)
    Next

  7. Dennis van der Stelt says:

    Thanks Andy, that’s awesome!

  8. Paul says:

    You can also use the Linq to Excel open source project (http://code.google.com/p/linqtoexcel/) to easily get data from Excel. It takes care of the OleDB connection for you and you can use the where clause. All you have to do is create a class with properties corresponding to the excel column names. Here’s an example:

    IExcelRepository repo = new ExcelRepository(@”C:myfile.xls”);
    var largeClients = from c in repo.Worksheet
    where c.Employees > 200
    select c;
    foreach (Client client in largeClients)
    Console.WriteLine(client.ClientName);

  9. Dennis van der Stelt says:

    @Paul : That’s a cool project! Thanks for the info!

  10. slabo says:

    where is the parameter “ExcelInfo” coming from?
    Anyone have references for this code?

  11. Dennis van der Stelt says:

    @slabo: It’s just a name, could be anything, but it specifies what name the table should have in the DataSet. That’s all.

  12. otti says:

    Hi,
    what should I change to get data from an *.xlsx-file?

  13. Vijay says:

    It works.. awesome post..

    Thanks :)

Click on a tab to select how you'd like to leave your comment

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>