Microsoft Excel spreadsheet editing directly from within emacs.

Microsoft Excel spreadsheet editing directly from within emacs.

[Hongyi Zhao]

Is it possible for me to edit Microsoft Excel spreadsheet directly
from within emacs, especially utilizing the powerful capabilities of
orgmode?

Regards
-- 
Assoc. Prof. Hongyi Zhao <hongyi.zhao@gmail.com>
Theory and Simulation of Materials
Hebei Polytechnic University of Science and Technology engineering
NO. 552 North Gangtie Road, Xingtai, China

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Daniel Martín]

Hongyi Zhao <hongyi.zhao@gmail.com> writes:

> Is it possible for me to edit Microsoft Excel spreadsheet directly
> from within emacs, especially utilizing the powerful capabilities of
> orgmode?

Not directly, but you could first save the Excel spreadsheet as CSV and
then import it into Org-Mode using M-x org-table-import.  I haven't
checked how this works in practice, so YMMV.

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Tomas Nordin]

Daniel Martín <mardani29@yahoo.es> writes:

> Hongyi Zhao <hongyi.zhao@gmail.com> writes:
>
>> Is it possible for me to edit Microsoft Excel spreadsheet directly
>> from within emacs, especially utilizing the powerful capabilities of
>> orgmode?
>
> Not directly, but you could first save the Excel spreadsheet as CSV and
> then import it into Org-Mode using M-x org-table-import.  I haven't
> checked how this works in practice, so YMMV.

I think even you might be able to just copy / paste into a buffer and
use the org table import thing. What I have seen the copy produce
tab-separated fields that can be understood by org import...

--
Tomas

Re: Microsoft Excel spreadsheet editing directly from within emacs.

>> Is it possible for me to edit Microsoft Excel spreadsheet directly
>> from within emacs, especially utilizing the powerful capabilities of
>> orgmode?
>
> Not directly, but you could first save the Excel spreadsheet as CSV and
> then import it into Org-Mode using M-x org-table-import.

There's also a `csv-mode` in GNU ELPA.


        Stefan

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Stefan Monnier]

>> Is it possible for me to edit Microsoft Excel spreadsheet directly
>> from within emacs, especially utilizing the powerful capabilities of
>> orgmode?
>
> Not directly, but you could first save the Excel spreadsheet as CSV and
> then import it into Org-Mode using M-x org-table-import.

There's also a `csv-mode` in GNU ELPA.


        Stefan

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Filipp Gunbin]

On 28/12/2020 17:21 +0100, Daniel Martín wrote:

> Hongyi Zhao <hongyi.zhao@gmail.com> writes:
>
>> Is it possible for me to edit Microsoft Excel spreadsheet directly
>> from within emacs, especially utilizing the powerful capabilities of
>> orgmode?
>
> Not directly, but you could first save the Excel spreadsheet as CSV and
> then import it into Org-Mode using M-x org-table-import.  I haven't
> checked how this works in practice, so YMMV.

For very simple spreadsheets csv-mode / tsv-mode will do.

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Jean Louis]

* Daniel Martín <mardani29@yahoo.es> [2020-12-28 19:22]:
> Hongyi Zhao <hongyi.zhao@gmail.com> writes:
> 
> > Is it possible for me to edit Microsoft Excel spreadsheet directly
> > from within emacs, especially utilizing the powerful capabilities of
> > orgmode?
> 
> Not directly, but you could first save the Excel spreadsheet as CSV and
> then import it into Org-Mode using M-x org-table-import.  I haven't
> checked how this works in practice, so YMMV.

It performs poorly and takes very long time. It is limited unless
limits are raised. And its usability on the scale from 0 to 10 is 1.

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 732 bytes --]

>>> "JL" == Jean Louis <bugs@gnu.support> writes:

> * Daniel Martín <mardani29@yahoo.es> [2020-12-28 19:22]:
>> Hongyi Zhao <hongyi.zhao@gmail.com> writes:
>> 
>> > Is it possible for me to edit Microsoft Excel spreadsheet directly
>> > from within emacs, especially utilizing the powerful capabilities of
>> > orgmode?
>> 
>> Not directly, but you could first save the Excel spreadsheet as CSV and
>> then import it into Org-Mode using M-x org-table-import.  I haven't
>> checked how this works in practice, so YMMV.

> It performs poorly and takes very long time. It is limited unless
> limits are raised. And its usability on the scale from 0 to 10 is 1.


Not for me. What sort of files were you converting?

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Jean Louis]

* Uwe Brauer <oub@mat.ucm.es> [2020-12-29 00:07]:
> >>> "JL" == Jean Louis <bugs@gnu.support> writes:
> 
> > * Daniel Martín <mardani29@yahoo.es> [2020-12-28 19:22]:
> >> Hongyi Zhao <hongyi.zhao@gmail.com> writes:
> >> 
> >> > Is it possible for me to edit Microsoft Excel spreadsheet directly
> >> > from within emacs, especially utilizing the powerful capabilities of
> >> > orgmode?
> >> 
> >> Not directly, but you could first save the Excel spreadsheet as CSV and
> >> then import it into Org-Mode using M-x org-table-import.  I haven't
> >> checked how this works in practice, so YMMV.
> 
> > It performs poorly and takes very long time. It is limited unless
> > limits are raised. And its usability on the scale from 0 to 10 is 1.
> 
> Not for me. What sort of files were you converting?

Directory of businesses with their names and addresses in CSV
format. It is not the only file that would have those problems. One
way to go could be not to use Org mode, but to use org-table-importa
as that is then much faster, and then to turn on org mode again.

I have just said my user experience, I am not using org-table-import
as I find it hackish function low on the scale of usability.

When importing CSV documents I usually import it into database by
using the built in PostgreSQL features or Emacs Lisp parsing of
CSV.

Org tables I used mostly for transactions but not any more as it does
not scale up with many people, it becomes hell and it would be tedious
or impossible to teach any staff on distance on how to use it. There
is no collaboration.

PostgreSQL offers collaboration feature with its server model right
out of the box and staff member on distance can enter their
transactions straight. So I replaced Org mode tables with database
backed. Right tool for right purpose.

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 1418 bytes --]


> * Uwe Brauer <oub@mat.ucm.es> [2020-12-29 00:07]:

