Create an `Excel’ / `LibreOffice Calc’ Spreadsheet from an `Org’ spreadsheet


Introduction: A word about Org mode‘s spreadsheet capabilities

Power users of Emacs Org mode swear by its Spreadsheet capabilities. I know of dons in universities who use the feature to score their students in exams. This example in the Org Reference Manual suggests how one may prepare mark cards with Org spreadsheets.

As far as I know, there wasn’t an ability to export an Org Spreadhseet to a Calc or Excel Spreadsheet. The situation has changed.

Breaking News: You can now export an Org Spreadsheet to a Microsoft Excel or LibreOffice Calc spreadsheets

Now, you can export an Org spreadsheet—an Org table with TBLFM lines—to LibreOffice Calc and XLSX spreadsheets. When I say spreadsheet, I mean a real spreadsheet with real Calc and Excel formulas.

Test driving the ox-ods.el: OpenDocument Spreadsheet exporter

The enhaced ODT exporter now supports export to a OpenDocument Spreadsheet format through the ox-ods.el library.

IMPORTANT: The ox-ods.el library depends on the enhanced  ox-odt.el library, and this means that you should NOT be using the ox-odt.el that comes with your Emacs installation, but instead use the ox-odt.el from this third-party repository.

    1. Copy the following Org snippet to a file, say revenue.org.
      #+ODT_PREFERRED_OUTPUT_FORMAT: xlsx
      
      #+NAME: revenue
      #+CAPTION: Quarterly Revenue
      |       | Jan | Feb | Mar | Sum |
      |-------+-----+-----+-----+-----|
      | North | 5.1 | 1.7 | 5.6 |     |
      | South | 7.6 | 3.7 | 0.4 |     |
      | East  | 9.5 | 0.4 | 9.6 |     |
      | West  | 4.8 | 5.7 | 5.9 |     |
      |-------+-----+-----+-----+-----|
      | Sum   |     |     |     |     |
      #+TBLFM: $5=vsum($2..$4)::@6$2..@6$4=vsum(@I..@II)::@>
    2. Put your cursor within the table, and do C-c C-e o S.

      Export Dispatcher has a menu entry for exporting to
      a ODS (OpenDocument Spreadsheet) file

      formula-1-export-dispatcher

    3. You will get an XLSX spreadsheet with a working spreadsheet formula.

      An XLSX spreadsheet with a real formula cell

      formula-2-org-and-ods-spreadsheet


       

Parting words

I have test driven this feature simple tables. I am happy with the results.

The exporter is a bit rough on the edges. For example, the exporter gets the grid lines in my demo example wrong. There could be other things that are broken. So, exercise caution befor your repose full faith on this new exporter, particularly, if you maintain your finances or your small-business accounts with Org.

I don’t expect the new spreadsheet exporter to support the exotic Emacs Calc or Emacs Lisp incantations.

I am a simple user. And my needs are simple.

I am extremely happy that the new ODS exporter can get the basic high-school Math—you know addition, subtraction, multiplication and division—right.

The information about the new Spreadsheet export feature is scattered in this Github Issue. If you are interested, you can consult the issue page for more information.

As an aside, if you use unknown Emacs Lisp libraries, the librarties that do not make their ways in to ELPAs and MELPAs, I suggest that you watch the original repositories closely. You would be surprised to discover gems which never hit popular discourse.

Categories gnu

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close