In this article I will explain you how to read and display data from excel file. We will be using microsoft OLEDB provider to connect the excel file, extract data and then display the data in Grid View.
This article allows you to upload excell sheet and display in grid view. Lets start with coding!
Below code allows you to create a connection and displays all the sheets abailable in the excel file.
string strString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\TestCode\NewExcel.xls;Extended Properties=Excel 8.0";
// Create the connection object
OleDbConnection oledbConn = new OleDbConnection(strString);
oledbConn.Open();
DataTable dtTables = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dtTables != null && dtTables.Rows.Count > 0)
{
for(int i = 0; i < dtTables.Rows.Count; i++)
{
Response.Write(dtTables.Rows[i]["TABLE_NAME"].ToString());
Response.Write("<br/>");
}
}
oledbConn.Close();
Once you have the information about the sheets in excel then you have able to read the sheets according to your requirement.
Below code allows you to open a connection to excel and read then excel sheet you want to read and display in the interface.
try
{
// Open connection
oledbConn.Open();
// Create OleDbCommand object and select a sheet as per the requirement. Here I am using a fixed name of the sheet name.
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, "Friends");
// Bind the data to the GridView
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}
catch
{
}
finally
{
// Close connection
oledbConn.Close();
}
