This project is read-only.

Updating Calculations

Feb 18, 2010 at 1:40 PM

Hello all,

Fairly new to the OpenXML/ExtremeML, but I was pointed in this direction because of creating server side Excel applications and it was leaving Excel open. I have been able to go through many of the basics, and find the code a bit confusing at first but able to get done what i want. I know this is a dicussion for ExtremeML, but if someone could post a solution  using OpenXML that would be another possible solution.

My overall goal is that I am given a spreadsheet to perform a risk analysis, and we want to programmaticly fill in the input values. However, I would like to be able to get the calculated values out as well. Simple Example:

A1 = 5, A2 = 7, A3 = "=sum(A1:A2)" , so A3 = 12.

If I update A1 to 7, when I open Excel i see A1=7, A2=7, A3 = 12. To remedy this, I had removed the value of A3 in Excel (through code), and when it opens the value is recalculated properly. HOWEVER, this is only when i open the file in Excel. Is there any way to force a refresh/update of the calculated values without a user opening the file? If i delete the value, and open it again (using ExtremeML or OpenXML) the code fails saying that no such object exists. (Object reference not set to an instance of an object) because there's nothing there.

any help would be greatly appreciated! my alternative is to go back to the Excel interop and just kill the process once the files has been opened and saved :-(

~Nick

Feb 18, 2010 at 2:37 PM

Hi Nick,

Thanks for your question.

Both ExtremeML and the OpenXML SDK are simply mechanisms for manipulating OpenXML documents. (In fact ExtremeML is just a wrapper around the SDK, so all of its features are built on top of those exposed by the SDK). This approach doesn't have any inherent capability to infer results because neither ExtremeML nor the SDK has its own calculation engine. As you have correctly observed, if you modify the input parameters to a spreadsheet formula (cell values), the result is only re-calculated once the workbook is re-open by Excel.

For most applications this behavior is acceptable, because a spreadsheet it usually intended to be opened by a user with Excel. However, if you need to retrieve interim recalculated results, you will need to incorporate a calculation engine into the design.

Depending on the scope and complexity of the formulas, you could write some code to emulate the behavior of Excel. In effect, your code would have to parse the formulas and write the results (as well as the input parameters) into the spreadsheet. Beware, however, that if your spreadsheet template is user-maintainable, this strategy exposes the risk that a user will create new formulas that are not fully supported by your algorithms. At the very least, you would need to incorporate a mechanism to notify the user (perhaps via an exception message inserted into the spreadsheet) if your code encounters a formula that it chokes upon. If you need further help with this approach, I may be able to help. (Send me a PM via CodePlex or contact me here).

The alternative is to move to a library that incorporates its own calculation engine, such as http://www.spreadsheetgear.com/ or http://www.aspose.com/.

Good luck with your project,

Tim.

Feb 18, 2010 at 5:04 PM

Tim,

Thanks for the "confirmation" on this info. I figured that if were working with XML, its basically only going to be the cached copy of the values, and that the Excel application will have to do the brute work of calculating. The reason I don't want to do the calculations in my code is because of the template i will (eventually) be working with. The long term goal is to continue to use the Excel Spreadsheet as the "Risk Assessment" but just using a third party software (my companies software) which is a web-based BPM tool. These Excel templates will eventually go out to other users, and the complicated formulas have already been defined and in place for many years. No sense re-inventing the wheel :-) Even though we all do most of the time.

My "final" solution (this is a quick mock up for an internal demo by tomorrow) is to just use OpenXML to do everything with the reading and writing of values. However, any time a calculation is to be done, a function ClearValues just deletes the values of specified cells. It then calls another function which is then using the Excel interopt to just open/save the file, and releases the com objects into memory. i have seen in Visual Studio that no more than one instance of Excel is ever created (and is disposed of properly when the sample app closes) so i'm hoping the same will hold true when i implement the code into my third party application.

If anyone is interested in the code i will post it, just needs to be tweaked a little.

Thanks again for a wonderful product! I'll have to check out the other commercially available products.

~Nick

 

Feb 18, 2010 at 5:43 PM
Edited Feb 18, 2010 at 5:45 PM

Nick - I can see that you need to get something working quickly for your demo, but I would recommend avoiding Interop as a long-term strategy if at all possible (especially in a server environment!)

I recall seeing a Microsoft video (sorry I can't find the link) in which Zeyad Rajabi (SDK Program Manager) spoke about a case study in which a Microsoft customer replaced over 100 servers (all of which were dedicated to producing OpenXML output using Interop) with a single machine running the SDK. Performance gains of this scale can't be ignored.