Using ADO 3.5 to Import Data From Excel into 64-bit Application

Question

Hi,

I am currently developing a 64-bit C# application that needs to import its data from an Excel spreadsheet. I have heard that the best way to do this is by using ADO data connections. However, I have read (and experienced) that in order to make this work, I must be writing a 32-bit application as there is no OleDb odbc driver for 64-bit applications.

The problem is that I cannot compile my application as a 32-bit application or other parts of the program (which are not re-writable) will break down.

The fact that I cannot import data from Excel in a 64-bit application seems like a fairly ridiculous issue. How do I solve this problem? Or if not, what work-arounds are available?

Answer 1

Have you looked into using VSTO (Microsoft Visual Studio Tools for Office, which I believe are now included as part of VS 2008, and were available in earlier versions as a free download from Microsoft)? Or using OLE Automation?

Answer 2

Two possible solutions for you:

1. Use the Open XML SDK instead. This is Office 2007 specific, but it deals directly with the files and doesn't have to go through any DB driver. This is my weapon of choice because it's much faster and less flaky than automation and can handle the more "advanced" tasks like formatting.

2. Compile a separate x86 binary and launch the process from your x64 app as needed. The importer app can either provide feedback using some form of IPC or simply convert the file to something like CSV which you can read natively in the x64 app. (I'm not exactly a fan of this type of kludge, but you do what you've gotta do...)

I agree that it's kind of ridiculous that we still don't have an x64 JET or ACE driver, but that seems to be the way it is for now. Even MSDASQL won't work for you; there's a 64-bit library but I've read that the Excel component still only works in 32-bit mode.

Answer 3

SpreadsheetGear for .NET is an Excel compatible .NET component which is safe managed code written in C# and compiled for "Any CPU", so it works with 32 bit and 64 bit .NET. This is one of the big advantages of .NET - one build of SpreadsheetGear.dll works equally well with with the 32 bit .NET CLR and the 64 bit .NET CLR.

You can see online samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC