rubyXL
rubyXL copied to clipboard
weshatheleopard
Implement formula support
I have a cell C55 which is being referenced by other cells in a formula. Here's the code I'm using to update the cell:
@open_book = RubyXL::Parser.parse(@base_file)
@worksheet = @open_book[0]
@worksheet[54][2].change_contents(5.0)
@open_book.write(@new_file)
I can see that C54 is updated to 5.0 but I think it's being written as a string instead of a number which is why other cells aren't updated.
Unfortunately, I do not exactly understand what you are trying to do and what is happening that is wrong.
Sorry meant to attach the spreadsheet. In the spreadsheet if you can change cell C55, it affects the values in rows 57 and 58. When I update the value of C55 from the code I posted earlier, it doesn't change the row 57 and 58.
xlsx: http://cl.ly/2u2B2Z310N0N
From what I see, C57 and C58 contain formulas that reference C55. As of now, RubyXL does not perform any kind of calculations, it can only change data and the formula expressions; if you want values recalculated, you need to load your workbook into Excel and force recalculation (see http://msdn.microsoft.com/en-us/library/office/bb687891.aspx). Formula support in RubyXL is being worked on, but it's still in its infancy.
@weshatheleopard what's the progress on this? I'd be glad to help see this feature :) ... I see you have a formula_support branch but wouldn't really know where to start
Thanks for the great job so far!
It will be a pretty long while, since I will pretty much have to re-implement half of Excel in Ruby. I'm planning to, eventually, but I have my primary job and all.
Things that are needed first of all are formula parser, and reference parser. (So far, reference parser only can locate cells on the same sheet, but there are a lot of capabilities in real Excel, like 'C:\Reports[Budget.xlsx]Annual'!C10:$C$25) that need to be implemented still...)
Sorry I took so long to reply. Like you say it looks like a big project so in the meantime I ended up using openpyxl as a temporary solution. I really don't like the hack of having to implement just this one thing in python within the same project but I needed to ship something. I forked the project and I will try to see if there is anything I could help with. Thanks again!
I am having the same issue here. Is there any fixed for this? @mauromorales I am looking for temporary solution with openpyxl but still no luck. Could you share your solution? Thank you.
What I'm doing is updating my file using rubyXL and as soon as I finish I just open and save it with openpyxl. This process recalculates all the values in the formulas.
In order to run openpyxl I use the 'rubypython' gem and then in my model here is what I run:
RubyPython.start
openpyxl = RubyPython.import "openpyxl"
wb = openpyxl.load_workbook(self.xl_file.path)
wb.save(self.xl_file.path)
RubyPython.stop
Like I said, not a very nice hack but I hope it helps @reshajp
@mauromorales Have you managed to deploy this code to a Heroku instance by chance?
Does this difference?
book = RubyXL::Parser.parse(file) book.calc_pr.full_calc_on_load = true
Hi, is there any news on this issue? I have the same problem, and for some reasons openpyxl is not working for me.
What I'm doing is updating my file using rubyXL and as soon as I finish I just open and save it with openpyxl. This process recalculates all the values in the formulas.
In order to run openpyxl I use the 'rubypython' gem and then in my model here is what I run:
RubyPython.start openpyxl = RubyPython.import "openpyxl" wb = openpyxl.load_workbook(self.xl_file.path) wb.save(self.xl_file.path) RubyPython.stop
@mauromorales It was not worked for me. I open and save the xlxs file after to modify it with RubyXL, just as you said, but the formulas was not recalculated, instead it save "" as .value in the cell with the formula. Im missing something?
@Pistorius sorry, I've moved to work on different projects now so I haven't used this in a while.
Currently running into this issue.
@Pistorius were you able to get it working with openpyxl?
@weshatheleopard any progress?
Currently running into this issue.
@Pistorius were you able to get it working with openpyxl?
@weshatheleopard any progress?
Does kisara-icy's method not work for you? I had the issue of functions not being run when excel opened after editing with rubyXL and this line fixed that.
book = RubyXL::Parser.parse(file) book.calc_pr.full_calc_on_load = true
From what I see, C57 and C58 contain formulas that reference C55. As of now, RubyXL does not perform any kind of calculations, it can only change data and the formula expressions; if you want values recalculated, you need to load your workbook into Excel and force recalculation (see http://msdn.microsoft.com/en-us/library/office/bb687891.aspx). Formula support in RubyXL is being worked on, but it's still in its infancy.
As of now, is there any way one could extract the formula instead of getting its computed value?
(I realize that OP wants to do something different; seems related though.)
fwiw, the problem of not being able to access the formula itself, is easily solved by reading the document a second time with Roo and calling Excelx#formula(row, col) on the cell. dirty, but good enough for me for the moment.
@sixtyfive What do you mean "not able to access the formula itself"???
x = y = 1
workbook = RubyXL::Parser.parse("example.xlsx")
c = workbook[0][y][x]
c.formula
Oh damn! That's what I was looking for and didn't find. Ouch, embarrassing! Thank you!!! (Edit: fwiw, .formula isn't enough, and .formula.to_s is not what you think it is. Needs to be .formula.expression instead).