Skip to main content

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

Comments

Popular posts from this blog

MS KB928365, ASP.NET Request.Headers.Add() Hack No Longer Works

So a project that I am currently a part of is using an ASP.NET 2.0 HttpModule to add some additional values to the incoming HTTP request's headers in the DEV environment (i.e., our local disconnected laptops) to simulate what an enterprise single-sign-on solution is performing in the production environment. It has worked like a charm. That is until I installed the new security update for the .NET Framework 2.0 release this past Wednesday, July 10, MS KB928365.

Apparently this "hack"has been disabled with the release of this security update.

When attempting to call Headers.Add(), with or without the above hack in place, you will now receive a PlatformNotSupported exception.

All in all, this post is by no means a rant against the security update, but simply an attempt to add a quick answer to the "Google answer machine" for those searching. I am also already aware of a number of other potentially better solutions than the one currently in place for simulating th…

Temporal Database Design, Soft Deletes, and Ayende's Razor

This is a formal follow-up to a post on Ayende's blog on "Avoiding Soft Delete's" in your database where I question the lack of temporal database solutions being applied to these types of problems.

After Oren claimed the following, I felt it necessary to expand on it in a blog post of my own, rather than continuing to clutter his comments, and hopefully finally bring some traffic to my own blog :-)
Ayende’s RazorThis is a response to a comment on another post:Oren, in all seriousness, I thought that problems that were "(a) complex, (b) hard to understand (c) hard to optimize" were the kinds that folks like you and I get paid to solve...Given two solutions the match the requirements of the problem, the simpler one is the better.I could just as well call that statement "Jim's Razor", as I believe in it as much as you do Oren, so no arguments there.

But in the same vane, "wise" (i.e., experienced) software architects/developers strategical…

It was bound to eventually happen...

...that I would start a blog.

Anyway, I've spent better than the past 5 years now reading other people's blogs, and steering clear of starting my own. I still don't think I have the time to offer a lot of content, but hopefully what I do post will be more useful than this obligatory intro. The only real reason I decided to start this was to give myself somewhere to post write-ups on random topics that I couldn't find an answer to via Google. Hopefully posting those write-ups here will save someone else some time some day.

In the mean time, you can keep yourself busy reading my Google Reader Shared Posts.