What options do I have for reading Excel files and evaluating formulas in C#, Java and PHP?

Question

I've seen various means of reading (and writing) Excel spreadsheets. For example:

• In .Net as an ADO.Net source (as per Reading Excel files from C#);
• In Java with either JExcelAPI ("jxl") or Apache POI;
• In PHP with the Excel Spreadsheet Writer (as per Format text in Excel file via PHP and PHP:Writing to excel file using PEAR).

But I'm interested in more than just reading/writing Excel files. I want something where I can evaluate arbitrary Excel formulas. So far in this vein I've seen:

• C# Excel Interop, although this seems to require that you have Excel running; and
• POI-HSSF: this formula support seems incomplete.

Are there better options or is that it?

Answer 1

If you're only targeting the windows platform, you really have no substitute for running Excel, and controlling it via a COM bridge.

As you are finding, any other solution is likely to be

• incomplete, and/or
• incompatible and/or
• inconsistent

with Excel.

Unfortunately, this does add a dependency on Excel to your software.

Answer 2

SpreadsheetGear for .NET is compatible enough with Excel that Microsoft uses it to calculate Excel based calculators on their MSN Money web site.

Answer 3

If you want to evaluate Excel formulae, frankly Excel is the only reliable way to do this. So running Excel via a COM bridge is the way to go.

I've done this before in Java using JACOB and that works very nicely. Excel looks after all the functionality. You just have to populate your spreadsheet (in memory) and let Excel do the rest.