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