使用python来实现报表自动化

本文转载于http://blog.csdn.net/coraline_m?viewmode=contents

本篇文章将介绍：

• xlwt 常用功能
• xlrd 常用功能
• xlutils 常用功能
• xlwt写Excel时公式的应用
• xlwt写入特定目录（路径设置）

xlwt 语言中，写入Excel文件的扩展工具。可以实现指定表单、指定单元格的写入。支持excel03版到excel2013版。使用时请确保已经安装python环境。

---

xlrd Python语言中，读取Excel的扩展工具。可以实现指定表单、指定单元格的读取。使用时请确保已经安装python环境。

NOTICE:

• xlwt对Excel只能进行只写操作
• xrrd对Excel只能进行只读操作

此外，还有xlutils.copy可以实现Excel的复制再编辑。

---

1.python写excel — xlwt常用功能

A.准备工作

• 安装xlwt ：在终端中输入pip install xlwt或者easy_install xlwt
• 引入xlwt包 ：

import xlwt # 写

	
	

		1
	




	
	

		1
	

B.基础教程

• 新建工作簿&增加sheet： 新建一个工作簿，然后往里添加sheet

f = xlwt.Workbook() # 创建工作簿 sheet1 = f.add_sheet(u'sheet1', cell_overwrite_ok=True) #一个excel表格中可以添加多个sheet

	
	

		1
	

	

		2
	

	

		3
	




	
	

		1
	

	

		2
	

	

		3
	

• 往sheet中写入内容: sheet.write函数可以传三个参数 
  第i（参数1）第j（参数2）列存入内容（参数3）

sheet1.write(i, j, '第i行第j列存放此内容', style) # 这条语句实现的功能就是往第i行第j列存第三个参数的内容，第四个参数是样式(如字体，背景)，可以不传第四个参数。

	
	

		1
	

	

		2
	




	
	

		1
	

	

		2
	

• 合并单元格并写入内容：

sheet1.write_merge(x, x + m, y, y + n, '内容', style) # 这条y语句表示将[x:x+m]行[y:y+n]列的矩阵合并成一个单元格。存放第五个参数的内容，同理，style参数可以不传参

	
	

		1
	

	

		2
	




	
	

		1
	

	

		2
	

• 最后使用f.save(‘demo’) 
  就可以把f保存到excel了

C.实战

• 我们可以先新建一个工作簿，然后往里添加两个sheet，然后查看效果

#coding=utf-8 import xlwt

f = xlwt.Workbook() # 创建工作簿 sheet1 = f.add_sheet(u'葡小萄', cell_overwrite_ok=True)
sheet2 = f.add_sheet(u'小葡萄', cell_overwrite_ok=True)

f.save('xlwt_tutorial')

	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	




	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	

• 效果如下，发现表格xlwt_tutorial中有两个sheet。

• 我们开始往sheet中写入内容，不传入style参数 
  先只使用write函数

#coding=utf-8 import xlwt

f = xlwt.Workbook() # 创建工作簿 sheet1 = f.add_sheet(u'葡小萄', cell_overwrite_ok=True)
sheet2 = f.add_sheet(u'小葡萄', cell_overwrite_ok=True)

row = 0 temp = [u'姓名',u'年龄',u'学校',u'专业'] for pos,v in enumerate(temp):
    sheet1.write(row,pos,v)
row += 1 sheet1.write(row,0,u'葡萄')
sheet1.write(row,1,18)
sheet1.write(row,2,u'北京电影学院')
row += 1 sheet1.write(row,0,u'椰子')
sheet1.write(row,1,20)
sheet1.write(row,2,u'帝国国王科技大学')

f.save('xlwt_tutorial')

	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	

	

		9
	

	

		10
	

	

		11
	

	

		12
	

	

		13
	

	

		14
	

	

		15
	

	

		16
	

	

		17
	

	

		18
	

	

		19
	

	

		20
	

	

		21
	




	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	

	

		9
	

	

		10
	

	

		11
	

	

		12
	

	

		13
	

	

		14
	

	

		15
	

	

		16
	

	

		17
	

	

		18
	

	

		19
	

	

		20
	

	

		21
	

