Excel calculation problems

Question

I have Excel .xlsx document in which I need to import some values to cells with defined names. Few of those cells are formated as currency and values that I'm importing in those cells are of decimal type which I import in this manner:

cell.CellValue = new CellValue(value.ToString().Replace(",", "."));

In the same spreadsheet there are a few cells that have a formula in which currency cells I imported are used (for example, I'm importing value in cell H27 with defined name Total, and in the field I27 there is a formula =Total*0.23).

After import is completed, values are successfuly imported (and correctly formated as currency), but formula cells aren't correctly calculated until I either click on formula check marks for each formula cell or I change the currency value (in this case, all formulas containing this cell are refreshed).

What do I have to do for cells with formulas to automaticaly calculate values after import is completed?

Answer 1

I've figured it out. The cells with formula have <CellFormula> fields inside of them. Those fields have bool attribute CalculateCell which, if set to true, tells Excel to calculate the formula after opening the file. Since I don't know up front which formula cells are affected by the cells, I manage all of them like this:

   foreach (var cell in sheetData.Descendants<CellFormula>())
      cell.CalculateCell = true;