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

Xanthic

Can't you just import the excel spreadsheet into a "real" database? Since you're writing a web service, you'll probably have some trouble with concurrency, if you keep your datasource as a excel file... Then you could schedule a job to update the sql server table from time to time. – Brawn

No. It is a business requirement that the information be stored in the excel file. Not my choice... – Xanthic

I didnt know it was possible to exclude WOW64 from being installed - in my experience any 32 bit program runs on a 64 bit machine - presumably WOW64 is there but you dont even notice it. – Ss

Mrk Mnl: It is no longer installed by default in Server 2008 R2 server core. msdn.microsoft.com/en-us/library/dd371790(VS.85).aspx – Xanthic

I see (have to make up the min chars) – Ss

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]!

Ss

That was what I was about to suggest. 64 bit might pose a wrinkle or two but it should work. – Caprice

Brilliant! Only thing is that this requires a copy of Office 2010 installed on the server, which isn't ideal - but I am sure it will work. Thanks. – Xanthic

I think, whatever you decide, you are going to require at least some Office components. – Caprice

@JD: you can just install the PIA's rather than office, get them here: microsoft.com/downloads/en/… – Ss

@Mrk: I somehow doubt that. The PIA just passes messages between your program and Excel, it doesn't actually handle any behavior itself. If there's no Excel to hand the requests to, your file doesn't get loaded, your data doesn't get returned. – Acropolis

But then how is it users without office but with PIA can run your program? Or can they? – Ss

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.

Caprice

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.

Manual

Answer 4

Here is another 3rd party package which has just been suggested to me by a co-worker: Aspose Excel Spreadsheet Components

Looks like this will mitigate the need for interop and having to have the Office PIA's installed.

There are no prices though, so it may not be cheap.

Xanthic

I think genuine Microsoft components will be more reliable, and there shouldn't be a cost issue because it is already a Microsoft based server - ie. It isn't as if it is a Linux box that is forcing the use of 3rd party components. – Caprice

As stated above, it requires Office 2010 installed on the server, so there are cost issues associated with using an interop approach (As you require a Microsoft license to use Office). Secondly, the package I have suggested is a small footprint DLL which is more suited to a server environment where it may not be possible/desired to install Office 2010. – Xanthic