2020-12-08

代码简介：python读取excel数据，并汇总结果，统计厂商个数，每个厂商的版本个数，提测型号种类，厂商总的测试通过率

import xlrd
import openpyxl
import xlwt
from xlutils.copy import copy
import os
import numpy as np
import pandas as df
from xlutils.copy import copy as xl_copy
from collections import defaultdict

wb=xlrd.open_workbook('Q:\pythonProject1\excel\盘点sun.xlsx')
sheet1=wb.sheet_by_name('梳理表0701-1116')
nrowNum=sheet1.nrows#获取行数
ncolNum=sheet1.ncols#获取列数
for i in range(0,ncolNum):
    if  sheet1.cell_value(0,i) == '厂商':
        factorycolnum=i;
       #print(sheet1.cell_value(0, factorycolnum))
    if sheet1.cell_value(0, i) == '型号':
        modelcolnum = i;
    if sheet1.cell_value(0, i) == '验收测试结果':
        resultcolnum = i;
print("厂商列号：%d 型号列号：%d 测试结果的列号：%d" % (factorycolnum,modelcolnum,resultcolnum))

print("1、加载原有的工作簿,创建新的表格，将厂商，型号，测试结果写入新的表格中")
newwb=copy(wb)#在原有表的基础上新增sheet，用来存储汇总的数据，修改原来的工作簿需要先复制
newsheet=newwb.add_sheet('年终数据盘点')#添加新的工作簿
newsheet.write(0,0,"厂商")
newsheet.write(0,1,"型号")
newsheet.write(0,2,"测试结果")
#从原有的表格中读取厂商，型号，测试验收结果
for j in range(1, nrowNum):
    newsheet.write(j, 0, sheet1.cell_value(j,factorycolnum))
    newsheet.write(j, 1, sheet1.cell_value(j, modelcolnum))
    newsheet.write(j, 2, sheet1.cell_value(j, resultcolnum))
print("2、基本信息读写完成")
newwb.save('Q:\pythonProject1\excel\盘点sun.xls')  # 读取数据不需要保存，修改需要保存为xx.xls文件

wb=xlrd.open_workbook('Q:\pythonProject1\excel\盘点sun.xls')
dataSheet=wb.sheet_by_name('年终数据盘点')
newwb=copy(wb)
newws1=newwb.get_sheet('年终数据盘点')
r=0
l=[]#创建临时列表
while r<dataSheet.nrows-1:
    r+=1
    l+=[dataSheet.row_values(r)]
# print(l)
print("3、对l的的数据进行排序")

#sorted(可迭代的对象，使用函数来确定比较方式，reverse排序的顺序，true为反向排序)
l1=sorted(l,key=lambda x:(str(x[0]),str(x[1])))
print(l1)
print('将排序后的数据，重新写入表格')
n=0
for v in l1:
    n+=1
    newws1.write(n,0,v[0])
    newws1.write(n,1,v[1])
    newws1.write(n,2,v[2])
newwb.save('Q:\pythonProject1\excel\盘点sun.xls')

print("4、对数据进行统计，并把统计结果写入excel")

wb=xlrd.open_workbook('Q:\pythonProject1\excel\盘点sun.xls',formatting_info=True)
dataSheet=wb.sheet_by_name('年终数据盘点')
newwb=copy(wb)
# newsheet=newwb.add_sheet('年终数据盘点1')
newws2=newwb.get_sheet('年终数据盘点')
print('统计数据')
totalTestNum=dataSheet.nrows
print('共提测:%d ' % totalTestNum)
# print('读取每一行，统计将厂商去重后的个数')
r=0;lfactory=[];lmodel=[]
while r<dataSheet.nrows-1:
    lfactory += [dataSheet.cell_value(r, 0)]
    lmodel += [dataSheet.cell_value(r, 1)]
    r+=1
b = set()
print('共对接厂商个数：%d' % len(set(lfactory)))
print('共对接产品型号个数：%d' % len(set(lmodel)))

i=0;d=dict()
dd=dict()
while i<totalTestNum:
    key1= dataSheet.cell_value(i, 0)
    val1= dataSheet.cell_value(i, 1)
    val2 = dataSheet.cell_value(i, 2)
    if key1 in d.keys():
        d[key1]+=[val1]
        dd[key1]+= [val2]
    else:
        d[key1]=[val1]
        dd[key1] = [val2]
    i+=1
print('将厂商名字，提测版本数，提测型号种类，通过率写入excel')
hang1=1;
newws2.write(0, 4, '厂商');newws2.write(0, 5, '提测版本数');newws2.write(0, 6, '提测型号种类')
newws2.write(0, 7, '提测通过率')
for k1 in d:#将厂商
  # print(k1,len(d[k1]),len(set(d[k1])))
  newws2.write(hang1,4,k1)
  newws2.write(hang1,5,len(d[k1]))
  newws2.write(hang1,6,len(set(d[k1])))
  hang1+=1
#计算每个厂商的通过率
hang2=1
floatdata=0.00
for k2 in dd:
   floatdata=dd[k2].count('已通过') / len(dd[k2])
   newws2.write(hang2, 7, '%.2f' %  floatdata)#写入的数据自动变成整数了，需要调整
   print('%.2f' % floatdata)
   hang2+=1
newwb.save('Q:\pythonProject1\excel\盘点sun.xls')