Update data in Excel file using Oledb in ASP.Net

Hi Vincenzo67,

I have modified your code. Please refer below code and modify as per your database table.

Database

I have made use of the following table Customers with the schema as follows.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<asp:FileUpload ID="FileUploadControl" runat="server" />
<asp:ImageButton ID="btnUpload" runat="server" AlternateText="Upload" OnClick="UploadButton_Click" />

Namespaces

Default

using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

Download

using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

Code

Default

protected void UploadButton_Click(object sender, ImageClickEventArgs e)
{
    if (FileUploadControl.HasFile && FileUploadControl.PostedFile.ContentLength > 0)
    {
        try
        {
            string excelPath = Server.MapPath("~/aspnet/public/") + Path.GetFileName(FileUploadControl.PostedFile.FileName);
            FileUploadControl.SaveAs(excelPath);

            string conString = string.Empty;
            string extension = Path.GetExtension(FileUploadControl.PostedFile.FileName);
            switch (extension)
            {
                case ".xls": //Excel 97-03
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 or higher
                    conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                    break;
            }

            conString = string.Format(conString, excelPath);

            DataTable dtExcelData = new DataTable();
            dtExcelData.Columns.AddRange(new DataColumn[]
                {
                    new DataColumn("CustomerId", typeof(int)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("Country", typeof(string))                      
                });

            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {
                excel_con.Open();
                string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "];", excel_con))
                {
                    oda.Fill(dtExcelData);
                }
                excel_con.Close();
            }

            string consString = ConfigurationManager.
                ConnectionStrings["ConnSqlServerH20AWIDEMF00"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(consString))
            {
                using (SqlCommand command = new SqlCommand("_TRUNCATE_DOTABLE", conn)
                    {
                        CommandType = CommandType.StoredProcedure
                    })
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                    conn.Close();
                }
            }

            using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    sqlBulkCopy.DestinationTableName = "[dbo].[Customers]";
                    sqlBulkCopy.ColumnMappings.Add("CustomerId", "CustomerId");
                    sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                    sqlBulkCopy.ColumnMappings.Add("Country", "Country");
                    con.Open();
                    sqlBulkCopy.WriteToServer(dtExcelData);
                    con.Close();
                }
            }

            using (SqlConnection conn = new SqlConnection(consString))
            {
                using (SqlCommand command = new SqlCommand("_UPDATE_DOTABLE", conn)
                    {
                        CommandType = CommandType.StoredProcedure
                    })
                {
                    conn.Open();
                    command.ExecuteNonQuery();
                    conn.Close();
                }
            }

            Response.Redirect("Download.aspx?guid=" + Guid.NewGuid().ToString().ToUpper() + "_" +
                DateTime.Now.ToString("yyyyMMddHHmmss"), false);
        }
        catch (Exception ex)
        {
            Page.ClientScript.RegisterStartupScript(this.GetType(), "Msg", "alert('Error " + ex.Message + "');", true);
        }
    }
    else
    {
        Page.ClientScript.RegisterStartupScript(this.GetType(), "Msg", "alert('Excel please.');", true);
    }
}

Download

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        string consString = ConfigurationManager.
            ConnectionStrings["ConnSqlServer"].ConnectionString;
        GridView GridView1 = new GridView();
        using (SqlConnection con = new SqlConnection(consString))
        {
            string strQuery = "SELECT * FROM [dbo].[Customers];";
            using (SqlCommand cmd = new SqlCommand(strQuery))
            {
                DataTable dt = GetData(cmd);
                GridView1.AllowPaging = false;
                GridView1.DataSource = dt;
                GridView1.DataBind();
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    GridView1.Rows[i].Attributes.Add("class", "textmode");
                }
            }
        }
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.RenderControl(hw);
        string style = @"<style> .textmode { mso-number-format:\@; } </style>";
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition",
            "attachment;filename=Export_ " + Guid.NewGuid().ToString().ToUpper() + "_"
                                                        + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

private DataTable GetData(SqlCommand cmd)
{
    string strConnString = ConfigurationManager.ConnectionStrings["ConnSqlServer"].ConnectionString;
    using (SqlConnection con = new SqlConnection(strConnString))
    {
        cmd.Connection = con;
        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
        {
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                return dt;
            }
        }
    }
}

Screenshot

I rewrote the code and now it works, thanks for the never banal support.

private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
    string conStr = "";
    switch (Extension)
    {
        case ".xls": //Excel 97-03
            conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
            break;
 
        case ".xlsx": //Excel 07
            conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
            break;
 
    }
 
    conStr = String.Format(conStr, FilePath, isHDR);
    OleDbConnection connExcel = new OleDbConnection(conStr);
    OleDbCommand cmdExcel = new OleDbCommand();
    OleDbDataAdapter oda = new OleDbDataAdapter();
    DataTable dt = new DataTable();
    cmdExcel.Connection = connExcel;
 
    //Get the name of First Sheet
    connExcel.Open();
    DataTable dtExcelSchema;
    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
    connExcel.Close();
 
    //Read Data from First Sheet
    connExcel.Open();
    cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
    oda.SelectCommand = cmdExcel;
    oda.Fill(dt);
    connExcel.Close();
 
    string consString = ConfigurationManager.
        ConnectionStrings["ConnSqlServer"].ConnectionString;
 
    using (SqlConnection conn = new SqlConnection(consString))
    {
        using (SqlCommand command = new SqlCommand("_TRUNCATE_DOTABLE", conn)
        {
            CommandType = CommandType.StoredProcedure
        })
        {
            conn.Open();
            command.ExecuteNonQuery();
            conn.Close();
        }
    }
 
    try
    {
        if (dt.Rows.Count > 0)
        {
            foreach (DataRow dr in dt.Rows)
            {
                if (dr != null)
                {
                    using (SqlConnection connection =
                        new SqlConnection(ConfigurationManager.ConnectionStrings["ConnSqlServer"].ConnectionString))
                    {
                        connection.Open();
                        string sql = "INSERT INTO [dbo].[_DOTABLE] (TEL) VALUES(@param1);";
 
                        using (SqlCommand cmd = new SqlCommand(sql, connection))
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.Parameters.AddWithValue("@param1", dr[0].ToString().ToString().Trim());
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        Page.ClientScript.RegisterStartupScript(this.GetType(), "Msg", "alert('Error " + ex.Message + "');", true);
    }
 
    using (SqlConnection conn = new SqlConnection(consString))
    {
        using (SqlCommand command = new SqlCommand("_UPDATE_DOTABLE", conn)
        {
            CommandType = CommandType.StoredProcedure
        })
        {
            conn.Open();
            command.ExecuteNonQuery();
            conn.Close();
        }
    }
 
    //Bind Data to GridView
    GridView1.Caption = Path.GetFileName(FilePath);
    GridView1.DataSource = dt;
    GridView1.DataBind();
 
    Response.Redirect("Download.aspx?guid=" + Guid.NewGuid().ToString().ToUpper() + "_" +
        DateTime.Now.ToString("yyyyMMddHHmmss"), false);
}
 
protected void UploadButton_Click(object sender, EventArgs e)
{
    if (FileUploadControl.HasFile)
    {
        string FileName = Path.GetFileName(FileUploadControl.PostedFile.FileName);
        string Extension = Path.GetExtension(FileUploadControl.PostedFile.FileName);
        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
        string FilePath = Server.MapPath(FolderPath + FileName);
        FileUploadControl.SaveAs(FilePath);
        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
        FileUploadControl.Dispose();
    }
}