Add support for exporting a tables with TBLFMs to ODS spreadsheet

[kjambunathan]

Cc @ouboub

Thanks, I attach a zip file with a README file that explains the purpose of each file
HG-CVS-Formular.zip

See also #94

[kjambunathan]

Cc @ouboub

Just wanted to let you know that exporting of simple Table with TBLFMs to ODS is feasible. You will see commits on this issue. You can ignore the commits until you hear from me. Once the feature is complete I will share my Unit Test files.

[kjambunathan]

https://github.com/kjambunathan/org-mode-ox-odt/files/9099809/HG-CVS-Formular.zip

I am able to successfully convert the 2 tables you attached. Let me know if you face any issues.

Instructions

1. In the below file, put the cursor within a table
2. Do M-x org-ods-translate. It will insert a table with ODS formula inline.
3. In any table-be it the original simple table or the translated table-, do org-ods-convert to export that file to any of ODS formats--xlsx, ods, html etc etc. Use C-u org-ods-convert to open the XLSX, ODS file in your default app, presumable LibreOffice.
4. Done.

Input .org file

#+NAME: table1
#+CAPTION: Table 1
| Student  | Mark1 | Mark2 | Mark3 | Result | Result-simple | ResultSkaled |
|----------+-------+-------+-------+--------+---------------+--------------|
| Student1 |     2 |     3 |     4 |      9 |             9 |          900 |
| Student2 |     4 |     2 |     1 |      7 |             7 |          700 |
#+TBLFM: $5=vsum($2..$4)::$6=$2+$3+$4::$7=vsum($2..$4)*100

#+NAME: table2
#+CAPTION: Table 2
| Account      |     |
|--------------+-----|
| Account1     | 200 |
| Account1     | 300 |
|--------------+-----|
| All accounts | 500 |
#+TBLFM: @4$2=vsum(@I..@II)

#+NAME: table3
#+CAPTION: Table 3
| First |   |     |
|-------+---+-----|
|     3 | 6 |   9 |
|     1 | 3 |   4 |
|     1 | 2 |   3 |
|     8 | 9 |  17 |
|   100 | 1 | 101 |
#+TBLFM: $3=vsum($1..$2);f-2::@2$3=vsum(@2$1..@2$2)

#+NAME: table4
#+CAPTON: Table 4
|---+---------+--------+--------+--------+-------+------|
|   | Student | Prob 1 | Prob 2 | Prob 3 | Total | Note |
|---+---------+--------+--------+--------+-------+------|
| ! |         |     P1 |     P2 |     P3 |   Tot |      |
| # | Maximum |     10 |     15 |     25 |    50 | 10.0 |
| ^ |         |     m1 |     m2 |     m3 |    mt |      |
|---+---------+--------+--------+--------+-------+------|
| # | Peter   |     10 |      8 |     23 |    41 |  8.2 |
| # | Sam     |      2 |      4 |      3 |     9 |  1.8 |
|---+---------+--------+--------+--------+-------+------|
|   | Average |        |        |        |  25.0 |      |
| ^ |         |        |        |        |    at |      |
| $ | max=50  |        |        |        |       |      |
|---+---------+--------+--------+--------+-------+------|
#+TBLFM: $6=vsum($P1..$P3)::$7=10*$Tot/$max;%.1f


# #+NAME: table5
# #+CAPTON: Table 5
# |---+---------+--------+--------+--------+-------+------|
# |   | Student | Prob 1 | Prob 2 | Prob 3 | Total | Note |
# |---+---------+--------+--------+--------+-------+------|
# | ! |         |     P1 |     P2 |     P3 |   Tot |      |
# | # | Maximum |     10 |     15 |     25 |    50 | 10.0 |
# | ^ |         |     m1 |     m2 |     m3 |    mt |      |
# |---+---------+--------+--------+--------+-------+------|
# | # | Peter   |     10 |      8 |     23 |    41 |  8.2 |
# | # | Sam     |      2 |      4 |      3 |     9 |  1.8 |
# |---+---------+--------+--------+--------+-------+------|
# |   | Average |        |        |        |  25.0 |      |
# | ^ |         |        |        |        |    at |      |
# | $ | max=50  |        |        |        |       |      |
# |---+---------+--------+--------+--------+-------+------|
# #+TBLFM: $6=vsum($P1..$P3)::$7=10*$Tot/$max;%.1f::$at=vmean(@-II..@-I);%.1f

Output org file after org-ods-translate on each of the above tables

#+NAME: table1
#+CAPTION: Table 1
| Student  | Mark1 | Mark2 | Mark3 | Result | Result-simple | ResultSkaled |
|----------+-------+-------+-------+--------+---------------+--------------|
| Student1 |     2 |     3 |     4 |      9 |             9 |          900 |
| Student2 |     4 |     2 |     1 |      7 |             7 |          700 |
#+TBLFM: $5=vsum($2..$4)::$6=$2+$3+$4::$7=vsum($2..$4)*100


