rubyformulaxlsxrubyxl

Update cell values referenced by a formula with RubyXL


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?


Solution

  • 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.