Existing formulas do not calculate fields set by rspreadsheet about rspreadsheet HOT 9 OPEN

Wel I guess I know what is going on there and it is somehow difficult to tell what is the best solution. The cause of this is that in ODS format each formula field has two parts - the formula and the calculated result as it was calculated last time. Fro example K7 has "=A1+A3" or "=CONFIDENCE.NORM(0,3;0,1;400)" in one part and "7" or "0,0051821669" or in another. If the field A1 is changed, my plugin only changes A1 and is NOT looking whether A1 is used in any of the formulas. Why? Just because even if it finds that K7 uses A1 in the formulat, what should it do? It can not recalculate the value part of K7 because it can be very complex task to do and the plugin should invlucde myriad of complex functions wich are in LibreOffice now. Also it is not trivial task to find dependend field, becase of functions like COUNITF, ADDR etc. It is much much more diifcult than only search for A1.

Here is the good news - I know how to force Calc to recalculat all value parts when it starts next time. It takes just DELETING the value part for ALL cells. Calc will find out that it is missing and will recalculate. I could do that automatically on save, but somehow I feel that it is AGAINST the philosophy of the whole gem. The gem tries to change as less as possible, which means it is probably highely forward compaticble. If Calc adds some supercrazy functionlality that the gem will stil work untill you are editing other fields and only stops to work if you touch directly the cell with the new thing.

Maybe I can istrduce some method like sheet.recalculate which would do that if user wants. What is you point of view on this @twnaing ?

from rspreadsheet.

This relates to the problem
https://askubuntu.com/questions/31303/how-do-you-refresh-formulas-in-openoffice-libreoffice-calc

from rspreadsheet.

Just for the record - I made a fresh sheet with only few cells and strangely the bug is NOT there. I can still reproduce it with your file. This is really strange.

from rspreadsheet.

Maybe I can istrduce some method like sheet.recalculate which would do that if user wants. What is you point of view on this @twnaing ?

@gorn, this approach will solve my bug and will not act against the philosophy of the gem.

Or

If there is formula in the ods, the gem will recalculate or user can force, e.g.

book.​save(filename, recalculate: true) 

Anyway, this syntax will be syntactic sugar for your sheet.recalculate

I know how to force Calc to recalculat all value parts when it starts next time. It takes just DELETING the value part for ALL cells. Calc will find out that it is missing and will recalculate.

Does this approach will work if the file will not be opened by user, but convert to PDF using LibreOffice via command line? I know this is not the responsibility of this gem. And for the record, this is the way to convert ods to pdf.

For my ods file, i created with LibreOffice on Xubuntu 18.04.

from rspreadsheet.

Honestly I have no idea if it will work. I will need to try this - I have also found some options withing LibreOffice itself which can influence this (Under Tools > Options > LibreOffice Calc > Formula) so one approach would be to make the script somehow mark the file as "Older Libreoffice" and leave user to choose what will be done in this case. The problem is that I did not find any documentation which would tell me what excatly "old Libreoffice file" means.

Sidenote: It was refreshing to find oud that LibreOffice itself is struggling with recalculation (and in history there were complicated related bugs to this).

It might be helpful if understand your workflow and what you are trying to achieve. I am guessing that you generate osd file with ruby script, save it, convert it to pdf and than rerun it to used as part of some web application. Is that right? Are you generating the ods file entirely with ruby of you have some handmade template and you only fill in some values with ruby? (BTW the second approch proved valuable for me, because it is much easied to make the template look good manually than if you need to script it)

from rspreadsheet.

In this discussion, LibreOffice support replied there are two reasons auto calculation does not work

• LibreOffice program setting (you just mentioned)
• cell setting (this could be the problem in my ods file)

Also go to Tools – Cell Contents and be sure that AutoCalculate is selected.

Yes, I am using rspreadsheet in rails application that will auto fill the data in user provided template file. In short, the workflow is second approach.

I am contemplating to convert to pdf because ods file compatibility for Microsoft Office is poor and not all users can/do not want to install LibreOffice (due to various reasons)

from rspreadsheet.

Interesting link, thanks. However:

What do you mean by "cell setting" - I find it nowhere on that page. I did try to see if the LibreOffice somehow marks the cell which is not working, but so far I did not find anything, the cell seems not different from any others. Btw, you can yourself have a look at aby cell "internals" just by saying

sheet.cell('H13').xmlnode.to_s

from rspreadsheet.

Interestingly enough it seems that LibreOffice itself has/had several issues regarding the recalculation. See https://listarchives.libreoffice.org/global/users/msg51487.html

from rspreadsheet.

Subsequent reply stated that (I haven’t try it yet myself)

The solution to this was found by my coworker. It was to set the /OOXMLRecalcMode/ option in
/~/.config/libreoffice/4/user/registrymodifications.xcu/ to "1"

from rspreadsheet.