Table of content
如何在不删除32位Office的情况下安装AccessDataEngine64位
XLSX -- Excel Workbook (Office 2010..Office 2016)
XLSM -- Excel Macro-Enabled Workbook
XLSB -- Excel Binary Workbook
XLS -- Excel 97-2003 Workbook
如果后缀为:.xls 则这个Excel版本是97-2003版的
如果后缀为:.xlsx 则这个Excel版本是2007之后版的
.xls版本和.xlsx版本所支持最多的行和列
行数 列数
.xls版本 65536 256(IV列)
.xlsx版本 1048576 16384(XFD列)
.xls格式的文件所有OFFICE程序都能打开,但.xlsx只有OFFICE2007或者更新的版本才能打开.
共同点:
Microsoft ACE driver will allow you to query Office files (Including Access database AND Excel files)
Microsoft.Jet.OLEDB.4.0和Microsoft.ACE.OLEDB.12.0 一样,都是连接到Excel对象的接口引擎;
访问ACCESS或者Excel时,会用到数据库访问的组件。
有时候会出现如下图所示的错误,即Microsoft.Jet.OLEDB.4.0,microsoft.ACE.oledb.12.0 provideer is not registered on the local machine.
不同点:
对于不同的Excel版本,有两种接口可供选择:
Microsoft.Jet.OLEDB.4.0(以下简称 Jet 引擎)
Microsoft.ACE.OLEDB.12.0(以下简称 ACE 引擎)。
Jet 引擎,可以访问 Office 97-2003,但不能访问 Office 2007。
ACE 引擎是随 Office 2007 一起发布的数据库连接组件,既可以访问 Office 2007,也可以访问 Office 97-2003。
另外:Microsoft.ACE.OLEDB.12.0 可以访问正在打开的 Excel 文件,而 Microsoft.Jet.OLEDB.4.0 是不可以的。
Microsoft.Jet.OLEDB.4.0由Microsoft Access Database Engine 2007 Redistributable 提供
Microsoft.ACE.OLEDB.12.0由Microsoft Access Database Engine 2010 Redistributable 提供
Microsoft.ACE.OLEDB.16.0由Microsoft Access Database Engine 2016 Redistributable提供
Download Microsoft Access Database Engine 2010 Redistributable
from https://www.microsoft.com/en-us/download/details.aspx?id=13255
Version:SP2
Date Published:12/20/2019
File Name: File Size:
AccessDatabaseEngine.exe 25.3 MB
AccessDatabaseEngine_X64.exe 27.3 MB
Download Microsoft Access Database Engine 2016 Redistributable
from https://www.microsoft.com/en-us/download/details.aspx?id=54920
64位系统下.NET访问Access 数据库,最彻底的方法是安装64位的Access数据访问驱动安装程序 并使用新的Access提供程序 Microsoft.ACE.OLEDB.12.0
1.The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine.
An OLE DB (Object Linking and Embedding Database) provider is a program that can retrieve information from a database and pass it to a requesting application, such as BarTender. Each type of database (such as Oracle or Access) requires its own provider program.
Resolution
These steps should be tried in the following order until the issue is resolved.
step 1:Make sure the Jet Data Manager and Excel have the same bit-rate. If Excel is 32-bit, you will need to install the 32-bit version of the Jet Data Manager. If Excel is 64-bit, you will need to install the 64-bit version of the Jet Data Manager.
step 2:Install Microsoft Access Database Engine 2016 Redistributable These components can be found here: Microsoft Access Database Engine 2016 Redistributable
Ensure that you explicitly run the executable as an administrator
step 3:Install 2007 Office System Driver: Data Connectivity Components. These components can be automatically downloaded here: 2007 Office System Driver: Data Connectivity Components
step 4:remove this download
To remove this download:
To remove the download file itself, delete the file AccessDatabaseEngine.exe
On the Start menu, point to Settings and then click Control Panel.
Double-click Add/Remove Programs.
In the list of currently installed programs, select “Microsoft Access database engine 2016” and then click Remove or Add/Remove. If a dialog box appears, follow the instructions to remove the program.
Click Yes or OK to confirm that you want to remove the program.
问题
Connecting to Microsoft Excel documents requires the Microsoft Access Database Engine. This is a tool provided by Microsoft that allows applications (like BarTender) to connect to Microsoft Excel and Access.
You can install the latest version of the Microsoft Access Database Engine like this:
Download AccessDatabaseEngine.exe from Microsoft.
Run AccessDatabaseEngine.exe.
Click through the installation wizard.
Close and reopen BarTender.
Re-create the connection to the Excel file using the Database Connection Setup wizard.
你是64位的Office,你就安装AccessDatabaseEngine_X64.exe。
Download Accessdatabaseengine from Microsoft.
If your bartender version is 64-bit, please download accessdatabaseengine_x64.exe;
你是32位的Office,你就安装AccessDatabaseEngine.exe。
If your bartender version is 32-bit, please download AccessDatabaseEngine.exe.
2) execution of AccessDatabaseEngine.exe or accessdatabaseengine_x64.exe;
3) Click Install Wizard;
4) Close and re-open bartender;
5) Re-establish the Excel tag database.
只能二选一,装了一个之后另一个就装不上。
我是64位Office,那必然是安装AccessDatabaseEngine_X64.exe
用c#写了个控制台应用实现使用 OleDbConnection 读取Excel(支持.xls与.xlsx文件)
2.Developers number one Connection Strings reference(Excel connection strings)
https://www.connectionstrings.com/excel/
class Program
{
//函数用来读取一个excel文件到DataSet集中
public static DataSet ExcelToDataSet(string filename, string tableName)
{
//获取文件扩展名
string strExtension = System.IO.Path.GetExtension(filename);
OleDbConnection myConn = null;
switch (strExtension)
{
case ".xls":
myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"");
break;
case ".xlsx":
myConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"");
//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
//"HDR=yes;"是说Excel文件的第一行是列名而不是数,"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致,使用"IMEX=1"可必免数据类型冲突。
break;
default:
myConn = null;
break;
}
if (myConn == null)
{
return null;
}
string strCom = " SELECT * FROM ["+ tableName + "$]";
myConn.Open();
//获取Excel指定Sheet表中的信息
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
DataSet ds;
ds = new DataSet();
myCommand.Fill(ds, tableName);
myConn.Close();
return ds;
}
static void Main(string[] args)
{
var tablename = "sheet1";
//括号中为表格地址
DataSet ds = ExcelToDataSet("D:\\get.xls", tablename);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string str = ds.Tables[tablename].Rows[i]["价格"].ToString(); //Rows[i]["col1"]表示i行"col1"字段
Console.WriteLine(str);
}
Console.WriteLine("正确执行...");
Console.ReadKey();
}
}
Microsoft Access 2010 Runtime
The Microsoft Access 2010 Runtime enables you to distribute Access 2010 applications to users who do not have the full version of Access 2010 installed on their computers.
AccessRuntime.exe
AccessRuntime_X64.exe
https://www.microsoft.com/en-gb/download/details.aspx?id=10910
useful links
在不删Office的情况下安装AccessDataEngine64位
由于某些64位应用程序需要访问Access数据库,而访问数据库须使用AccessDataEngine即Access数据库引擎64位。而正常情况下,如果有32位Office安装在系统中,AccessDataEngine64位将会提示无法安装.
https://blog.csdn.net/DiXinWang/article/details/104438803
Make sure that you download the Access Database Engine 2016 Redistributable and not the Microsoft Access 2016 Runtime.
If the computer already has a 32-bit version of Office, then you have to install the 32-bit version of the components. You also have to ensure that you run the SSIS package in 32-bit mode, or run the 32-bit version of the Import and Export Wizard.
If you have a Microsoft 365 subscription, you may see an error message when you run the installer. The error indicates that you can't install the download side by side with Office click-to-run components. To bypass this error message, run the installation in quiet mode by opening a Command Prompt window and running the .EXE file that you downloaded with the /quiet switch. For example:
C:\Users\<user_name>\Downloads\AccessDatabaseEngine.exe /quiet
If you have trouble installing the 2016 redistributable, install the 2010 redistributable instead from here: Microsoft Access Database Engine 2010 Redistributable. (There is no redistributable for Excel 2013.)
最新的Access 2013 安装后,已经不能对dbf文件进行链接读取了,Access 2010以前的版本都可以,如果以前的项目用了Access链接大量的dbf文件的话,
升级Access 2013后,访问链接dbf文件的表,就会出错,具体的解决办法是,安装Access 2010的运行时环境,在改注册表。
我的环境是win7 64bit + Office 2013 plus 64bit。
1,下载Access 2010的支持环境AccessDatabaseEngine_X64.exe
https://www.microsoft.com/en-us/download/details.aspx?id=13255
2,修改注册表,将下面的文件保存为一个merge.reg文件,双击这个文件导入注册表。
https://www.cnblogs.com/xiashengwang/p/5694650.html
问题不在于是Access 2010还是Access 2013而已,不必钻牛角尖,何不求其次,安装一个Access 2007/2010的Database Engine就行