Wednesday 23 July 2014

Read Excel file in Asp.net


            string path = System.IO.Path.GetFullPath(Server.MapPath("~/InformationNew.xlsx"));
            
            if (Path.GetExtension(path) == ".xls")
            {
                oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
                Data Source=" + path + ";
                Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
            }
            else if (Path.GetExtension(path) == ".xlsx")
            {
                oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;
                Data Source=" + path + ";
                Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
            }
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand(); ;
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            
            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [IMD_1deg_wgn$]"
            oleda = new OleDbDataAdapter(cmd);
            oleda.Fill(ds, "dsIMD_1deg_wgn");
            ddlSlno.DataSource = ds.Tables["dsIMD_1deg_wgn"].DefaultView;