Working with Excel files
in Python
Chris Withers with help from John Machin
EuroPython 2009, Birmingham
The Tutorial Materials
These can be obtained by CD, USB drive or downloaded from here:
• http://www.simplistix.co.uk/presentations/europython2009excel.zip
The Website
The best place to start when working with Excel files in Python is the website:
• http://www.python-excel.org
© Simplistix Ltd 2009
Page 2
License
THE WORK (AS DEFINED BELOW) IS PROVIDED UNDER THE TERMS OF THIS CREATIVE COMMONS PUBLIC LICENCE ("CCPL" OR "LICENCE"). THE WORK IS
PROTECTED BY COPYRIGHT AND/OR OTHER APPLICABLE LAW. ANY USE OF THE WORK OTHER THAN AS AUTHORIZED UNDER THIS LICENCE OR COPYRIGHT
LAW IS PROHIBITED. BY EXERCISING ANY RIGHTS TO THE WORK PROVIDED HERE, YOU ACCEPT AND AGREE TO BE BOUND BY THE TERMS OF THIS
LICENCE. THE LICENSOR GRANTS YOU THE RIGHTS CONTAINED HERE IN CONSIDERATION OF YOUR ACCEPTANCE OF SUCH TERMS AND CONDITIONS.
This Creative Commons England and Wales Public Licence enables You (all capitalised terms defined below) to view, edit, modify, translate and distribute
Works worldwide for Non-Commercial purposes under the terms of this Licence, provided that You credit the Original Author.
'The Licensor' [one or more legally recognised persons or entities offering the Work under the terms and conditions of this Licence]
and
'You'
agree as follows:
1. Definitions
a) "Attribution" means acknowledging all the parties who have contributed to and have rights in the Work or Collective Work under this Licence.
b) "Collective Work" means the Work in its entirety in unmodified form along with a number of other separate and independent works, assembled
into a collective whole.
c) "Derivative Work" means any work created by the editing, modification, adaptation or translation of the Work in any media (however a work that
constitutes a Collective Work will not be considered a Derivative Work for the purpose of this Licence). For the avoidance of doubt, where the Work
is a musical composition or sound recording, the synchronization of the Work in timed-relation with a moving image ("synching") will be
considered a Derivative Work for the purpose of this Licence.
d) "Licence" means this Creative Commons England and Wales Public Licence agreement.
e) "Non-Commercial" means "not primarily intended for or directed towards commercial advantage or private monetary compensation". The
exchange of the Work for other copyrighted works by means of digital file-sharing or otherwise shall not be considered to be intended for or
directed towards commercial advantage or private monetary compensation, provided there is no payment of any monetary compensation in
connection with the exchange of copyrighted works.
f) "Original Author" means the individual (or entity) who created the Work.
g) "Work" means the work protected by copyright which is offered under the terms of this Licence.
h) For the purpose of this Licence, when not inconsistent with the context, words in the singular number include the plural number.
2. Licence Terms
2.1 The Licensor hereby grants to You a worldwide, royalty-free, non-exclusive, Licence for Non-Commercial use and for the duration of copyright in the Work.
You may:
• copy the Work;
• incorporate the Work into one or more Collective Works;
• copy the Work as incorporated in any Collective Work; and
• publish, distribute, archive, perform or otherwise disseminate the Work or the Work as incorporated in any Collective Work, to the public
in any material form in any media whether now known or hereafter created.
HOWEVER,
You must not:
• impose any terms on the use to be made of the Work, the Work as incorporated in a Collective Work that alter or restrict the terms of
this Licence or any rights granted under it or has the effect or intent of restricting the ability to exercise those rights;
• impose any digital rights management technology on the Work, the Work as incorporated in a Collective Work that alters or restricts the
terms of this Licence or any rights granted under it or has the effect or intent of restricting the ability to exercise those rights;
• make any Derivative Works;
• sublicense the Work;
• subject the Work to any derogatory treatment as defined in the Copyright, Designs and Patents Act 1988.
FINALLY,
You must:
• make reference to this Licence (by Uniform Resource Identifier (URI), spoken word or as appropriate to the media used) on all copies of
the Work and Collective Works published, distributed, performed or otherwise disseminated or made available to the public by You;
• recognise the Licensor's / Original Author's right of attribution in any Work and Collective Work that You publish, distribute, perform or
otherwise disseminate to the public and ensure that You credit the Licensor / Original Author as appropriate to the media used; and
• to the extent reasonably practicable, keep intact all notices that refer to this Licence, in particular the URI, if any, that the Licensor
specifies to be associated with the Work, unless such URI does not refer to the copyright notice or licensing information for the Work.
Additional Provisions for third parties making use of the Work
2.2. Further licence from the Licensor
Each time You publish, distribute, perform or otherwise disseminate
• the Work; or
• the Work as incorporated in a Collective Work
the Licensor agrees to offer to the relevant third party making use of the Work (in any of the alternatives set out above) a licence to use the Work on the
same terms and conditions as granted to You hereunder.
2.3. This Licence does not affect any rights that the User may have under any applicable law, including fair use, fair dealing or any other legally recognised
limitation or exception to copyright infringement.
© Simplistix Ltd 2009
Page 3
2.4. All rights not expressly granted by the Licensor are hereby reserved, including but not limited to, the exclusive right to collect, whether individually or via
a licensing body, such as a collecting society, royalties for any use of the Work for any reason which results in commercial advantage or private monetary
compensation.
3. Warranties and Disclaimer
Except as required by law, the Work is licensed by the Licensor on an "as is" and "as available" basis and without any warranty of any kind, either express or
implied.
4. Limit of Liability
Subject to any liability which may not be excluded or limited by law the Licensor shall not be liable and hereby expressly excludes all liability for loss or
damage howsoever and whenever caused to You.
5. Termination
The rights granted to You under this Licence shall terminate automatically upon any breach by You of the terms of this Licence. Individuals or entities who
have received Collective Works from You under this Licence, however, will not have their Licences terminated provided such individuals or entities remain in
full compliance with those Licences.
6. General
6.1. The validity or enforceability of the remaining terms of this agreement is not affected by the holding of any provision of it to be invalid or unenforceable.
6.2. This Licence constitutes the entire Licence Agreement between the parties with respect to the Work licensed here. There are no understandings,
agreements or representations with respect to the Work not specified here. The Licensor shall not be bound by any additional provisions that may appear in
any communication in any form.
6.3. A person who is not a party to this Licence shall have no rights under the Contracts (Rights of Third Parties) Act 1999 to enforce any of its terms.
6.4. This Licence shall be governed by the law of England and Wales and the parties irrevocably submit to the exclusive jurisdiction of the Courts of England
and Wales.
7. On the role of Creative Commons
7.1. Neither the Licensor nor the User may use the Creative Commons logo except to indicate that the Work is licensed under a Creative Commons Licence.
Any permitted use has to be in compliance with the Creative Commons trade mark usage guidelines at the time of use of the Creative Commons trade mark.
These guidelines may be found on the Creative Commons website or be otherwise available upon request from time to time.
7.2. Creative Commons Corporation does not profit financially from its role in providing this Licence and will not investigate the claims of any Licensor or user
of the Licence.
7.3. One of the conditions that Creative Commons Corporation requires of the Licensor and You is an acknowledgement of its limited role and agreement by all
who use the Licence that the Corporation is not responsible to anyone for the statements and actions of You or the Licensor or anyone else attempting to use
or using this Licence.
7.4. Creative Commons Corporation is not a party to this Licence, and makes no warranty whatsoever in connection to the Work or in connection to the
Licence, and in all events is not liable for any loss or damage resulting from the Licensor's or Your reliance on this Licence or on its enforceability.
7.5. USE OF THIS LICENCE MEANS THAT YOU AND THE LICENSOR EACH ACCEPTS THESE CONDITIONS IN SECTION 7.1, 7.2, 7.3, 7.4 AND EACH
ACKNOWLEDGES CREATIVE COMMONS CORPORATION'S VERY LIMITED ROLE AS A FACILITATOR OF THE LICENCE FROM THE LICENSOR TO YOU.
© Simplistix Ltd 2009
Page 4
Introduction
This tutorial covers the following libraries:
xlrd
• http://pypi.python.org/pypi/xlrd
• reading data and formatting from .xls files
• this tutorial covers version 0.7.1
• API documentation can be found at:
◦ https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html
xlwt
• http://pypi.python.org/pypi/xlwt
• writing data and formatting to .xls files
• this tutorial covers version 0.7.2
• incomplete API documentation can be found at:
◦ https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/doc/xlwt.html
• fairly complete examples can be found at
◦ https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/examples/
xlutils
• http://pypi.python.org/pypi/xlutils
• a collection of utilities using both xlrd and xlwt:
◦ copying data from a source to a target spreadsheet
◦ filtering data from a source to a target spreadsheet
• this tutorial covers version 1.3.0 and above.
• documentation and examples can be found at:
◦ https://secure.simplistix.co.uk/svn/xlutils/trunk/xlutils/docs/
There are still reasons why automating an Excel instance via COM is necessary:
• manipulation of graphs
• rich text cells
• reading formulae in cells
• working with macros and names
• the more esoteric things found in .xls files
© Simplistix Ltd 2009
Page 5
Installation
There are several methods of installation available. While the following examples are for
xlrd, the exact same steps can be used for any of the three libraries.
Install from Source
On Linux:
NB: Make sure you use the python you intend to use for your project.
On Windows, having used WinZip or similar to unpack xlrd-0.7.1.zip:
NB: Make sure you use the python you intend to use for your project.
Install using Windows Installer
On Windows, you can download and run the xlrd-0.7.1.win32.exe installer.
Beware that this will only install to Python installations that are in the Windows registry.
Install using EasyInstall
This cross-platform method requires that you already have EasyInstall installed. For more
information on this, please see:
• http://peak.telecommunity.com/DevCenter/EasyInstall
© Simplistix Ltd 2009
$ tar xzf xlrd.tgz
$ cd xlrd-0.7.1
$ python setup.py install
C:\> cd xlrd-0.7.1
C:\xlrd-0.7.1> \Python26\python setup.py install
easy_install xlrd
- 1
- 2
- 3
- 4
- 5
前往页