Add a command to convert an Org table to xlsx

[kjambunathan]

Add a command to convert an Org table to xlsx

See

• [O] orgtable-->csv--> excel coding problems UTF8

• [O] org-table export to ods/xlsx etc

Helpful links

• Filter Options - Apache OpenOffice Wiki
• Is it possible to specify CSV filter options on the command line? [closed] - Ask LibreOffice

[kjambunathan]

See

[O] orgtable-->csv--> excel coding problems UTF8

[O] org-table export to ods/xlsx etc

Hi

Consider

| Name   | Value |
| nación |    10 |

The small function I wrote

(defun org-export-to-xlsx () 
  (interactive)
  (let* ((source-file  (file-name-sans-extension (buffer-file-name 
(current-buffer))))
         (csv-file (concat source-file ".csv")))
    (org-table-export csv-file "orgtbl-to-csv")
    (org-odt-convert csv-file "xlsx")))

Converts to an excel file, when opened with LO has the wrong coding, the same happened to ods. I still can use gnumeric with a different lisp function, but I am puzzled. Any idea?

Uwe Brauer

---

I am sometimes in need to send my org-table spreadhseets to colleagues who are using (sigh) excel. I thought sending csv would be enough but it turns out,

-  that sometimes technical inapt people don't know how to open it,

-  when using a non english language setting say spanish, numbers
   like 3.4 are interpreted as text.

That can be circumvented by sending the spreadheet as ods/xlsx

I did not find any built in function so I came up with two solutions. One is using gnumeric but then a path must be set to its binaries and that is platform dependent. The other relies on `org-odt-convert' which uses in my case LO/OO.

Somebody with better lisp skills might want to generalize the function to include in a more comfortable way other formats

The report doesn't say what

• the encoding of the input csv file is and
• what is the default encoding for the libreoffice installation

Both the above settings have a bearing on the issue.

Here is an attemp to recreate the issue and suggest a fix for it.

The file below is in Windows-1251 encoding, and is in Russian.

x.csv

The LibreOffice installation I uses is for English (India)

Here is a sample conversion with the default options and with input encoding explicitly specified

$  cp x.csv x1.csv

$  rm x.xlsx 

$  libreofficedev7.2 --headless --convert-to xlsx x.csv
convert /home/kjambunathan/Downloads/junk/test/1/x.csv -> /home/kjambunathan/Downloads/junk/test/1/x.xlsx using filter : Calc Office Open XML

$  libreofficedev7.2 --headless --convert-to xlsx --infilter=CSV:44,34,34 x1.csv 
convert /home/kjambunathan/Downloads/junk/test/1/x1.csv -> /home/kjambunathan/Downloads/junk/test/1/x1.xlsx using filter : Calc Office Open XML

Here is the result with default encoding --- note that the output is garbled.

Here is the correct output.

Note that the corresponding command line specifies input encoding of 34, the third of the filter options, which stands for Windows-1251 (Cyrillic) according to Filter Options - Apache OpenOffice Wiki. See https://github.com/kjambunathan/org-mode-ox-odt/issues/94#issuecomment-881846106 for how one can use LibreOffice's macro facility for identifying the integer value to use for a specific character set / encoding.

Long story short, use the infilter command line option to specify input encoding of a file.

~$ libreofficedev7.2 --help

   --infilter={filter} Force an input filter type if possible. For example:    
                       --infilter="Calc Office Open XML"                     
                       --infilter="Text (encoded):UTF8,LF,,,"                

The suggested fix is based on the following bug#36313 – CLI: Encoding issue when Converting documents: esp. UTF-8 in headless mode

[kjambunathan]

Long story short, use the infilter command line option to specify input encoding of a file.

Forgot to cc you @ouboub. See https://github.com/kjambunathan/org-mode-ox-odt/issues/10#issuecomment-881891375.

Essentially, there should be a way to specify additional options--both infilter and outfilter options--to org-odt-convert command line. These options are most likely to be chosen on an ad-hoc basis rather than on a system-wide or installation-wide or on permanent basis. For example, in the outfilter case of xlsx-->csv the ability to export the formula verbatim is an ad-hoc conversion that is likely to be less frequent, and more a niche usage.

Btw, I am really not sure how gnumeric or whatever other command line converter that you are using deal with encoding issues.