Friday, December 20, 2013

Getting Data From Excel into DataTable

Excel to DataTable in C#:
Here is the way to Convert EXCEL Table Data in to Our C# Data Table in button Click:

 protected btnsubmit_Click(object sender, EventArgs e)
 {
 

 try
 {
 OleDbConnection oledbConn;
 string fpath = Server.MapPath("~/Excelfiles/");
   if (filexcel.HasFile)
     {
         if (Path.GetExtension(filexcel.PostedFile.FileName) == ".xlsx")
         {

         //Through File Upload
         //filexcel.PostedFile.SaveAs(string.Concat(fpath, filexcel.PostedFile.FileName));
         //string ftpath = System.IO.Path.GetFullPath(Server.MapPath("~/Excelfiles/New Microsoft Office Excel Worksheet (2)"));
       
         //From web.COnfig
          string ftpath = ConfigurationManager.AppSettings["ftpath"].ToString(); //give your excel path here directly

         //Or Directly You can assign the Path
         var ft = @"D:\manu\New Microsoft Office Excel Worksheet (2).xlsx";
         var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ft + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";

       
             using (var conn = new OleDbConnection(connectionString))
             {
              conn.Open();
              var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); 

using (var cmd = conn.CreateCommand())
              {
                 cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";
                 var adapter = new OleDbDataAdapter(cmd);
                 var ds = new DataSet();
                 adapter.Fill(ds);
              }

             }
         }
     }
 }
 catch (Exception ex) { throw ex; }
 }

No comments: