Wednesday, August 14, 2013

How to read excel file data into datatable in c# to store in db

 protected void Page_Load(object sender, EventArgs e)
    {
       DataTable dt=  ReadExcelToTable("E:\\31october\\Excel\\Files\\empdata.xlsx");
    }
 
 
 
 
private DataTable ReadExcelToTable(string path)    
 {

     //Connection String

     string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";  
     //the same name 
     //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + //";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; 

     using(OleDbConnection conn = new OleDbConnection(connstring))
     {
        conn.Open();
        //Get All Sheets Name
        DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"Table"});  

        //Get the First Sheet Name
        string firstSheetName = sheetsName.Rows[0][2].ToString(); 

        //Query String 
        string sql = string.Format("SELECT * FROM [{0}]",firstSheetName); 
        OleDbDataAdapter ada =new OleDbDataAdapter(sql,connstring);
        DataSet set = new DataSet();
        ada.Fill(set);
        return set.Tables[0];   
   }
 }

1 comment:

  1. Thanks for sharing this code. I have found another code to export excel data to datatable using C#/.NET with Aspose.Cells for .NET library. It also allows you to export/import data from/to different sources into excel file.

    ReplyDelete