'Using OLEDB for uploading file with Excel 2016

my application basically accepts an excel file and uploads data to my database which used to work perfect with Excel 2010 with the below code. However we update the system to Excel 2016 and it stopped working for some reason, could you please assist me in what updates I should make to my code please.

This is the current code to connect:

 openFileDialog1.ShowDialog();
            var fileName = string.Format(openFileDialog1.FileName);

            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(fileName, 1, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, null, false);

var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + "; Extended Properties=Excel 12.0;", fileName);
c++sharpexceloledbexcel-2016office-2016


Solution 1:[1]

I answered this in a related question, it was due to an upgrade to Office 16: oledb connection string for excel 2016 in c#

Solution 2:[2]

I don't have Excel 2016 so I can't test it but this should work.

private DataTable ReadExcelFile(string sheetName, string path)
{
    using (OleDbConnection conn = new OleDbConnection())
    {
        DataTable dt = new DataTable();
        string Import_FileName = path;
        string fileExtension = Path.GetExtension(Import_FileName);
        if (fileExtension == ".xls")
        {
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";
        }
        if (fileExtension == ".xlsx")
        {
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
        }
        using (OleDbCommand comm = new OleDbCommand())
        {
            comm.CommandText = "Select * from [" + sheetName + "$]";
            comm.Connection = conn;
            using (OleDbDataAdapter da = new OleDbDataAdapter())
            {
                da.SelectCommand = comm;
                da.Fill(dt);
                return dt;
            }

        }
    }
}

Also, consider doing it this way.

OleDb.OleDbConnectionStringBuilder Builder = new OleDb.OleDbConnectionStringBuilder();
Builder.DataSource = "test.xlsx";
Builder.Provider = "Microsoft.ACE.OLEDB.12.0";
Builder.Add("Extended Properties", "Excel 12.0;HDR=Yes;IMEX=1");
Console.WriteLine(Builder.ConnectionString);

Finally, check out this site about Excel connection strings.

Solution 3:[3]

This may be because the install broke or changed the existing version of the ACE driver that was registered. Reinstalling ACE may be required to get it working again. Note if the version changed, your connection string may need to be updated

You should be able to see which versions are available on the machine through the registry:

HKCR\Microsoft.ACE.OLEDB.XX.0

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Community
Solution 2
Solution 3

Related Questions