Read Excel using LINQ

Question

I want to read excel 2003( cannot change as its coming from third party) and group data in List or Dictionary (I don't which one is good) for example below (Excel formatting ) Books Data [first row and first column in excel] second row( no records) Code,Name,IBN [third row (second column, third column] Aust [fourth row, first column] UX test1 34 [ fifth row (second column, third column] ...... ....

Books Data

     Code     Name     IBN

Aust

    UX         test1     34
   UZ         test2     345
   UN         test3     5654

US

   UX         name1     567
  TG         nam2      123
  UM         name3     234

I am reading excel data using following code( some help from Google)

        string filename = @"C:\\" + "Book1.xls";
        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                      "Data Source=" + filename + ";" +
                                      "Extended Properties=Excel 8.0;";

        OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
        DataSet myDataSet = new DataSet();
        dataAdapter.Fill(myDataSet, "BookInfo");
        DataTable dataTable = myDataSet.Tables["BookInfo"];


       var rows = from p in dataTable.AsEnumerable()
       where p[0].ToString() != null || p[0].ToString() != "" && p.Field<string>("F2") != null
       select new 
       { countryName= p[0],
           bookCode= p.Field<string>("F2"),
           bookName= p.Field<string>("F3")
      };

The code above is not good as to get the “Code” I am using “ F2” and for country I am using p[0].What should I use to get the code and name for each country.

Also it’s give the information I want but I don't how to put in list or dictionary or in class so I can get data by passing parameter as a country name.

In short first it must put all data in list or dictionary and then you can call list or dictionary get data filter by country. Thanks

Answer 1

There's two things you need to do:

First, you need to reformat the spreadsheet to have the column headers on the first row like the table below shows

| Country | Code | Name    | IBN  |
|---------|------|---------|------|
| Aust    | UX   | test1   | 34   |
| Aust    | UZ   | test2   | 345  |
| Aust    | UN   | test3   | 5654 |
| US      | UX   | name1   | 567  |
| US      | TG   | name2   | 123  |
| US      | UM   | name3   | 234  |

Second, use the Linq to Excel library to retrieve the data. It takes care of making the oledb connection and creating the sql for you. Below is an example of how easy it is to use the library

var book = new ExcelQueryFactory("pathToExcelFile");
var australia = from x in book.Worksheet()
                where x["Country"] == "Aust"
                select new
                {
                   Country = x["Country"],
                   BookCode = x["Code"],
                   BookName = x["Name"]
                };

Checkout the Linq to Excel intro video for more information about the open source project.

Answer 2

Suggestion 1

Checkout THIS link......as AKofC suggests, creating a class to hold your data would be your first port of call. The link I have posted has a small example of the sort of idea we are proposing.

Suggestion 2 with example...

The obvious thing to do from the code you have posted would be to create a new class to store your book information in.

Then you simply define which fields from your excel document it is that you want to pass into the new instance of your bookinformation class.

New Book Information Class:

class MyBookInfo
{
    public string CountryName { get; set; }
    public string BookCode { get; set; }
    public string BookName { get; set; }
}

Method To Retrieve Info:

public void GetMyBookInfoFromExcelDocument()
        {
            string filename = @"C:\\" + "Book1.xls";
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                          "Data Source=" + filename + ";" +
                                          "Extended Properties=Excel 8.0;";

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
            DataSet myDataSet = new DataSet();
            dataAdapter.Fill(myDataSet, "BookInfo");
            DataTable dataTable = myDataSet.Tables["BookInfo"];


            var rows = from p in dataTable.AsEnumerable()
                       where p[0].ToString() != null || p[0].ToString() != "" && p.Field<string>("F2") != null
                       select new MyBookInfo
                       {
                           CountryName = p.Field<string>("InsertFieldNameHere"),
                           BookCode = p.Field<string>("InsertFieldNameHere"),
                           BookName = p.Field<string>("InsertFieldNameHere")
                       };
        }