Export single `org` file with multiple tables to a single `ods` file mutliple sheets.

If one is faced with large tables with a lot of columns the most convient solution for org mode is to split these tables and use the remote function here is an example (<> are just for the export to latex)

#+Name: table1
|   | Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
| / |  <>    |  <> |  <> |  <> |  <> |  <> |     <> |
|---+--------+-----+-----+-----+-----+-----+--------|
|   | Smith  |   2 |   3 |   4 |   6 |   7 |     22 |
|   | Miller |   2 |  10 |   1 |   1 |   5 |     19 |
|   | Wick   |   1 |   2 |   3 |  10 |   2 |     18 |
#+TBLFM: $8=vsum($3..$7);f2

#+Name: table2   
|   | Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
|   | <>     |  <> |  <> |  <> |  <> | <>  |     <> |
|---+--------+-----+-----+-----+-----+-----+--------|
|   | Smith  |   8 |   3 |   5 |   8 |   9 |     33 |
|   | Miller |   9 |   4 |   6 |   9 |   3 |     31 |
|   | Wick   |   1 |   5 |   9 |   1 |   2 |     18 |
|---+--------+-----+-----+-----+-----+-----+--------|
#+TBLFM: $8=vsum($3..$7);f2

#+Name: final
| / | Name   | Some | ResSh1 | ResSh2 | ResSh1 + ResSh2 |
|   |  <>    |   <> |     <> |     <> |              <> |
|---+--------+------+--------+--------+-----------------|
|   | Smith  |    4 |     22 |     33 |              59 |
|   | Miller |    4 |     19 |     31 |              54 |
|   | Wick   |    4 |     18 |     18 |              40 |
|---+--------+------+--------+--------+-----------------|
#+TBLFM: $4='(identity remote(table1,@@#$8))::$5='(identity remote(table2,@@#$8))::$6=vsum($3..$5);f2

I attach the corresponding ods that uses VLOOKUP and different sheets.

I am not sure that the exporter could produce such an ODS file.

org-lookup.txt scalc_lookup.ods

I attach the corresponding ods that uses VLOOKUP and different sheets.

