[306]python操作excel表格(xlrd/xlwt)
[306]python操作excel表格(xlrd/xlwt)
python操作excel主要用到xlrd和xlwt这两个库，即xlrd是读excel，xlwt是写excel的库。可从这里下载https://pypi.python.org/pypi。下面分别记录python读和写excel.
python读excel——xlrd
这个过程有几个比较麻烦的问题，比如读取日期、读合并单元格内容。下面先看看基本的操作：
首先读一个excel文件，有两个sheet，测试用第二个sheet，sheet2内容如下：
python 对 excel基本的操作如下：
# -*- coding:utf-8 -*-
import requests, xlrd, xlwt
headers = {
'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8',
'Accept-Encoding': 'gzip, deflate',
'Accept-Language': 'zh-CN,zh;q=0.9',
'Connection': 'keep-alive',
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36',
}
def read_excel():
url = 'http://www.chinaclear.cn/zdjs/editor_file/20180523163635140.xls'
response = requests.get(url, headers=headers)
content = response.content
# 打开文件，是直接操作excel内容
workbook = xlrd.open_workbook(file_contents=content)
# 方式二：打开文件，是直接操作excel文档
workbook = xlrd.open_workbook(filename=r'F:\demo.xlsx')
# 获取所有sheet
print(workbook.sheet_names()) # [u'sheet1', u'sheet2']
sheet2_name = workbook.sheet_names()[0]
# 根据sheet索引或者名称获取sheet内容
sheet2 = workbook.sheet_by_index(0) # sheet索引从0开始
# sheet2 = workbook.sheet_by_name('sheet2')
# sheet的名称，行数，列数
print(sheet2.name, sheet2.nrows, sheet2.ncols)
# 获取整行和整列的值（数组）
rows = sheet2.row_values(3) # 获取第四行内容
cols = sheet2.col_values(2) # 获取第三列内容
print(rows)
print(cols)
# 循环获取每一行内容
for i in range(sheet2.nrows):
row = sheet2.row_values(i)
print(row)
# 获取单元格内容
print(sheet2.cell(1, 0).value.encode('utf-8'))
print(sheet2.cell_value(1, 0).encode('utf-8'))
print(sheet2.row(1)[0].value.encode('utf-8'))
# 获取单元格内容的数据类型
print(sheet2.cell(1, 0).ctype)
if __name__ == '__main__':
read_excel()运行结果如下：
那么问题来了，上面的运行结果中红框框中的字段明明是出生日期，可显示的确实浮点数。好的，来解决第一个问题：
1、python读取excel中单元格内容为日期的方式
python读取excel中单元格的内容返回的有5种类型，即上面例子中的ctype:
ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
即date的ctype=3，这时需要使用xlrd的xldate_as_tuple来处理为date格式，先判断表格的ctype=3时xldate才能开始操作。现在命令行看下：
>>> sheet2.cell(2,2).ctype #1990/2/22
3
>>> sheet2.cell(2,1).ctype #24
2
>>> sheet2.cell(2,0).ctype #小胖
1
>>> sheet2.cell(2,4).ctype #空值（这里是合并单元格的原因）
0
>>> sheet2.cell(2,2).value #1990/2/22
33656.0
>>> xlrd.xldate_as_tuple(sheet2.cell_value(2,2),workbook.datemode)
(1992, 2, 22, 0, 0, 0)
>>> date_value = xlrd.xldate_as_tuple(sheet2.cell_value(2,2),workbook.datemode)
>>> date_value
(1992, 2, 22, 0, 0, 0)
>>> date(*date_value[:3])
datetime.date(1992, 2, 22)
>>> date(*date_value[:3]).strftime('%Y/%m/%d')
'1992/02/22' 即可以做下简单处理，判断ctype是否等于3，如果等于3，则用时间格式处理：
if (sheet.cell(row,col).ctype == 3):
date_value = xlrd.xldate_as_tuple(sheet.cell_value(rows,3),book.datemode)
date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d')那么问题又来了，上面 sheet2.cell(2,4).ctype 返回的值是0，说明这个单元格的值是空值，明明是合并的单元格内容"好朋友"，这个是我觉得这个包功能不完善的地方，如果是合并的单元格那么应该合并的单元格的内容一样，但是它只是合并的第一个单元格的有值，其它的为空。
>>> sheet2.col_values(4)
[u'\u5173\u7cfb', u'\u597d\u670b\u53cb', '', u'\u540c\u5b66', '', '', u'\u4e00\u4e2a\u4eba', '']
>>> for i in range(sheet2.nrows):
print sheet2.col_values(4)[i]
关系
好朋友
同学
一个人
>>> sheet2.row_values(7)
[u'\u65e0\u540d', 20.0, u'\u6682\u65e0', '', '']
>>> for i in range(sheet2.ncols):
print sheet2.row_values(7)[i]
无名
20.0
暂无
>>> 2、读取合并单元格的内容
这个是真没技巧，只能获取合并单元格的第一个cell的行列索引，才能读到值，读错了就是空值。
即合并行单元格读取行的第一个索引，合并列单元格读取列的第一个索引，如上述，读取行合并单元格"好朋友"和读取列合并单元格"暂无"只能如下方式：
>>> print sheet2.col_values(4)[1]
好朋友
>>> print sheet2.row_values(7)[2]
暂无
>>> sheet2.merged_cells # 明明有合并的单元格，为何这里是空
[]疑问又来了，合并单元格可能出现空值，但是表格本身的普通单元格也可能是空值，要怎么获取单元格所谓的"第一个行或列的索引"呢？
这就要先知道哪些是单元格是被合并的！
3、获取合并的单元格
读取文件的时候需要将formatting_info参数设置为True，默认是False，所以上面获取合并的单元格数组为空，
>>> workbook = xlrd.open_workbook(r'F:\demo.xlsx',formatting_info=True)
>>> sheet2 = workbook.sheet_by_name('sheet2')
>>> sheet2.merged_cells
[(7, 8, 2, 5), (1, 3, 4, 5), (3, 6, 4, 5)]merged_cells返回的这四个参数的含义是：(row,row_range,col,col_range),其中[row,row_range)包括row,不包括row_range,col也是一样，即(1, 3, 4, 5)的含义是：第1到2行（不包括3）合并，(7, 8, 2, 5)的含义是：第2到4列合并。
利用这个，可以分别获取合并的三个单元格的内容：
>>> print sheet2.cell_value(1,4) #(1, 3, 4, 5)
好朋友
>>> print sheet2.cell_value(3,4) #(3, 6, 4, 5)
同学
>>> print sheet2.cell_value(7,2) #(7, 8, 2, 5)
暂无发现规律了没？是的，获取merge_cells返回的row和col低位的索引即可！ 于是可以这样一劳永逸：
>>> merge = []
>>> for (rlow,rhigh,clow,chigh) in sheet2.merged_cells:
merge.append([rlow,clow])
>>> merge
[[7, 2], [1, 4], [3, 4]]
>>> for index in merge:
print sheet2.cell_value(index[0],index[1])
暂无
好朋友
同学
>>> python写excel——xlwt
写excel的难点可能不在构造一个workbook的本身，而是填充的数据，不过这不在范围内。在写excel的操作中也有棘手的问题，比如写入合并的单元格就是比较麻烦的，另外写入还有不同的样式。这些要看源码才能研究的透。
我"构思"了如下面的sheet1，即要用xlwt实现的东西：
基本上看起来还算复杂，而且看起来"很正规"，完全是个人杜撰。
代码如下：
'''
设置单元格样式
'''
def set_style(name,height,bold=False):
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = name # 'Times New Roman'
font.bold = bold
font.color_index = 4
font.height = height
# borders= xlwt.Borders()
# borders.left= 6
# borders.right= 6
# borders.top= 6
# borders.bottom= 6
style.font = font
# style.borders = borders
return style
#写excel
def write_excel():
f = xlwt.Workbook() #创建工作簿
'''
创建第一个sheet:
sheet1
'''
sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True) #创建sheet
row0 = [u'业务',u'状态',u'北京',u'上海',u'广州',u'深圳',u'状态小计',u'合计']
column0 = [u'机票',u'船票',u'火车票',u'汽车票',u'其它']
status = [u'预订',u'出票',u'退票',u'业务小计']
#生成第一行
for i in range(0,len(row0)):
sheet1.write(0,i,row0[i],set_style('Times New Roman',220,True))
#生成第一列和最后一列(合并4行)
i, j = 1, 0
while i < 4*len(column0) and j < len(column0):
sheet1.write_merge(i,i+3,0,0,column0[j],set_style('Arial',220,True)) #第一列
sheet1.write_merge(i,i+3,7,7) #最后一列"合计"
i += 4
j += 1
sheet1.write_merge(21,21,0,1,u'合计',set_style('Times New Roman',220,True))
#生成第二列
i = 0
while i < 4*len(column0):
for j in range(0,len(status)):
sheet1.write(j+i+1,1,status[j])
i += 4
f.save('demo1.xlsx') #保存文件
if __name__ == '__main__':
#generate_workbook()
#read_excel()
write_excel()需要稍作解释的就是write_merge方法：
write_merge(x, x + m, y, w + n, string, sytle) x表示行，y表示列，m表示跨行个数，n表示跨列个数，string表示要写入的单元格内容，style表示单元格样式。其中，x，y，w，h，都是以0开始计算的。
这个和xlrd中的读合并单元格的不太一样。
如上述：sheet1.write_merge(21,21,0,1,u’合计’,set_style(‘Times New Roman’,220,True))
即在22行合并第1,2列，合并后的单元格内容为"合计"，并设置了style。
如果需要创建多个sheet，则只要f.add_sheet即可。
如在上述write_excel函数里f.save(‘demo1.xlsx’) 这句之前再创建一个sheet2，效果如下：
代码也是真真的easy的了：
'''
创建第二个sheet:
sheet2
'''
sheet2 = f.add_sheet(u'sheet2',cell_overwrite_ok=True) #创建sheet2
row0 = [u'姓名',u'年龄',u'出生日期',u'爱好',u'关系']
column0 = [u'小杰',u'小胖',u'小明',u'大神',u'大仙',u'小敏',u'无名']
#生成第一行
for i in range(0,len(row0)):
sheet2.write(0,i,row0[i],set_style('Times New Roman',220,True))
#生成第一列
for i in range(0,len(column0)):
sheet2.write(i+1,0,column0[i],set_style('Times New Roman',220))
sheet2.write(1,2,'1991/11/11')
sheet2.write_merge(7,7,2,4,u'暂无') #合并列单元格
sheet2.write_merge(1,2,4,4,u'好朋友') #合并行单元格
f.save('demo1.xlsx') #保存文件还可以添加超链接，如：
# 添加超链接
n= "HYPERLINK"
sheet2.write_merge(9,9,2,8, xlwt.Formula(n +'("http://www.cnblogs.com/zhoujie";"jzhou\'s blog")'),set_style('Arial',300,True))
sheet2.write_merge(10,10, 2, 8, xlwt.Formula(n +'("mailto:zhoujie0111@126.com";"contact me")'),set_style('Arial',300,True))完整代码：
# -*- coding: utf-8 -*-
import xlwt
import xlrd
'''
设置单元格样式
'''
def set_style(name,height,bold=False):
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = name # 'Times New Roman'
font.bold = bold
#f.underline= Font.UNDERLINE_DOUBLE
font.color_index = 4
font.height = height
# borders= xlwt.Borders()
# borders.left= 6
# borders.right= 6
# borders.top= 6
# borders.bottom= 6
style.font = font
# style.borders = borders
return style
#读excel
def read_excel():
# 打开文件
workbook = xlrd.open_workbook(r'F:\demo.xlsx')
# 获取所有sheet
print workbook.sheet_names() # [u'sheet1', u'sheet2']
sheet2_name = workbook.sheet_names()[1]
# 根据sheet索引或者名称获取sheet内容
sheet2 = workbook.sheet_by_index(1) #sheet索引从0开始
sheet2 = workbook.sheet_by_name('sheet2')
# sheet的名称，行数，列数
print sheet2.name,sheet2.nrows,sheet2.ncols
# 获取整行和整列的值（数组）
rows = sheet2.row_values(3) #获取第四行内容
cols = sheet2.col_values(2) #获取第三列内容
print rows
print cols
# 获取单元格内容
print sheet2.cell(1,0).value.encode('utf-8')
print sheet2.cell_value(1,0).encode('utf-8')
print sheet2.row(1)[0].value.encode('utf-8')
# 获取单元格内容的数据类型
print sheet2.cell(1,0).ctype
#写excel
def write_excel():
f = xlwt.Workbook() #创建工作簿
'''
创建第一个sheet:
sheet1
'''
sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True) #创建sheet
row0 = [u'业务',u'状态',u'北京',u'上海',u'广州',u'深圳',u'状态小计',u'合计']
column0 = [u'机票',u'船票',u'火车票',u'汽车票',u'其它']
status = [u'预订',u'出票',u'退票',u'业务小计']
#生成第一行
for i in range(0,len(row0)):
sheet1.write(0,i,row0[i],set_style('Times New Roman',220,True))
#生成第一列和最后一列(合并4行)
i, j = 1, 0
while i < 4*len(column0) and j < len(column0):
sheet1.write_merge(i,i+3,0,0,column0[j],set_style('Arial',220,True)) #第一列
sheet1.write_merge(i,i+3,7,7) #最后一列"合计"
i += 4
j += 1
sheet1.write_merge(21,21,0,1,u'合计',set_style('Times New Roman',220,True))
#生成第二列
i = 0
while i < 4*len(column0):
for j in range(0,len(status)):
sheet1.write(j+i+1,1,status[j])
i += 4
'''
创建第二个sheet:
sheet2
'''
sheet2 = f.add_sheet(u'sheet2',cell_overwrite_ok=True) #创建sheet
row0 = [u'姓名',u'年龄',u'出生日期',u'爱好',u'关系']
column0 = [u'小杰',u'小胖',u'小明',u'大神',u'大仙',u'小敏',u'无名']
#生成第一行
for i in range(0,len(row0)):
sheet2.write(0,i,row0[i],set_style('Times New Roman',220,True))
#生成第一列
for i in range(0,len(column0)):
sheet2.write(i+1,0,column0[i],set_style('Times New Roman',220))
sheet2.write(1,2,'1991/11/11')
sheet2.write_merge(7,7,2,4,u'暂无') #合并列单元格
sheet2.write_merge(1,2,4,4,u'好朋友') #合并行单元格
# 插入图片
sheet2.insert_bitmap(r'F:\1.bmp',10,2)
# 添加超链接
n= "HYPERLINK"
sheet2.write_merge(9,9,2,8, xlwt.Formula(n +'("http://www.cnblogs.com/zhoujie";"jzhou\'s blog")'),set_style('Arial',300,True))
sheet2.write_merge(10,10, 2, 8, xlwt.Formula(n +'("mailto:zhoujie0111@126.com";"contact me")'),set_style('Arial',300,True))
f.save('demo1.xlsx') #保存文件
if __name__ == '__main__':
read_excel()
write_excel()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安装.whl文件执行下面命令：
pip install <你的.whl文件>使用pip安装前要确认你的网络是可用的，然后执行命令：
pip install <模块名>当你看到类似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情况会有些不同。