Existing formulas do not calculate fields set by rspreadsheet

[twnaing]

Existing ods file has formula pre-set.

I use rspreadsheet (v0.5.1) to

• open the file
• write data
• save

The pre-set formulas do not calculate the data set by rspreadsheet.

If I open the file in LibreOffice and

1. make change to the data (insert space and backspace) the formula (that use the changed cell) works.
2. recalculate (by pressing F9), does not work.
3. recalculate hard (by pressing Ctrl + Shift + F9) does work.

Step to reproduce 1

• download attached ods file (generated using rspreadsheet)
• check row with formula (row 37)
• make changes to the row with data (row 13)
• re-check row with formulat (row 37)

screenshot

in the attached file and screenshot, I made changes to first two filled data and the formula calculate them.

Step to reproduce 2

• download attached ods file (generated using rspreadsheet)
• check row with formula (row 37)
• press F9
• re-check row with formulat (row 37)

Step to reproduce 3

• download attached ods file (generated using rspreadsheet)
• check row with formula (row 37)
• press Ctrl + Shift + F9
• re-check row with formulat (row 37)

resulting file: http://share.getcloudapp.com/BlupjKEg

[gorn]

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 ?

[gorn]

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

[gorn]

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.

[twnaing]

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.

[gorn]

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)

[twnaing]

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)

[gorn]

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

[gorn]

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

[twnaing]

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"