> Directory of businesses with their names and addresses in CSV
> format. It is not the only file that would have those problems. One
> way to go could be not to use Org mode, but to use org-table-importa
> as that is then much faster, and then to turn on org mode again.

> I have just said my user experience, I am not using org-table-import
> as I find it hackish function low on the scale of usability.

> When importing CSV documents I usually import it into database by
> using the built in PostgreSQL features or Emacs Lisp parsing of
> CSV.

> Org tables I used mostly for transactions but not any more as it does
> not scale up with many people, it becomes hell and it would be tedious
> or impossible to teach any staff on distance on how to use it. There
> is no collaboration.

> PostgreSQL offers collaboration feature with its server model right
> out of the box and staff member on distance can enter their
> transactions straight. So I replaced Org mode tables with database
> backed. Right tool for right purpose.


Interesting. I only import and export xls(x) files, with say 300 column
and say 4 columns containing marks of students. For this purpose the
export and import functions via csv work fine and I have the benefit of
using the power of Emacs to edit the list. The spreadsheet functionality
offered by org (via calc) is enough for me.


[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Jean Louis]

* Hongyi Zhao <hongyi.zhao@gmail.com> [2020-12-28 18:19]:
> Is it possible for me to edit Microsoft Excel spreadsheet directly
> from within emacs, especially utilizing the powerful capabilities of
> orgmode?

You have high expectations but Emacs Org mode does not nearly replace
the capabilities of a dedicated spreadsheet.

In comparison to all major known spreadsheets Org tables is not
powerful and not even comparable.

LibreOffice Calc and Gnumeric on free systems are proper tools for
serious spreadsheet work.

http://gnumeric.org/

https://www.libreoffice.org/

https://apps.kde.org/en/calligrasheets

Jean

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Daniele Nicolodi]

On 28/12/2020 20:36, Jean Louis wrote:
> * Hongyi Zhao <hongyi.zhao@gmail.com> [2020-12-28 18:19]:
>> Is it possible for me to edit Microsoft Excel spreadsheet directly
>> from within emacs, especially utilizing the powerful capabilities of
>> orgmode?
> 
> You have high expectations but Emacs Org mode does not nearly replace
> the capabilities of a dedicated spreadsheet.
> 
> In comparison to all major known spreadsheets Org tables is not
> powerful and not even comparable.

Without specific details on which functionalities are not supported in
Org tables, this advice is not useful.

There are many use cases in which Org tables are superiors to
spreadsheets, there are use cases where a dedicated spreadsheet
application works better, and there are use cases where a spreadsheet is
an horrible solution but whoever found themselves solving the problem
didn't know better and hammered around till they got a spreadsheet to
output what they wanted.

In my personal experience, in the 80% of the cases where a spreadsheet
has been used, it was the wrong tool for the job.

Without knowing what is the problem at hand, it is impossible to know
which solution is the more appropriate.

Cheers,
Dan

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Jean Louis]

* Daniele Nicolodi <daniele@grinta.net> [2020-12-28 22:56]:
> On 28/12/2020 20:36, Jean Louis wrote:
> > * Hongyi Zhao <hongyi.zhao@gmail.com> [2020-12-28 18:19]:
> >> Is it possible for me to edit Microsoft Excel spreadsheet directly
> >> from within emacs, especially utilizing the powerful capabilities of
> >> orgmode?
> > 
> > You have high expectations but Emacs Org mode does not nearly replace
> > the capabilities of a dedicated spreadsheet.
> > 
> > In comparison to all major known spreadsheets Org tables is not
> > powerful and not even comparable.
> 
> Without specific details on which functionalities are not supported in
> Org tables, this advice is not useful.

I just think that 3 references to proper spreadsheets are sparing time
of users who think that Org mode is great and powerful wizard of Oz.

> There are many use cases in which Org tables are superiors to
> spreadsheets

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.

Please show if you have some practical example where it could be
superior to spreadsheet. I just do not see how it is comparable as Org
tables are simply not a spreadsheet, rather hack in text to visualize
something similar to spreadsheet programs.

Within Emacs use Org tables and table mode and similar ARE useful. But
that does not make them comparable. A toy computer with sounds is
useful for a child, but that does not make it comparable to real
computer, as analogy.

> there are use cases where a dedicated spreadsheet application works
> better

Without considering the use within Emacs, as for that use Org tables
ARE good (but are toy computer), in all other cases a dedicated
spreadsheet works better. That would mean if file is not tied to Emacs
then in all cases spreadsheet works better.

> and there are use cases where a spreadsheet is an horrible solution
> but whoever found themselves solving the problem didn't know better
> and hammered around till they got a spreadsheet to output what they
> wanted.

Absolutely yes. One way to replace spreadsheet is to use the
database. There are horrible spreadsheet errors in the world.

> In my personal experience, in the 80% of the cases where a
> spreadsheet has been used, it was the wrong tool for the job.

I would like to know how you use it. I think they are useful for
simple non-critical applications, let us say expenses reports sent to
central database, quotations, invoices, lists of things and their
prices and similar. Entry errors and formula errors apply everywhere
also in Emacs. Emacs does not make it more useful for anybody who need
spreadsheet functionalities as it is a hack, not accessible
application. Org mode requires user to be advanced, careful reader of
a manual. Spreadsheet is more or less intuitive, Org mode tables are
not, so comparison is hard.

If we compare it from Emacs Lisp side or formula side, there is
nothing that Emacs cannot process in formula. I have myself in the
database so much more than just a spreadsheet and Emacs processes
anything necessar, just like any other programming language. But that
approach from programming side does not make program accessible and
usable as that is for small number of users.

Spreadsheet users are more or less average or basic computer
literates. 

References on spreadsheets at Wikipedia:

https://en.wikipedia.org/wiki/Spreadsheet

https://en.wikipedia.org/wiki/Comparison_of_spreadsheet_software

Maybe Org tables shall be included there?
https://en.wikipedia.org/wiki/Talk:Comparison_of_spreadsheet_software

For me, I am not convinced it is a spreadsheet program.

From manual:

"The table editor makes use of the Emacs Calc package to implement
spreadsheet-like capabilities.  It can also evaluate Emacs Lisp forms
to derive fields from other fields."

Great and we love our horse, but it is not an elephant.

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.

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Eric S Fraga]

Stefan,

