FYI: getting data from an OpenOffice.org spreadsheet

Discussion in 'Python' started by alf, Sep 3, 2006.

  1. alf

    alf Guest

    Sybren Stuvel wrote:
    > Hi folks,
    >
    > Sometimes I have to feed data from an OpenOffice.org spreadsheet into
    > some Python program. To make that really easy, I've written a small
    > example program that connects to a running OpenOffice.org instance and
    > reads the data from the currently opened spreadsheet.
    >
    > Check out http://www.stuvel.eu/archive/28/ to see the source and
    > requirements. It took a lot of digging in the rather complex OOo API
    > documentation, but the end result is just as I like it: clean and
    > simple. I hope this helps people out!
    >
    > Sybren


    thx for sharing the experience. Do you know how to save a OO doc into M$
    Excel format - I have a Python based server running on Linux box and
    need to produce Excel files.

    --
    alf
    alf, Sep 3, 2006
    #1
    1. Advertising

  2. Hi folks,

    Sometimes I have to feed data from an OpenOffice.org spreadsheet into
    some Python program. To make that really easy, I've written a small
    example program that connects to a running OpenOffice.org instance and
    reads the data from the currently opened spreadsheet.

    Check out http://www.stuvel.eu/archive/28/ to see the source and
    requirements. It took a lot of digging in the rather complex OOo API
    documentation, but the end result is just as I like it: clean and
    simple. I hope this helps people out!

    Sybren
    --
    The problem with the world is stupidity. Not saying there should be a
    capital punishment for stupidity, but why don't we just take the
    safety labels off of everything and let the problem solve itself?
    Frank Zappa
    Sybren Stuvel, Sep 3, 2006
    #2
    1. Advertising

  3. alf enlightened us with:
    > thx for sharing the experience. Do you know how to save a OO doc
    > into M$ Excel format - I have a Python based server running on Linux
    > box and need to produce Excel files.


    Again, it required some digging and tinkering, but I figured it out.
    Check out the script at
    http://www.stuvel.eu/archive/29/ooo-spreadsheet-to-excel-with-python

    Sybren
    --
    The problem with the world is stupidity. Not saying there should be a
    capital punishment for stupidity, but why don't we just take the
    safety labels off of everything and let the problem solve itself?
    Frank Zappa
    Sybren Stuvel, Sep 3, 2006
    #3
  4. Hi folks,

    I just noticed I still had the "no archive" header on, which is rather
    stupid. If I want to make life easier for people, the information I
    posted in this thread should be archived! Here is a small summary:

    Get data from an OpenOffice.org spreadsheet with a Python script. It
    works on the currently opened spreadsheet, so you can just as well
    open an Excel sheet in OpenOffice.org and get the data from that.
    http://www.stuvel.eu/archive/28/getting-data-from-an-openofficeorg-spreadsheet

    Saving the currently opened OpenOffice.org spreadsheet to an Excel
    sheet with just a few lines of Python code. There are still people
    that favour MS Office and request Excel output from software. This is
    easily done with OpenOffice.org and Python:
    http://www.stuvel.eu/archive/29/ooo-spreadsheet-to-excel-with-python

    Sybren
    --
    Sybren Stüvel
    Stüvel IT - http://www.stuvel.eu/
    Sybren Stuvel, Sep 3, 2006
    #4
  5. alf

    John Machin Guest

    Sybren Stuvel wrote:
    > Hi folks,
    >
    > Sometimes I have to feed data from an OpenOffice.org spreadsheet into
    > some Python program. To make that really easy, I've written a small
    > example program that connects to a running OpenOffice.org instance and
    > reads the data from the currently opened spreadsheet.
    >
    > Check out http://www.stuvel.eu/archive/28/ to see the source and
    > requirements. It took a lot of digging in the rather complex OOo API
    > documentation, but the end result is just as I like it: clean and
    > simple. I hope this helps people out!
    >


    Suppose one has over a hundred spreadsheets (real-life example: budgets
    from an organisation's reporting centres) ... manually opening each in
    OOo Calc is less than appealing, and not very robust.

    With the 2nd example (alf's question: copy OOo Calc file to Excel xls
    file), I don't see any advantage at all over the manual procedure (1)
    start OOo Calc (2) open Calc file (3) File > Save As > select Microsoft
    Excel 97 etc format > ... what am I missing? Your solution requires (1)
    edit script to change name of output file; save script (where?) (2)
    start OOo Calc with magic-spell on the command line (3) open calc file
    (4) run script.

    How does one write a script that can be in control e.g. script starts
    OOo (if necessary), and extracts some data from all spreadsheet files
    in a given directory (while one is at lunch)?

    Cheers,
    John
    John Machin, Sep 3, 2006
    #5
  6. John Machin enlightened us with:
    > Suppose one has over a hundred spreadsheets (real-life example:
    > budgets from an organisation's reporting centres) ... manually
    > opening each in OOo Calc is less than appealing, and not very
    > robust.


    True. There are functions that can load files as well. Combined with
    the glob module, this could automatically handle all files in a
    certain directory.

    > With the 2nd example (alf's question: copy OOo Calc file to Excel
    > xls file), I don't see any advantage at all over the manual
    > procedure (1) start OOo Calc (2) open Calc file (3) File > Save As >
    > select Microsoft Excel 97 etc format > ... what am I missing? Your
    > solution requires (1) edit script to change name of output file;
    > save script (where?) (2) start OOo Calc with magic-spell on the
    > command line (3) open calc file (4) run script.


    It's just a demonstration of the file saving process. Indeed, the
    saving not all that useful, but someone requested it and I wrote it.

    > How does one write a script that can be in control e.g. script
    > starts OOo (if necessary),


    That's easily done with a system call or the subprocess or popen2
    modules.

    > and extracts some data from all spreadsheet files in a given
    > directory (while one is at lunch)?


    Just add a few lines to load the file instead of picking the currently
    opened one. Do you want me to do more digging and extend the code? I'm
    willing to do that, but only if it makes you happy :)

    Sybren
    --
    Sybren Stüvel
    Stüvel IT - http://www.stuvel.eu/
    Sybren Stuvel, Sep 3, 2006
    #6
  7. alf

    John Machin Guest

    Sybren Stuvel wrote:
    > John Machin enlightened us with:
    > > Suppose one has over a hundred spreadsheets (real-life example:
    > > budgets from an organisation's reporting centres) ... manually
    > > opening each in OOo Calc is less than appealing, and not very
    > > robust.

    >
    > True. There are functions that can load files as well. Combined with
    > the glob module, this could automatically handle all files in a
    > certain directory.
    >
    > > With the 2nd example (alf's question: copy OOo Calc file to Excel
    > > xls file), I don't see any advantage at all over the manual
    > > procedure (1) start OOo Calc (2) open Calc file (3) File > Save As >
    > > select Microsoft Excel 97 etc format > ... what am I missing? Your
    > > solution requires (1) edit script to change name of output file;
    > > save script (where?) (2) start OOo Calc with magic-spell on the
    > > command line (3) open calc file (4) run script.

    >
    > It's just a demonstration of the file saving process. Indeed, the
    > saving not all that useful, but someone requested it and I wrote it.


    Other possible responses: (1) suggest the obvious(?)
    less-labour-intensive no-script method (2) show how to automate it
    robustly.

    >
    > > How does one write a script that can be in control e.g. script
    > > starts OOo (if necessary),

    >
    > That's easily done with a system call or the subprocess or popen2
    > modules.
    >
    > > and extracts some data from all spreadsheet files in a given
    > > directory (while one is at lunch)?

    >
    > Just add a few lines to load the file instead of picking the currently
    > opened one. Do you want me to do more digging and extend the code? I'm
    > willing to do that, but only if it makes you happy :)


    Firstly, let me say that you are highly commended for wading so far
    into the OOo docs and producing two pieces of code that actually do
    something. I've opened up the docs two or three times, said "Waaahht
    the ...." and closed them rapidly.

    I don't "want" you to do anything. However the interests of
    evangelising/spreading the use of OOo software might be advanced were
    you to go the extra step or two and post a recipe for simple data
    extraction from a named file, rather than the currently open file. Add
    a few hints like what are the types of the data that you get back
    (especially how dates are distinguished from numbers, if at all) and
    you'll be a hero :)

    Cheers,
    John
    John Machin, Sep 4, 2006
    #7
  8. John Machin enlightened us with:
    > Firstly, let me say that you are highly commended for wading so far
    > into the OOo docs and producing two pieces of code that actually do
    > something. I've opened up the docs two or three times, said "Waaahht
    > the ...." and closed them rapidly.


    Thanks. I had the same response, except that I had the gut feeling
    that in the end things were going to be easy ;-)

    > I don't "want" you to do anything. However the interests of
    > evangelising/spreading the use of OOo software might be advanced
    > were you to go the extra step or two and post a recipe for simple
    > data extraction from a named file, rather than the currently open
    > file. Add a few hints like what are the types of the data that you
    > get back (especially how dates are distinguished from numbers, if at
    > all) and you'll be a hero :)


    Sounds very tempting. I'll see what I can come up with, and write a
    proper article on my website. Don't count on it being finished soon,
    though, because this week is going to be very busy for me, and after
    that I'll be on a two-week holiday.

    Sybren
    --
    Sybren Stüvel
    Stüvel IT - http://www.stuvel.eu/
    Sybren Stuvel, Sep 4, 2006
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. HokBen
    Replies:
    0
    Views:
    735
    HokBen
    Nov 9, 2006
  2. News123
    Replies:
    0
    Views:
    279
    News123
    Apr 28, 2010
  3. Torsten Mohr

    Read / Write OpenOffice SpreadSheet ?

    Torsten Mohr, Dec 17, 2010, in forum: Python
    Replies:
    10
    Views:
    820
    Hans-Peter Jansen
    Dec 18, 2010
  4. PerlFAQ Server
    Replies:
    0
    Views:
    657
    PerlFAQ Server
    Feb 3, 2011
  5. PerlFAQ Server
    Replies:
    0
    Views:
    662
    PerlFAQ Server
    Apr 4, 2011
Loading...

Share This Page