Re: export org table to other formats (gnumeric or scalc or xlsx)

[Tim Cross <theophilusx@gmail.com>]

Uwe Brauer <oub@mat.ucm.es> writes:

> Hi
>
> A couple of days ago I asked about importing excel formula into org
> tables, and they only ways seems to do it manually.
>
> I just realised that I need it also the other way around, exporting  to
> some spreadsheet format, like gnumerica or scalc or xlsx.
>
> But that look equally difficult, isn't. 
>
> Some hints would be welcome.
>

The big problem here is that there is no single format understood by all
these different programs which you can use. While CSV works OK for data,
it does not support formulas and other meta data. In particular,
translating formulas is a real challenge.

I went down this rabbit hole some years back i.e. having a workflow
which allowed me to interact with others who used Excel and allowing me
to use org mode.

It took hours and hours of additional work and never worked reliably
because

- I never found a way of 'exporting' to a format which could be imported
  by Excel and included formulas

- None of the Excel export formats support full export of Excel -
  especially at the meta data level i.e. Visual Basic macros and other
  'objects'. Workbooks were a real pain.

- Constantly having to do 'hand tweaking' to fix formulas and other
  'meta' information (both directions). When exporting to Excel, I would
  have to open the spreadsheet in another program to 'clean it up'
  before sending it to colleagues.  

 - Too many different Excel versions. This issue may not be as bad now,
   but back then, there were multiple xlsx versions and you would get
   different results depending on the version.

In the end, I gave up and either just used LibreOffice (Linux) or an OSX
program (I think it was called numerics, but too long ago to recall
accurately - it was not an Apple program). In the end, my decision was
to only use org if either I only needed the data (possibly adding
formulas manually), would not need to export back to Excel (no
collaboration) and only need export to org once. Otherwise, use
LibreOffice or another program able to understand xlsx natively.

One thing which we did use for a time was to connect the excel
spreadsheets with a database. The excel spreadsheet would pull the data
from the database and apply formulas/macros to that data. I was then
able to pull data from the database into org and then export it back
into the database. It took a bit to setup - used Visual Basic to manage
data import/export into database and needed an Excel based UI to do some
CRUD operations and then some scripts on the Linux side to
extract/update the data from org. This sort of worked, but had issues
with synchronisation of data. It really needed a much more sophisticated
database API to make it work well which could handle versioning or
resolve data conflicts and dependencies etc.

To some extent, I guess Excel is a good example of what RMS was
concerned about and the problem with closed proprietary standards. While
someone can reverse engineer the formats and spend the time to develop
converters, they remain at the mercy of MS who can simply change the
formatting at their whim.  Most efforts I've seen seem to run out of
puff because of the efforts needed to maintain things. 

Of course, life would probably be better if so many project managers
stopped using Excel for EVERYTHING! I've noticed over the last 10+ years
a growing use of Excel in the 'enterprise' as the default 'tool' to
collect and manage information. To many, it seems like a simple
solution, but in reality, you end up with all sorts of valuable
information floating around in multiple spreadsheets and spend far too
much time entering/updating data using a crappy interface and tweaking
format commands. I've seen more than one project almost hit the rocks
because someone was working off an old excel spreadsheet with the wrong
data. If you raise this concern, the likely outcome is a decision to use
MS Project, then your really stuffed!



-- 
Tim Cross