I agree with everything you said!  For me, the fact that you have a full
computer algebra system (in the form of Calc) *and* Lisp behind the
scenes in org makes org tables incredibly more powerful than any
spreadsheet calculator I have ever seen or used.

The only issue is the possibly steep learning curve imposed by Calc for
use within tables (as opposed to interactively via the RPN calculator
display).

-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4.3-152-g713b3b

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Jean Louis]

Dear Stefan,

You speak of a programming language and what is possible with
programming language. But language is not a spreadsheet, an integrated
program that helps users with handling of data.

The Gnumeric as spreadsheet may be extended by using Python
programming language. In that sense I think there is no limit to
extension of a spreadsheet.

Libreoffice Calc spreadsheet may be extended by using Libreoffice
Basic, Javascript, BeanShell, Python.

Extensibility and programming capabilities do not make software a
"spreadsheet" program. Manual describes it as spreadsheet-like. That
is more accurate.

* Stefan Nobis <stefan-ml@snobis.de> [2020-12-29 14:52]:
> 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).

For interchange, one could choose those open document formats if it
contains formulas or CSV, TSV if it does not. I do not know if CSV/TSV
formats could carry some formula around.

And if data is not that complex one can export it as text or anything
else, maybe as PDF, it all depends how the other side wish to use the
data. Various conversion program may help to convert Org tables to
something else and it is useful.

> 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).

I have a feeling you refer to programming capabilities.

> 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

I see the above example as demonstration of functional or mathematical
capability. Yet I do not perceive it as spreadsheet capability as the
word spreadsheet is since decades established on the market. A HTML
table with Javascript could do the same as the example above but that
does not make it a spreadsheet.

Spreadsheet is table or tabular view with its rows and cells that
offers entry of data, management of data, especially calculations and
various scripting, charting abilities.

In a spreadsheet program I may visually and nicely presented see a
slice of a whole table. I may move from slice to slice to other pieces
of the whole table of data. Moving from cell to cell is pretty easy
and there are no screen distortions.

Org tables are text, not really tabular information even if so
visualized. But I cannot move easily and nicely from slice to
slice. The header is not there or does not remain fixed to tell me on
whhich row or which column I would be located.

Creating an Org table of 200x300 takes quite some time. I need not
wait when opening a true spreadsheet program. Further there are no
rows unless I start making them by hand.

Spreadsheet programs do not ask me to make a row by my own hand, it is
silly even to think of it.

Org user interface is for zen people who think they can do everything
with specifically complex key bindings.

Spreadsheet user interface is integrated, it does not require user to
remember much, or maybe nothing, just use a mouse. It is intuitive
just as Doug Engelbart envisioned computing how it should be back in
sixties.

Org mode is bringing us back into the era before Doug Engelbart. Back
to history.

Combining various sheets is standard spreadsheet feature. Examples you
have shown are cryptic for spreadsheet users.

But I give you applause for trying so hard. It is just not
realistic. I love Org mode too, but I know what it is. 

> In respect to core features (listing data, using formulas for
> calculation) I doubt that Emacs with Org tables and Calc is missing
> anything.

For example it misses to have rows' named positions by default. Like
from 1 to maybe 100 or from 1 to 10000 or 100000. User has to write it
by hand. No spreadsheet require user to write rows by hand as it would
be absurd. That is core feature of every spreadsheet.

It misses columns' as their named location or position by default. I
would need to name columns, but there are no defined columns. I cannot
say that column H shall contain a count of apples as example as there
is no designated column H.

There are no headers that user can fix to see what and where cursor is
positioned within spreadsheet.

But I see it is better to stop talking about something that is obvious
as we are lovers and enthusiasts and we love our own horse.

The reason why I am writing this is to warn those people that they are
entering the twilight zone of Org mode as if somebody need to do
serious spreadsheet work, Org tables are not spreadsheet. It may help
that subset of users to avoid wasting their time with a tool that is
not meant to be spreadsheet. It will help also those who like Org
mode, to use Org tables. Discussion like this is useful for review by
other people and their decision making.

> 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.

That is like saying to a user to switch from Emacs to C programming
language as it gives user far more capabilities, for example to make
his own editor or his own programming languages as it offers all the
powers, and if user wish to have more powers than better look at
assembly or machine language.

Users need integration. Not underlying programming capabilities, that
is for programmers, not for users. Users are majority, not
programmers. Programmers already know the powers you mentioned.

Spreadsheet is for users, not really for programmers.

Users need integration, they don't mind of the mentioned powers. They
want to enter rows, columns and click button and get a chart.

I just wish you would see that. Myself I program and I understand you,
but what I wish that you see is the distinction between users and
integrated computing and programmers. We all know programmers have all
powers at ther availability. We are in agreement on that, but you
forget users.

> From my point of view, MS Excel is the toy (I have not too much
> experience with the other GUI spreadsheet programs).

Well, that is what I speak about.

> 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.

I like that enthusiasm. I wish Emacs Lisp could handle what I handle
with Common Lisp, but it does not, it blocks, even crushes, there are
many problems. Maybe one day.

Sure I understand you have powers but you are narrowing your own world
and live in the illusion.

I do use Org tables, but if I would recommend somebody who does not
know Emacs, I would never recommend Org tables for reason that I want
person to spare the time, not waste the time. Computer is there to
help human, not human to help computer work, what we do in Org mode,
we constantly wish to make it work as it is not complete software, it
is far from being well integrated.

Well integrated software are today mobile applications. They have
already got about 50% of population using computers if not more. Those
applications are today built mostly out of the foundation that they
need to become integrated, putting things together and helping
users. Not other way around.

> I love that Org tables are fully self-explained, everything is
> explicit and quite obvious.

Org tables are not at all self-explained. It begins from creation of a
table, user creates a table 200x300 and waits maybe 10 seconds that
something happens. In the mean time nothing is told to user about
waiting time, user is implicitly advised to become religious and pray
for good.

Try creating empty 200x300 table and tell me if your interface is not
blocking you?

Spreadsheet interface does not.

> 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.

I do not know what they hide. I was using Lotus 1,2,3 spreadsheet on
Apple //c and it was all open and well documented, but it was
proprietary software. Libreoffice is well documented, Gnumeric is well
documented.

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

