此文转载自：https://blog.csdn.net/weixin_52855865/article/details/110109460#commentBox
Python-Excel处理
这是一个关于测试报告（Excel）的总结与绘制直方图的脚本。脚本的功能是实现对测试报告中多个工作页（sheet）提取P/Fp/F的单元格（cell），并分别统计数量，再据此绘制直方图。
一、模块的安装
我们需要用到以下几个模块：
1.pip：该工具包提供了对Python包的查找、下载、安装、卸载功能。
2.xlrd：读取excel
3.xlwt：写入excel
4.matplotlib：Python的2D绘图库
pip官网：https://pypi.org/project/pip/
为了下载pip.py先安装wget：
yum -y install wget
下载pip安装文件并安装：
wget https://bootstrap.pypa.io/get-pip.py
python get-pip.py
安装驱动（ddt）、xlrd和xlwt：
pip install ddt
pip install xlrd
pip install xlwt
安装matplotlib：
sudo apt-get install python3-matplotlib
二、读取Excel——xlrd
首先，我们需要理解Excel文件的三层级对象：workbook、sheet、cell。
即：
workbook = xlrd.*
sheet = workbook.*
cell = sheet.cell*
1.xlrd基础接口用法
xlrd各接口用法：
(1) 打开文件
workbook = xlrd.open_workbook('123.xlsx')
(2) 获取文件中包含的所有工作表名称
sheets = workbook.sheet_names()
(3) 选定工作表
sheet = workbook.sheets()[0] #通过索引获取
sheet = workbook.sheet_by_index(0) #通过顺序获取
sheet = workbook.sheet_by_name('sheet1') #通过表名获取
(4) 获取行数和列数
nrows = sheet.nrows
ncols = sheet.ncols
(5) 打印“表名”“行数”“列数”
print（sheet.name,sheet.nrows,sheet.ncols）
(6) 获取全表数据
for i in range(1,nrows+1): #对于行，从第一行读取到总行数
for j in range(1,ncols+1): #对于列，从第一列读取到总列数
data = sheet.cell(i-1,j-1).value
Tips1：
for i in range(1,n)
i 的取值为1,2,3……n-1。
当然也可以写成：
for i in range(0,nrows):
for j in range(0,ncols):
data = sheet.cell(i,j).value
但我个人更倾向于第一种，因为第一种写法的意义更明确。
Tips2：对于工作表的第一行第一列
data = sheet.cell(0,0).value
Tips3：value表示“值”，是否加value决定输出格式。
data = sheet.cell(1,2).value
输出如下：
--------------------------------------------------------------------------
data = sheet.cell(1,2)
输出如下：
2.循环读取sheet
代码如下：
workbook = xlrd.open_workbook(fname)
sheets = workbook.sheet_names() #获取所有工作表名称并形成数组
nsheets = len（sheets） #得出数组长度
for z in range(1,nsheets+1):
sheet = workbook.sheet_by_index(z-1)
3.在sheet中定位关键字
代码如下：
global rowst #定义为全局变量方便其他函数调用
global colst #定义为全局变量方便其他函数调用
for i in range(1,nrows+1):
for j in range(1,ncols+1):
data1 = sheet.cell(i-1,j-1).value
if (data1=='P')or(data1=='Fp')or(data1=='F'):
rowst = i
colst = j
#得到关键字所在单元格坐标（i,j）
Tips1：
if (data1==‘P’) or (data1==‘Fp’) or (data1==‘F’):
不能写成：
if data1 == （‘P’ or ‘Fp’ or ‘F’）:
4.在sheet中确定首位关键字
在上面的代码中获得的包含关键字的单元格有很多个，那么如何确定满足条件（所在行列全为P/Fp/F）的首位关键字呢？
我们需要以下两步：
① 判断是否满足条件
② 一旦满足条件立刻退出循环
代码如下：
count_l = 0
count_col = 0
for l in range(colst,ncols+1):
data_l = sheet.cell(rowst-1,l-1).value #读取关键字所在行的数据
if data_l != "": #如果单元格内容不为空
count_col+=1 #自加一，统计有数据的单元格个数
if (data_l=='P') or (data_l=='Fp') or (data_l=='F'):
count_l+=1 #自加一，统计含有关键字的单元格个数
if count_l == count_col: #如果两者的数量相等，则可以确定该关键字所在行全为P/Fp/F
#继续判断列是否满足条件
count_w = 0
count_row = 0
for w in range(rowst,nrows+1):
data_w = sheet.cell(w-1,colst-1).value #读取关键字所在列的数据
if data_w != "": #如果单元格内容不为空
count_row+=1 #自加一，统计有数据的单元格个数
if (data_w=='P' or data_w=='Fp' or data_w=='F'):
count_w+=1 #自加一，统计含有关键字的单元格个数
if count_w == count_row: #如果两者的数量相等，则可以确定该关键字所在列全为P/Fp/F
print (rowst,end=',')
print (colst)
return #确定首位满足条件的单元格即退出循环
Tips1：
if data_l == "":
""表示单元格数据为空，有框线无内容也为空。
如果写成
if data_l == None:
None也表示单元格内容为空，但是有框线无内容不判定为空。
Tips2:
count_l+=1
表示变量自加一，也可以写成：
count_l = count_l +1
Tips3:
print (rowst,end=’,’)
print (colst)
end=’'表示输出结果不换行显示，‘，’表示用逗号隔开，则输出结果表示为（rowst，colst）。
5. 统计数据并将结果保存为数组
代码如下：
global count_P
global count_Fp
global count_F
count_P = 0
count_Fp = 0
count_F = 0
list = [] #创建一个空数组列表
for m in range(rowst,nrowst+1):
for n in range(colst,ncols+1):
data2 = sheet.cell(m-1,n-1).value
if data2 == 'P':
count_P+=1 #统计P的数量
elif data2 == 'Fp':
count_Fp+=1 #统计Fp的数量
elif data2 == 'F':
count_F+=1 #统计F的数量
total = count_P + count_Fp + count_F
list.append(count_P)
list.append(count_Fp)
list.append(count_F)
list.append(total)
print (list)
Tips1：append()函数用于在列表末尾添加新的对象。
Tips2：将结果保存成数组是为了方便后续将数据写入新的Excel。
三、写入新Excel——xlwt
1.目的表格
欲获得的表格如下：
| Bug Summary | ||||
|---|---|---|---|---|
| – | sheet1 | sheet2 | …… | sheet n |
| P | ||||
| Fp | ||||
| F | ||||
| Total |
2.创建新Excel并写入数据
代码如下：
workbook = xlwt.Workbook() #创建空白工作表，注意W大写
worksheet = workbook.add_sheet('sheet1') #创建空白工作页并命名
worksheet.write_merge(0,0,0,nsheets,"Bug Summary",style) #写入表头并合并第一行单元格
i=1
for sheet in sheets： #对数组sheets中的数据扫描
worksheet.write(1,i,sheet,style) #从第二行第二列开始将数据写入第二行
i+=1
worksheet.write(2,0,'P',style)
worksheet.write(3,0,'Fp',style)
worksheet.write(4,0,'F',style)
worksheet.write(5,0,'Total',style)
list1 = list[::4] #从第一位到最后一位，每隔4位取一个值组成新的数组，则list1为所有工作页中P的数量的数组
list2 = list[1::4] #从第二位到最后一位，每隔4位取一个值组成新的数组，则list2为所有工作页中Fp的数量的数组
list3 = list[2::4] #从第三位到最后一位，每隔4位取一个值组成新的数组，则list3为所有工作页中F的数量的数组
list = list[3::4] #从第四位到最后一位，每隔4位取一个值组成新的数组，则list4为所有工作页中Total总数的数组
#写入数据
j=1
for num in list1:
worksheet.write(2,j,num,style) #在第三行写入list1
j+=1
j=1
for num in list2:
worksheet.write(3,j,num,style) #在第四行写入list2
j+=1
j=1
for num in list3:
worksheet.write(4,j,num,style) #在第五行写入list3
j+=1
j=1
for num in list4:
worksheet.write(5,j,num,style) #在第六行写入list4
j+=1
workbook.save('111.xlsx') #保存为111.xlsx
print ("Done") #输出已完成
Tips1：对于一行数据的数组，写入excel时只能按行写入，所以本代码中将数组进行拆分，按行写入。
Tips2：
worksheet.write(i,j,num,style)
如果未定义格式，则style可省略，按默认格式录入。
需要注意的是，带格式写入只有以上一种写法，以下写法是错误的：
worksheet.write(0,0,label=“Bug Summary”,style)
而对于不带格式写入，以下两种写法都是正确的：
worksheet.write(0,0,label=“Bug Summary”)
worksheet.write(0,0,“Bug Summary”)
Tips3：注意 j+=1，不然会报错重复写入。
Tips4：
for num in list
num不需要定义，表示对数组内容按位读取。
3.自定义表格样式
先定义一个空style，再定义字体、边框。
这是一个设定好格式但允许改变颜色的style，代码如下：
① 新建一个style
def set_color(color):
#新建一个style
style = xlwt.XFStyle()
② 设置字体样式
font = xlwt.Font() #新建一个字体样式
font.name = 'Arial' #字体
font.bold = True #黑体加粗
font.colour_index = color #允许自定义颜色，注意接口中的拼写为colour
font.underline = True #下划线
font.italic = True #斜体
style.font = font
③ 设置边框
borders = xlwt.Borders() #新建一个边框样式
borders.left = xlwt.Borders.THIN #设置左边框为实线
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
style.borders = borders
Tips： 其他样式
borders.top = xlwt.Borders.DASHED #虚线
borders.top = xlwt.Borders.NO_LINE #无框线
④ 设置对齐方式
alignment = xlwt.Alignment() #新建一个对齐模板
alignment.horz = xlwt.Alignment.HORZ_CENTER #水平方向居中对齐
#alignment.horz = xlwt.Alignment.HORZ_LEFT 左对齐
#alignment.horz = xlwt.Alignment.HORZ_RIGHT 右对齐
alignment.vert = xlwt.alignment.VERT_CENTER
#alignment.vert = xlwt.Alignment.VERT_TOP 上对齐
#alignment.vert = xlwt.Alignmnet.VERT_BOTTOM 下对齐
style.alignment = alignment
Tips：其他对齐
#alignment.horz = xlwt.Alignment.HORZ_GENERAL 默认对齐
--------------------------------------------------------------------------
#alignment.horz = xlwt.Alignment.HORZ_FILLED 填满对齐
--------------------------------------------------------------------------
#alignment.horz = xlwt.Alignment.HORZ_DISTRIBUTED 分布式对齐
⑤ 设置背景色
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN #实填充
pattern.pattern_fore_color = 4 #填充为蓝色
style.pattern = pattern
下图是颜色对照表：
⑥ 设置完成
设置完成后记得：
return style
四、绘制直方图
绘制直方图需要用到以下几个模块
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
1.在直方图上标注数据
先定义一个在直方图上标注数据的函数
def autolabel(rects):
for rect in rects:
height = rect.get_height() #获取高度
plt.text(rect.get_x() + rect.get_width()/2,height,height) #标注点的位置为（x的坐标+直方的宽度）/2 取中
2.绘制直方图
代码如下：
df = pd.read_excel('111.xlsx','sheet1') #读取111.xlsx的sheet1工作页
fig = plt.figure()
x = np.arange(1,nsheets) #定义横坐标x的取值数组，必须为数组
list1 = list[::4]
list2 = list[1::4]
list3 = list[2::4] #在其他函数里需要再取一遍
t0 = plt.bar(x,list1,width=0.3,label='P',fc='b') #画出P的直方图，表示为蓝色
t1 = plt.bar(x+0.3,list2,width=0.3,label='Fp',fc='r') #画出Fp的直方图，表示为红色
t2 = plt.bar(x+0.6,list3,width=0.3,label='F',fc='g') #画出F的直方图，表示为绿色
autolabel(t0)
autolabel(t1)
autolabel(t2) #用autolabel函数进行标注
plt.legend(['P','Fp','F']) #绘制右上角的注释图
plt.xlabel('case') #设置横轴
plt.ylabel('amount') #设置纵轴
plt.title('summary') #设置标题
plt.show() #绘制图象
绘制的直方图如下：
五、其他
以下这篇文章对纠错很有帮助：python操作excel中遇到的错误
希望这篇文章对正在学习的你也有帮助，如有疑问欢迎留言o(≧v≦)o~~
