[melbourne-pug] Excel spreadsheet munging under Linux. Limits?

[Tim Richardson]

There is another way, which I have also used.
You can issue a spreadsheet with a VBA-embedded query which fetches data
from a web-service (VBA support for this is very stone-age but you can make
it work), or use a view on a database server and query that (also via an
embedded macro). That is, you make the spreadsheet the client, basically.


On Tue, Feb 4, 2014 at 3:38 PM, Javier Candeira <javier at candeira.com> wrote:

> Thanks everyone. Some more background and answers:
>
> - I'm a LibreOffice fan (this is me after a love night with two
> LibreOffice hackers [1]), but this has to be MS Office. I'm automating
> an entrenched reporting process that produces pixel-perfect A4
> documents for printing, and I can't recreate the accumulated work
> expressed in the existing sheets.
> - I'm also a Google Spreadsheets fan, to the point where I have
> contributed to a library for accessing it. [2] But same as above.
> - xlwt or xlutil might work if it allows me to manipulate existing
> spreadsheets. If not, I'd try creating my reporting template and
> linking it to sheets with sample data, and to recreate the
> data-bearing sheets every week/month/whenever.
> - I don't really need to generate macros or charts, because there are
> templates that have them already. I really only have to put in data in
> an existing database (and munge the incoming data a bit before writing
> the neat rows and columns).
>
> [1] http://www.flickr.com/photos/hiperactivo/4364689348/
> [2] https://github.com/burnash/gspread
>
> Thanks again, and sorry for not giving enough background at the start
> of the thread.
>
> J
>
> On Tue, Feb 4, 2014 at 3:22 PM, Rasjid Wilcox <rasjidw at openminddev.net>
> wrote:
> > Hi Javier,
> >
> > I use xlwt (https://pypi.python.org/pypi/xlwt) to generate XLS
> spreadsheets.
> > It is a fork of pyExcelerator which is not longer maintained.
> >
> > For fairly standard spreadsheets it works well.  You can do basic
> > formatting, both in terms of fonts (bold etc), and also in terms of
> numeric
> > formatting (currency and date formats etc).
> >
> > Last I checked (and I don't think this has changed), it does not do
> charts
> > or fancy stuff like that.
> >
> > As long as it meets your needs it terms of spreadsheet complexity, it
> will
> > work well.  Runs on any platform (will be fine on a Linux web-server),
> and
> > is fast.
> >
> > I looked into this extensively a few years ago - at the time it was
> > definitely the way to go as long as it meets your requirements in terms
> of
> > capabilities.
> >
> >
> > Your other options are:
> >
> > Under Linux etc.
> >   Use a head-less copy of OpenOffice, and use its automation (pyUno) to
> > generate a spreadsheet that way.  Be prepared for a lot of pain.
> >   Use Jython (or bridge to java in some way) and use one of the Java
> > libraries to generate XLS files.  At least one of these has basic support
> > for charts (although you may need a base template file to work from).
> >
> >   I know there are some .Net libraries that can also generate xlsx files
> -
> > these may work under Mono and IronPython.
> >
> >
> > Under Windows:
> >   All of the above, with the bonus that any .Net libraries are definitely
> > going to work.
> >   Automate MS Office using Python's COM support.
> >
> >
> > The pro's of using automation (either OpenOffice or MS Office) is that
> you
> > will get access to a much greater range of features.  It will, however,
> be
> > slow, and can take noticeable amounts of time to generate a large
> > spreadsheet.
> >
> > Any of the libraries that generate the file directly will be *much*
> faster,
> > and a better fit for server-side deployment.
> >
> >
> >
> > Hmm... just seen your last email regarding macros.  Not sure if xlwt
> > supports that.  You may have luck with the xlutils library mentioned -
> > otherwise you may need to move to one of the more heavy duty options -
> > either using a Java or .Net / Mono library, or going for full automation
> > (I'd suggest MS Office over OpenOffice if going that route).
> >
> >
> > Cheers,
> >
> > Rasjid.
> >
> >
> >
> >
> >
> >
> >
> > On 4/02/2014 2:29 PM, Javier Candeira wrote:
> >>
> >> At work I need to manipulate a series of excel spreasheets, and I seem
> >> to remember that people have discussed the topic in a meeting. I have
> >> two questions to anyone who's already had to do the same:
> >>
> >> - What's the current best Python library for excel manipulation?
> >> - Are there any limitations for handling Excel sheets under Linux?
> >> What kind of thing would I need Windows (and presumably, a live copy
> >> of Office) for?
> >>
> >> As background for the question, I'm trying to avoid using Windows at
> >> all. My ideal workflow would involve users downloading generated
> >> spreadsheets from a webserver.
> >>
> >> Thanks,
> >>
> >> Javier
> >> _______________________________________________
> >>
> >> melbourne-pug mailing list
> >> melbourne-pug at python.org
> >> https://mail.python.org/mailman/listinfo/melbourne-pug
> >
> >
> > _______________________________________________
> >
> > melbourne-pug mailing list
> > melbourne-pug at python.org
> > https://mail.python.org/mailman/listinfo/melbourne-pug
> _______________________________________________
> melbourne-pug mailing list
> melbourne-pug at python.org
> https://mail.python.org/mailman/listinfo/melbourne-pug
>



-- 



*Tim Richardson, Director*
GrowthPath, Data-driven profit growth

Mobile: +61 423 091 732
Office: +61 3 8678 1850
I tweet useful business & IT tips at growthpath_au

GrowthPath Pty Ltd
ABN 76 133 733 963




<http://www.growthpath.com.au/>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/melbourne-pug/attachments/20140204/560a5048/attachment-0001.html>