引子
最近在做一些数据处理和计算的工作,因为数据是以.csv格式保存的,因此刚开始直接用Excel来处理。但是做着做着发现重复的劳动其实并没有多大的意义,于是就想着写个小工具帮着处理。以前正好在一本书上看到过使用Python来处理Excel表格,可惜没有仔细看。于是我到处查找资料,终于算是完成了任务,因此撰写此文就算是总结吧。
在这里我还会顺带介绍一下如何处理.csv文件,因为某些情况下这是必须的(后面会提到)。
环境配置
下面的环境配置是针对Windows操作系统的,其他操作系统差别不是很大,可以参考一下。
我使用的Python版本号是2.7.12,如果你的Python是2.x,请确保在2.6以上。在Python 3.x上可能会有些不太一样,所以要注意,可以参考其他的文档和博客。
Python自带的csv
模块可以处理.csv文件。
xlrd
和xlwt
两个模块分别用来读Excel和写Excel,只支持.xls和.xlsx格式,Python不默认包含。这两个模块之间相互独立,没有依赖关系,也就是说可以根据需要只安装其中一个。
xlutils
模块可以同时读写一个已存在的Excel文件,依赖于xlrd
和xlwt
。
安装这三个模块时,可以去PyPI去搜索需要的模块并下载。在我写这篇文章时,xlrd
是有一个适合python 3.x的.whl文件和一个通用的.tar.gz的源码文件。xlwt
是提供了同时适用于python 2.x和3.x的.whl文件和一个通用的.tar.gz的源码文件,还提供了pip的安装方式。xlutils
与xlwt
情况相同。
安装过程中我是默认你已经配好了环境变量,并且pip是可用的,如果在命令行里执行pip命令显示找不到命令,可以在网上搜索相关解决方案,这里不做赘述。
下面简单说一下几种安装模块的方式。
安装源码文件需要先解压,然后进入到源码目录执行下面命令:
python setup.py install
- 1
安装.whl文件执行下面命令:
pip install <你的.whl文件>
- 1
使用pip安装前要确认你的网络是可用的,然后执行命令:
pip install <模块名>
- 1
当你看到类似Successfully的字样时,表明已经安装成功了。如果没成功,那就一定是有什么原因了,可以自己上网搜索具体解决方案。
使用xlrd
读Excel
xlrd
提供的接口比较多,常用的如下:
open_workbook()
打开指定的Excel文件,返回一个Book对象。
通过Book对象可以得到各个Sheet对象(一个Excel文件可以有多个Sheet,每个Sheet就是一张表格)。
Book.nsheets
返回Sheet的数目。
Book.sheets()
返回所有Sheet对象的list。
Book.sheet_by_index(index)
返回指定索引处的Sheet。相当于Book.sheets()[index]
。
Book.sheet_names()
返回所有Sheet对象名字的list。
Book.sheet_by_name(name)
根据指定Sheet对象名字返回Sheet。
通过Sheet对象可以获取各个单元格,每个单元格是一个Cell对象。
Sheet.name
返回表格的名称。
Sheet.nrows
返回表格的行数。
Sheet.ncols
返回表格的列数。
Sheet.row(r)
获取指定行,返回Cell对象的list。
Sheet.row_values(r)
获取指定行的值,返回list。
Sheet.col(c)
获取指定列,返回Cell对象的list。
Sheet.col_values(c)
获取指定列的值,返回list。
Sheet.cell(r, c)
根据位置获取Cell对象。
Sheet.cell_value(r, c)
根据位置获取Cell对象的值。
Cell.value
返回单元格的值。
下面是一段示例代码:
import xlrd
wb = xlrd.workbook('test.xls')
# 打印每张表的最后一列
# 方法1
for s in wb.sheets():
print "== The last column of sheet '%s' ==" % (s.name)
for i in range(s.nrows):
print s.row(i)[-1].value
# 方法2
for i in range(wb.nsheets):
s = wb.sheet_by_index(i)
print "== The last column of sheet '%s' ==" % (s.name)
for v in s.col_values(s.ncols - 1):
print v
# 方法3
for name in wb.sheet_names():
print "== The last column of sheet '%s' ==" % (name)
s = wb.sheet_by_name(name)
c = s.ncols - 1
for r in range(s.nrows):
print s.cell_value(r, c)
使用xlwt
写Excel
相对来说,xlwt
提供的接口就没有xlrd
那么多了,主要如下:
Workbook()
是构造函数,返回一个工作簿的对象。
Workbook.add_sheet(name)
添加了一个名为name的表,类型为Worksheet。
Workbook.get_sheet(index)
可以根据索引返回Worksheet(前提是已经添加到Workbook中了)。
Worksheet.write(r, c, vlaue)
是将vlaue填充到指定位置。
Worksheet.row(n)
返回指定的行。
Row.write(c, value)
在某一行的指定列写入value。
Worksheet.col(n)
返回指定的列。
通过对Row.height
或Column.width
赋值可以改变行或列默认的高度或宽度。(单位:0.05 pt,即1/20 pt)
Workbook.save(filename)
保存文件。
有这么几点需要注意一下:
-
xlwt
模块最大能写65535行,256列,如果超过这个范围,程序运行就会出现错误,那么可能需要找一些其他方法来解决。如果我们只注重数据的处理,那么可以采用csv
模块来替代。 -
文件默认的编码方式是
ascii
,如果要改变编码方式,指定Workbook()
的encoding
参数,如
Workbook(encoding='utf-8')
。 -
表的单元格默认是不可重复写的,如果有需要,在调用
add_sheet()
的时候指定参数cell_overwrite_ok=True
即可。
下面是一段示例代码:
import xlwt
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet('sheet_test', cell_overwrite_ok=True)
sheet.write(0, 0, 'Python')
sheet.row(0).write(1, 'is')
sheet.write(0, 2, 'very very useful.')
sheet.col(2).width = 4000
book.save('test.xls')
除了基本的写入数据之外,xlwt
还可以改变单元格格式。上面的write
方法允许接受一个XFStyle
(意为eXcel File Style)类型的参数,放在最后的位置。easyxf()
可以快速生成一个XFStyle对象。
这里简单介绍一下其用法:
import datetime, xlwt
# ... some code
font = xlwt.Font()
font.name = 'Arial'
font.height = 240 # font size: 12pt
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x0A
style = xlwt.XFStyle()
style.num_format_str = '0.00%'
style.font = font
style.pattern = pattern
a = 8
b = 10
# 以百分比的形式显示,保留两位小数
sheet.write(0,3, float(a) / b, style)
# 显示日期
sheet.row(0).write(4, datetime.date(2016,8,14), xlwt.easyxf(
'font: name Arial, height 240;'
'pattern: pattern squares, fore_color red;',
num_format_str = 'YYYY-MM-DD'
)
)
使用xlutils
修改Excel
通过xlrd.open_workbook()
打开的Book对象是只读的,不能直接对其进行修改操作,而xlwt.Workbook()
返回的Workbook对象虽然可写,但是写的时候只能从零写起,那如果要修改一个已经存在的Excel该怎么办呢?
庆幸的是,在xlutils.copy
中有个copy()
方法,我们可以将一个xlrd.Book
对象转化为一个xlwt.Workbook
对象,这样我们就可以直接对已存在的Excel文件进行修改了。
用法举例如下:
import xlrd
import xlutils.copy
book = xlrd.open_workbook('test.xls', formatting_info=True)
wtbook = xlutils.copy.copy(book)
wtsheet = wtbook.get_sheet(0)
wtsheet.write(0, 0, 'Ok, changed!')
wtbook.save('test.xls')
要注意的是:
-
调用
xlrd.open_workbook()
时,如果不指定formatting_info=True
,那么修改后整个文档的样式会丢失。对一个单元格进行write
操作时,如果不指定样式,也会将原来的样式丢失。 -
注意调用
copy()
的方法。也可以通过声明from xlutils.copy import copy
来直接调用copy()
。
使用csv
读写csv文件
与读写Excel文件相比,csv文件的读写是相当方便的。
直接看下面的例子吧。
import csv
file1 = open('test1.csv', 'rb')
reader = csv.reader(file1)
for line in reader:
for col in line:
print col,
print
file1.close()
file2 = file('test2.csv', 'wb')
writer = csv.writer(file2)
writer.writerow([1,2,3,4,5])
writer.writerows([
[6,7,8],
[9,10,11,12],
[13,14,15,16,17]
])
file2.close()
这个程序涉及到了下面的方法:
csv.reader()
返回一个DictReader对象。csv.writer()
返回一个DictWriter对象。DictWriter.writerow()
写一行。DictWriter.writerows()
写多行。
需要注意的问题是:当我们需要写csv的时候,打开文件一定要带上’b’,否则可能会往文件里输出空行。Python 3.x情况会有些不同。
More…
[1] https://github.com/python-excel/xlrd/
[2] https://github.com/python-excel/xlwt/
[3] https://github.com/python-excel/tutorial/