LINQ to Excel using Dataset or Datatable

using  System;

using  System.Collections;

using  System.Collections.Generic;

using  System.Data;

using  System.Diagnostics;

using  TWebservice;

using  System.Xml;

using  System.Linq;

using  System.Xml.Linq;

using  System.Web;

using  System.Web.UI;

using  System.Web.UI.WebControls;

using  System.Data.SqlClient;

using  System.Web.Configuration;

using  System.Data.OleDb;

public  partial  class  Thanks  : System.Web.UI.Page 

{

//Resturns the excel sheet as Dataset or DataTable 

private  DataTable  GetExcel()

{

DataTable  dataTable;

if  (Cache["ExcelDatas" ] == null )

{

string  filename = Server.MapPath("~/Common_Inc/Country.xls" );

string  connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"  + "Data Source="  + filename + ";"  + "Extended Properties=Excel 8.0;" ;

OleDbDataAdapter  dataAdapter = new  OleDbDataAdapter ("SELECT * FROM [citystatecountry]" , connectionString);// [excel sheet name] 

DataSet  myDataSet = new  DataSet ();

dataAdapter.Fill(myDataSet, "ExcelInfo" );

dataTable = myDataSet.Tables["ExcelInfo" ];

Cache["ExcelDatas" ] = dataTable;

}

else 

{

dataTable = (DataTable )Cache["ExcelDatas" ];

}

return  dataTable;

}

protected  void  Page_Load(object  sender, EventArgs  e)

{

if  (!IsPostBack)

{

//Now we can use LINQ to Dataset to query the data in Excel. 

DataTable  dataTable = GetExcel();            

var  ExcelDatas = (from  r in  dataTable.AsEnumerable().GroupBy(g => g.Field<string >("Country" )).Select(f => f.First()) select  new  { Country = r.Field<string >("Country" ) }).Distinct();

ddlHomecountry.DataSource = ExcelDatas;

ddlHomecountry.DataTextField = "Country" ;

ddlHomecountry.DataValueField = "Country" ;

ddlHomecountry.DataBind();        

}

}

}

Update: This tutorial uses Microsoft.Jet.OLEDB.4.0 provider for returning a Dataset from Excel. There is now a better tool to do it from codeplex. it has even support for 2007 office format. This tool is available at http://exceldatareader.codeplex.com/