3

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:)

drf
  • 8,461
  • 32
  • 50
Pavdro
  • 411
  • 1
  • 9
  • 18

2 Answers2

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

JackWang
  • 191
  • 2
  • 6
  • Thanks mate! That's what conclusion I got to as well:) I'm opening file in the background on save and that makes values to be calculated. It might not be optimal solution technically, but it's the only option and since I don't do it often it works out pretty well. Thanks for answer! – Pavdro Jan 19 '12 at 12:02
  • Pavdro, can you post your sample code on how you opened and saved the file in back ground to flush the cache and make recalculate work? – dotnet-practitioner Jan 24 '14 at 02:02
  • 1
    @dotnet-practitioner you can read this to open and save the excel file using excel application back ground: [Open and Save Excel](http://stackoverflow.com/questions/8875120/save-an-excel-file-in-c-sharp) or search it. holp this could help you.^_^ – JackWang Feb 11 '14 at 09:30
0

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.

ptrc
  • 840
  • 8
  • 16