[.NET] Not registered “Microsoft.ace.OleDb.12.0” provider directory on the local computer

Text

Back to top

#Error description:

  In the development of the .net project, passed When microsoft.ACE.oledb reads excel file information, an error is reported:

   “The “microsoft.ACE.oledb.12.0″ provider is not registered on the local computer”

Back to top

#Code example:

Copy code
 1       static void Main(string[] args)

2 {
3 readexcel("D:\\test\\xlsxtest.xlsx");
4}
5 public static void readexcel(string _path)
6 {
7 DataTable dt = new DataTable();
8 string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + _path + ";" + "Extended Properties=\"Excel 12.0;HDR=No\"";
9
10 using (OleDbConnection connection = new OleDbConnection(connectionString))
11 {
12 string SQL = "select * from [sheet1$]";
13 try
14 {
15 OleDbCommand comm = new OleDbCommand(SQL, connection);
16 if (connection.State != ConnectionState.Open)
17 connection.Open();
18 OleDbDataAdapter Adpter = new OleDbDataAdapter(comm);
19 Adpter.Fill(dt);
20}
21 catch (Exception ex)
22 {
23 dt = null;
24}
25 finally
26 {
27 if (connection.State == ConnectionState.Open)
28 connection.Close();
29}
30
31 foreach (DataRow item in dt.Rows)
32 {
33 string sds = item[0].ToString();
34 Console.WriteLine(item[0].ToString() + "//" + item[1].ToString() + "//" + item[2].ToString());
35 if (item[1].ToString() == string.Empty)
36 {
37 break;
38}
39}
40 Console.ReadKey();
41}
42 }
copy code

Back to top

#Reason for error:

There are mainly the following reasons:

  1. The data access component is not installed, and the corresponding version of the data access component (AccessDatabaseEngine) needs to be installed;

  2 If the corresponding version of the Office client is not installed, the corresponding version of the Office client needs to be installed;

   3. No default properties are configured in the IIS application pool, and 32-bit applications need to be enabled in the corresponding IIS application pool ;

  4, the connection string problem. Using Microsoft.Jet.OleDb.4.0, you can read the previous version of excel2007, and there is no need to deploy office on the client. When using Microsoft.Ace.OleDb.12.0, you need to install the engine.

   5. By the way, when using “Microsoft.Jet.OLEDB.4.0”, a similar error will be reported. The reason may be that Microsoft.Jet.OLEDB.4.0 is not supported on 64-bit systems. , You need to modify the architecture from x64 to x86, whether it is WinForm or ASP.NET; or modify the connection string to Microsoft.ACE.OLEDB.12.0 and install the AccessDatabaseEngine x64 data access component;

back to the top

#Solution:

  1, install the data access component:

  1) suitable for Office2007

  Microsoft Access Database Engine 2007 Office system driver: data connection component
  https://www.microsoft.com/zh -cn/download/details.aspx?id=23734 (This link is no longer valid, just use Microsoft Access Database Engine 2010 Redistributable)

  2) For office2010

  Microsoft Access Database Engine 2010 Redistributable
  https://www.microsoft.com /zh-CN/download/details.aspx?id=13255
   This download will install a set of components, non-Microsoft Office applications can use them from 2007 /2010 Office system files to read data, such as from Microsoft Office Access 2007/2010 (mdb and accdb) files and Microsoft Office Excel 2007/2010 (x ls, xlsx and xlsb) files to read data. These components also support connections to Microsoft Windows SharePoint Services and text files.
   In addition, ODBC and OLEDB drivers will be installed for application developers to use when developing applications that connect to the Office file format.

  2. In the IIS application pool, set “”Enable compatibility32< span lang="zh-CN">bit application”, this setting is suitable for web projects;

As shown:

  Share pictures
Note:
In When downloading the Microsoft Access Database Engine 2010 Redistributable, it will let you choose x86 or x64, as shown in the figure:
Share pictures
The premise is to see whether the server is x64 or x86, and there are two x64 servers The version can be installed;

   If the download and installation is x64, then your desktop program must choose anycpu or x64 to publish, and the web project is not compatible, no matter how you publish it ;

   If you download and install x86, then your desktop program must choose x86 to publish, and the web project is published normally;

*Summary: If you It is a web project, you can download x86, choose anycpu for release, and then set the application pool to be 32-bit compatible;

*Finally, use this , There is no need to install office software;

————————– ——— ————————————————– ————————————————– ————————————–

