Export to ods, from org with formulas that contain ifs, (nan or typeof =12) are not correctly exported

[ouboub]

Hi Maybe this is too much to ask, but currently I want to share org spreadsheet with my colleagues that only use LO or Excel, so I'd like to export the org table to ods. I have tables like this

#+Name: test-if
| Miller | pos | range | weight |
|--------+-----+-------+--------|
|        |  33 |   329 |    297 |
|        |  71 |   329 |    259 |
|        |  95 |   267 |    173 |
|        | 102 |   329 |    228 |
|        |  64 |   333 |    270 |
|        |  48 |   329 |    282 |
|        |  55 |   329 |    275 |
|        | 124 |   329 |    206 |
|        |     |       |        |
|        |     |       |        |
|        |     |       |        |
|        |     |       |        |
|--------+-----+-------+--------|
|        |     |       |   1990 |
#+TBLFM: $4=if("$3"=="nan",string(""),($3+1)-$2);E f2::@14$4=vsum(@I$4..@II$4);f2

I need these empty rows, that actual table is larger and more complex. When exporting this table to ods, my LO cannot open the file

so I replaced

"$3"=="nan"

by this

#+Name: test2-if
| Miller | pos | range | weight |
|--------+-----+-------+--------|
|        |  33 |   329 |    297 |
|        |  71 |   329 |    259 |
|        |  95 |   267 |    173 |
|        | 102 |   329 |    228 |
|        |  64 |   333 |    270 |
|        |  48 |   329 |    282 |
|        |  55 |   329 |    275 |
|        | 124 |   329 |    206 |
|        |     |       |        |
|        |     |       |        |
|        |     |       |        |
|        |     |       |        |
|--------+-----+-------+--------|
|        |     |       |   1990 |
#+TBLFM: $4=if(typeof($3) == 12,string(""),($3+1)-$2);E f2::@14$4=vsum(@I$4..@II$4);f2

that exported table can be open by my LO but the last colum reads just error, I attach both ods in a zip file it seems that I can't use such if constructions and might be forced to insert the corresponding formula just cellwise, without an if regards

Uwe Brauer

if-problems.zip

[kjambunathan]

That is a nice bug you have run in to ... It gave me good insights on where the ODS exporter falls short and how it can be improved.

I have pushed an experimental and expolratory fix

As always some of the remarks here is meant for my future self, but a part of it is meant for your consumption. Pay attention to the LO Calc screenshots and comments in there. Pay attention to the remarks which are enclosed in "multi-box".

Don't rely on ODS exporter too much, it is a prototype code ... Don't trust it too much and bet your money on it. As long as you approach what ODS does with a sceptical eye, you will be doing good.

Zip file

org-mode-ox-odt-issue-252.zip

To get this

do this

#+TITLE: Arg separator in LO Calc & Custom Emacs Calc function to mimic LO Calc function

* More notes

#+begin_src emacs-lisp :exports results :results none
  (setq org-ods-formula-arg-separator ";")
  (defmath ISBLANK (x)
    (if (numberp x)
        0
      1))
#+end_src

#+Name: test-if
| Miller | pos | range | weight |
|--------+-----+-------+--------|
|        |  33 |   329 |  20297 |
|        |  71 |   329 |  20259 |
|        |  95 |   267 |  20173 |
|        | 102 |   329 |  20228 |
|        |  64 |   333 |  20270 |
|        |  48 |   329 |  20282 |
|        |  55 |   329 |  20275 |
|        | 124 |   329 |  20206 |
|        |     |       |      0 |
|        |     |       |      0 |
|        |     |       |      0 |
|        |     |       |      0 |
|--------+-----+-------+--------|
|        |     |       | 161990 |
#+TBLFM: $4=if(ISBLANK($3),0,30000+($3+1)-$2);E f2::@14$4=vsum(@I$4..@II$4);f2
# #+TBLFM: $4=if("$3"=="nan",string(""),($3+1)-$2);E f2::@14$4=vsum(@I$4..@II$4);f2

State of the Nation' report for ox-ods' exporter

LO Calc Hard Recalculate

Reconfiguring formula separator in LO Calc

[kjambunathan]

One of the "multi-box" comments says that the TBLFM formula MUST BE A MIRROR IMAGE of LO Calc formula .... as long as this condition is meant by hook-or-crook, ODS exporter will behave well.