+TBLFM: $4='(identity remote(table1,@@#$8))::$5='(identity remote(table2,@@#$8))::$6=vsum($3..$5);f2

There is lookup in VLOOKUP in the spreadsheet. But I see no counterpart in `Org'.

If the names in table1 and table2 are not in row-to-row correspondence, the third table will backfire.

Please improve your example, so that there is a clear one-to-one correspondence between the TBLFM formula and ODS formula.

Without the VSLOOKUP counterpart, this is a request reduces

Support for multiple tables in a sheet
Support for translating remote calls to table2.$A$1.

To re-work your example so that there is a clear one-to-one mapping,

enhace the Org' TBLFM so that it includes theOrg' counterpart for VLOOKUP

Fix the ODS spreadsheet, so that it really has no VSLOOKUP.

As a further suggestion---learning from my our earlier example of providing for VSUM but not VMIN or VMAX---you can create a catalogue of VLOOKUP and friends which all need to be mapped.

I am not sure that the exporter could produce such an ODS file.

Yes it can. It is a question of how much time I have. I anticipate that I would get very busy with other stuff in 3 months, so there is that. We will see.

On related note, I am willing to take up this request org-table-transpose-table-at-point does not convert formulas, provided you could re-work your example to clarify what happens when the horizontal hrules becomes vertical vrules, and vice versa. Also, what happens when you use a var name to capture the value in the preceding row ... (just walk through the different examples in `Org' Info manual, to get an idea of the semantics of things you are NOT using). You demo example conveniently ignores the complexity. As an exporter, the table has to looked at in its entirety---the stuff you use, and the stuff you do NOT use but is catalogued.

| | <> | <> | <> | <> | <> | <> | <> |

Please remove these cookies from example ... That they create issues--off by one errors--is documented in https://github.com/kjambunathan/org-mode-ox-odt/issues/254#issuecomment-1745084784

I want to separate out the concerns, so that things are mutually independent ... I suggest that you

change the title of this bug so that it says "Support for multiple tables in sheet, and support for remote formula
Create a separate bug report for VLOOKUP

Just an observation ...

Github is not a mailing list ... If you are not going to leave your comfort zone, Gnus, I will assume that you aren't much invested in LibreOffice. (For colloboration to happen, each of us have to meet half way) What this means is that you need to switch over from "I hate working with Github interface, I will reply to this thread from Gnus" to "Github is a different medium ... I will adapt to whatever is best for the medium".

scalc_lookup.ods

I would prefer LibreOffice ... It is beyond my current interest to start using scalc ... You need to convince me that you are interested in atleast mentally switching over to LibreOffice. I have been sharing screenshots of LibreOffice out of good will, and if you continue using scalc (Gnumeric?), ... it is a bit discouraging for me. It gives me an impression that I am exchanging notes with someone who has no stakes in the game ....

I am old ... not very old. Old enough to assess if a person has sufficient stakes in the game so that we can play along.

I promise that you will have a first class ODS exporter (say in 1 months time) if you just play along. And the good thing for you would be you don't have to ever get in to LibreOffice or scalc stuff ...

(Take my remarks in good spirit)

As an illustration of what a minimal example looks like ... this is what I would start with.

(Compare my example here, to the example you provided. Your example has too many moving parts, but my example is minimal and spare)

Also ... Huh! what does ~identity~ do here ... Why complicate things?

For the purpose of this issue, I will consider the following org snippet to export successfully. This means

ODS file has two sheets named table1 and table2
table2 has the ODS equivalent for remote(...) call

Also the headline (essentially non-table elements should be stripped from the ODS export. It just doesn't make sense for ODS table to have non-table contents)

#+Name: table1
| Smith  | 10 |
| Miller | 20 |
| Wick   | 30 |

#+Name: table2
| Smith  | -1 | 10 | 20 |
| Miller | -2 | 20 | 40 |
| Wick   | -3 | 30 | 60 |
#+TBLFM: $3=remote(table1,@@#$2)::$4=2*remote(table1,@@#$2)

* COMMENT Huh! what does ~identity~ do here ... Why complicate things?

#+Name: table3
| Smith  | 1 | 10 | 20 |
| Miller | 2 | 20 | 40 |
| Wick   | 3 | 30 | 60 |
#+TBLFM: $3='(identity remote(table1,@@#$2))::$4=2*remote(table1,@@#$2)

I have changed the title of the issue to reflect above comments.

If you want to additional features like VLOOKUP etc, please open a fresh issue ... and make sure that the bug is a minimal example ...

Just a couple of comments

my apologies for the example, I should have tested them more.
I opened the issue, and did not use gnus.
I use LO for years now, and on linux the spreadsheet is called scalc, most likely to distinguish it from oocalc from openoffie
```
/opt/libreoffice7.2/program/scalc
```
I don't know how to change the title of an issue I opened, do you want me to close this issue and open 2 new ones? I opened only one because for me the goal was to obtain and ods document with various tables and VLOOKUP. But if you prefer I open one issue for exporting several tables to one ods with several sheets and another one. So I am waiting for your answer, before proceeding.
concerning the transpose question I asked, right now I can live with the current solution I found, (exporting the org table as it is and then transpose it in LO)

I use LO for years now, and on linux the spreadsheet is called scalc, most likely to distinguish it from oocalc from openoffie

scalc is a lib as opposed to a bin thing.

I was assuming that Gnumeric calls its binary scalc, and you were using Gnumeric at some point in time, and you were continuing to use it.

In Debian, the LibreOffice executable ultimately leads to soffice.bin are some such thing and had never seen a scalc at all ...

~/src1/org-mode-ox-odt/master$ sca
scalar     scanimage  

~/src1/org-mode-ox-odt/master$ scalc
Command 'scalc' not found, did you mean:
  command 'kcalc' from snap kcalc (23.04.3)
  command 'calc' from deb calc
  command 'xcalc' from deb x11-apps
  command 'wcalc' from deb wcalc
  command 'lcalc' from deb lcalc
  command 'kcalc' from deb kcalc
  command 'scafc' from deb pd-scaf
  command 'scale' from deb csound-utils
See 'snap info <snapname>' for additional versions.

~/src1/org-mode-ox-odt/master$ lsb_release -a
No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux trixie/sid
Release:    n/a
Codename:   trixie

~/src1/org-mode-ox-odt/master$ uname -a
Linux debian 6.4.0-3-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.4.11-1 (2023-08-17) x86_64 GNU/Linux

Since both of us are using LibreOffice, our efforts are in sync.

I am surprised that there is a package called libreoffice-calc. I usually

I don't know how to change the title of an issue I opened,

Next to the title of the issue, you will see a green colored Edit button. I have changed the title of the issue already.

I opened only one because for me the goal was to obtain and ods document with various tables and VLOOKUP.

I feel that version of remote() calls that occur in conjunction with VLOOKUP calls are more useful than the plain version of remote() which copies over a cell or a column or row vector.

I was picking on this aspect: There was VLOOKUP on ODS side, but no counterpart on the TBLFM side. When you are cooking up the example, I can work on hacking.

(I making things up as I go. So, it will take effort for me to cookup good and simple TBLFMs. )

But if you prefer I open one issue for exporting several tables to one ods with several sheets and another one.

This is done. There is no support for remote.

I have evaluated the effort, and it requires some care to get it right. I will take the support for remote separately.

The @# and @$ construct seems to be used commonly with remote() calls, and so my earlier suggestion of translating in to @# and @$ are wrong. In other words, the first two entries in the below mapper is wrong. The mapping was created as part of commit 2476271509. (Me making that suggestion, is an indicator that I was quickly making up things, just as an expedience)

(defvar org-ods-calc-f->ods-f-alist
  '(("$#" . "COLUMN()")
    ("@#" . "ROW()")
    ("vmax" . "MAX")
    ("vmean" . "AVG")
    ("vmin" . "MIN")
    ("vsum" . "SUM")))

As a programmer, something about remote() smells. Anyways ... the multi-sheet support is now supported

To get this

mulisheet-formula

do this

#+TITLE: ODS export: Demo of multi-sheet spreadsheets

When exporting to spreadsheet, non-table contents are ignored.  When
you export this ~Org~ file, you will get a 2-sheet ODS file.

#+NAME: table1
| Smith  | 10 | 100 |
| Miller | 20 | 200 |
| Wick   | 30 | 300 |
#+TBLFM: $3=10*$2::

#+NAME: table2
| Smith  | -1 | -100 |
| Miller | -2 | -200 |
| Wick   | -3 | -300 |
#+TBLFM: $3=100*$2::

I use LO for years now, and on linux the spreadsheet is called scalc, most likely to distinguish it from oocalc from openoffie

scalc is a lib as opposed to a bin thing.

I was assuming that Gnumeric calls its binary scalc, and you were using Gnumeric at some point in time, and you were continuing to use it.

In Debian, the LibreOffice executable ultimately leads to soffice.bin are some such thing and had never seen a scalc at all ...
~/src1/org-mode-ox-odt/master$ sca
scalar     scanimage  

~/src1/org-mode-ox-odt/master$ scalc
Command 'scalc' not found, did you mean:
  command 'kcalc' from snap kcalc (23.04.3)
  command 'calc' from deb calc
  command 'xcalc' from deb x11-apps
  command 'wcalc' from deb wcalc
  command 'lcalc' from deb lcalc
  command 'kcalc' from deb kcalc
  command 'scafc' from deb pd-scaf
  command 'scale' from deb csound-utils
See 'snap info <snapname>' for additional versions.

~/src1/org-mode-ox-odt/master$ lsb_release -a
No LSB modules are available.
Distributor ID:   Debian
Description:  Debian GNU/Linux trixie/sid
Release:  n/a
Codename: trixie

~/src1/org-mode-ox-odt/master$ uname -a
Linux debian 6.4.0-3-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.4.11-1 (2023-08-17) x86_64 GNU/Linux
Since both of us are using LibreOffice, our efforts are in sync.

I am surprised that there is a package called libreoffice-calc. I usually

I don't know how to change the title of an issue I opened,

Next to the title of the issue, you will see a green colored Edit button. I have changed the title of the issue already.

I opened only one because for me the goal was to obtain and ods document with various tables and VLOOKUP.

I feel that version of remote() calls that occur in conjunction with VLOOKUP calls are more useful than the plain version of remote() which copies over a cell or a column or row vector.

I was picking on this aspect: There was VLOOKUP on ODS side, but no counterpart on the TBLFM side. When you are cooking up the example, I can work on hacking.

(I making things up as I go. So, it will take effort for me to cookup good and simple TBLFMs. )

But if you prefer I open one issue for exporting several tables to one ods with several sheets and another one.

This is done. There is no support for remote.

I have evaluated the effort, and it requires some care to get it right. I will take the support for remote separately.

The @# and @$ construct seems to be used commonly with remote() calls, and so my earlier suggestion of translating in to @# and @$ are wrong. In other words, the first two entries in the below mapper is wrong. The mapping was created as part of commit 2476271. (Me making that suggestion, is an indicator that I was quickly making up things, just as an expedience)
(defvar org-ods-calc-f->ods-f-alist
  '(("$#" . "COLUMN()")
    ("@#" . "ROW()")
    ("vmax" . "MAX")
    ("vmean" . "AVG")
    ("vmin" . "MIN")
    ("vsum" . "SUM")))
As a programmer, something about remote() smells. Anyways ... the multi-sheet support is now supported

To get this

do this
#+TITLE: ODS export: Demo of multi-sheet spreadsheets

When exporting to spreadsheet, non-table contents are ignored.  When
you export this ~Org~ file, you will get a 2-sheet ODS file.

#+NAME: table1
| Smith  | 10 | 100 |
| Miller | 20 | 200 |
| Wick   | 30 | 300 |
#+TBLFM: $3=10*$2::

#+NAME: table2
| Smith  | -1 | -100 |
| Miller | -2 | -200 |
| Wick   | -3 | -300 |
#+TBLFM: $3=100*$2::

thanks I will pull. compile and install. I presume it will take me some time to get things to work, since you merged an official org-mode in which has some changes that break backwards compatiblity, last time I checked (you are not to blame of course) just one question, so the to-ods export command will now export all tables into different sheet, or

did you introduce a new command
or, a new variable (export to many sheets) that can be set to t or nil.

Now to the remote call issue

Several things before I proceed there (issue 257)

I used for ODS VLOOKUP, because that is what somebody suggested on the mailing list and it worked, may there are other commands, that do the same and would be easier to deal with for the export.
the remote call in org. Another confession: although in the last years I collected columns from other tables in org mode, I did it with a 3rd party package orgtbl-aggregate (which has a simple syntax for the user so no @@# syntax in the call. However, I did not want to mention it here, since it is another complication), so I looked up, and found that remote could do the job, I copied an example that include the indentity command for no good reason. I am not sure whether one could use other org commands. As I understand it would be rather difficult for the moment, the exporter could produce an ods document with links between different tables (remote or VLOOKUP or whatsoever).

did you introduce a new command

No new command. Just do a C-c C-e etc ANYWHERE in the file; ALL tables will be exported to a SINGLE ODS

1.

Now to the remote call issue

Several things before I proceed there (issue 257)

1.

I used for ODS VLOOKUP,

What is the counterpart to VLOOKUP in 'Org"?

See https://orgmode.org/worg/org-tutorials/org-lookups.html

1.

1.
I collected columns from other tables in org mode, I did it with a
3rd party package orgtbl-aggregate (which has a simple syntax for the user so no @@# syntax in the call.

I am forcing you to talk about orgtbl-aggregate ...

My question is how do you get a horizontal monster with that package. The table is horizontally spliced so that it is being unwieldily wider ... and what puzzles me is this ... who makes the aggregated table grow in HRORIZONTAL direction. There is missing ingredient in your recipe and I believe it is a 'colview' in there somewhere. (If you dont mind, you may consider opening a new discussion on github, where you can share how you make use of agggregate package.

That said ...

I have looked at that aggregate package.

The counterpart to aggregator in the ESS/R world is a 'group by' and 'summarize'. In excel side, they are called pivot tables with the groups created by the pivot table expanded / collapsed by clicking on outlining "+" or "-" you used in video conference ... I recommend that you watch couple of videos on pivot tables on YouTube

An aggregator converts a vector to a scalar. It is a "statistic" on the "sample". sum, min, max, median, mode, count / frequency, sd, var etc .

The "sum" formula in your last col of your example is THE aggregator ... and you also do a sum of sums ...

'remote' is NOT an aggregator ... it is a COPIER, so to speak.

In the @@#$2 variation and in @2$$# variation it copies a cell (To appreciate this comment, mentally replace '@#' with a fixed number say 5 and you will see @5$2. This is an Org cell reference. Likewise replace $# with a number say 8, and you will see @2$8 which is again an Org field)

In the worg tutorial I mentioned, 'remote' copies a sub-matrix. You are copying a sub-matrix (as opposed to a scalar value)

'remote" ====> COPY CELL or SUBMATRIX

'org-lookup ...' yields the COL / ROW the indexer for the BIG MATRIX; the row / col index is passed to 'remote' for yielding a SUBMATRIX.

I recommend that you spend some time doing some reading ... The tutorial link I posted above, and pivot tables ...

1.

did you introduce a new command No new command. Just do a C-c C-e etc ANYWHERE in the file; ALL tables will be exported to a SINGLE ODS

Hm, but I have also documents that contains several (auxiliary tables) but I only want to export one, which then send. In order to avoid the export of the others, I have to delete the titles of the auxiliary tables? Would it be possible to introduce a variable (export-all-tables)? If this lead to complications that don't do it.

Now to the remote call issue Several things before I proceed there (issue 257) 1. I used for ODS VLOOKUP, What is the counterpart to VLOOKUP in 'Org"? See https://orgmode.org/worg/org-tutorials/org-lookups.html Frankly I don't know. I tried first to use the org lookup commands you cite, but did not obtain what I wanted. An old example with remote (and identity) worked so I used it.

I will ask on the org mode list later. But just in this moment I received email from the ods mailing list, providing me with an ods document and various sheets obtaining the same as I did with VLOOKUP up using other commands. I prefer to clarify this with this author and then come back to you

I collected columns from other tables in org mode, I did it with a 3rd party package orgtbl-aggregate (which has a simple syntax for the user so no @@# syntax in the call. I am forcing you to talk about orgtbl-aggregate ... My question is how do you get a horizontal monster with that package. The table is horizontally spliced so that it is being unwieldily wider ... and what puzzles me is this ... who makes the aggregated table grow in HRORIZONTAL direction. There is missing ingredient in your recipe and I

ok I was a bit more complicated than that.

First step write 7 tables

use this command

#+BEGIN_SRC emacs-lisp :var t1= jcr-cuartiles t2=proy t3=conf t4=Docencia-Form t5=Formacion  t6=Estancia t7=Idiomas t8=Aneca
(cl-mapcar #'append t1 t2 t3 t4 t5 t6 t7 t8)
#+END_SRC

then to the result add a table name,

#+Name: total

then use

#+Name: propuesta-pad
#+BEGIN: aggregate :table total :cols " Nombre  Q1  PesoQ1  Q2  PesoQ2  Q3  PesoQ3  PubRes  PubScl..3       ProyNac  PesoPrNac  ProyEu  PesoEU  ProyAut  PesoAut  ProyTot  ProySca..1       CongInter  PesoCongInter  CongNac  PesoCongNac  Conf  PesoConf  Sem  PesoSem  Conftot  ConfSca..1  InvTot..3       Horas  PesoHoras  NumForm  PesoForm  NumResto  PesoResto  DocTot  DocSca..2       PremDoc..0.5  DocInt..0.5  FPU..0.5  Ayud..0.5  PostDoc..1  ActProf..0.5  FormTot  FormTotSca..2       EstMenos  PesoMenos  EstMas  PesoMas  EstTot  EstTot..2       Grad  PesoGrad  Master  PesoMaster  Idiomas  RestoGrad  RestoGradSca..1.5       Aneca"
#+END:

and voila I think best is I wait for the answer of the ods author and will later as on the org mailing list

kjambunathan / org-mode-ox-odt

Export single `org` file with multiple tables to a single `ods` file mutliple sheets. #256

+TBLFM: $4='(identity remote(table1,@@#$8))::$5='(identity remote(table2,@@#$8))::$6=vsum($3..$5);f2