public void ExportToGrid(String path)
{
try
{
System.Data.DataTable dt = new System.Data.DataTable();
OleDbConnection MyConnection = null;
DataSet DtSet = null;
OleDbDataAdapter MyCommand = null; //Connection for MS Excel 2003 .xls format
string sheetname = "Sheet1";
// MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;");
//Connection for .xslx 2007 format //
if (path.Substring(path.LastIndexOf('.')).ToLower() == ".xlsx")
{
MyConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties=Excel 12.0;"); //Select your Excel file
}
else
{
MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties= 'Excel 8.0;HDR=YES;IMEX=1;';");
}
MyConnection.Open();
System.Data.DataTable schemaTable = MyConnection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (schemaTable.Rows.Count > 0)
{
sheetname = schemaTable.Rows[0]["TABLE_NAME"].ToString();
}
MyConnection.Close();
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", MyConnection);
DtSet = new System.Data.DataSet(); //Bind all excel data in to data set
MyCommand.Fill(DtSet, "[" + sheetname + "]");
dt = DtSet.Tables[0];
MyConnection.Close(); //Check datatable have records
if (dt.Rows.Count > 0)
{
gvUploadKitty.DataSource = dt;
gvUploadKitty.DataBind();
ViewState["kittyTable"] = dt;
} //Delete temporary Excel file from the Server path
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
}
catch (Exception ex)
{
string js = "alert('The uploaded file is not in correct format !!');"; //"alert('" + ex + "');";
ScriptManager.RegisterStartupScript(this, this.GetType(), "script2", js, true);
}
}