• 效果如下，我们建立了一个3行4列的表格。（write函数行和列值都是从0开始的）

• 下面我们使用write_merge函数来合并单元格并写入 
  在f.save之前添加一行代码

sheet1.write_merge(1,2,3,3,u'汉语言文学')

	
	

		1
	




	
	

		1
	

• 效果如下，将第2-3行第4列合并 
  

---

2.pythonxd读excel —xlrd常用功能

A.准备工作

• 安装xlrd ：在终端中输入pip install xlrd或者easy_install xlrd
• 引入xlrd包 ：

import xlrd # 读

	
	

		1
	




	
	

		1
	

B.基础教程&实战

• 打开一个Excel，然后输出所有sheet的名字

#coding=utf-8 import xlrd import uniout

f = xlrd.open_workbook(r'xlwt_tutorial') print f.sheet_names()

	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	




	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

输出：[u’葡小萄’, u’小葡萄’]

• 得到表格里的所有的sheet

for i in range(len(f.sheet_names())):
     sheet1 = workbook.sheet_by_index(i)

	
	

		1
	

	

		2
	




	
	

		1
	

	

		2
	

• 得到sheet中的内容

f = xlrd.open_workbook(r'xlwt_tutorial')
sheet1 = f.sheet_by_index(0) #打开第一个sheet sheet2 = f.sheet_by_name(u'小葡萄') #打开名字为小葡萄的sheet #输出sheet的名称,行数,列数 print sheet1.name,sheet1.nrows,sheet1.ncols print sheet2.name,sheet2.nrows,sheet2.ncols

	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	




	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

输出为： 
葡小萄 3 4 
小葡萄 0 0 
.

print sheet1.row_values(1) #获取第二行内容 print sheet1.col_values(2) #获取第三列内容

	
	

		1
	

	

		2
	




	
	

		1
	

	

		2
	

输出为: 
[u’葡萄’, 18.0, u’北京电影学院’, u’汉语言文学’] 
[u’学校’, u’北京电影学院’, u’帝国国王科技大学’] 
.

# 获取单元格内容 print sheet1.cell(1,0).value # 获取单元格内容的数据类型 print sheet1.cell(1,1).ctype #ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	




	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

输出为： 
葡萄 
2

---

3.xlutils 常用功能

A.准备工作

• 安装xlutils ：在终端中输入pip install xlutils或者easy_install xlutils
• 引入xlutils包 ：

import xlutils 

	
	

		1
	




	
	

		1
	

B.xlutils中copy功能

我们可能会遇到一个问题，想对一个存储好的Excel进行编辑。 
但是xlrd是只读模式，不能进行编写。 
而xlwt是只写模式，不能读入Excel文件进行编辑。 
我们可以采用xlrd打开一个文档，后采用xlutils中copy功能把文档拷贝，然后进行编辑即可。

import xlrd from xlutils.copy import copy

f = xlrd.open_workbook(r'xlwt_tutorial')
wb = copy(f) # 将f拷贝到wb sheet1 = wb.get_sheet(0) # 打开sheet print sheet1.name
sheet1.write(3,0,'change')

wb.save('xlwt_tutorial')

	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	

	

		9
	

	

		10
	

	

		11
	




	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	

	

		9
	

	

		10
	

	

		11
	

输出为： 
葡小萄 
输出的表格已经改变。 

PS: 可以看到第二行第四列和第三行第四列合并格已经在COPY的时候被毁掉了。

---

4.xlwt写Excel时公式的应用

我们写用xlwt写一个表格

#coding=utf-8 import xlwt

f = xlwt.Workbook() # 创建工作簿 sheet1 = f.add_sheet(u'得分统计', cell_overwrite_ok=True)

mdict = {"monkey":{"writing":80,"reading":60,"speaking":70,"listening":60}, "grape":{"writing":100,"reading":80,"speaking":70,"listening":60}}

sheet1.write(0,0,u'得分统计')
sheet1.write(1,0,u'书法得分')
sheet1.write(2,0,u'阅读得分')
sheet1.write(3,0,u'演讲得分')
sheet1.write(4,0,u'听力得分')
temp = ['writing','reading','speaking','listening'] for pos,name in enumerate(mdict):
    sheet1.write(0,pos+1,name) for p,v in enumerate(temp):
        sheet1.write(p+1,pos+1,mdict[name][v])

