【Python3】xlwt/xlrd模块读取和新建excel并生成直方图 | 您所在的位置:网站首页 › excel怎么创建空白工作簿表格 › 【Python3】xlwt/xlrd模块读取和新建excel并生成直方图 |
Python-Excel处理
一、模块的安装二、读取Excel——xlrd1.xlrd基础接口用法2.循环读取sheet3.在sheet中定位关键字4.在sheet中确定首位关键字5. 统计数据并将结果保存为数组
三、写入新Excel——xlwt1.目的表格2.创建新Excel并写入数据3.自定义表格样式
四、绘制直方图1.在直方图上标注数据2.绘制直方图
五、其他
这是一个关于测试报告(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).valueTips1: 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).valueTips3:value表示“值”,是否加value决定输出格式。 data = sheet.cell(1,2).value输出如下: 输出如下: 代码如下: 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–sheet1sheet2……sheet nPFpFTotal 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 = bordersTips: 其他样式 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 = alignmentTips:其他对齐 #alignment.horz = xlwt.Alignment.HORZ_GENERAL 默认对齐
⑤ 设置背景色 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~~ |
CopyRight 2018-2019 实验室设备网 版权所有 |