Is ODS out of scope?

[matt-dray]

Hi, to my knowledge there's no analogue of {openxlsx2} that can write a styled wbWorkbook-like object to an ODS file. So:

1. Would it be out of scope for {openxlsx2} to have a wb_save() variant or argument that writes to ODS? I realise this is a nontrivial feature and that the package name literally contains 'xlsx' and not 'ods'.
2. Do you know of any other R package that can write styled wbWorkbook-like objects to ODS files, or that allows for programmatic conversion of an xlsx file to an ODS file? I'm aware of {readODS} and {tidyods} for reading and writing simple ODS files without styling.

For context, I've developed a package called {a11ytables}, whose goal is to create best-practice accessible spreadsheets for publication by the UK government. It's built on {openxlsx} but I'm considering a switch to {openxlsx2}. Our central analytical organisation encourages spreadsheets to be published as ODS, so users of {a11ytables} must manually convert their xlsx output to ODS as things stand.

[JanMarvin]

Hi @matt-dray , yes at the moment ods is out of scope. I started openxlsx2 because of buisness requirements and I have not encountered any ods file outside of my own limited libreoffice experiments. Researching one zipped XML format is enough for me at the moment, but it should be possible to use the tools openxlsx2 is built upon to read, modify and create ods files. And if someone wants to provide a pull request or presumably a series of pull requests we will of course consider it.

As a workaround I assume that you can write a wrapper function to convert XLSX files to ods files via headless libreoffice. But I'm not sure how well this conversion works. Libreoffice still lacks support for certain Excel specific openxml features (I guess form control and sliders iirc).

[matt-dray]

That's great, thank you for the swift response. I appreciate the challenges of working with just one zipped XML format 😅. This may be something I can look into in future and I will keep you updated if so.

Thank you for all your efforts on {openxlsx2}.

[JanMarvin]

The following works on my Arch Linux machine. This uses LibreOffice's headless mode to convert xlsx to ods. The output looks identical in libreoffice, I have not checked it with other spreadsheet programs. You might have to add a path to the binary file if LibreOffice is not in your $PATH.

While I'm glad that your civil service agency would like to see ods files, I've personally given up my hopes for wider distribution and use of ods a long time ago, and at the same time wonder why official data is still shared in xlsx/xlsm(!) files instead of open web formats like sdmx. German authorities like to do that too, like the Federal Employment Agency for example.

Anyway, although my impression as a Linux user and open source advocate at the time was that the entire standardization process of office open xml (ooxml) was manipulated, after almost two decades there is a clear winner in the ooxml vs. open document format race. The entire business world is still largely based on Excel and will remain so, so supporting yet another largely unused open standard unfortunately seems like a waste of time. Perhaps things would be different if ooxml could not be processed by the appropriate office programs, but that is also a negligible problem these days.

library(openxlsx2)

tmp <- temp_xlsx()
tmp_dir <- tempdir()

# create a slightly styled workbook
wb_workbook() %>% 
  wb_add_worksheet() %>%
  wb_add_data(x = iris) %>% 
  wb_add_border(dims = "A1:E1") %>% 
  wb_add_fill(dims = "A1:E1", color = wb_color("gray")) %>% 
  wb_save(tmp)

# convert xlsx output to ods
system2("soffice", sprintf("--headless --convert-to ods %s --outdir %s", tmp, tmp_dir))

[matt-dray]

That's very helpful and I appreciate the insight. I believe it's possible for most of our users to install LibreOffice on their corporate machines, so the headless approach is viable and preferable to doing nothing.