That the TBLFM should be a mirror image of the LO Calc formula, save for dumb substituitions, is the underlying simplifying assumption I have made to impement the ODS exporter. (This assumption has carried us quite far ....) If this assumption is not honored, the ODS exporter cannot do much ...

Alternatively stated, both of your attempts failed because of the reasons I list below--and remember the breakage is because of the breach in the "mirror image" rule

1. The TBLFM formula uses comma' as function arg separator. (In German locale, LO Calc uses semicolon' as fn arg separator). In other words, `commas' weren't mirrrored.
2. There is no == in LO Calc. (In Lo Calc, = is the equality operator). In other words, the TBLFM's == wasn't mirrored on LO Calc side.
3. There is no NaN' in LO Calc. In other words, the NaN' wasn't mirrored on the LO Calc side.
4. There is no typeof in LO Calc. In other words, if typeof is mirrored you are inviting trouble for yourself.

=====

To address (1) above, in my fix, I have introduced (setq org-ods-formula-arg-separator ";") so that the fn arg separator could be mirrored.

To address (2) and (3) above, I have used Emacs Calc's defmath to teach Emacs how to do a ISBLANK. (In your version of the TBLFM the NaN check comes across as 2-ary function. If a function has two arguments, and one of the arguments is a constant, it is NOT a binary function, but a unary-funcion. The ISBLANK version on Emacs Calc, is a unary function, and its name and functionality is deliberately chosen to match a pre-existing function of LO Calc.

---

I had made an assumption that an empty paragraph in a ODS cell qualifies as blank table cell. I was wrong; a blank ODS cell has nothing at all, NOT EVEN an empty paragraph. This "thinko" has been fixed.

---

A possible roadmap for ODS exporter

1. Teach the exporter about the Calc modes, the E and N thing etc.
2. Make the TBLFM look and behave exactly like Excel / Calc formula using Calc's defmath functionality ...Theoretically this is possible.
3. Teach ox-ods to parse the RHS of TBLFMs using Parsing Expression Grammars in Emacs Lisp (also available at https://github.com/emacsmirror/peg ) This way the ,' to ;' replacement could be much more intelligent

[ouboub]

"J" == Jambunathan @.***> writes:

That is a nice bug you have run in to ... It gave me good insights on where the ODS exporter falls short and how it can be improved. I have pushed an experimental and expolratory fix

First of all thanks for your answer and the remarks you made, I will study what you sent to me, tomorrow. I have to add that I am fully aware that the exporter is quite difficult because

1. The syntax between org and ods is quite different.

2. ods is cell orientated, while org is column orientated.

(I also have to admit that I most likely don't enough ods syntax, for what you called mirror expression.

Currently I have to write up a bit complicated table that I have to share with my colleagues. I prefer to do this in org, since it is emacs and I am acquainted with its table syntax (but not really an expert) my colleagues on the other hand use ods/xlsx.

So I have somehow to try to translate the formulas. In that process I found to additional problems.

1. One has to do with the max function. I wanted to create a new column which is the rescaled version of another column. By this I mean each element of the new column is the element of the corresponding old column divided by its largest member. I know how to do this in org and I know to do that in ods. But I don't know who to write in in org so that it translates to ods.

2. Concerns a case in which I don't sum elements of two columns but elements of two rows. Again the same problem occur.

Can/shall I open an new issue for that problem (or two?)

Thanks

-- Warning: Content may be disturbing to some audiences I strongly condemn Putin's war of aggression against Ukraine. I support to deliver weapons to Ukraine's military. I support the NATO membership of Ukraine. I support the EU membership of Ukraine. https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/

[kjambunathan]

Let me know how my ISBLANK fix fared on your end

So I have somehow to try to translate the formulas ...

Start with LO end ... and teach Calc how to LO thing

When I say TBLFM being a mirror of Calc formula, think of it as an isomorphism ... The two things---things here being Calc formula, and LO formula---are structurally the same, with some components being replaced.

This is how I came up with the example, I cited ... and that is the way you can follow to atleast attempt at what can and cannot be accomplished with this simplistic assumption.

but not really an expert

I am not an expert either ... with TBLFM and LO Calc etc.

Let us pretend you are teaching me TBLFM and I would pretend that you get some insights about LibreOffice or ODS format in return ....

I sense a reluctance your part to acclimatize yourself with LibreOffice and this reluctance on your part has to go away ...

Can/shall I open an new issue for that problem (or two?)

Put them two different issues.

1. One has to do with the max function. I wanted to create a new column which is the rescaled version of another column. By this I mean each element of the new column is the element of the corresponding old column divided by its largest member. I know how to do this in org and I know to do that in ods.

Give both Org and Calc examples ... It is psychologically barrier for me to come up with examples, based on just descriptions alone ...

2. Concerns a case in which I don't sum elements of two columns but elements of two rows. Again the same problem occur.

Yes, give examples ... simple ones, and some annotation with screenshots ....

... and while suggesting examples both (1) AND (2) below

1. You can upload a zip file .... so that the other party can just download the zip file locally and start experimenting with it
2. You can also copy-paste the individual files to the github issue so that one can (a) browse it as notepad (b) and I can use github issues to refer back to that issue in a different future (c) search engines can index the issue

... and all the above is extra work, and is not really palatable to a typical Emacs who user asserts "I want everything in Email and that too in my Gnus" ... I have been in that ship, and not any more .. I have moved on from Gnus to Thunderbird ... and I take more notes in Whatsapp than in my Org mode ..

Based on what conversations we have been having, it looks like we are in for a long haul

1. The syntax between org and ods is quite different.
2. ods is cell orientated, while org is column orientated.

Pretend that they are untested hypothesis, and let your examples be simple enough to understand ... and complex enough to test the above hypothesis.

(Your bug about nan thing was good one ... it was simple but poking holes all over ... )

---

In my Org table example that uses defmath---without exporting the table itself to ODS--it is worth our while to experiment with TBLFM formula, and see what the observed period, and subsequently hypothesize about what the Emacs Calc and Org TBLFM models of the world are ...

Some suggestion for experimentation ...

1. add or remove the E
2. replace " with string("") to explore how the Org Table does conversions among types.
3. Add an E the formula in the last column.

[ouboub]

"J" == Jambunathan @.***> writes:

Let me know how my ISBLANK fix fared on your end

It did! sorry I forgot to tell you.

So I have somehow to try to translate the formulas ...

Start with LO end ... and teach Calc how to LO thing

When I say TBLFM being a mirror of Calc formula, think of it as an isomorphism ... The two things---things here being Calc formula, and LO formula---are structurally the same, with some components being replaced.

but not really an expert

I am not an expert either ... with TBLFM and LO Calc etc. Let us pretend you are teaching me TBLFM and I would pretend that you get some insights about LibreOffice or ODS format in return .... I sense a reluctance your part to acclimatize yourself with LibreOffice and this reluctance on your part has to go away ...

Can/shall I open an new issue for that problem (or two?)

Put them two different issues.

1. One has to do with the max function. I wanted to create a new column which is the rescaled version of another column. By this I mean each element of the new column is the element of the corresponding old column divided by its largest member. I know how to do this in org and I know to do that in ods.

Give both Org and Calc examples ... It is psychologically barrier for me to come up with examples, based on just descriptions alone ...

I will do, but I yesterday night found out that it boils down to replace the word vmax (org) by max (ods).

2. Concerns a case in which I don't sum elements of two columns but elements of two rows. Again the same problem occur.

Yes, give examples ... simple ones, and some annotation with screenshots ....

This problem I solved, so no need for a different issue.

... and while suggesting examples both (1) AND (2) below

1. You can upload a zip file .... so that the other party can just download the zip file locally and start experimenting with it
2. You can also copy-paste the individual files to the github issue so that one can (a) browse it as notepad (b) and I can use github issues to refer back to that issue in a different future (c) search engines can index the issue

I am not sure I understand, when I try to drag and drop an org file github rejects it claiming that it does not support this format. But of course I can copy its content as code (as you do)

I am currently not sure about ods.

... and all the above is extra work, and is not really palatable to a typical Emacs who user asserts "I want everything in Email and that too in my Gnus" ... I have moved on from Gnus to Thunderbird ... and I take more notes in Whatsapp than in my Org mode ...

Based on what conversations we have been having, it looks like we are in for a long haul

Right! And me usually having to deal with these issues extensively at the beginning and at the end of the course.

1. The syntax between org and ods is quite different.
2. ods is cell orientated, while org is column orientated.

-- Warning: Content may be disturbing to some audiences I strongly condemn Putin's war of aggression against Ukraine. I support to deliver weapons to Ukraine's military. I support the NATO membership of Ukraine. I support the EU membership of Ukraine. https://addons.thunderbird.net/en-US/thunderbird/addon/gmail-conversation-view/