Org tables are not spreadsheet so it cannot replace spreadsheet unless
it is a simple case. In terms of formulas, anything is possible just
as it is scriptable with any known major free software spreadsheet
today. In terms of being spreadsheet Org tables do not comply neither
with rows, columns nor display of slices of a spreadsheet. It can show
bars on screen but not tell to user where cursor is
positioned. Unspoken on how low usability it has. Just create empty
table 200x300 and see if you may move around it in the empty table. 

> 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.

I do have same tendencies to take a collection of tools and round them
in my mind as being perfect enough for my work, and justify all the
procrastination I create with it and to avoid anything else. But that
approach, while being self satisfactorily does not help other people
who would be wasting their time maybe for months or year or even more
until they find out that it is not right tool for their job.

Computer programs should spare humans their efforts and
times. Programmers help users to spare their efforts, time, energy,
money, their resources. I do share the opinion that every person
should learn how to program, and Emacs, Emacs Lisp and GNU and
programming languages help so much in that endeavour.

Myself I use database as a spreadsheet, but I cannot say to somebody
who need spreadsheet capabilities to use a database maybe because I
find it personally useful, as it is better to advise people to use
what is better integrated for them or their group of collaborators so
that they spare their working time.

If their group are users who need specific input of data, maybe
spreadsheet is best. If they are handling more critical data most
probably they would never ask about it on the mailings list as they
would have already their consultant hired and managing all issues for
them. Org table users will accommodate themselves, I have no doubts.

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Stefan Nobis]

Jean Louis <bugs@gnu.support> writes:

> You speak of a programming language and what is possible with
> programming language.

That's not the point. Org table is integrated with Calc and Calc is a
Computer Algebra System. That is something like Excel combined with
Mathematica (with a little less GUI stuff) - that is powerful
integration to me.

Lisp is nice and for me as a programmer a helpful tool. But the main
point is Calc.

Just putting data in a tabulated form is seldom the interesting part.
It starts to get interesting when you try to do something with the
data, e.g. make some calculations. At this point it is significant,
how many calculations you can do out-of-the box (without much
programming). I did not count, but I assume that Calc has more
calculations to offer than Excel and even for a wider range of topics
(even symbolic math).

Therefore, from this point of view of core functionality, I would say
Org tables with Calc are more powerful that MS Excel. And thinking of
the many quirks and bugs in MS Excel, I still say: Excel is the toy
software and Emacs + Org + Calc is more mature and more professional
and the file format much more future proof (yes, nowadays Excel uses
XML, but the definition and documentation is lacking and too complex;
it seems not possible to re-implement it fully).

I emphasize this, because you wrote: "In comparison to all major known
spreadsheets Org tables is not powerful and not even comparable."

And "powerful" for me means core features, i.e. calculations. And in
this respect, I think the statement is wrong.

> In a spreadsheet program I may visually and nicely presented see a
> slice of a whole table. I may move from slice to slice to other
> pieces of the whole table of data. Moving from cell to cell is
> pretty easy and there are no screen distortions.

If your point of view is, that the main feature are the visual
interactions (hiding/revealing rows/columns, sorting, filtering data
etc.) then yes, maybe Org lacks a bit in these areas (but I think not
quite as much, as you imply; e.g. Org can show labels for rows and
columns and there are ways for hiding, sorting and filtering).

On the other hand: I do find GUI spreadsheets also quite lacking in
this regard (e.g. regex pattern are seldom easy to use). If I need to
tinker with data, explore it, I would put it into a database (or into
R or Julia or - depending on the size of the data - using R/Julia from
within Emacs via Org tables). That's much faster, maintainable and
reproducible.

> Spreadsheet user interface is integrated, it does not require user
> to remember much, or maybe nothing, just use a mouse.

Nice try: There are tons of books about the question what are the best
user interfaces. I have seen people working with computers with
exactly this mindset: "I do not need to learn/remember anything, it
should be easy, just clicking a bit with my mouse". At this level, it
just does not work.

So, yes, a very text-oriented UI is not the best solution for every
task. But then a GUI is also not the best solution for every task.

> Org mode is bringing us back into the era before Doug Engelbart.
> Back to history.

That's IMHO a very simplistic and ignorant view on UX. The problem is
much more complex and yes, in quite some cases are text-based
interfaces even one of the best solutions (and next to never exists a
single best solution, UX always depends very strongly on context and
details).

> Combining various sheets is standard spreadsheet feature. Examples
> you have shown are cryptic for spreadsheet users.

I once showed rather simple Excel sheet to an Excel user and he was
overwhelmed and did not understand how to create such a structure (2-3
tables with formulas across tables). So Excel is cryptic for
spreadsheet users, too. :)

With other words: No tool is simple, you need to learn at least a
little bit - in all cases. I only showed the final result, not the way
how it has been created. Emacs and Org have ways to ease some of the
seemingly complicated parts, just as Excel tries to help formulate
complicated calculations. And then comes habit.

I fully understand that a tool like Excel at least seems to be easier
for the first steps. But that does not mean that it is a given fact,
that it is easier and better suited for any task that is spreadsheet
like (you already mentioned databases - I would say for the typical
spreadsheet users databases are even more cryptic that Emacs and Org).

>> 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.

> That is like saying to a user to switch from Emacs to C programming
> language as it gives user far more capabilities

No, quite the contrary. I think, this is the main point, you seem not
to understand. Emacs with Elisp is much more expressive than C. Org
with Calc is (for calculations) more expressive than Excel. And this
expressiveness can help in efficiency and maintainability.

Is this the one and only solution for everyone? No, of course not.

But you seem to imply, because some data viewing aspects and the very
first steps with spreadsheet programs may(!) be easier with tools like
Excel, everyone who needs some spreadsheet-like tools should always
only look at Excel or LibreOffice. With this argument you could also
say that no one should use CAD tools - with Paint you can draw
everything and 3D modelling with CAD is too complex and has a very
steep learning curve.

We talk about Org users and therefore Emacs users. Not every Emacs
user knows about Calc, but some do and other may benefit from learning
about it. And for users already comfortable with Emacs and Org, Calc
and the combination of Org tables with Calc may be even easier to
handle than Excel or LibreOffice. That's the point: GUI spreadsheets
have some disadvantages (and some advantages) when compared to Org
tables; maybe not many, maybe not relevant for everybody - but they
exist. Thus GUI spreadsheets are not the best tool for everybody and
all tasks.

