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.

[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]

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

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

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

[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