This is in web.config
<connectionStrings>
<add name="excelo3cnstring" connectionString="Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}';" providerName="OleDb;"/>
<add name="excelo7cnstring" connectionString="Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}';" providerName="OleDb;"/>
</connectionStrings>
<add name="excelo3cnstring" connectionString="Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}';" providerName="OleDb;"/>
<add name="excelo7cnstring" connectionString="Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}';" providerName="OleDb;"/>
</connectionStrings>
Microsoft.Jet.OLEDB.4.0 is for MSOffice 2003 and
Microsoft.ACE.OLEDB.12.0 is for MSOffice 2007
.aspx.cs
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
FileUpload1.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
}
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["excelo3cnstring"]
.ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["excelo7cnstring"]
.ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection conExcel= new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter odadp = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
conExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
conExcel.Close();
//Read Data from First Sheet
conExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
odadp.SelectCommand = cmdExcel;
odadp.Fill(dt);
conExcel.Close();
//Bind Data to GridView
GridView1.Caption = Path.GetFileName(FilePath);
{
if (FileUpload1.HasFile)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
FileUpload1.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
}
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
string conStr = "";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["excelo3cnstring"]
.ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["excelo7cnstring"]
.ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection conExcel= new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter odadp = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
conExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
conExcel.Close();
//Read Data from First Sheet
conExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
odadp.SelectCommand = cmdExcel;
odadp.Fill(dt);
conExcel.Close();
//Bind Data to GridView
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
Thanks a lot for such a useful info.. :)
ReplyDelete