< p>

  3, connection string

   mainly the following two situations:

  1) Use Office 2007 OLEDB driver ( ACE 12.0) Connect to an older 97-2003 Excel workbook.

  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\myFolder\\myOldExcelFile.xls;

  Extended Properties="Excel 8.0;HDR=YES";

     “HDR =Yes;” Indicates that the first row contains column names, not data. “HDR =No;” indicates the opposite;

  2) Read excel in xlsx format

  Provider=Microsoft. ACE.OLEDB.12.0;Data Source=c:\\myFolder\\myExcel2007file.xlsx;

  Extended Properties="Excel 12.0 Xml;HDR=YES";

    ”HDR =Yes;” Indicates that the first row contains column names, not data. “HDR =No;” indicates the opposite;
  Microsoft.ACE.OLEDB connection string reference address: https://www.connectionstrings.com /ace-oledb-12-0/

Back to top

Back to top

copy code
 1      static void Main(string[] args)

2 {
3 readexcel("D:\\test\\xlsxtest.xlsx");
4}
5 public static void readexcel(string _path)
6 {
7 DataTable dt = new DataTable();
8 string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + _path + ";" + "Extended Properties=\"Excel 12.0;HDR=No\"";
9
10 using (OleDbConnection connection = new OleDbConnection(connectionString))
11 {
12 string SQL = "select * from [sheet1$]";
13 try
14 {
15 OleDbCommand comm = new OleDbCommand(SQL, connection);
16 if (connection.State != ConnectionState.Open)
17 connection.Open();
18 OleDbDataAdapter Adpter = new OleDbDataAdapter(comm);
19 Adpter.Fill(dt);
20}
21 catch (Exception ex)
22 {
23 dt = null;
24}
25 finally
26 {
27 if (connection.State == ConnectionState.Open)
28 connection.Close();
29}
30
31 foreach (DataRow item in dt.Rows)
32 {
33 string sds = item[0].ToString();
34 Console.WriteLine(item[0].ToString() + "//" + item[1].ToString() + "//" + item[2].ToString());
35 if (item[1].ToString() == string.Empty)
36 {
37 break;
38}
39}
40 Console.ReadKey();
41}
42 }
copy code

copy code

copy code

back to top

back to top

  Microsoft Access Database Engine 2007 Office system driver: data connection component

  https://www.microsoft.com/zh-cn/download/details.aspx?id=23734 (The link is no longer valid , Just use Microsoft Access Database Engine 2010 Redistributable)

  Microsoft Access Database Engine 2010 Redistributable

  https://www.microsoft.com/zh-CN/download/details.aspx?id=13255

   This download will install a Group components, non-Microsoft Office applications can use them to read data from 2007/2010 Office system files, such as from Microsoft Read data from Office Access 2007/2010 (mdb and accdb) files and Microsoft Office Excel 2007/2010 (xls, xlsx, and xlsb) files. These components also support connections to Microsoft Windows SharePoint Services and text files.
   In addition, ODBC and OLEDB drivers will be installed for application developers to use when developing applications that connect to the Office file format.

  share picture

Note:

On downloading Microsoft Access Database Engine 2010 Redistributable will let you choose to download x86 or x64, as shown in the figure:

share picture

The premise is to see whether the server is x64 or x86. Both versions of the x64 server can be installed;

p>

   If the download and installation is x64, then your desktop program must choose anycpu or x64 to publish, and the web project is incompatible, no matter how you publish it;

  If you download and install x86, then your desktop program should choose x86 to publish, and the web project is published normally;

*Summary: If you are a web project, you will download For x86, just choose anycpu for release, and then set the application pool to be 32-bit compatible;

*Finally, with this, there is no need to install office software Of;

——————————— ————————————————– ————————————————– —————————————-

  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\myFolder\\myOldExcelFile.xls;

  Extended Properties="Excel 8.0;HDR=YES";

  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\ \myFolder\\myOldExcelFile.xls;

  Extended Properties="Excel 8.0;HDR=YES";

    ”HDR =Yes;” indicates that the first row contains column names, not data. “HDR =No;” indicates the opposite;

  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\myFolder\\myExcel2007file.xlsx;

  Extended Properties="Excel 12.0 Xml;HDR=YES";

    ”HDR =Yes;” Indicates that the first row contains column names, not data. “HDR =No;” indicates the opposite;

  Microsoft.ACE.OLEDB connection string reference address: https://www.connectionstrings.com /ace-oledb-12-0/

Leave a Comment

Your email address will not be published.