Programmatically Accessing Excel Data via .NET on 64-bit servers

So it turns out that using ADO.NET to access MS Excel worksheet data in an ASP.NET application running on the 64-bit version of Windows Server 2003 does not quite work as expected. This also goes for any 64-bit .NET code. In short, the Microsoft.Jet.OLEDB.4.0 provider is a 32-bit driver that does not work when directly used by 64-bit code. The following post from a Microsoft employee describes the issue:

Connect to Excel. Using x64 (64-bit) platform. Compiled as x64

"Since there are no 64-bit OleDb drivers for anything other than SQL Server, therefore, we cannot write 64-bit apps which interface with databases directly.

What you need to do is split your application into a 32 bit part and a 64 bit part, use COM interop to cross the 64/32 bit boundary. For instance, drop the code (just a simple class library compiled as 32 bit) that retrieves the Excel data into a COM+ (System.EnterpriseServices) as a "server type" application, and call those server methods from your 64 bit Windows service. This is exactly why System.EnterpriseServices are made for."

To restate the above in a list of to do items:

• Refactor the Excel access code into a class by itself in a class library by itself
• Make sure that this new class inherits from System.EnterpriseServices.ServicedComponent so that it can be hosted within COM+ (Component Services)
• Make sure the new ServicedComponent's class library is compiled in 32-bit mode
• Alter the calling code (ASP.NET web page, control, web part, etc.) to call the new ServicedComponent for the Excel data retrieval routines
• Install the new ServicedComponent into Component Services