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?

peter · Answer 1 · 2019-09-04T11:32:37.290

5

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

edited Sep 04 '19 at 11:32

answered Sep 04 '19 at 11:26

peter

41,770
5
64
108

score 1 · Answer 2 · answered Aug 08 '17 at 17:28

1

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.

answered Aug 08 '17 at 17:28

Tom Copeland

1,221
10
10

I can see the changes when i open the file afther i write it, but not the formulas recalculated. Ill check win32ole thx – Pistorius Aug 08 '17 at 18:11
1

Thanks for the hint Tom, win32ole helped me to solve the problem – Pistorius Aug 09 '17 at 15:21
Cool, I'm glad that helped! – Tom Copeland Aug 13 '17 at 04:58

score 1 · Accepted Answer · answered Aug 09 '17 at 15:19

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.

Update cell values referenced by a formula with RubyXL

3 Answers3