#+NAME: table1
#+CAPTION: Table 1
| Student  | Mark1 | Mark2 | Mark3 | Result      | Result-simple | ResultSkaled    |
|----------+-------+-------+-------+-------------+---------------+-----------------|
| Student1 |     2 |     3 |     4 | =SUM(B2:D2) | =B2+C2+D2     | =SUM(B2:D2)*100 |
| Student2 |     4 |     2 |     1 | =SUM(B3:D3) | =B3+C3+D3     | =SUM(B3:D3)*100 |



#+NAME: table2
#+CAPTION: Table 2
| Account      |     |
|--------------+-----|
| Account1     | 200 |
| Account1     | 300 |
|--------------+-----|
| All accounts | 500 |
#+TBLFM: @4$2=vsum(@I..@II)


#+NAME: table2
#+CAPTION: Table 2
| Account      |             |
|--------------+-------------|
| Account1     |         200 |
| Account1     |         300 |
|--------------+-------------|
| All accounts | =SUM(B2:B3) |


#+NAME: table2
#+CAPTION: Table 2
| Account      |             |
|--------------+-------------|
| Account1     |         200 |
| Account1     |         300 |
|--------------+-------------|
| All accounts | =SUM(B2:B3) |





#+NAME: table3
#+CAPTION: Table 3
| First |   |     |
|-------+---+-----|
|     3 | 6 |   9 |
|     1 | 3 |   4 |
|     1 | 2 |   3 |
|     8 | 9 |  17 |
|   100 | 1 | 101 |
#+TBLFM: $3=vsum($1..$2);f-2::@2$3=vsum(@2$1..@2$2)


#+NAME: table3
#+CAPTION: Table 3
| First |   |             |
|-------+---+-------------|
|     3 | 6 | =SUM(A2:B2) |
|     1 | 3 | =SUM(A3:B3) |
|     1 | 2 | =SUM(A4:B4) |
|     8 | 9 | =SUM(A5:B5) |
|   100 | 1 | =SUM(A6:B6) |



#+NAME: table4
#+CAPTON: Table 4
|---+---------+--------+--------+--------+-------+------|
|   | Student | Prob 1 | Prob 2 | Prob 3 | Total | Note |
|---+---------+--------+--------+--------+-------+------|
| ! |         |     P1 |     P2 |     P3 |   Tot |      |
| # | Maximum |     10 |     15 |     25 |    50 | 10.0 |
| ^ |         |     m1 |     m2 |     m3 |    mt |      |
|---+---------+--------+--------+--------+-------+------|
| # | Peter   |     10 |      8 |     23 |    41 |  8.2 |
| # | Sam     |      2 |      4 |      3 |     9 |  1.8 |
|---+---------+--------+--------+--------+-------+------|
|   | Average |        |        |        |  25.0 |      |
| ^ |         |        |        |        |    at |      |
| $ | max=50  |        |        |        |       |      |
|---+---------+--------+--------+--------+-------+------|
#+TBLFM: $6=vsum($P1..$P3)::$7=10*$Tot/$max;%.1f


|---------+--------+--------+--------+-------------+-----------|
| Student | Prob 1 | Prob 2 | Prob 3 | Total       | Note      |
|---------+--------+--------+--------+-------------+-----------|
| Maximum |     10 |     15 |     25 | =SUM(B2:D2) | =10*E2/50 |
|---------+--------+--------+--------+-------------+-----------|
| Peter   |     10 |      8 |     23 | =SUM(B3:D3) | =10*E3/50 |
| Sam     |      2 |      4 |      3 | =SUM(B4:D4) | =10*E4/50 |
|---------+--------+--------+--------+-------------+-----------|
| Average |        |        |        | =SUM(B5:D5) |           |
|---------+--------+--------+--------+-------------+-----------|




# #+NAME: table5
# #+CAPTON: Table 5
# |---+---------+--------+--------+--------+-------+------|
# |   | Student | Prob 1 | Prob 2 | Prob 3 | Total | Note |
# |---+---------+--------+--------+--------+-------+------|
# | ! |         |     P1 |     P2 |     P3 |   Tot |      |
# | # | Maximum |     10 |     15 |     25 |    50 | 10.0 |
# | ^ |         |     m1 |     m2 |     m3 |    mt |      |
# |---+---------+--------+--------+--------+-------+------|
# | # | Peter   |     10 |      8 |     23 |    41 |  8.2 |
# | # | Sam     |      2 |      4 |      3 |     9 |  1.8 |
# |---+---------+--------+--------+--------+-------+------|
# |   | Average |        |        |        |  25.0 |      |
# | ^ |         |        |        |        |    at |      |
# | $ | max=50  |        |        |        |       |      |
# |---+---------+--------+--------+--------+-------+------|
# #+TBLFM: $6=vsum($P1..$P3)::$7=10*$Tot/$max;%.1f::$at=vmean(@-II..@-I);%.1f

Sample console ouput

Wrote /tmp/org-ods-1Uj8Cz.csv
Executing soffice --headless --convert-to xlsx --outdir /tmp/ /tmp/org-ods-1Uj8Cz.csv
convert /tmp/org-ods-1Uj8Cz.csv -> /tmp/org-ods-1Uj8Cz.xlsx using filter : Calc Office Open XML

