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

Question

Is it possible to query an excel excel spreadsheet without using Jet or exporting to CSV and querying the csv file.

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

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

Additional information: Sharepoint 2010 is running on the server.

Thanks,

Jd

Answer 1

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

FYI: It is not recommended to run daemons on the server using Interop: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2 , however if it is a short program launched by the user and you control it should be okay.

If you want your clients to run the program, they will need to install the primary investment assembly (PIA), if they have not already installed an office, they can be found 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 use Office11 on my x64 Vista machine, so I don't think x64 will be a problem.

Be careful, the documentation is terrible! There are about 10 different versions of help, documentation there. I recommend:

Acquaintance with:

• Application
• Workbook
• Worksheet
• Range

objects. You can read the data in List <List <string →> (in this case all your cells would have to be formatted as text) or something in memory, then you no longer have to deal with Interop and its v. fast on. A method is always used from C # (some documentation mistakenly states that you cannot use the methods that they use for internal use), for example: Range.get_Values ​​("A1"), unlike: Range.Cells;

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

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

And be careful: the Excel index is based on 1, i.e. the first element in the returned 2D array starts with my2DArray [1,1]!

Answer 2

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

Answer 3

I also came across this. My situation: a non-trivial client application that needs to be run on Windows 7 x64, as well as on x86. It is necessary to extract data from Excel spreadsheets; no flexibility and minimal user pain. ACEDB (Access Database Engine redist.) Did not do this, since apparently x64 cannot be installed when x86 (32-bit) is already installed. PIA was not an option - "minimal user pain."

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

It comes with extensive samples, and I was able to create an application for "Any CPU", and not be limited to x86, which caused problems with some other third-party DLL servers that we tried to use. I worked with NPOI for about 10 minutes, so I can definitely recommend it.

Answer 4

Here is another third-party package that I just suggested from an employee: Aspects of Excel spreadsheet components

This seems to reduce the need for interoperability and the need to install Office PIA.

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