Wednesday, 23 November 2016

Upload Excel file using connection Excel2003"].ConnectionString and ["Excel2007"].ConnectionString



  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