6

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

Improve this question
5
  • 2
    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. Commented Nov 2, 2010 at 0:56
  • No. It is a business requirement that the information be stored in the excel file. Not my choice... Commented Nov 2, 2010 at 1:01
  • 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. Commented Nov 2, 2010 at 1:20
  • 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 Commented Nov 2, 2010 at 2:08
  • I see (have to make up the min chars) Commented Nov 2, 2010 at 2:27

4 Answers 4

Reset to default
11

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

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

That was what I was about to suggest. 64 bit might pose a wrinkle or two but it should work.
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.
I think, whatever you decide, you are going to require at least some Office components.
@JD: you can just install the PIA's rather than office, get them here: microsoft.com/downloads/en/…
@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.
|
4

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.

Improve this answer

Comments

4

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.

Improve this answer

Comments

1

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.

Improve this answer

2 Comments

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.
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.