Read Excel using LINQ

Viewed 35398

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

3 Answers
Related