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

Henry Walshaw henry.walshaw at gmail.com
Tue Feb 4 06:13:42 CET 2014 

Javier,

The only thing I could think of trying would be pyodbc (assuing you're on a Windows machine, which I know you were trying to avoid) and trying to write through an ODBC connection. Something like:

	cnxn=pyodbc.connect("""Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; Dbq=c:\\datafiles\\XLSfile.xls; readonly=0""", autocommit=True)

I haven't tested this though, so it's really just clutching at straws. Otherwise I'm not sure of any Python files that modify Excel in place. Good luck!

Cheers,
Henry

On 4 Feb 2014, at 3:53 pm, Javier Candeira <javier at candeira.com> wrote:

> Then, just to be clear: there doesn't seem to exist a Python library
> that will roundtrip an excel sheet to its original state just by
> reading it in and writing it again, without modification. Correct?
> 
> J
> 
> On Tue, Feb 4, 2014 at 3:48 PM, Henry Walshaw <henry.walshaw at gmail.com> wrote:
>> Hi Javier,
>> 
>> Like Matt below I'd recommend taking a look at openpyxl (http://pythonhosted.org/openpyxl/) We've used it a lot recently and it does give a great deal over formatting of the outgoing Excel workbook. Unfortunately it does not support reading images and charts from an existing workbook, so they would be lost if you tried reading an existing workbook as a template (openpyxl works with Excel data in memory and only accesses the file handler on save or load, which means that modifying an existing workbook is a case of reading the data in, updating the data and then saving a new excel file over the original one). However I think it's a good choice if all you're doing is manipulating workbooks full of data that the template is applied to later.
>> 
>> Cheers
>> -Henry
>> 
>> 
>> On 4 Feb 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
>> 
>> 
>> _______________________________________________
>> 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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 495 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: <http://mail.python.org/pipermail/melbourne-pug/attachments/20140204/3537d21c/attachment.sig>

More information about the melbourne-pug mailing list