Re: recalculate excel formulas

hi,

i have an excel file with two sheets: sheet1 and sheet2. let's say that sheet1 cell A1 has a value. in sheet2, cell A1 there's a formula, something like Sheet1!A1 + 10. so, if i put 10 in sheet1, sheet2 should have 20. in excel 2007 this is a basic formula, and it works perfectly. however, i want to do the same thing using open xml sdk 2.0. i can insert values in sheet1, cell A1, but the sdk doesn't recalculate the value of sheet2, A1. using mycell.CellValue.Remove(), the formula is updated when i open excel. but i need do do this directly using sdk and not excel. something like:

my function()

   insert value in sheet1, A1

   do some processing

   get value from sheet2, A1 -> this gives the wrong value

   more processing

end function

can you help me?

thanks

kras

so far as i know, SDK cannot automatically refresh the cache value for formula.

Does this imply that when I create an .xlsx using openXML, unless I open in it MS-Excel or any other 3rd party tool which supports this format, i can't get the calculated values?

Yes.

That is correct. The OpenXML format specifies the syntax of the spreadsheet functions but it is up to an implementor to actually implement the processing of those functions. OpenXML is only a document format.

So in order to refresh the values you will need to either open it in a supporting application or you will need to write code to execute the spreadsheet functions.

Hi,

In future version will the OpenXML SDk will have the capability of auto recalculating the dependent cell in the workbook?

Regards,

Tushar

I think it is unlikely since the full calculation engine for a spreadsheet is quite complex. As was stated above, the Open XML SDK simplifies modification of the document format. It is not a spreadsheet application. Also, there are various Excel services that will process the calculations. I don't have the links handy for that information, but you can search for excel services on MSDN. There is a lot of information there. Just one link that looks like it might be helpful is: msdn.microsoft.com/.../ff640648%28office.14%29.aspx