And I never said, that Org tables should strive for world domination
(hmmm... maybe it would be a better world). I just questioned your
statement, that Org tables are a toy and not really up to real tasks
that Excel et. al. can handle and that only GUIs provide good UX.

BTW: I know quite some people missing good old mainframe UIs. A simple
text based mask with only a couple of actions to be executed via
F-keys. And todays mobile apps are rather a revival of those simple
mainframe screens (more shiny and with touch instead of F-keys) rather
than a copy of complex desktop GUIs.

> Users need integration, they don't mind of the mentioned powers.
> They want to enter rows, columns and click button and get a chart.

This is possible with Org tables - besides the point that not every
user is the same. I fully understand that Emacs is not the tool for
everyone, neither is Org. In my younger days I often tried (sometimes
very hard) to convince people to use LaTeX or Linux (or both). I
learned my lessons. :)

But just because there are some or even a majority of people that
prefer tool A does not mean that you should advocate tool A to
everyone. There might be quite some people that would much more
benefit from using tool B or C. I've been a teacher for adult evening
classes, I know a little bit about how Jane or Jo User struggles with
computers. But I've also seen how some of these everyday users are
able to use complex tools. It's just not as simple and black-and-white
as you picture it.

As a loose analogy: See how Maker spaces are flourishing nowadays.
There are many people that are eager to understand, how the world is
build (at least some parts of it). Yes, that is not true for everyone,
but do not underestimate curiosity and the fun to tinker. Even
nowadays new, young users discover Emacs and love to use it (despite
all the shiny sparkling GUI and web-based tools out there).

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

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Samuel Wales]

angels fear to tread but imo the degree to which one can say /a
priori/ that a personal ui preference is "WRONG" can have limits?

org spreadsheets can have strengths and weaknesses.  whether they
matter depends on the user.  example: for me, they are:

- i use them infrequently and have to learn their syntax and commands
anew every time.  that is /just me/.  [dunno if this could be improved
such as with ergonomics/accessibility improvements and more standard
and less horizontal using interfaces.]
- for some reason, ses and excel are intuitive /for me/.  i can't give
a song and dance about why, but i can say that it is not because i
have huge experience with those.  i do not.  they just are quicker to
pick up again after years of non-use.  /for me/.
- i like elisp [this is /just me/] and unfortuately do not know r, so
elisp is great
- calc is awe inspiring even though i almost never use it.  it would
be great if it, or org, or both, had even better and
intuitive/convenient /for me/ chart producing ability.
- plain text is great [few on the org list will disagree]
- [perhaps a compromise would include a new feature in which gui
spreadsheet data or ses gets saved entirely in org blocks?  dunno.]
- i suppose i vary between needing only a little data and data size
that org is unlikely to deal with in an org table [as opposed perhaps
to an enormous block or something]

but that is merely my experience.  reference to authorities or
argumentation is unlikely to change that much.  what might help imo is
figuring out if org can accommodate different use cases, including
basic gui/ses-mode type spreadsheet learning curve.

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[andres.ramirez]

Hi. Jean Louis.

>>>>> "Jean" == Jean Louis <bugs@gnu.support> writes:

    Jean> You have high expectations but Emacs Org mode does not nearly replace the capabilities of
    Jean> a dedicated spreadsheet.

Emacs has an spreadsheet mode builtin:
--8<---------------cut here---------------start------------->8---
(info "(ses) Quick Tutorial")
--8<---------------cut here---------------end--------------->8---

ses-mode could be embedded within an 'org source code block'.

Perhaps someone has a tool for converting ms-spreadsheet files to
ses-mode.

Best Regards

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Carson Chittom]

On Mon, Dec 28, 2020, at 6:06 PM, andres.ramirez wrote:
> Hi. Jean Louis.
> 
> >>>>> "Jean" == Jean Louis <bugs@gnu.support> writes:
> 
>     Jean> You have high expectations but Emacs Org mode does not nearly 
> replace the capabilities of
>     Jean> a dedicated spreadsheet.
> 
> Emacs has an spreadsheet mode builtin:
> --8<---------------cut here---------------start------------->8---
> (info "(ses) Quick Tutorial")
> --8<---------------cut here---------------end--------------->8---
> 
> ses-mode could be embedded within an 'org source code block'.
> 
> Perhaps someone has a tool for converting ms-spreadsheet files to
> ses-mode.

I have tried to use ses-mode. I recommend that you not.

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Bob Newell]

andres.ramirez <rrandresf@gmail.com> writes:

> Emacs has an spreadsheet mode builtin:
>
> (info "(ses) Quick Tutorial")
>
> ses-mode could be embedded within an 'org source code block'.
>
> Perhaps someone has a tool for converting ms-spreadsheet files to
> ses-mode.

Aloha everyone,

I've used SES in the past with good results. I've found it
easier to use than org-mode spreadsheets, although that said,
I often do go with org-mode just to keep things in one file,
and within the org-mode ecosystem. I haven't tried the
intriguing idea of a SES code block in org --- is there a
back-end for that?

SES, I think, doesn't get enough attention. It's quite
powerful given that formulas are simply elisp expressions (of
course org-mode offers that option too), but it's definitely
easier to manage formulas in SES (my opinion, of course).

As to conversion, there is a SES2ANY utility out there
somewhere which purports to make Excel out of SES. I haven't
tried it, and I'm not sure how easy (hard!) it would be to go
the other direction if there were to be any degree of
complexity.

