Tuesday, June 14, 2011

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/

No comments:

Post a Comment