Query Excel spreadsheet with C# .NET without using Jet OLE DB

Question

Is there any way to query an excel 2010 spreadsheet, without using Jet or exporting as a CSV and querying the csv file.

The reason why I cannot use Jet is because the application needs to run as a web service, on Windows Server 2008 R2, with no 32 bit support - hence Jet will not work. This is a given, as WOW 64 is not installed on the target server.

This thread suggests using a 32 bit proxy machine, but this is also not an option.

Further info: The server is running Sharepoint 2010.

Thanks,

JD

Answer 1

Yes - you can use Excel.Interop - reference it from your C# program by adding a reference to the Microsoft.Office.Interop.Excel (version 13 I think for Excel 2010) in the .Net tab of VS add reference dialog.

FYI: Its not a good idea to run daemons on a server using Interop: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2, however if it is a short running program, run by a user and you monitor it, it should be ok..

If you want your clients to run the program they will need to install the Primary Interop Assemblies (PIA's) if they didnt already when installing office, they can be got here:

XP: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=C41BD61E-3060-4F71-A6B4-01FEBA508E52
2003: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=3C9A983A-AC14-4125-8BA0-D36D67E0F4AD
2007: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=59DAEBAA-BED4-4282-A28C-B864D8BFA513
2010: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=938fe8ad-583b-4bd7-a345-23250dc15855

I have been using Office11 on my x64 Vista machine so i dont think x64 will pose any problems.

Be warned the documentation is terrible! there are about 10 different versions of the help, documentation out there. I reccomend:

Become familiar with the:

• Application
• Workbook
• Worksheet
• Range

objects. You can read data into a List<List<string>> (in which case all your cells would have to formatted as text) or something in memory then you dont have to deal with Interop anymore and its v. fast thereon. From C# always use a method (some documentation erroneously tells you you cannot use there methods they are for internal use) such as: Range.get_Values("A1") as opposed to: Range.Cells;

A good place to start is here: http://dotnetperls.com/excel-interop

Official documentation is here: http://msdn.microsoft.com/en-us/library/bb726434(v=office.12).aspx, but it contains a load of marketing waffle until you get to the Interop library parts of which are a decade old.

And beware: Excel index's are 1 based, i.e. the first element in the returned 2D array starts at my2DArray[1,1]!

Answer 2

As well as Excel.Interop, can you use ADO.Net to use the ACE engine rather than the older JET engine? ACE was introduced with Excel/Access 2007, and is intended to replace JET.

Answer 3

I faced this too. My situation: non-trivial client app that needs to run on Windows 7 x64 as well as x86. Need to extract data from Excel spreadsheets; no flexibility and minimal "user pain" required. ACEDB (Access Database Engine redist.) didn't do it, since apparently the x64 version of that can't be installed when x86 (32-bit) Office is already installed. PIA wasn't an option - "minimal user pain".

I used this library: http://npoi.codeplex.com

It comes with extensive samples and I was able to build the app to "Any CPU" rather than constrain to x86, which caused problems with some other third-party dlls we were trying to use. I was up and running with NPOI within about 10 minutes, so I can definitely recommend it.