Best approach to export data from Excel file to SQL Server table using C# and ASP.NET?

Question

I made a little web app to able users to upload a .xlsx file to the server's database table, but I hit a wall with the OLEDB connection string:

System.InvalidOperationException: 'El proveedor 'Microsoft.ACE.OLEDB.12.0' no está registrado en el equipo local.'

Since the app is going to be uploaded into the company's server installing x32 drivers are a no go and changing the project to run as x32 release also didn't help either. I'm looking for a better approach to make this possible and keeping the .xlsx file as the users input.

protected void btnImportar_Click(object sender, EventArgs e)
{
        //SUBE Y GUARDA ARCHIVO
        string rutaArchivo = Server.MapPath(subeArchivo.FileName);

        string excelPath = Server.MapPath("~/cargas/") + Path.GetFileName(subeArchivo.PostedFile.FileName)/* + "_" + DateTime.Now.ToString("yyyyMMdd")*/;
        lblRutaArchivo.Text = rutaArchivo;

        subeArchivo.SaveAs(excelPath);

        string conStringExcel = string.Empty;
        string extension = Path.GetExtension(subeArchivo.PostedFile.FileName);

        switch (extension)
        {
            case ".xls": //Excel 97-03
                conStringExcel = ConfigurationManager.ConnectionStrings["Excel03conStringExcel"].ConnectionString;
                break;

            case ".xlsx": //Excel 07 or higher
                conStringExcel = ConfigurationManager.ConnectionStrings["Excel07+conStringExcel"].ConnectionString;
                break;
        }

        conStringExcel = string.Format(conStringExcel, excelPath);

        using (OleDbConnection connExcel = new OleDbConnection(conStringExcel))
        {
            connExcel.Open(); // ERROR SHOWS UP HERE
            string hojaExcel = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["CAR_DCT"].ToString();
            DataTable dtExcelData = new DataTable();

            //TODAS LAS COLUMNAS COMO STRING
            dtExcelData.Columns.AddRange(new DataColumn[7] {
               new DataColumn("ID_USU_CAR", typeof(string)),
               new DataColumn("RUT_DCT", typeof(string)),
               new DataColumn("TIP_CON", typeof(string)),
               new DataColumn("ID_CON", typeof(string)),
               new DataColumn("INI_BEN", typeof(string)),
               new DataColumn("FIN_BEN", typeof(string)),
               new DataColumn("MON_CLP", typeof(string))
            });

            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + hojaExcel + "]", connExcel))
            {
                oda.Fill(dtExcelData);
            }

            connExcel.Close();

            using (connSQL)
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connSQL))
                {
                    //Set the database table name
                    sqlBulkCopy.DestinationTableName = "dbo.TH_AD.CAR_DCT_BEN_TMP";

                    //MAPEO COLUMNAS
                    //ID_USU_CARGA RUT_DV  TIP_CON ID_CON  INI_BEN FIN_BEN MON_CLP

                    sqlBulkCopy.ColumnMappings.Add("ID_USU_CAR", "ID_USU_CAR");
                    sqlBulkCopy.ColumnMappings.Add("RUT_DCT", "RUT_DCT");
                    sqlBulkCopy.ColumnMappings.Add("TIP_CON", "TIP_CON");
                    sqlBulkCopy.ColumnMappings.Add("ID_CON", "ID_CON");
                    sqlBulkCopy.ColumnMappings.Add("INI_BEN", "INI_BEN");
                    sqlBulkCopy.ColumnMappings.Add("FIN_BEN", "FIN_BEN");
                    sqlBulkCopy.ColumnMappings.Add("MON_CLP", "MON_CLP");

                    connSQL.Open();
                    sqlBulkCopy.WriteToServer(dtExcelData);
                    connSQL.Close();
                }
            }
        }
}

Answer 1

try to check Spire.XLS, it has function to get DataTable and you can then load to your bulk function.