Reading Excel or Libreoffice files

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Reading Excel or Libreoffice files

luciano de souza-2
Hello all,
I want to deal with Excel or Libreoffice files.
I found the package xlsxwriter, available as a rock.
But I could not find something to read a xls, xlsx or ods files. I am
in Linux, so I can't use luacom. I have few experience, so perhaps to
understand the xml structure of a xlsx file would not be viable.
Regarding this aspects, what do you think it's the best approach for me?

--
Luciano de Souza

Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel or Libreoffice files

Hisham
On 25 December 2016 at 09:25, luciano de souza <[hidden email]> wrote:
> Hello all,
> I want to deal with Excel or Libreoffice files.
> I found the package xlsxwriter, available as a rock.
> But I could not find something to read a xls, xlsx or ods files. I am
> in Linux, so I can't use luacom. I have few experience, so perhaps to
> understand the xml structure of a xlsx file would not be viable.
> Regarding this aspects, what do you think it's the best approach for me?

If you only want to extract the data, you can use LibreOffice in
headless mode and use it to convert xlsx to an easier-to-parse format
such as csv. Type `scalc --help` for more details (check out the
--convert-to option).

-- Hisham

Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel or Libreoffice files

luciano de souza-2
In reply to this post by luciano de souza-2
Unfortunately, this approach is not possible for me.
I have a big number of forms saved in a Excel sheet.
The name and the last name will be in a fixed position, for example, ""b3".
For each data, I have a mapped position. What I want is to read all
sheets and consolidate the result.
In other words, I really need to read the sheet in a batch process.

2016-12-25 9:25 GMT-02:00, luciano de souza <[hidden email]>:

> Hello all,
> I want to deal with Excel or Libreoffice files.
> I found the package xlsxwriter, available as a rock.
> But I could not find something to read a xls, xlsx or ods files. I am
> in Linux, so I can't use luacom. I have few experience, so perhaps to
> understand the xml structure of a xlsx file would not be viable.
> Regarding this aspects, what do you think it's the best approach for me?
>
> --
> Luciano de Souza
>


--
Luciano de Souza

Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel or Libreoffice files

Erik Hougaard
In reply to this post by luciano de souza-2
On 25-12-2016 03:25, luciano de souza wrote:
> I want to deal with Excel or Libreoffice files.
> I found the package xlsxwriter, available as a rock.
> But I could not find something to read a xls, xlsx or ods files. I am
> in Linux, so I can't use luacom. I have few experience, so perhaps to
> understand the xml structure of a xlsx file would not be viable.
> Regarding this aspects, what do you think it's the best approach for me?
I'm always using OpenXML to manipulate .xlsx files (they are in reality
a zip files with a bunch of xml files). You can just unzip the xlsx
file, manipulate the xml and zip the result back into a xlsx file.

/Erik




TW
Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel or Libreoffice files

TW
In reply to this post by luciano de souza-2
2016-12-26 23:57 GMT+01:00 luciano de souza <[hidden email]>:
> Unfortunately, this approach is not possible for me.
> I have a big number of forms saved in a Excel sheet.
> The name and the last name will be in a fixed position, for example, ""b3".
> For each data, I have a mapped position. What I want is to read all
> sheets and consolidate the result.
> In other words, I really need to read the sheet in a batch process.
>


You *can* use LibreOffice in a batch process. In Bash:

  tmpdir=$(mktemp --directory)
  soffice "-env:UserInstallation=file://""$tmpdir""/soffice" \
          --headless --invisible --convert-to csv "$xslfile"

The "-env:UserInstallation=..." part is to ensure that LibreOffice
will not use the GUI even if there is an instance of LibreOffice with
GUI already running.

Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel or Libreoffice files

luciano de souza-2
In reply to this post by Erik Hougaard
Yes, I found the data in sharedstrings.xml, but parse it, regarding my
few experience, it's now
inviable.
If there is no a Lua module to read xlsx, it will be more realistic to
use Pascal for this purpose.

2016-12-26 22:10 GMT-02:00, Erik Hougaard <[hidden email]>:

> On 25-12-2016 03:25, luciano de souza wrote:
>> I want to deal with Excel or Libreoffice files.
>> I found the package xlsxwriter, available as a rock.
>> But I could not find something to read a xls, xlsx or ods files. I am
>> in Linux, so I can't use luacom. I have few experience, so perhaps to
>> understand the xml structure of a xlsx file would not be viable.
>> Regarding this aspects, what do you think it's the best approach for me?
> I'm always using OpenXML to manipulate .xlsx files (they are in reality
> a zip files with a bunch of xml files). You can just unzip the xlsx
> file, manipulate the xml and zip the result back into a xlsx file.
>
> /Erik
>
>
>
>
>


--
Luciano de Souza

Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel or Libreoffice files

