Wednesday, June 24, 2009

Read Excel files using C-Sharp windows application

This article explains how to read excel files using c sharp in windows applications.

using System.Data.OleDb;

private void btnRead_Click(object sender, EventArgs e)
string connStr = connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + @"\Courier.xls;Extended Properties=ImportMixedTypes=Text;Excel 8.0;HDR=Yes;IMEX=1;";

//You must use the $ after the object you reference in the spreadsheet
OleDbDataAdapter adap = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connStr);

DataSet ds = new DataSet();
adap.Fill(ds, "Courier");
dataGridView1.DataSource = ds.Tables["Courier"].DefaultView;

catch (Exception ex)

Explanation of the above code:
Here I used OleDb to read the excel file. The excel file resides with exe file as Courier.xls.

Sheet1$ is the name of excel sheet.
Courier is the name of file so i am using the same name in filling the DataSet.

Now at last the data is filled into dataGridView.

Now if you are not able to read the integer value or any value other then text then
include the
Extended Properties=ImportMixedTypes=Text;Excel 8.0;HDR=Yes;IMEX=1 property in connection string.

The above code can also be used in a web application, for this you need to replace the
Application.StartupPath with Server.MapPath

HaPpY CoDiNg...........


Anonymous said...

gud one i appreciate special reading text as well

Anonymous said...

gud one i appreciate special reading text as well