Sunday, August 18, 2013

Read and Write data from Excel sheet - Asp.net

Write

 OleDbConnection connection = new OleDbConnection();

int i;
        string name, email;
        string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/Excel/" + filename);
        string connectionString1 = @";Extended Properties= ""Excel 8.0;HDR=YES;""";
        string connectionString3 = connectionString + connectionString1;
        connection.ConnectionString = connectionString3;
        string str = "select * from [Sheet1$]";
        OleDbDataAdapter adp = new OleDbDataAdapter(str, connection);
        DataTable dt = new DataTable();
        adp.Fill(dt);
        for (i = 0; i < dt.Rows.Count; i++)
        {
            obj.insert("tb_customer", "cust_name,cust_mailid", "'" + dt.Rows[i]["Customer Name"].ToString() + "','" + dt.Rows[i]["Email"].ToString() + "'");
        }
         
        GridView1.DataSource = dt;
        GridView1.DataBind();



Read

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("Excel\\"+filename);
                       string connectionString1 = @";Extended Properties= ""Excel 8.0;HDR=YES;""";
                       string connectionString3 = connectionString +  connectionString1;
                       connection.ConnectionString = connectionString3;

                       using (DbCommand command = connection.CreateCommand())
                       {

                           string sel = "SELECT *  FROM [Sheet1$]";
                           connection.Open();
                           DataTable dt = new DataTable();
                           OleDbDataAdapter ada = new OleDbDataAdapter(sel, connectionString3);
                           ada.Fill(dt);

}












No comments:

Post a Comment