使用OleDbConnection类读取excel文件时出错 [英] Error reading excel file using OleDbConnection class

问题描述

我正在使用此代码在asp.net Web应用程序中阅读excel表（xlsx文件）

I am reading excel sheets (xlsx files) in a asp.net web application with this code

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFileName + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
            // if you don't want to show the header row (first row) use 'HDR=NO' in the string
            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open();
            // Get the data table containg the schema guid.
            DataTable dtWorksheetTables = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dtWorksheetTables == null || dtWorksheetTables.Rows.Count == 0) return null;
            string worksheetName = GetWorksheetName(dtWorksheetTables);
            string strExcelSQL = "SELECT * FROM [" + worksheetName + "]";
            OleDbCommand oleDbCommand = new OleDbCommand(strExcelSQL, excelConnection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(oleDbCommand);
            DataTable excelDataTable = new DataTable();
            dataAdapter.Fill(excelDataTable);
            //Dispose 
            dataAdapter.Dispose();
            oleDbCommand.Dispose();
            excelConnection.Close();
            excelConnection.Dispose();
            GC.Collect();

此代码在我的本地机器上正常工作（ 32位Windows 7）和64位UAT服务器。我在这两个系统上安装了MS Office 2010.



问题出在客户端服务器上，一个64位的Windows Server 2008.我的代码给出了一个错误 -

This code works fine on my local machine ( 32 bit Windows 7) and on 64 bit UAT server. I have MS office 2010 installed at both these systems.

The problem is on the client server , a 64 bit Windows Server 2008. My code gives an error as-

System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format.
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at ProviderService.ReadDataFromExcel(String excelFileName) in D:\\Admin\MyProject\KDService\ProviderService.cs:line 1374

此行代码为 -

The code on this line is -

OleDbConnection excelConnection = new OleDbConnection(connectionString);

我在这台服务器上检查过没有安装MS office。所以我从这里安装了MS Access数据库引擎2010 Redistributable http://www.microsoft。 com / zh-CN / download / details.aspx？id = 13255



但我仍然得到上面提到的相同错误。



在客户端服务器上安装MS office是我的最后一个选择。我正在寻找一个解决方案，我可以使用可再发行组件解决这个问题。



请指导我解决这个问题。在此先感谢

I checked on this server that MS office is not installed. So I installed the MS Access Database Engine 2010 Redistributable from here http://www.microsoft.com/en-us/download/details.aspx?id=13255

But still I get the same error mentioned above.

Installing MS office on the client server is my last option. I am looking for a solution where I can fix this using redistributable assemblies.

Please guide me to solve this. Thanks in advance

推荐答案

尝试在服务器上安装MSXML6.0软件包。

以下链接说明了您遇到的类似问题。



http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/57cb3121-755d-4cd5-8cda-a0630cbf535b [ ^ ]



一切顺利

-Kiran

Try installing MSXML6.0 package on the server.
The following link explains the similar issue that you are facing.

http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/57cb3121-755d-4cd5-8cda-a0630cbf535b[^]

All the best
-Kiran

这篇关于使用OleDbConnection类读取excel文件时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！