Thursday, September 30, 2010

EXTRACT DATA FROM XLS AND XLSX FILE FORMAT

Dear All,

Todays post is about how to load data from excel sheet. This post is emphasized on following file formats:

1) xls (Excel Sheet 1997-2003)
2) xlsx (Excel Sheet 2007)

Both sheets require connection string to extract data from sheet.

Connection String for xls:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0"

Connection String for xlsx:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0"

Below is a small program for your understanding. The program is in ASP.NET.

protected void Page_Load(object sender, EventArgs e)
{
string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
// Create the connection object
OleDbConnection oledbConn = new OleDbConnection(connString);
// Open connection
oledbConn.Open();
// Create OleDbCommand object and select data from worksheet Sheet1
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
// Create new OleDbDataAdapter
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
// Create a DataSet which will hold the data extracted from the worksheet.
DataSet ds = new DataSet();
// Fill the DataSet from the data extracted from the worksheet.
oleda.Fill(ds, "Employees");
// Bind the data to the GridView
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}

I hope this will help you.