-- 
Bob Newell
Honolulu, Hawai`i

- Via GNU/Linux/Emacs/Gnus/BBDB

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Jean Louis]

* Bob Newell <bobnewell@bobnewell.net> [2020-12-30 19:57]:
> 
> andres.ramirez <rrandresf@gmail.com> writes:
> 
> > Emacs has an spreadsheet mode builtin:
> >
> > (info "(ses) Quick Tutorial")
> >
> > ses-mode could be embedded within an 'org source code block'.
> >
> > Perhaps someone has a tool for converting ms-spreadsheet files to
> > ses-mode.
> 
> Aloha everyone,
> 
> I've used SES in the past with good results. I've found it
> easier to use than org-mode spreadsheets, although that said,
> I often do go with org-mode just to keep things in one file,
> and within the org-mode ecosystem. 

Just to mention, SES mode is fine for smaller values, when it starts
becoming big, it slows down very much, including for empty cells, and
if it is doing something in background and I interrupt it with C-g,
the file gets corrupted.

In general it is good for simpler purposes, not for serious data handling.

Jean

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 957 bytes --]

>>> "HZ" == Hongyi Zhao <hongyi.zhao@gmail.com> writes:

> Is it possible for me to edit Microsoft Excel spreadsheet directly
> from within emacs, especially utilizing the powerful capabilities of
> orgmode?


You can export and import them to org files, without the excel formula
of course

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

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


> Regards

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Eric S Fraga]

On Monday, 28 Dec 2020 at 21:12, Uwe Brauer wrote:
> You can export and import them to org files, without the excel formula
> of course
>
> (defun org-table-import-xlsx-to-csv-org () 

[...]

I use these functions all the time.  Work well for me for spreadsheets
where it's the data that matters.

-- 
: Eric S Fraga via Emacs 28.0.50, Org release_9.4.3-152-g713b3b

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Robert Thorpe]

Hongyi Zhao <hongyi.zhao@gmail.com> writes:

> Is it possible for me to edit Microsoft Excel spreadsheet directly
> from within emacs, especially utilizing the powerful capabilities of
> orgmode?

People here have suggested lots of things.

It depends on the application.  Jean Louis is correct to say that Emacs
spreadsheets are not as fully featured as things like Libreoffice.

Plotting charts is a problem.  So is sending spreadsheets to other
people in a way that they can read the file.

In my past jobs I've used spreadsheets a lot.  I've found that Emacs is
very useful for the beginning steps.  If you have data in
comma-separated-value format or tab-separated-value format then you can
quickly check it over in Emacs and do cleaning-up changes.  But then
I've found it best to take that CSV/TSV output and put it into a full
spreadsheet package, to generate final output.  That is, if you need
charts and distribution to others.

BR,
Robert Thorpe

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Hongyi Zhao]

On Tue, Dec 29, 2020 at 12:02 PM Robert Thorpe
<rt@robertthorpeconsulting.com> wrote:
>
> Hongyi Zhao <hongyi.zhao@gmail.com> writes:
>
> > Is it possible for me to edit Microsoft Excel spreadsheet directly
> > from within emacs, especially utilizing the powerful capabilities of
> > orgmode?
>
> People here have suggested lots of things.
>
> It depends on the application.  Jean Louis is correct to say that Emacs
> spreadsheets are not as fully featured as things like Libreoffice.
>
> Plotting charts is a problem.  So is sending spreadsheets to other
> people in a way that they can read the file.
>
> In my past jobs I've used spreadsheets a lot.  I've found that Emacs is
> very useful for the beginning steps.  If you have data in
> comma-separated-value format or tab-separated-value format then you can
> quickly check it over in Emacs and do cleaning-up changes.  But then
> I've found it best to take that CSV/TSV output and put it into a full
> spreadsheet package, to generate final output.  That is, if you need
> charts and distribution to others.

From this point of view, if we want to have both full-features and
powerful capabilities in manipulating spreadsheet, it seems that only
the python based programmatic tools/packages, say, openpyxl
<https://openpyxl.readthedocs.io>, can meet the requirements
currently.

Regards
-- 
Assoc. Prof. Hongyi Zhao <hongyi.zhao@gmail.com>
Theory and Simulation of Materials
Hebei Polytechnic University of Science and Technology engineering
NO. 552 North Gangtie Road, Xingtai, China

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Jean Louis]

* Hongyi Zhao <hongyi.zhao@gmail.com> [2020-12-29 07:55]:
> From this point of view, if we want to have both full-features and
> powerful capabilities in manipulating spreadsheet, it seems that only
> the python based programmatic tools/packages, say, openpyxl
> <https://openpyxl.readthedocs.io>, can meet the requirements
> currently.

What are you requirements?

- do you also use charts?

- do you personally use spreadsheet or multiple people have to enter
  data?

- how many rows do you enter over months, or years?

- are there complex formulas to be followed?

- if not secret, you can as well show the table of your data to see
  here

Then people reading the mailing list may help you better with advise
how to proceed.

For some new data coming over and over again I will almost always use
a database to track it.

Example is weekly statistics, as that is something I may keep for
years in a database and see slices in various periods of times.

Another example of keeping it in the database are geological
coordinates like latitude and longitude that may have plethora of
various notations and geographic datums. Such coordinates can belong
to a set of coordinates and set of coordinates could be something like
a route or something like area. A route would be drawn differently and
linearly and area would be drawn differently, by connecting the begin
and end position.

Similar things may be accomplished with spreadsheets as well where
some columns can determine the type of the column with the drop down
list.

Example of a cell of a spreadsheet with type-like+
  Column A     	       	Column B       	     	 +
 +-------------------++-----------------+
 | Mr. Joe Doe       || From website    |
 +-------------------+| From poster     |
		      | By partner      |
		      | By radio        |
		      +-----------------+


When you wish to determine a type for specific entry spreadsheet
programs do offer that, I just find it so much harder then using the
SQL database.

Once you explain your data entry and your need I will see if I can
help.

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Hongyi Zhao]

On Tue, Dec 29, 2020 at 5:40 PM Jean Louis <bugs@gnu.support> wrote:
>
> * Hongyi Zhao <hongyi.zhao@gmail.com> [2020-12-29 07:55]:
> > From this point of view, if we want to have both full-features and
> > powerful capabilities in manipulating spreadsheet, it seems that only
> > the python based programmatic tools/packages, say, openpyxl
> > <https://openpyxl.readthedocs.io>, can meet the requirements
> > currently.
>
> What are you requirements?

I use Linux as my working environment exclusively. So, I can't access
the native MS Office supplied for macOS/Windows. But I sometimes
really need to manipulate and process MS Office documents, especially
DOCX and XLSX files. Though there are some free and open source office
suites, e.g. LibreOffice, but none of them can completely compatible
with the MS Office.

So, I want to find a way that can be used to programmatically complete
any possible work done by MS Office word/excel.

>
> - do you also use charts?
>
> - do you personally use spreadsheet or multiple people have to enter
>   data?
>
> - how many rows do you enter over months, or years?
>
> - are there complex formulas to be followed?
>
> - if not secret, you can as well show the table of your data to see
>   here
>
> Then people reading the mailing list may help you better with advise
> how to proceed.
>
> For some new data coming over and over again I will almost always use
> a database to track it.
>
> Example is weekly statistics, as that is something I may keep for
> years in a database and see slices in various periods of times.
>
> Another example of keeping it in the database are geological
> coordinates like latitude and longitude that may have plethora of
> various notations and geographic datums. Such coordinates can belong
> to a set of coordinates and set of coordinates could be something like
> a route or something like area. A route would be drawn differently and
> linearly and area would be drawn differently, by connecting the begin
> and end position.
>
> Similar things may be accomplished with spreadsheets as well where
> some columns can determine the type of the column with the drop down
> list.
>
> Example of a cell of a spreadsheet with type-like+
>   Column A              Column B                 +
>  +-------------------++-----------------+
>  | Mr. Joe Doe       || From website    |
>  +-------------------+| From poster     |
>                       | By partner      |
>                       | By radio        |
>                       +-----------------+
>
>
> When you wish to determine a type for specific entry spreadsheet
> programs do offer that, I just find it so much harder then using the
> SQL database.
>
> Once you explain your data entry and your need I will see if I can
> help.
>
>


-- 
Assoc. Prof. Hongyi Zhao <hongyi.zhao@gmail.com>
Theory and Simulation of Materials
Hebei Polytechnic University of Science and Technology engineering
NO. 552 North Gangtie Road, Xingtai, China

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Greg Minshall]

Hongyi Zhao,

> I use Linux as my working environment exclusively. So, I can't access
> the native MS Office supplied for macOS/Windows. But I sometimes
> really need to manipulate and process MS Office documents, especially
> DOCX and XLSX files. Though there are some free and open source office
> suites, e.g. LibreOffice, but none of them can completely compatible
> with the MS Office.

i've been privileged, especially as i'm now retired, to say to people,
"sorry, i can't use excel; please send me a .csv file", just get the
data (no formulas, etc.), and go from there (i typically use R to look
at/analyze the data).  more than 95% of the time, i'm quite happy with
the results of this method.

cheers and good luck, Greg

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Jean Louis]

* Hongyi Zhao <hongyi.zhao@gmail.com> [2020-12-29 17:49]:
> On Tue, Dec 29, 2020 at 5:40 PM Jean Louis <bugs@gnu.support> wrote:
> >
> > * Hongyi Zhao <hongyi.zhao@gmail.com> [2020-12-29 07:55]:
> > > From this point of view, if we want to have both full-features and
> > > powerful capabilities in manipulating spreadsheet, it seems that only
> > > the python based programmatic tools/packages, say, openpyxl
> > > <https://openpyxl.readthedocs.io>, can meet the requirements
> > > currently.
> >
> > What are you requirements?
> 
> I use Linux as my working environment exclusively. So, I can't access
> the native MS Office supplied for macOS/Windows. But I sometimes
> really need to manipulate and process MS Office documents, especially
> DOCX and XLSX files. Though there are some free and open source office
> suites, e.g. LibreOffice, but none of them can completely compatible
> with the MS Office.

Yes, not everything is compatible. Please mention that specific
feature not compatible that you know of.

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Robert Thorpe]

Hongyi Zhao <hongyi.zhao@gmail.com> writes:

> On Tue, Dec 29, 2020 at 5:40 PM Jean Louis <bugs@gnu.support> wrote:
>>
>> * Hongyi Zhao <hongyi.zhao@gmail.com> [2020-12-29 07:55]:
>> > From this point of view, if we want to have both full-features and
>> > powerful capabilities in manipulating spreadsheet, it seems that only
>> > the python based programmatic tools/packages, say, openpyxl
>> > <https://openpyxl.readthedocs.io>, can meet the requirements
>> > currently.
>>
>> What are you requirements?
>
> I use Linux as my working environment exclusively. So, I can't access
> the native MS Office supplied for macOS/Windows. But I sometimes
> really need to manipulate and process MS Office documents, especially
> DOCX and XLSX files. Though there are some free and open source office
> suites, e.g. LibreOffice, but none of them can completely compatible
> with the MS Office.
>
> So, I want to find a way that can be used to programmatically complete
> any possible work done by MS Office word/excel.

Who creates the spreadsheet first?  Is it you or someone else?

If it's you then things can be quite simple.  You can create a
comma-separated or tab-separated file in Emacs or something else.  You
can then import that file into Libreoffice and then save it as a .XLSX
or .XLS file.  You can make a script in Libreoffice to do the import
just the way you want it.

Things are more difficult if you are given a .XLSX or .DOCX file by
someone else.  In that case you have to use Libreoffice or something
like it straight away.  You also have to be very careful was re-saving
the file with changes because Libreoffice has some subtle
incompatabilities with MS Excel and MS Word. 

If you just want to add new sheets to existing spreadsheets that is not
too bad.  The problem is changing values in existing sheets.

BR,
Robert Thorpe

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Hongyi Zhao]

On Wed, Dec 30, 2020 at 1:55 AM Robert Thorpe
<rt@robertthorpeconsulting.com> wrote:
>
> Hongyi Zhao <hongyi.zhao@gmail.com> writes:
>
> > On Tue, Dec 29, 2020 at 5:40 PM Jean Louis <bugs@gnu.support> wrote:
> >>
> >> * Hongyi Zhao <hongyi.zhao@gmail.com> [2020-12-29 07:55]:
> >> > From this point of view, if we want to have both full-features and
> >> > powerful capabilities in manipulating spreadsheet, it seems that only
> >> > the python based programmatic tools/packages, say, openpyxl
> >> > <https://openpyxl.readthedocs.io>, can meet the requirements
> >> > currently.
> >>
> >> What are you requirements?
> >
> > I use Linux as my working environment exclusively. So, I can't access
> > the native MS Office supplied for macOS/Windows. But I sometimes
> > really need to manipulate and process MS Office documents, especially
> > DOCX and XLSX files. Though there are some free and open source office
> > suites, e.g. LibreOffice, but none of them can completely compatible
> > with the MS Office.
> >
> > So, I want to find a way that can be used to programmatically complete
> > any possible work done by MS Office word/excel.
>
> Who creates the spreadsheet first?  Is it you or someone else?

From/by others, in most instances.

>
> If it's you then things can be quite simple.  You can create a
> comma-separated or tab-separated file in Emacs or something else.

Do you mean export the org table with CSV/TSV formats? I noticed that the
default orgmode table column separator is |.

> You can then import that file into Libreoffice and then save it as a .XLSX
> or .XLS file.  You can make a script in Libreoffice to do the import
> just the way you want it.

Sounds wonderful. Is there such an example script?

>
> Things are more difficult if you are given a .XLSX or .DOCX file by
> someone else.  In that case you have to use Libreoffice or something
> like it straight away.  You also have to be very careful was re-saving
> the file with changes because Libreoffice has some subtle
> incompatabilities with MS Excel and MS Word.
>
> If you just want to add new sheets to existing spreadsheets that is not
> too bad.  The problem is changing values in existing sheets.

You're absolutely right.

BR,
-- 
Assoc. Prof. Hongyi Zhao <hongyi.zhao@gmail.com>
Theory and Simulation of Materials
Hebei Polytechnic University of Science and Technology engineering
NO. 552 North Gangtie Road, Xingtai, China

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[Robert Thorpe]

Hongyi Zhao <hongyi.zhao@gmail.com> writes:

> On Wed, Dec 30, 2020 at 1:55 AM Robert Thorpe
> <rt@robertthorpeconsulting.com> wrote:
>>
>> Hongyi Zhao <hongyi.zhao@gmail.com> writes:
>>
>> > On Tue, Dec 29, 2020 at 5:40 PM Jean Louis <bugs@gnu.support> wrote:
>> >>
>> >> * Hongyi Zhao <hongyi.zhao@gmail.com> [2020-12-29 07:55]:
>> >> > From this point of view, if we want to have both full-features and
>> >> > powerful capabilities in manipulating spreadsheet, it seems that only
>> >> > the python based programmatic tools/packages, say, openpyxl
>> >> > <https://openpyxl.readthedocs.io>, can meet the requirements
>> >> > currently.
>> >>
>> >> What are you requirements?
>> >
>> > I use Linux as my working environment exclusively. So, I can't access
>> > the native MS Office supplied for macOS/Windows. But I sometimes
>> > really need to manipulate and process MS Office documents, especially
>> > DOCX and XLSX files. Though there are some free and open source office
>> > suites, e.g. LibreOffice, but none of them can completely compatible
>> > with the MS Office.
>> >
>> > So, I want to find a way that can be used to programmatically complete
>> > any possible work done by MS Office word/excel.
>>
>> Who creates the spreadsheet first?  Is it you or someone else?
>
> From/by others, in most instances.
>
>>
>> If it's you then things can be quite simple.  You can create a
>> comma-separated or tab-separated file in Emacs or something else.
>
> Do you mean export the org table with CSV/TSV formats? I noticed that the
> default orgmode table column separator is |.

I was thinking of editing using csv-mode, not using orgmode table.  But
if you have well behaved data it's not hard to change the separate with
a search-and-replace.  You've got to watch out for corner cases though
like commas being used in text.

>> You can then import that file into Libreoffice and then save it as a .XLSX
>> or .XLS file.  You can make a script in Libreoffice to do the import
>> just the way you want it.
>
> Sounds wonderful. Is there such an example script?

Last time I did this a few years ago at work.  I don't have the code
anymore.

You can find a few things like that at the Libreoffice extensions site
though https://extensions.libreoffice.org/ .

>> Things are more difficult if you are given a .XLSX or .DOCX file by
>> someone else.  In that case you have to use Libreoffice or something
>> like it straight away.  You also have to be very careful was re-saving
>> the file with changes because Libreoffice has some subtle
>> incompatabilities with MS Excel and MS Word.
>>
>> If you just want to add new sheets to existing spreadsheets that is not
>> too bad.  The problem is changing values in existing sheets.
>
> You're absolutely right.

BR,
Robert Thorpe

Re: Microsoft Excel spreadsheet editing directly from within emacs.

[H. Dieter Wilhelm]

Hongyi Zhao <hongyi.zhao@gmail.com> writes:

> On Tue, Dec 29, 2020 at 5:40 PM Jean Louis <bugs@gnu.support> wrote:
>>
>> * Hongyi Zhao <hongyi.zhao@gmail.com> [2020-12-29 07:55]:
>> > From this point of view, if we want to have both full-features and
>> > powerful capabilities in manipulating spreadsheet, it seems that only
>> > the python based programmatic tools/packages, say, openpyxl
>> > <https://openpyxl.readthedocs.io>, can meet the requirements
>> > currently.
>>
>> What are you requirements?
>
> I use Linux as my working environment exclusively. So, I can't access
> the native MS Office supplied for macOS/Windows. But I sometimes
> really need to manipulate and process MS Office documents, especially
> DOCX and XLSX files. Though there are some free and open source office
> suites, e.g. LibreOffice, but none of them can completely compatible
> with the MS Office.
>
> So, I want to find a way that can be used to programmatically complete
> any possible work done by MS Office word/excel.

For directly reading Excel sheets I highly recommend "readxl" from the
R package bundle "tidyverse".  

  https://r4ds.had.co.nz/

It's an investment though but it pays in the long run for serious data
science.  After processing you should be able to write data in an excel
file with the "writexl" package.  (Did not test it, never had the need
to export into Excel. :-))

By the way, I'm mostly doing data processing in org-mode R source
blocks.  And I'm also converting data into org-mode tables but mostly
small ones for latex table exports. And you can also do spreadsheet
calculations with them

      https://orgmode.org/manual/The-Spreadsheet.html

Here's a small example

       #+LATEX: \definecolor{VitescoYellow}{RGB}{242,229,0}
       #+LATEX: \rowcolors[]{1}{VitescoYellow!100}{VitescoYellow!5}
       #+caption: Required motor torque to reach src_R{N} rpm in src_R{T} s
       | *Cases*    | *Torque* in  Nmm |
       |------------+------------------|
       | Best case  |              9.5 |
       | Worst case |             11.6 |
       #+TBLFM: @2$2='(org-sbe mintorque):: @3$2='(org-sbe maxtorque)

(Above org table is reading results from named org source blocks.)

If you really, really need to return data in MS formats you might have
to run a virtual machine with Windows, not that I recommend that but
live is ...

Hope that is interesting

     Dieter


-- 
Best wishes
H. Dieter Wilhelm
Zwingenberg, Germany