private DataSet UploadFile(HttpPostedFileBase flbupload)
{
string strcon = string.Empty;
DataSet ds = new DataSet();
string exten = Path.GetExtension(flbupload.FileName);
if (exten.ToLower() == ".xls") //Excel 97-03
{
strcon = ConfigurationManager.ConnectionStrings["Excel2003"].ConnectionString;
}
else if (exten.ToLower() == ".xlsx") //Excel 07
{
strcon = ConfigurationManager.ConnectionStrings["Excel2007"].ConnectionString;
}
string uploaddir = ConfigurationManager.AppSettings["UploadExelFile"];
string physicalDirPath = Server.MapPath("~/content/" + uploaddir + "/");
if (!Directory.Exists(physicalDirPath))
{
Directory.CreateDirectory(physicalDirPath);
}
string filename = physicalDirPath + Path.GetFileNameWithoutExtension(flbupload.FileName) + Guid.NewGuid() + Path.GetExtension(flbupload.FileName);
flbupload.SaveAs(filename);
using (OleDbConnection con = new OleDbConnection())
{
con.ConnectionString = string.Format(strcon, filename, true);
if (con.State == ConnectionState.Closed)
con.Open();
//Get the name of First Sheet
System.Data.DataTable dtExcelSchema;
dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
OleDbCommand cmd = new OleDbCommand("SELECT * From [" + SheetName + "]", con);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
}
return ds;
}
//Connection String
<add name="Excel2003" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" />
<add name="Excel2007" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" />
No comments:
Post a Comment