Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET Excel
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();
 

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);
 
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
Posted 31-Jan-13 5:41am
Comments
sjelen at 31-Jan-13 10:54am
   
Which version of MSAD Engine did you install, 32bit and 64bit?
Also is your app compiled as x86, x64 or AnyCPU?
Your app and driver need to be the same version - otherwise app can't load excell driver.
If you compiled as AnyCPU, I guess it will be executed based on OS version (and installed .NET version).
 
Also, on download page it states that this library should not be "called from server-side web application such as ASP.NET"
aspnet_regiis -i at 31-Jan-13 11:50am
   
Hi sjelen
Thanks for your time looking into my problem
I installed 64 bit MSAD engine
My app is compiled as AnyCPU ( I guess I will not be able to change it to 32/64 since it may affect other functionalities)
 
richcb at 31-Jan-13 14:26pm
   
I have had this issue before and it was because the provider I was using was not the correct one for the type of file I was trying to read. It could also be said that the file type is wrong and the provider is fine. Have you saved the file you are using as an Excel file from another format?

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
Comments
aspnet_regiis -i at 4-Feb-13 6:49am
   
Great.. This one worked for me.. Thanks... You saved me...+5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 270
1 Sergey Alexandrovich Kryukov 185
2 BillWoodruff 173
3 CPallini 155
4 Kamal Rocks 144
0 OriginalGriff 5,655
1 DamithSL 4,506
2 Maciej Los 3,997
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 4 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100