CODEWITHSUNDEEP
xmlopenxml
Pavdro
Pavdro

Reputation: 427

calculate cell values from formula programmatically in OpenXML sdk2

I'm using OpenXML SDK2.0 for excel calculations and stuck with 1 thing.

Basically I'm populating an excel file with data from database and sending it to the user...that bit works fine. Now I want to get the calculated cell value after I'm putting a formula in it, but without opening the excel document... Is there a way to populate excel file with openxml sdk2.0 and get calculated values back straight away?

Maybe someone has encountered similar problem or has a working solution !

Thanks:)

Upvotes: 3

Views: 4881

Answers (2)

JackWang
JackWang

Reputation: 311

spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;  
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

this will work very well,when you open the Excel manually.but also can't Programmatically get the cell value with formulae.If you want to get the cell value by Open Xml SDK , have to open the excel file backgroud by Excel Application,and save the file. then can get the cell value programmatically.

This is the Information from Office Development Center:

How to automatically recalculate the formula value for cells in Spreadsheet?

A cell with a formula will store the cached value in the markup and show the cached value as the result. If the cell value referenced by the formula is changed programmatically, the cell with the formula has no idea of that change. The cached value of this formula is then obsolete, and should be updated. The automatic recalculation requires runtime layout functionality, which is not supported in the SDK. The workaround for this situation is to: delete the cached value of the formula cell, and then invoke Excel to recalculate the value for you at backend. The Excel service could help here.

Upvotes: 5

ptrc
ptrc

Reputation: 880

Aside from writing your own parser to calculate the value of the cell, this is not currently possible. You could of course use automation under a service account to recalculate the formulae behind the scenes, but that can get messy fast.

Upvotes: 0

Related Questions