0

I am writing an application that will live on a server I will not have access to.
My task is to build a web interface that will encapsulate some EXTENSIVE (200+ formulas) logic based in an Excel spreadsheet.
I will not be able to install any software on this server.
I do not want to recode all the formulas in C#.
What I would like is to use a standalone dll that will allow me to open an OpenXML formatted spreadsheet file, change the input cell values, and then extract the end values from cells containing formulas which have run on the new data.
Is there anything out there other than Excel interop (which requires Excel be installed) that will actually compute the formulas realtime?

John Saunders
  • 160,644
  • 26
  • 247
  • 397
user1161804
  • 1
  • 1
    Consider Excel services (part of Microsoft Office SharePoint Services). Separate note: "realtime" as in reasonably fast (seconds) or you have actual hard realtime requirements? – Alexei Levenkov Jan 21 '12 at 06:30
  • I'll never understand restrictions like that. Why would anybody refuse to install software that's been tested by millions of users but allow installing software that's been tested by nobody? Re-inventing this wheel is also particularly wasteful. – Hans Passant Jan 21 '12 at 16:15
  • Microsoft basically does not recommend to use Automation of Office (Interop) working server-side. It may work, but some complications can occur. There is an article about it: http://support.microsoft.com/kb/257757/en-us?fr=1. – Lukasz M Jan 21 '12 at 21:40

1 Answers1

1

Since you want to work with OpenXML files, you can use OpenXML SDK 2.0, which is available here: http://www.microsoft.com/download/en/details.aspx?id=5124. You should install it on your developer environment and it helps you to read and modify contents of OpenXML files in your .NET application.

I haven't tried to work with formulas using this SDK, but I suppose it's possible, however, it may require some work (like writing a parser) to recalculate the formulas with you program. There is, however, a workaround for this, but I'm not sure if it's acceptable for you. You can make Excel reevaluate all the formulas itself when the file is opened by a user. You can find some information about it here: OpenXML SDK: Make Excel recalculate formula.

I also recommend you to use OpenXML SDK 2.0. Productivity Tool, which is a part of the SDK. It allows you to view the contents of an OpenXML file and even show you the c# code required to recreate it programmatically, so it can be really helpful.

Community
  • 1
  • 1
Lukasz M
  • 5,635
  • 2
  • 22
  • 29