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.