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

Text

#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”

#Code example:

 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 }

#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;

#Solution:

　　1, install the data access component:

　　1) suitable for Office2007

　　2) For office2010

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

As shown:

　　

< 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";

　　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";

Back to top

Back to top

 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 }

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.

Note:

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

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/