Update cell values referenced by a formula with RubyXL

Question

I have a xlsx with values in cells, many of them are referenced with a formula in other cell (in the same sheet). Im working with RubyXL because i wasn't found another gem which help me to write, edit and save an existed xlsx file. Now to be clear, lets see an example of what im doing and i want. Imagine a group of 3 cells; A1, B1 and C1 where C1 is the sum of A1 and B1 (=A1+B1), so if we have a 4 in A1, a 6 in B1 then C1 is equivalent to 10. I'm opening the xlsx with workbook = RubyXL::Parser.parse('example.xlsx'), afther that i modify the value of cell A1 from 4 to 5 and save it. Here is the problem, if we read the cell C1 after the change we still have the previous result 10. How i can update that accord to the formula? Is posible with RubyXL? or is there another solution?

Answer 1

Finally i solved this. If you are interested I used win32ole because after tested a lot of rubygems this was the unic which works like i said in the question.

require 'win32ole'

begin
    xl = WIN32OLE.new('Excel.Application')
    workbook = xl.Workbooks.Open('my_route')
    worksheet = workbook.Worksheets(1)
    # Here we make operations like this one...
    worksheet.Cells(2,2).value = 2
    # After any operations we can see the results of referenced cells inmediatly
    # Save the file if you want
    # workbook.Save
    workbook.Close
rescue => e
    xl.Quit
end

So in conclusion RubyXL work fine but dont reflect the results of cells referenced in formulas when you edit the file. win32ole do that.

Answer 2

From the RubyXL README it sounds like that utility is intended to read Excel files and then write them back out. Then when you opened the file in Excel you would see your changes and the formulas would be recalculated.

You might want to look at win32ole if you want to do COM automation of Excel.

Answer 3

The problem with the accepted answer is that on servers office often isn't installed, so the COM automation won't work there. When you use the following code the formulas are recalculated when Excel opens the spreadsheet.

workbook = RubyXL::Parser.parse(file)
workbook.calc_pr.full_calc_on_load = true