how to operate the excel by python?

John Machin sjmachin at lexicon.net
Sun Jun 12 03:16:26 CEST 2005


Rune Strand wrote:
> The key is Python for Windows :
> http://starship.python.net/crew/mhammond/win32/
> 
> See here for an Excel dispatch example:
> http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/325735
> 
> When doing such operations, I generally save all the Excel files to CSV
> files and do the operations on them using the csv module.
> 

Problems with that approach:

1. Unfortunately, "save as CSV" is very much a WYSIWYG operation. If the 
"number formats" are not sensible, loss of information can result.

Example: user gets (text) data file which needs extra columns added. 
User loads it into Excel, adds extra data, saves as csv. One column is 
an identifier which just happens to be numeric -- say a 12-digit credit 
card number 123456789012. The user doesn't care that this is showing on 
the Excel screen as 1.23457E+11 (default format) as he is using the 
6-digit customer account number 654321 to find the extra data he needs 
to add. He may not even see the 1.23457E+11 because it's in column BQ 
and he's inserting 3 columns in front of column E.

To avoid that problem, one has to check all the columns and reformat 
those that do not display all the data. This is not something that a 
user can be relied on to do, even when stated clearly in a procedure manual.

2. The tedium and error-proneness of "saving as": (a) you get given a 
file named "fubar.csv" but it was "fubar.csv.xls" before the user 
renamed it. (b) Excel files can have multiple worksheets, which have to 
be saved each as a separate csv file.

Consequently, an approach which reads the .XLS file directly has 
attractions.

One such approach, which unlike the COM approach doesn't need Excel to 
be on the reading machine, and doesn't even need Windows, is the free 
"xlrd" module [of which I am the author] -- see 
http://www.lexicon.net/sjmachin/xlrd.htm
or
http://www.python.org/pypi/xlrd/

Regards,
John



More information about the Python-list mailing list