Cell Formula cache updating difficulties and not intuitive

[awls99]

If you have an Excel file (I was using excel 2010 xlsx format) in which you have a column A that each cell in it is (for example) the sum of the next two cells in column B and C.
If you use jruby-poi to open said file and add values to column B and C on new row (where the value of A was 0 because the formula was there but C and B were empty), then you save the file and open it on Microsoft Excel the value on the new row's A will still be zero despite the fact that B and C are filled.
If you just click on the formula the value will update and you can't even undo back in to the original state. This had let me to conclude that it was formula cache.

I've used a workaround to this issue on code I used jruby-poi to edit a rather big file with a lot of formulas:

%w[jobs defects].each do |sheet_name|
            sheet    = self.workbook.worksheets[ sheet_name ]
            rows     = sheet.rows
            from, to = 2, 70
            rows.each_with_index do |row, i|
                next if i <= from
                #order is important!
                %w[L K D E F N O P Q U V X Y Z W AA AB AC AD AE AF AG AH AI AJ].each do |letter|
                    column = letter.to_number - 1
                   puts sprintf( 'updating formulas on C: %s row %d', letter, i )
                    self.workbook.on_formula_update rows[ i ][ column ]
                end
                break if i >= to
            end
end

Maybe there could be something like setForceFormulaRecalculation as it's described in http://poi.apache.org/spreadsheet/eval.html , maybe there already is, but the gem documentation could use some improvements.

[sdeming]

Thanks for the pointer. When I get an opportunity to work on this I'll take a look at setForceFormulaRecalculation and see how we can fit that in.

You are also correct that our documentation could use some improvements. It's downright pathetic right now. Again, it boils down time constraints. This is a useful gem that needs a lot of love and attention.

[all4miller]

You can use this to calculate all formulas in the workbook, make sure to use the right FormulaEvaluator depending on XLS vs. XLSX

org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook.poi_workbook)
```ruby