update excel using sql table data

Question

User-651692633 posted

Is it possible to update existing excel file located in share drive using sql data in vb.net

Answer 1

User1316246260 posted

Here is an example of a Response type update:

protected void Button1_Click(object sender, EventArgs e)
        {
            String connStr = ConfigurationManager.ConnectionStrings["MDFdb"].ConnectionString;
            String cmdStr = "SELECT * FROM TableMDF;";
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    using (SqlCommand cmd = new SqlCommand(cmdStr, conn))
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                        DataSet ds = new DataSet();
                        DataTable dt = new DataTable();
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(ds);
                            GridView1.DataSource = ds;
                            GridView1.DataBind();
                            conn.Close();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Label2.Text = ex.Message;
            }
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "MDFtoExcel.xls"));
            Response.ContentType = "application/vnd.ms-excel";
            System.IO.StringWriter sw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw);
            GridView1.AllowPaging = false;
            GridView1.RenderControl(hw);
            Response.Write(sw.ToString());
            Response.End();
        }

Answer 2

User1724605321 posted

Hi anupamabr ,

Is it possible to update existing excel file located in share drive using sql data in vb.net

From my point of view ,the question may be divided into two parts: firstly ,you need to reading excel file which is on shared drive , when you could refer to link below which discuss and provide several ways to access file in shared file:

http://forums.asp.net/t/1788400.aspx?Reading+excel+file+which+is+on+shared+drive .

In addition ,If several users have the same file open, only the first person is using the active document, the rest are opening a read only version .

Secondly ,you need to open the excel and update it with vn.net ,please refer to links below for more details and demo:

http://vb.net-informations.com/excel-2007/vb.net_excel_update_data_oledb.htm

http://stackoverflow.com/questions/15307021/how-to-append-data-to-existing-excel-file-using-vb-net

Best Regards,

Nan Yu