python read excel.zip_excel读取_python excel_python_read_excel_pyt

import xlrd import xlwt import os import os.path import re pattern = re.compile(r'xlsx') ww=xlwt.Workbook() codelist = ["C1_1", "C1_2", "C1_3", "C1_4", "C1_5", "C1_6", "C1_7", "C1_8", "C1_9", "C1_10Q", "C1_10S", "C1_10T", "C1_11", "C1_12", "C1_13", "C1_14", "C1_15Y", "C1_15Z", "C1_16", "C1_17", "C2_1", "C2_2AD", "C2_2AE", "C2_3", "C3_1", "C3_2", "C3_3", "C3_4", "C3_5", "C4_1", "C4_2", "C5_1", "C5_2", "C5_3", "C5_4A", "C5_4B", "C5_4C"] rootdir=r"D:\国家行政学院\贵州项目\贵州省区县数据" for dirname in os.listdir(rootdir): for dirname1 in os.listdir(rootdir+"\\"+ dirname): bi = 0 print(rootdir + "\\" + dirname+"\\"+dirname1) # 创建该市的sheet表，并写入表头 sheetw = ww.add_sheet(dirname+dirname1) for ai in list(range(1, 38)): sheetw.write(0, ai, codelist[ai - 1]) sheetw.write(0, 0, "部门") sheetw.write(0, 38, "事项数") # sheetw.write(a, 0, dirname) for dirname2 in os.listdir(rootdir + "\\" + dirname+"\\"+dirname1): bi += 1 print(rootdir + "\\" + dirname + "\\" + dirname1+"\\"+dirname2) try: sheetw.write(bi, 0,dirname2) except Exception as e: pass print(dirname2) for dirname3 in os.listdir(rootdir + "\\" + dirname + "\\" + dirname1+"\\"+dirname2): if (re.findall(pattern, dirname3) != []): print(dirname3) try: wr = xlrd.open_workbook(rootdir + "\\" + dirname + "\\" + dirname1 + "\\" + dirname2+"\\"+dirname3) sheet = wr.sheets()[0] print(sheet.nrows,sheet.ncols) for number in list(range(4, sheet.nrows-5)): # print(sheet.cell(number,5).ctype) if (sheet.cell(number, 7).ctype != 2): num = number - 4 sheetw.write(bi, 38, number - 4) break # 将总事项数写入 # sheetw.write(bi, 38, number-4) # 将已经计算好平均值的列写入 # sheetw.write(bi, 0, dirname) try: sheetw.write(bi, 1, sheet.cell(sheet.nrows - 1, 5).value) except Exception as e: pass sheetw.write(bi, 4, sheet.cell(sheet.nrows - 1, 9).value) sheetw.write(bi, 6, sheet.cell(sheet.nrows - 1, 10).value) for ci in list(range(7, 9)): sheetw.write(bi, ci, sheet.cell(sheet.nrows - 1, ci + 6).value) sheetw.write(bi, 10, sheet.cell(sheet.nrows - 1, 16).value) for ci in list(range(13, 23)): sheetw.write(bi, ci, sheet.cell(sheet.nrows - 1, ci + 7).value) for ci in list(range(24, 35)): sheetw.write(bi, ci, sheet.cell(sheet.nrows - 1, ci + 7).value) # 将要计算平均值的列计算平均值后写入 # 命名按照在收集表中的列名 sumG = 0 sumH = 0 sumL = 0 sumP = 0 sumS = 0 sumT = 0 sumAE = 0 for di in list(range(4, number)): if (sheet.cell(di, 6).ctype == 1): sumG += 100 if (sheet.cell(di, 7).value == 100): sumH += 100 if (sheet.cell(di, 11).value == 100): sumL += 100 if ((sheet.cell(di, 15).value == 100) or (sheet.cell(di, 15).value == '无')): sumP += 100 if (sheet.cell(di, 18).value == 100): sumS += 100 if (sheet.cell(di, 19).value == 100): sumT += 100 if (sheet.cell(di, 30).value == 100): sumAE += 100 if(number!=4): sheetw.write(bi, 2, sumG / (number - 4)) sheetw.write(bi, 3, sumH / (number - 4)) sheetw.write(bi, 5, sumL / (number - 4)) sheetw.write(bi, 9, sumP / (number - 4)) sheetw.write(bi, 11, sumS / (number - 4)) sheetw.write(bi, 12, sumT / (number - 4)) sheetw.write(bi, 23, sumAE / (number - 4)) # 计算指标“审查标准”每个等级的个数，并写入 sumcha = 0 sumzhong = 0 sumyou = 0 for ei in list(range(4, number)): if (sheet.cell(ei, 42).value == "差"): sumcha += 1 if (sheet.cell(ei, 42).value == "中"): sumzhong += 1 if (sheet.cell(ei, 42).value == "优"): sumyou += 1 sheetw.write(bi, 35, sumyou) sheetw.write(bi, 36, sumzhong) sheetw.write(bi, 37, sumcha) except xlrd.biffh.XLRDError as e: pass ww.save(r"D:\国家行政学院\贵州项目\贵州县级数据汇总表.xls")