I have been trying to access 2016 ms Excel file using c#, but connection string is working only till 2013 ms excel.
my current connection string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";
is there any modified oledb connection string for ms excel 2016?
any help would be appreciable, thanks in advance.
解决方案
This occurred for me after upgrading from a local install of Office 13 to Office 16 through the Office 365 program. I was getting this exception: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
I was not able to find a way to install the driver through the office 365 install process.
I had to install https://www.microsoft.com/en-us/download/details.aspx?id=13255 - the x64 version did not solve the issue, had to use the 32bit version.
My connection string in App.config
Code using it:
var excelConnectionString = ConfigurationSettings.GetExcelConnection(fileLocation);
var dataTable = new DataTable();
using (var excelConnection = new OleDbConnection(excelConnectionString))
{
excelConnection.Open();
var dataAdapter = new OleDbDataAdapter("SELECT * FROM [Users$]", excelConnection);
dataAdapter.Fill(dataTable);
excelConnection.Close();
}
Console.WriteLine("OpenExcelFile: File successfully opened:" + fileLocation);
return dataTable;