Showing posts with label Excel into DataTable. Show all posts
Showing posts with label Excel into DataTable. Show all posts

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; }
 }