Exported to /tmp/org-ods-1Uj8Cz.xlsx [2 times]
Copying /tmp/org-ods-1Uj8Cz.xlsx to /home/kjambunathan/src1/org-mode-ox-odt/testing/examples/odt/export-to-ods/tblfms-to-ods-output:table1.xlsx
Wrote /tmp/org-ods-zdjFLI.csv
Executing soffice --headless --convert-to ods --outdir /tmp/ /tmp/org-ods-zdjFLI.csv
convert /tmp/org-ods-zdjFLI.csv -> /tmp/org-ods-zdjFLI.ods using filter : calc8

Exported to /tmp/org-ods-zdjFLI.ods [2 times]
Copying /tmp/org-ods-zdjFLI.ods to /home/kjambunathan/src1/org-mode-ox-odt/testing/examples/odt/export-to-ods/tblfms-to-ods-output:table1.ods

[kjambunathan]

Instruction are there at #197 (comment)

Unit test files, with input and output org table, and the xlsx and ods version of the above files.

export-to-ods.zip

[kjambunathan]

`spread-orginal:.ods' is this a feature or a minor bug?

An org file can have many tables. So, .. it tries to uniquify the table / spreadsheet by using its NAME or CAPTION. In your case, the table you are exporting has no name or caption. I recommend that you add a NAME or CAPTION your tables. (I have replaced : by #, and also fixed the "off-by-one` bug)

I solve this issue by exporting my org-table to csv, set scalc to German
as a default language, and then import the csv file, using American
English.

Show me the CLI that does org -> csv -> scalc -> csv -> org. I want to specifically see if you are setting any of the locale specifc LC types in command line. How do you deal with dates? I believe spanish or german dates may be like en_UK, which is definitely different from en_US. Show me the output of your locale command.

I think it will be useful to add a command to import an ods file in to org, by first exporting it to csv etc.

[ouboub]

I attach a zip file that ontains a README file, an example org file and various results I obtain when I export it to ods following different strategies
Test-Formats.zip
.

[kjambunathan]

Making a note for future use.