Joshua Jensen-2

On Dec 27, 2016, at 6:06 PM, luciano de souza <[hidden email]> wrote:

Yes, I found the data in sharedstrings.xml, but parse it, regarding my
few experience, it's now
inviable.
If there is no a Lua module to read xlsx, it will be more realistic to
use Pascal for this purpose.


-Josh
Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel or Libreoffice files

luciano de souza-2
Josh,
It is really what I need. The sintax is very clear, the code very
simple. This module is perfect for my needs. Thank you very much.

2016-12-28 0:07 GMT-02:00, Josh Jensen <[hidden email]>:

>
>> On Dec 27, 2016, at 6:06 PM, luciano de souza <[hidden email]>
>> wrote:
>>
>> Yes, I found the data in sharedstrings.xml, but parse it, regarding my
>> few experience, it's now
>> inviable.
>> If there is no a Lua module to read xlsx, it will be more realistic to
>> use Pascal for this purpose.
>
> https://github.com/jjensen/lua-xlsx
>
> -Josh


--
Luciano de Souza

Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel or Libreoffice files

Enrico Colombini
In reply to this post by TW
On 27-Dec-16 22:43, TW wrote:
> You *can* use LibreOffice in a batch process. In Bash:
>
>   tmpdir=$(mktemp --directory)
>   soffice "-env:UserInstallation=file://""$tmpdir""/soffice" \
>           --headless --invisible --convert-to csv "$xslfile"
>
> The "-env:UserInstallation=..." part is to ensure that LibreOffice
> will not use the GUI even if there is an instance of LibreOffice with
> GUI already running.

Thanks to Hisham and to you; this could be very useful (last time I
needed to read from an .ods file, I wrote a small XML parser...).

Even just lurking in this list [insert guilt here] is always rewarding :-)

--
   Enrico

Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel or Libreoffice files

Tomás Guisasola-2
In reply to this post by Joshua Jensen-2
Hi Josh

I am searching for the same tool Luciano is asking for and I had found
yours a promising solution.  But I cannot find how to install it,
although it seems pretty simple, since it is a lonely .lua file.  I took
a look at the code and found that it depends on other libraries, such as
ziparchive and xmlize which I couldn't install either (these are C
libraries; not so simple to install :-).  I notice also that there is an
issue reporting difficulties installing the library on Linux (which is
my platform :-) and on a MacOSX:

https://github.com/jjensen/lua-xlsx/issues/3

Have you consider producing a rockspec?  It should solve installation
problems on both Linux and MacOSX...

Regards,
Tomás

On 2016-12-28 00:07, Josh Jensen wrote:

> On Dec 27, 2016, at 6:06 PM, luciano de souza <[hidden email]>
> wrote:
>
>> Yes, I found the data in sharedstrings.xml, but parse it, regarding
>> my
>> few experience, it's now
>> inviable.
>> If there is no a Lua module to read xlsx, it will be more realistic
>> to
>> use Pascal for this purpose.
>
> https://github.com/jjensen/lua-xlsx [1]
>
> -Josh
>
> Links:
> ------
> [1] https://github.com/jjensen/lua-xlsx

Reply | Threaded
Open this post in threaded view
|

Re: Reading Excel or Libreoffice files

Joshua Jensen-2
tomas wrote on 12/28/2016 11:08 AM:

> Hi Josh
>
> I am searching for the same tool Luciano is asking for and I had found
> yours a promising solution.  But I cannot find how to install it,
> although it seems pretty simple, since it is a lonely .lua file.  I
> took a look at the code and found that it depends on other libraries,
> such as ziparchive and xmlize which I couldn't install either (these
> are C libraries; not so simple to install :-).  I notice also that
> there is an issue reporting difficulties installing the library on
> Linux (which is my platform :-) and on a MacOSX:
>
> https://github.com/jjensen/lua-xlsx/issues/3
>
> Have you consider producing a rockspec?  It should solve installation
> problems on both Linux and MacOSX...
>
> Regards,
> Tomás

I don't use LuaRocks and don't have any need of it. I've maintained the
LuaPlus[1] distribution for a dozen or so years, and it has everything I
need to build various modules under multiple platforms. Although they
can be built in a standalone fashion, the xlsx, xmlize, and ziparchive
modules are also built as part of the LuaPlus distribution using its
build system JamPlus[2], a derivative of Perforce's Jam. (JamPlus is
also bootstrapped as part of the LuaPlus bootstrap process, so only
LuaPlus[1] is needed.)

If you want to build the ziparchive module yourself using your own
preferred build system, have a look at the ziparchive.jam file for
fairly 'English' build instructions. xmlize is just a single Lua file
that uses LuaExpat.

-Josh

[1] http://luaplus.org
[2] http://jamplus.org