Working on an excel file using Microsoft.Jet.OLEDB.4.0 in C# ASP.Net

Hi all,

I use this C# ASP.Net code to import an excel file CLS or XLSX into a sql server DB, update this file and return it to the sender using a redirect to a aspx second page.

I don't have error, the XLS or XLSX file is correctly sent and written to the server folder but it does not download the updated file.

I have this driver https://www.microsoft.com/en-us/download/details.aspx?id=54920 installed on the server 

Can you help me?

webconfig

<add name="Excel03ConString"
     connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
 
<add name="Excel07+ConString"
     connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

Default.aspx

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);
 
 
            using (OleDbConnection excel_con = new OleDbConnection(conString))
            {
                excel_con.Open();
                string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                DataTable dtExcelData = new DataTable();
 
                dtExcelData.Columns.AddRange(new DataColumn[5]
                {
                    new DataColumn("ID", typeof(string)),
                    new DataColumn("ESR", typeof(string)),
                    new DataColumn("CAS", typeof(string)),
                    new DataColumn("CCO", typeof(string)),
                    new DataColumn("TEL", typeof(string)),
                     
                });
 
 
                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();
                }
 
                using (SqlConnection con =
                    new SqlConnection(consString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        sqlBulkCopy.DestinationTableName = "[dbo].[_DOTABLE]";
                        sqlBulkCopy.ColumnMappings.Add("ID", "ID");
                        sqlBulkCopy.ColumnMappings.Add("ESR", "ESR");
                        sqlBulkCopy.ColumnMappings.Add("CAS", "CAS");
                        sqlBulkCopy.ColumnMappings.Add("CCO", "CCO");
                        sqlBulkCopy.ColumnMappings.Add("TEL", "TEL");                         
                        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();
                }
 
                Response.Redirect("Download.aspx?guid=" + Guid.NewGuid().ToString().ToUpper() + "_" +
                    DateTime.Now.ToString("yyyyMMddHHmmss"));
            }
        }
        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.aspx

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        string consString = ConfigurationManager.
            ConnectionStrings["ConnSqlServer"].ConnectionString;
 
        using (SqlConnection con =
            new SqlConnection(consString))
        {
            string strQuery = "SELECT * FROM[dbo].[_DOTABLE];";
            SqlCommand cmd = new SqlCommand(strQuery);
            DataTable dt = GetData(cmd);
            GridView GridView1 = new GridView();
            GridView1.AllowPaging = false;
            GridView1.DataSource = dt;
            GridView1.DataBind();
            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";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                GridView1.Rows[i].Attributes.Add("class", "textmode");
            }
            GridView1.RenderControl(hw);
            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }
    }
}
 
private DataTable GetData(SqlCommand cmd)
{
    DataTable dt = new DataTable();
    String strConnString = ConfigurationManager.
         ConnectionStrings["ConnSqlServer"].ConnectionString;
    SqlConnection con = new SqlConnection(strConnString);
    SqlDataAdapter sda = new SqlDataAdapter();
    cmd.CommandType = CommandType.Text;
    cmd.Connection = con;
    try
    {
        con.Open();
        sda.SelectCommand = cmd;
        sda.Fill(dt);
        return dt;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
        sda.Dispose();
        con.Dispose();
    }
}