f.save('得分统计')

	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	

	

		9
	

	

		10
	

	

		11
	

	

		12
	

	

		13
	

	

		14
	

	

		15
	

	

		16
	

	

		17
	

	

		18
	

	

		19
	

	

		20
	

	

		21
	

	

		22
	




	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	

	

		9
	

	

		10
	

	

		11
	

	

		12
	

	

		13
	

	

		14
	

	

		15
	

	

		16
	

	

		17
	

	

		18
	

	

		19
	

	

		20
	

	

		21
	

	

		22
	

打开表格为： 

我们现在想做的是统计grape的总分和monkey的总分: 
在f.save之前加入代码：

sheet1.write(5,0,u'总分统计') for i in range(len(mdict)):
    forstr = chr(65+i+1)+'2+'+chr(65+i+1)+'3+'+chr(65+i+1)+'4+'+chr(65+i+1)+'5' print forstr
    sheet1.write(5,i+1,xlwt.Formula(forstr))

	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	




	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

输出为： 
B2+B3+B4+B5 
C2+C3+C4+C5 
打开表格为： 

---

5.xlwt写入特定目录（路径设置）

由于代码分层的缘故，使代码整体框架优美。 
我们需要把文件写入到特定目录下。 
但是由于xlwt中没有直接写入到特定目录的函数。 
因此使用shutil.move函数来把文件MOV到特定目录下：

#coding=utf-8 import xlwt import os import shutil

path = '../sheet/' isExists = os.path.exists(path) # 判断目录是否存在 if not isExists: # 如果目录不存在,新建目录 os.makedirs(path)

f = xlwt.Workbook() # 创建工作簿 sheet1 = f.add_sheet(u'得分统计', cell_overwrite_ok=True)

mdict = {"monkey":{"writing":80,"reading":60,"speaking":70,"listening":60}, "grape":{"writing":100,"reading":80,"speaking":70,"listening":60}}

sheet1.write(0,0,u'得分统计')
sheet1.write(1,0,u'书法得分')
sheet1.write(2,0,u'阅读得分')
sheet1.write(3,0,u'演讲得分')
sheet1.write(4,0,u'听力得分')
temp = ['writing','reading','speaking','listening'] for pos,name in enumerate(mdict):
    sheet1.write(0,pos+1,name) for p,v in enumerate(temp):
        sheet1.write(p+1,pos+1,mdict[name][v])

sheet1.write(5,0,u'总分统计') for i in range(len(mdict)):
    forstr = chr(65+i+1)+'2+'+chr(65+i+1)+'3+'+chr(65+i+1)+'4+'+chr(65+i+1)+'5' print forstr
    sheet1.write(5,i+1,xlwt.Formula(forstr))

f.save('得分统计')
shutil.move(u'得分统计', path)

	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	

	

		9
	

	

		10
	

	

		11
	

	

		12
	

	

		13
	

	

		14
	

	

		15
	

	

		16
	

	

		17
	

	

		18
	

	

		19
	

	

		20
	

	

		21
	

	

		22
	

	

		23
	

	

		24
	

	

		25
	

	

		26
	

	

		27
	

	

		28
	

	

		29
	

	

		30
	

	

		31
	

	

		32
	

	

		33
	

	

		34
	

	

		35
	

	

		36
	

	

		37
	




	
	

		1
	

	

		2
	

	

		3
	

	

		4
	

	

		5
	

	

		6
	

	

		7
	

	

		8
	

	

		9
	

	

		10
	

	

		11
	

	

		12
	

	

		13
	

	

		14
	

	

		15
	

	

		16
	

	

		17
	

	

		18
	

	

		19
	

	

		20
	

	

		21
	

	

		22
	

	

		23
	

	

		24
	

	

		25
	

	

		26
	

	

		27
	

	

		28
	

	

		29
	

	

		30
	

	

		31
	

	

		32
	

	

		33
	

	

		34
	

	

		35
	

	

		36
	

	

		37
	

效果图：