Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Stefan Nobis]

Jean Louis <bugs@gnu.support> writes:

> I find Org tables useful for small reports. Just as table mode is
> also useful within Emacs. Org tables are primitives that are not
> comparable to spreadsheet software.

It might be a difficult question, whether Org tables are the best
solution in a given situation or whether it is the best interchange
format to use with arbitrary people (but I also doubt that MS Excel is
a good way - I have seen really nasty and expensive problems caused by
the use of Excel to move data around).

But Org tables are very powerful and in many cases even far superior
than most other spreadsheet software (especially MS Excel - I can't
count the number of times that Excel tried to be smart and made a
total mess of my data).

Here is a short example of what is possible with Org tables:

#+begin_src org
,* Org Spreadsheet

,** Example from the Manual

See [[https://orgmode.org/manual/Advanced-features.html#Advanced-features][The 
Spreadsheet - Advanced features]].

|---+-------------+---+-----+--------------------------------------|
|   | Func        | n | x   | Result                               |
|---+-------------+---+-----+--------------------------------------|
| # | exp(x)      | 1 | x   | 1 + x                                |
| # | exp(x)      | 2 | x   | 1 + x + x^2 / 2                      |
| # | exp(x)      | 3 | x   | 1 + x + x^2 / 2 + x^3 / 6            |
| # | x^2+sqrt(x) | 2 | x=0 | x*(0.5 / 0) + x^2 (2 - 0.25 / 0) / 2 |
| # | x^2+sqrt(x) | 2 | x=1 | 2 + 2.5 x - 2.5 + 0.875 (x - 1)^2    |
| * | tan(x)      | 3 | x   | x pi / 180 + 5.72e-8 x^3 pi^3        |
|---+-------------+---+-----+--------------------------------------|
,#+TBLFM: $5=taylor($2,$4,$3);n3

,** Even symbolic math

|-------------+----------------------------+---------------------------|
| Func        | Derivative                 | Integral (over [a, b])    |
|-------------+----------------------------+---------------------------|
| x^2         | 2 x                        | x^3 / 3                   |
| exp(x^2)    | 2 x exp(x^2)               | erf(i x) sqrt(pi) / (2 i) |
| ln(x^2)     | 2 / x                      | 2 x ln(x) - 2 x           |
| sqrt(x)     | 0.5 / sqrt(x)              | 2:3 sqrt(x^3)             |
| 2x + sin(y) | 2                          | x^2 + x sin(y)            |
| sin(1/x)    | cos(1 / x) pi / (-180 x^2) | integ(sin(1 / x), x)      |
|-------------+----------------------------+---------------------------|
,#+TBLFM: $2=deriv($1, x);S::$3=integ($1, x);S

,** Combine data from different tables
,*** Some special expenses
    ,#+NAME: tab-special
    | Position         | Amount |
    |------------------+--------|
    | Abo part 1       | 299.22 |
    | Abo part 2       | 299.22 |
    | Some random item | 210.83 |
    |------------------+--------|
    | Sum              | 809.27 |
    ,#+TBLFM: @>$2=vsum(@I..II)
,*** Tax calculation for my room
    ,#+NAME: tab-room
    |   | Position                   |   Amount |
    |---+----------------------------+----------|
    |   | Area Room                  |    11.00 |
    |   | Area Flat                  |    77.42 |
    |   | Costs per year             | 12000.00 |
    |   | Some insurance             |   300.00 |
    |   | Electricity                |   700.00 |
    |---+----------------------------+----------|
    | _ |                            |        A |
    |   | Area fraction              |  0.14208 |
    |   | tax-deductible Rent        |  1747.58 |
    |   | tax-deductible Electricity |    99.46 |
    ,#+TBLFM: $A=@2$3/@3$3;%.5f::@>>$3=$A*@4$3+$A*@5$3;%.2f::@>$3=$A*@6$3;%.2f
,*** Summary
    ,#+NAME: tab-summary
    | Position               |  Amount | Comment             |
    |------------------------+---------+---------------------|
    | Special costs          |  809.27 |                     |
    | Deductible Rent        | 1747.58 | put in form field A |
    | Deductible Electricity |   99.46 | put in form field B |
    | Something else         | 1234.56 | very special        |
    |------------------------+---------+---------------------|
    | Sum                    | 3890.87 |                     |
    ,#+TBLFM: @2$2=remote(tab-special,@>$2)
    ,#+TBLFM: @3$2=remote(tab-room,@>>$3)
    ,#+TBLFM: @4$2=remote(tab-room,@>$3)
    ,#+TBLFM: @>$2=vsum(@I..II)
#+end_src

In respect to core features (listing data, using formulas for
calculation) I doubt that Emacs with Org tables and Calc is missing
anything. Maybe advanced visual presentation of the data is easier
with GUI Spreadsheets -- then again, it is so easy to combine Org
tables with the power of Gnuplot, R, Python, Julia, TeX etc. to create
astonishing visuals, that I prefer this way in many situations.

>From my point of view, MS Excel is the toy (I have not too much
experience with the other GUI spreadsheet programs). In Emacs I have
the power of Calc (a complete computer algebra system) and Lisp (the
best programming language, even if Elisp is not Common Lisp) at my
fingertips. And Emacs tries not to outsmart me and says: Hey, over
there in line 67 - that could be interpreted as a date, even if most
other data in that column looks quite different; let me just format
everything as a date for you. And Microsoft says that due to
backwards-compatiblity this brain dead behaviour (together with many
other nasty bugs) will never ever be fixed.

> Spreadsheet is more or less intuitive, Org mode tables are not, so
> comparison is hard.

Just the other way around! :)

I love that Org tables are fully self-explained, everything is
explicit and quite obvious. Formulas are easy to inspect. GUI
Spreadsheets may be a bit easier for the very first steps, but they
hide sooo much, that even power users with a decade or two of
experience have trouble of holding everything together.

Are Org tables for everyone? It would be great and IMHO it could work,
but it will not happen in the foreseeable future.

Are Org tables the solution for everything? Of course not.

But they are *very* powerful and Emacs + Org + Calc is able to replace
Spreadsheets in many situation and that solution may even be superior
in the long run.

-- 
Until the next mail...,
Stefan.