Importing all sheet of excel 2003 .xls,2007 .xlsx file into dataset and bind it to gridview in asp.net c#
Method ImportExcelXLS given below takes two parameter first full path of .xls or .xlsx file and another bool type hasHeaders that is for including header of sheet or not, if you want to import data client file then you need to upload this file through fileupload and save this posted file to the server then pass this server full file path to this method and display data to girdview.
Upload excel file to server extract data and bind it to gridview code
protected void btn_Import_Click(object sender, EventArgs e)
{
if (fu.HasFile)
{
if (Path.GetExtension(fu.FileName).ToLower() == ".xls" || Path.GetExtension(fu.FileName).ToLower() == ".xlsx")
{
fu.SaveAs(Server.MapPath(@"File/" + fu.FileName));
DataSet ds = ImportExcelXLS(Server.MapPath(@"File/" + fu.FileName), true);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
}
Import from excel file into dataset code -
private static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
{
string HDR = hasHeaders ? "Yes" : "No";
string strConn = "";
if (Path.GetExtension(FileName).ToLower() == ".xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
FileName + ";Extended Properties=\"Excel 8.0;HDR=" +
HDR + ";IMEX=1\"";
}
else
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
FileName + ";Extended Properties=\"Excel 12.0;HDR=" +
HDR + ";IMEX=1\"";
}
DataSet output = new DataSet();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow schemaRow in schemaTable.Rows)
{
string sheet = schemaRow["TABLE_NAME"].ToString();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
cmd.CommandType = CommandType.Text;
DataTable outputTable = new DataTable(sheet);
output.Tables.Add(outputTable);
new OleDbDataAdapter(cmd).Fill(outputTable);
}
}
return output;
}
