Cells with formulas does not recalculated

[Pistorius]

When i change some value in a cell referenced by other with a formula, the result of the value in the cell with the formula does not update. Is this a normal behaviour? Here is the example:

wb= RubyXL::Parser.parse('my_path')
ws= wb[0]

# Value 1
ws[0][0].value
> 5
# Value 2
ws[1][0].value
> 5
# The sum of both with a formula
worksheet[2][0].inspect
> "#<RubyXL::Cell(2,0): \"10\" =A2+A1, datatype=nil, style_index=nil>"
# Changing the value 1
worksheet[0][0].change_contents(55)
> 55
# Cheking the change
ws[0][0].value
> 55
# Cheking the sum
worksheet[2][0].value
> 10

[hugobarthelemy]

I have exactly the same problem

[amaizels-placed]

I am looking for a solution to this also. However, in Excel (on mac) pressing SHIFT+CTRL+ALT+FN+F9 forces rebuilding of dependency tree and does a full recalculation.

VBA solution, which I cannot try without Office subscription, is to put macros into the functions with a Application.Volatile call: https://docs.microsoft.com/en-us/office/vba/api/excel.application.volatile

[weshatheleopard]

@amaizels-placed There's a lot of work that needs to be done to fully support Excel formilas. If you are willing to help, I recommend looking at this branch as references are integral to dealing with formulas.

[amaizels-placed]

This snippet solved the problem for me.

book = RubyXL::Parser.parse(excel_path) book.calc_pr.full_calc_on_load = true

I also started saving the output to a separate file because "Excel does stuff I have no control over"

[sixtyfive]

This seems to be a duplicate of #94 (which is from 2014 btw...)

[tracyperangusta]

Can we close this issue?

[wendelscardua]

In case someone else comes here looking for an answer, one possible workaround that seemed to work for me is to clear the values of all cells that have formulas:

workbook.worksheets.each do |worksheet|
  worksheet.sheet_data.rows.each do |row| 
    row&.cells&.each do |cell|
      cell.value_container.value = nil if cell&.formula
    end
  end
end

... otherwise they would keep their previous values, even when using the book.calc_pr.full_calc_on_load = true setting mentioned above.

(I was going to use cell.change_contents(nil, cell.formula) but this doesn't work if the formula is "shared")