(defun my-toggle-org-date-format ()
  "This function allows use English (US) or German or Spanish date format."
  (interactive)
  (with-output-to-temp-buffer "local-set-buffer"
    (princ "List of functions\n")
    (princ "1: my-set-time-stamp-english \n")
    (princ "2: my-set-time-stamp-german \n")
    (princ "3: my-set-time-stamp-spanish \n"))
  (let ((ch (string-to-char (read-string "Which choice: 1: 2: 3: "))))
    (call-interactively (cond ((eql ch ?2) #'my-set-time-stamp-german)
                              (t (error 'args-out-of-range '(1 2 3 ch))))))
  (kill-buffer "local-set-buffer"))

(defun my-set-time-stamp-german ()
  "Set the local environment so that German dates are inserted."
  (interactive)
  (set-language-environment "German")
  (set-locale-environment "de_DE.UTF-8")
  (message "Language setting is German"))

(defun org-table-export-to-spreadsheet (arg)
  "Export org table to varios first to cvs and then via LO/OO
to various  spreadsheet  format, the most common are `ods', `xls' and
`xlsx'."
  (interactive "sFormat: ")
  (let* ((source-file (file-name-sans-extension (buffer-file-name (current-buffer))))
         (csv-file (concat source-file ".csv")))
    (org-table-export csv-file "my-tbl-to-csv")
    (org-odt-convert csv-file arg)))

(defun my-tbl-to-csv (table params)
  (orgtbl-to-csv table
                 (org-combine-plists '(:fmt my-format-timestamps) params)))

(defun my-format-timestamps (cell)
  (org-quote-csv-field
   (replace-regexp-in-string
    org-ts-regexp-both
    (lambda (m)
      (format-time-string
       (let ((hours? (string-match-p "[0-9]+:[0-9]+" m)))
         (funcall (if hours? #'cdr #'car) org-time-stamp-custom-formats))
       (apply #'encode-time (save-match-data (org-parse-time-string m)))))
    cell)))

(setq org-odt-convert-process "gnumeric")
(setq org-odt-convert-processes '(("gnumeric" "/usr/bin/ssconvert %i %o")))

---

~/$ LANG=de_DE ssconvert --export-type=Gnumeric_stf:stf_csv german-date-german-digits-imported-via-csv.ods de_DE.csv; cat de_DE.csv

Unexpected element 'style:header-first' in state : 
	document-styles -> master-styles -> master-page
Unexpected element 'style:footer-first' in state : 
	document-styles -> master-styles -> master-page
Date,Account1,Account2
"
	      

	      	
	        20.07.2022","3,5","2,7"
"
	      

	      	
	        15.07.2022","1,2","8,7"
~/$ LANG=en_IN ssconvert --export-type=Gnumeric_stf:stf_csv german-date-german-digits-imported-via-csv.ods de_DE.csv; cat de_DE.csv

Unexpected element 'style:header-first' in state : 
	document-styles -> master-styles -> master-page
Unexpected element 'style:footer-first' in state : 
	document-styles -> master-styles -> master-page
Date,Account1,Account2
"
	      

	      	
	        20.07.2022",3.5,2.7
"
	      

	      	
	        15.07.2022",1.2,8.7

Note how the decimal separator changes from , and . depending upon the locale.

---

Look at M-x woman ssconvert for more information.

---

[kjambunathan]

From M-x woman ssconvert

OPTIONS FOR THE CONFIGURABLE TEXT (*.txt) EXPORTER

	charset
		The  character  encoding  of the  output.  Defaults  to
		UTF-8.

	locale
		The  locale  to use  for  number  and date  formatting.
		Defaults  to   the  current   locale  as   reported  by
		locale(1).   Consult locale  -a  output for  acceptable
		values.

	quote
		The    character   or    string   used    for   quoting
		fields.  Defaults  to  "\"" (quotation  mark  /  double
		quote).

	separator
		The string used to separate fields. Defaults to space.

	format
		How  cells  should  be formatted.   Acceptable  values:
		"automatic"  (apply   automatic  formatting;  default),
		"raw"  (output data  raw,  unformatted), or  "preserve"
		(preserve the formatting from the source document).

		This  deals  with  the   difference  between  a  cell's
		contents and the way those contents are formatted.

		Consider a cell  in a Gnumeric input  document that was
		input as "4/19/73" in a US locale, with a format set to
		"d-mmm-yyyy" and thus formatted as "19-Apr-1973".

		With the default format  setting of "automatic" it will
		be  output   as  "1973/04/19".  With   "preserve",  the
		formatting will be  preserved and it will  be output as
		"19-Apr-1973". With "raw" it  will be output as "26773"
		(Gnumeric's  internal  representation:  days  since  an
		epoch).

	transliterate-mode
		How  to handle  unrepresentable characters  (characters
		that  cannot  be  represented   in  the  chosen  output
		character set).  Acceptable values: "transliterate", or
		"escape".

	quoting-mode
		When  does data  need  to be  quoted?  "never",  "auto"
		(puts quotes  where needed),  or "always".  Defaults to
		"auto".

	quoting-on-whitespace
		Controls whether initial  or terminal whitespace forces
		quoting. Defaults to TRUE.

EXAMPLE
	To  convert the  Gnumeric file  foo.gnumeric to  a Microsoft
	Excel(TM) format file foo.xls:

	ssconvert foo.gnumeric foo.xls

	The export  format can be specified  explicitly, to override
	the default (which is based on the file extension):

	ssconvert --export-type=Gnumeric_stf:stf_csv foo.xls foo.txt

	To convert an Excel format file statfuns.xls to a text file,
	specifying the semicolon as the separator character:

	ssconvert         -O        'separator=;         format=raw'
	samples/excel/statfuns.xls statfuns.txt

	To export the  charts from the input  file input.gnumeric to
	numbered SVG files:

	ssconvert --export-graphs input.gnumeric 'output.%n.svg'

	To export the  charts from the input  file input.gnumeric to
	numbered PNG files:

	ssconvert  --export-type=png --export-graphs  input.gnumeric
	'output.%n.png'

[kjambunathan]

Based on LibreOffice documentation, it is sufficient to typeset date as YYYY-MM-DD (ISO 8601), which is then automatically converted to Locale specific rendering

---

From Language (Options) for LibreOffice,

Date acceptance patterns

Specifies the date acceptance patterns for the current locale. Calc spreadsheet and Writer table cell input needs to match locale dependent date acceptance patterns before it is recognised as a valid date.

If you type numbers and characters that correspond to the defined date acceptance patterns in a table cell, and then move the cursor outside of the cell, LibreOffice will automatically recognise and convert the input to a date, and format it according to the locale setting.

The initial pattern(s) in Date acceptance patterns are determined by the locale (set in Locale setting), but you can modify these default patterns, and add more patterns. Use ; to separate each pattern.

Patterns can be composed according to the following rules:

• A pattern must start with D, M, or Y, and include at least two items, with at least one separator between each one.
• A pattern may also include all three, in any order.
• Y means year, M means month, and D means day, regardless of which locale is set. Each can only be used once in a pattern.
• . - : / , can be used as separators between and after D, M, and Y.
• Any combination of separators can be used, and more than one separator can be used between D, M, and Y, but the input must match the separator pattern exactly for recognition.
• Patterns can combine different separators, and may include a trailing separator.

Examples of valid patterns are: D,Y ; Y-M ; M.D.Y ; D-M/Y ; M.D.

If you change the Locale setting, the date acceptance pattern will be reset to the new locale default, and any user-defined modifications or additions will be lost.

In addition to the explicit patterns defined in the edit box, input matching the Y-M-D pattern is implicitly recognised and converted automatically to a date. Input that starts from 1 to 31 is not interpreted with this implicit Y-M-D pattern. Since LibreOffice 3.5, this input is formatted as YYYY-MM-DD (ISO 8601).

[kjambunathan]

Some more notes ...

I have looked at your ods spreadsheets. We need to distinguish between two things

• the text that is literally in the zip file,
• the text that is rendered on screen

Think of display property in Emacs. The underlying text may not change, but the display could be different.

We shouldn't let optics fool what is underneath in the core.

[kjambunathan]

(defun my-format-timestamps (cell)
  (org-quote-csv-field
   (replace-regexp-in-string
    org-ts-regexp-both
    (lambda (m)
      (format-time-string
       (let ((hours? (string-match-p "[0-9]+:[0-9]+" m)))
         (funcall (if hours? #'cdr #'car) org-time-stamp-custom-formats))
       (apply #'encode-time (save-match-data (org-parse-time-string m)))))
    cell)))

There is some crucial piece missing here. Do you make org-time-stamp-custom-formats to be locale dependent in your init file?

For the purpose of inputtting to LibreOffice or ssconvert, this should suffice.

(defun replace-org-ts-with-locale-ts-in-string (string &optional locale)
  (let ((local-env (or locale current-locale-environment)))
    (with-locale-environment locale
      (org-quote-csv-field
       (replace-regexp-in-string
        org-ts-regexp-both
        (lambda (time-string)
          (format-time-string
           (let ((hours? (string-match-p "[0-9]+:[0-9]+" time-string)))
             (funcall (if hours? #'cdr #'car) '("%x" . "%X")))
           (apply #'encode-time (save-match-data (org-parse-time-string time-string)))))
        string)))))

*** Welcome to IELM ***  Type (describe-mode) or press C-h m for help.
ELISP> (let ((cell "There is a time stamp here <2022-07-21 Thu>"))
  (mapcar
     (lambda (locale)
       (my-format-timestamps cell locale))
     '("en_US.UTF-8"
       "de_DE.UTF-8"
       "es_ES.UTF-8")))
("There is a time stamp here 07/21/2022" "There is a time stamp here 21.07.2022" "There is a time stamp here 21/07/22")

[kjambunathan]

ssconvert does numeric conversion well. See what is happening here

$ cat tblfms-to-ods.csv 
2022-07-21,1.1

~$ LANG=de_DE ssconvert --export-type=Gnumeric_stf:stf_assistant tblfms-to-ods.csv fd://0
2022/07/21,"1,1"

~$ LANG=es_ES ssconvert --export-type=Gnumeric_stf:stf_assistant tblfms-to-ods.csv fd://0
2022/07/21,"1,1"

~$ LANG=en_IN ssconvert --export-type=Gnumeric_stf:stf_assistant tblfms-to-ods.csv fd://0
2022/07/21,1.1

But there doesn't seem to be a way to convert an ISO date to LANG specific date. (The above example uses text based export.) The story is the same for ods file.

File an enhacement report with gnumeric, and point me to the bug report https://gitlab.gnome.org/GNOME/gnumeric/-/issues (if there is a need)

[kjambunathan]

There is another enhancement request you may want to file for gnumeric and ssconvert ...

Note that it changes the date which is in German format to ISO format, and the number which is in dot format to German comma format.

What you need from ssconvert is a way to preserve the Date fields, but convert the numeric fields. There is a global format=automatic field which is insufficient for what you want to achieve, and you want the format behaviour to be per-field type.

~$ cat /tmp/org-ods-2dM7pJ.csv
Date,Account1,Account2
20.07.2022,3.5,2.7
15.07.2022,1.2,8.7

~/$ LANG=de_DE.UTF-8 ssconvert --export-type=Gnumeric_stf:stf_assistant --export-options='format=automatic' /tmp/org-ods-apPdt6.csv fd://0
Date,Account1,Account2
2022/07/20,"3,5","2,7"
2022/07/15,"1,2","8,7"

[kjambunathan]

I will roll out my spreadsheet exporter. It won't use ssconvert or LibreOffice. That is .... instead of going from org->csv>ods, it will go right from org->ods. It is probably a week's work.

What surprises me about your setup--atleast the parts you have shared with me--it appears to be incomplete. I have already pointed issues with the Emacs config you shared with me. I have already shown how ssconvert depends on LANG field to tailor its behaviour .

I can articulate what you want in technical terms ... But I don't want to overwhelm you.

Take whatever works for you.

[ouboub]

Another point, since you are working on that exporter there is another structure (still no if involved) that would be nice to have
generate a numbered row, starting from an arbitrary number, here is the org code with the tables.

|   | Stuff |
|---+-------|
| 1 | test  |
| 2 | new   |
| 3 | this  |
| 4 | hello |
#+TBLFM: $1=@#-1



|     | Table2 |
|-----+--------|
| 101 | test   |
| 102 | new    |
| 103 | this   |
| 104 | hello  |
#+TBLFM: $1=@#-1+100

if you could implement that, it would be great, if not I can do this manually without much problem, the code in ods
would be

,Table 2
101,test
=A2+1,new
=A3+1,this
=A4+1,hello

sorry to bother you again

[kjambunathan]

See https://wiki.documentfoundation.org/Documentation/Calc_Functions/ROW

…

On Thu, 21 Jul, 2022, 21:29 Uwe Brauer, ***@***.***> wrote: Another point, since you are working on that exporter there is another structure (still no if involved) that would be nice to have generate a numbered row, starting from an arbitrary number, here is the org code with the tables. | | Stuff | |---+-------| | 1 | test | | 2 | new | | 3 | this | | 4 | hello | #+TBLFM: $1=@#-1 | | Table2 | |-----+--------| | 101 | test | | 102 | new | | 103 | this | | 104 | hello | #+TBLFM: $1=@#-1+100 if you could implement that, it would be great, if not I can do this manually without much problem, the code in ods would be ,Table 2 101,test =A2+1,new =A3+1,this =A4+1,hello sorry to bother you again — Reply to this email directly, view it on GitHub <#197 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEXTBKD7E4IQKUZIPNU45BLVVFXWJANCNFSM53X2VR3A> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[kjambunathan]

I was checking if Emacs provides locale dependent NUMERIC formatting. It doesen't

There are some recent LOCALE specific threads, in the context of CSV

• LC_NUMERIC formatting [FEATURE REQUEST]
• Re: CSV parsing and other issues (Re: LC_NUMERIC)

Probably there are others ... Probably easier to spot these threads in Gnus / Gmane / Gwene, rather than on a web interface.

[kjambunathan]

You can look at the commit message for other changes.

[ouboub]

A comment

given

#+Name: org-table
| Mark1 | Mark2 |
|-------+-------|
|    13 |     7 |
|     2 |    10 |
#+TBLFM: 

#+Name: agg-table
#+BEGIN: aggregate :table "org-table" :cols "Mark1 Mark2" :formula "@1$3=ResFinal"
| Mark1 | Mark2 | ResFinal |
|-------+-------+----------|
|    13 |     7 |       20 |
|     2 |    10 |       12 |
#+TBLFM: $3=vsum($1..$2);f2::@1$3=ResFinal
#+END:

then org-ods-translate
results in

#+Name: agg-table
#+BEGIN: aggregate :table "org-table" :cols "Mark1 Mark2" :formula "@1$3=ResFinal"
| Mark1 | Mark2 | ResFinal |
|-------+-------+----------|
|    13 |     7 |       20 |
|     2 |    10 |       12 |
#+TBLFM: $3=vsum($1..$2);f2::@1$3=ResFinal


| Mark1 | Mark2 | =ResFinal   |
|-------+-------+-------------|
|    13 |     7 | =SUM(A2:B2) |
|     2 |    10 | =SUM(A3:B3) |


#+END:

that is not good, better would be

#+Name: agg-table
#+BEGIN: aggregate :table "org-table" :cols "Mark1 Mark2" :formula "@1$3=ResFinal"
| Mark1 | Mark2 | ResFinal |
|-------+-------+----------|
|    13 |     7 |       20 |
|     2 |    10 |       12 |
#+TBLFM: $3=vsum($1..$2);f2::@1$3=ResFinal
#+END:

| Mark1 | Mark2 | =ResFinal   |
|-------+-------+-------------|
|    13 |     7 | =SUM(A2:B2) |
|     2 |    10 | =SUM(A3:B3) |

any change to achive that? thanks

[kjambunathan]

I have added a ods-table dblock. Just do a C-c C-x x and answer few questions and you will get a ods view of a table wherever you want. Once the ods-table is added its just a matter of C-c C-c of refreshing the view.

See the committed changes to get started.

---

#+Name: agg-table
#+BEGIN: aggregate :table "org-table" :cols "Mark1 Mark2" :formula "@1$3=ResFinal"
| Mark1 | Mark2 | ResFinal |
|-------+-------+----------|
|    13 |     7 |       20 |
|     2 |    10 |       12 |
#+TBLFM: $3=vsum($1..$2);f2::@1$3=ResFinal

In the above table agg-table is the name of the aggregate block, and not the name of the table. I suggest that you figure out a way to attach a name to the table inside. Check if aggregate package honors a :content argument just like colview blocks. If not I suggest you file a feature request with that package, to attach an arbitrary #+ATTR_ODT and #+CAPTION: ... and #+NAME: ... from that package. See #196 (comment) for more information.

#+Name: agg-table
#+BEGIN: aggregate :table "org-table" :cols "Mark1 Mark2" :formula "@1$3=ResFinal"
#+NAME: this-is-table-name
| Mark1 | Mark2 | ResFinal |
|-------+-------+----------|
|    13 |     7 |       20 |
|     2 |    10 |       12 |
#+TBLFM: $3=vsum($1..$2);f2::@1$3=ResFinal

[ouboub]

• convert => This is the terminology that has been used FOREVER for translating between ODF formats (odt -> pdf, csv -> ods) convert uses the shell command configured in org-odt-converter-processes. The converter processes---LibreOffice or ssconvert-- don't know about org syntax, and will simply carry whatever you see in the table cell. So, if a table cell is showing numeric value, it will show as a numeric value in the ODS file.
• translate => This is the new terminology. This basically replaces formula results with the formula right in table cell. Since the table cell displays formula, the converted file will carry over the formula. And the value will be inserted by converter's own recalculation.

You should be using dynamic blocks.

1. C-x C-f test-column.org.
2. On empty paragraph, do C-c C-x x. Choose ods-table. Choose beihilfe-agg when prompted again.
3. This will insert a new ods-table dynamic block which has formulae in the cells
4. Do C-c C-c whenever you want, or run C-u C-c C-x C-u
5. You have moved TBLFMs from the bottom of the table, to ODS Formulae right within the table. Now do org-ods-convert on the table that displays formula.

(I won't repeat my instructions again. So, whatever is important to you, make a note.)

thank you for these instructions. I have already copied them to my README file.
Sorry for the hassle, and yes I was afraid that the traceback did not contain anything useful.

I will try to nail that problem down and if I find anything useful I report back.
I sometimes reply to the notification by email since this somehow works and is more comfortable, but it seems it can cause problems.

[kjambunathan]

Some more things for your README.

• org-table: You know what it is. For our current purpose it is an org table with a TBLFM line at its bottom
• ods-table: It is an intermediate representation of an org table which has a ODS formula right in the table cells. Running M-x org-ods-convert on these tables creates spreadsheets with formulas.

This is how you unit test the changes

1. emacs -Q
2. C-x C-f ox-ods.el
3. M-x byte-compile-file
4. M-x load-file RET
5. C-x C-f tblfms-to-ods.org. The unit test file is part of this repo and can be downloaded at https://raw.githubusercontent.com/kjambunathan/org-mode-ox-odt/master/testing/examples/odt/tblfms-to-ods.org.
6. Install following elisp. The command C-C C-x C-SPC clears all dynamic blocks.

(require 'org)

(defun org-clear-all-dblocks ()
  "Clear all dynamic blocks in the buffer."
  (interactive)
  (when (derived-mode-p 'org-mode)
    (org-map-dblocks 'org-clear-dblock)))

(defun org-clear-dblock ()
  "Clear the dynamic block at point."
  (interactive)
  (save-excursion
    (let* ((win (selected-window))
	   (pos (point))
	   (line (org-current-line))
	   (params))
      (org-prepare-dblock))))

(define-key org-mode-map (kbd "C-c C-x C-SPC") #'org-clear-all-dblocks)

7. Do a C-c C-x C-SPC to clear all the dynamic blocks.
8. Do a C-u C-c C-x C-u to update al the dynamic blocks.
9. Place the cursor on any on of the ods-tables and do a C-u M-x org-ods-convert RET
10. Done!

[kjambunathan]

I have closed this bug.

Org Export Dispatcher for Spreadsheet export

LibreOffice Cac Menu -> Tools -> Options

Sample spreadsheets produced by this exporter.

tblfms-to-ods#generic1.ods
tblfms-to-ods#table1.ods

Put your cursor on a table--an org' table with TBLFMlines, NOT aods' table with inline ODS formulas--, and do a C-c C-e o s or C-c C-e o S.

You will get a spreasheet that is natively produced by this exporter.

• I have noted your request for locale specific dates, and numbers and honored them.
• For some reasons, inline spans and emphasis markers don't work. I consider them as LibreOffice bug, and will open a bug with LibreOffice team and Cc you. (The ODT exporter produces ODT/ODS documents "manually" as opposed to through the "UI" and it tests LibreOffice in new ways. This is not the first time I am running in to such issues.)

[ouboub]

thanks very much!

[ouboub]

I pulled, compiled and tested, a couple of remarks

1. the table name is not taken into account, an ods file is generated that carries the same name as the file
2. if the table contains an empty line, that line is translated with an error, I attach a zip that contains, the table and the two ods file. that is not very important, but I thought you should know.
   thanks again for this brilliant code!
   ODS-Converter.zip

[kjambunathan]

#+Name: error
| this | that | result |
|------+------+--------|
|    4 |   10 |     14 |
|      |      |        |
#+TBLFM: $3=$1+$2;f2

Empty string is a corner case. The f is probably an indication that the empty string is a float, and should be considered as zero. I ignore the calc-mode specifier, for now.

This is something that I can consider for a distant future, not now

[kjambunathan]

I have added support for extra emphasis markers in ox-ods. So, you need to update not only ox-ods but also org-extra-emphasis.

My experimentation suggests that

• the foreground colours are getting carried over, but not the background colors.
• cell borders introduced with GriddedTable or the org Table rules are getting carried over
• The relative columns widths are not getting carried over.

Regarding the background color, I need to review the styles or talk to the LibreOffice team. Nevertheless ... this is a useful improvement.

To get this (modulo the column widths)

do this

#+begin_src emacs-lisp :exports resutls :results none
(require 'org-extra-emphasis)
(custom-set-faces
 '(org-extra-emphasis-01 ((t (:foreground "red"))))
 '(org-extra-emphasis-02 ((t (:foreground "green"))))
 '(org-extra-emphasis-03 ((t (:background "light green"))))
 '(org-extra-emphasis-04 ((t (:background "DarkGreen")))))
#+end_src

#+ATTR_ODT: :style "GriddedTable"
#+ATTR_ODT: :widths "1,2,4"
#+NAME: text-table
| *Qui ex culpa veniam, laboris esse magna*     | /Anim consectetur amet, eu minim/            | =Minim do aute laborum qui=                   |
|-----------------------------------------------+----------------------------------------------+-----------------------------------------------|
| Minim nostrud anim do                         | Nostrud enim velit laboris elit, aliquip eu  | Tempor do ea labore                           |
| Occaecat !!labore aliquip nisi!! exercitation | Velit !@laborum aliquip quis!@ ad incididunt | Velit in !%aliquip incididunt!% in adipiscing |

[kjambunathan]

cells with a lot of text should be best auto-wrapped. It seems I can do this later in scalc, but is there any change that exporter does this per default? Just asking ....

I have used spreadsheet in the past for creating bursary application for my sister and nephew. The tables were regular spreadsheet columns--figures from pay check, income tax forms etc--and also text fields which adds some notes to the figures.

So, wrapping of cell text is something I have needed in the past. If you have suggestions or preferences I will consider it. The suggestion shouldn't be for your specific case, but for a hypothetical user.

Ultimately, I will add support for exporting all tables in an org file to individual sheets in the spreadsheet. But this export to multiple sheets will happen slowly. And the tables that will be exported won't be simple org tables, but list tables and transcluded tables as well.

I never dreamed that I would be adding a `ods' backend. A simple nudge was all that is needed to get this feature out. It was a marathon hacking session, and I haven't done this in quite some time. So, thanks.

[ouboub]

I have added support for extra emphasis markers in ox-ods. So, you need to update not only ox-ods but also org-extra-emphasis.

My experimentation suggests that

• the foreground colours are getting carried over, but not the background colors.
• cell borders introduced with GriddedTable or the org Table rules are getting carried over
• The relative columns widths are not getting carried over.

Regarding the background color, I need to review the styles or talk to the LibreOffice team. Nevertheless ... this is a useful improvement.

To get this (modulo the column widths)

do this

#+begin_src emacs-lisp :exports resutls :results none
(require 'org-extra-emphasis)
(custom-set-faces
 '(org-extra-emphasis-01 ((t (:foreground "red"))))
 '(org-extra-emphasis-02 ((t (:foreground "green"))))
 '(org-extra-emphasis-03 ((t (:background "light green"))))
 '(org-extra-emphasis-04 ((t (:background "DarkGreen")))))
#+end_src

#+ATTR_ODT: :style "GriddedTable"
#+ATTR_ODT: :widths "1,2,4"
#+NAME: text-table
| *Qui ex culpa veniam, laboris esse magna*     | /Anim consectetur amet, eu minim/            | =Minim do aute laborum qui=                   |
|-----------------------------------------------+----------------------------------------------+-----------------------------------------------|
| Minim nostrud anim do                         | Nostrud enim velit laboris elit, aliquip eu  | Tempor do ea labore                           |
| Occaecat !!labore aliquip nisi!! exercitation | Velit !@laborum aliquip quis!@ ad incididunt | Velit in !%aliquip incididunt!% in adipiscing |

thanks! I usually pull run make clean and then make again

I will try this out later

[ouboub]

cells with a lot of text should be best auto-wrapped. It seems I can do this later in scalc, but is there any change that exporter does this per default? Just asking ....

I have used spreadsheet in the past for creating bursary application for my sister and nephew. The tables were regular spreadsheet columns--figures from pay check, income tax forms etc--and also text fields which adds some notes to the figures.

So, wrapping of cell text is something I have needed in the past. If you have suggestions or preferences I will consider it. The suggestion shouldn't be for your specific case, but for a hypothetical user.

Ultimately, I will add support for exporting all tables in an org file to individual sheets in the spreadsheet. But this export to multiple sheets will happen slowly. And the tables that will be exported won't be simple org tables, but list tables and transcluded tables as well.

I never dreamed that I would be adding a `ods' backend. A simple nudge was all that is needed to get this feature out. It was a marathon hacking session, and I haven't done this in quite some time. So, thanks.

Thanks very much.

1. as for auto-wrapping, I have to test this more, I had a longer table and then the overall experience was not what I expected: I mean some cells looked definitely better but other lines looked strange. So I have to study this more
2. as for the ods exporter: I really have to thank you for implementing it! From time to time I am forced to share ods documents either with colleagues or with my brother so having this exporter helps me a lot

[ouboub]

>> "JK" == Jambunathan K ***@***.***> writes: > | | Stuff | > |---+-------| > | 1 | test | > | 2 | new | > | 3 | this | > | 4 | hello | > #+TBLFM: $1=@#-1 Your example works with the latest commit. (Lightly tested)

Thanks I run some tests: ``` | Number | Topic | |--------+-------| | 1 | test | | 2 | new | | 3 | this | | 4 | hello | | 5 | this | #+TBLFM: $1=@#-1 ``` Is translated into ``` | Number | Topic | |----------+-------| | =ROW()-1 | test | | =ROW()-1 | new | | =ROW()-1 | this | | =ROW()-1 | hello | | =ROW()-1 | this | ``` Which is correct, thanks!

…

-- I strongly condemn Putin's war of aggression against the Ukraine. I support to deliver weapons to Ukraine's military. I support the ban of Russia from SWIFT. I support the EU membership of the Ukraine.