python批量汇总工作簿和工作表
1、汇总一个工作簿中多个工作表
基本逻辑：
（1）新建一个列表存储内容
（2）打开第一张表，统计表有多少行
（3）将表的数据一行一行汇总在一起
（4）将汇总的内容保存到工作表中
import xlrd # XlsxWriter只能创建新文件。它无法读取或修改现有文件。 import xlsxwriter import os # 打开工作簿 wb = xlrd.open_workbook('D:/test01/年龄表.xlsx') # 读取工作簿的sheet名 sheets = wb.sheet_names() # 统计工作簿汇总表的个数 nsheets = len(sheets) # 每个工作表有多少行 for nsht in range(nsheets): table = wb.sheets()[nsht] nrows = table.nrows print(nrows) total = [['姓名', '年龄', '来源']] # 查看汇总表里面有多少字段 label = table.row_values(0) item1 = label.index("姓名") item2 = label.index("年龄") for nsht in range(nsheets): table = wb.sheets()[nsht] nrows = table.nrows for nr in range(1, nrows): item = [table.row_values(nr)[item1], table.row_values(nr)[item2]] item.append(sheets[nsht]) total.append(item) # 新建一个工作簿 workbook = xlsxwriter.Workbook("D:/test01/年龄汇总test.xlsx") worksheet = workbook.add_worksheet() # 新建一个工作表，命名为sheet for i in range(len(total)): for j in range(len(total[i])): worksheet.write(i, j, total[i][j]) workbook.close()
方法二：采用xlwings汇总数据，并且设置条件格式（大于平均值的标注颜色）
数据来源：国家统计局原煤、原油、天然气产量的月度数据
数据需求：将各月份当期产量数据汇总到一个新的工作簿
import pandas as pd
import xlwings as xw
raw_coal = pd.read_excel(r"统计局数据.xlsx", engine='openpyxl', sheet_name='原煤')
crude_oil = pd.read_excel(r"统计局数据.xlsx", engine='openpyxl', sheet_name='原油')
natural_gas = pd.read_excel(r"统计局数据.xlsx", engine='openpyxl', sheet_name='天然气')
data = pd.merge(raw_coal, crude_oil, on = '指标')
data = pd.merge(data, natural_gas, on = '指标')
finally_data = data[['指标', '原煤产量当期值(万吨)', '原油产量当期值(万吨)', '天然气产量当期值(亿立方米)']]
# print(finally_data)
# 新建文档汇总数据
wb = xw.Book()
sht = wb.sheets['sheet1']
sht.name = 'finally_data'
columns = list(finally_data.columns)
sht.range('A1').value = columns
finally_data1 = finally_data.values
sht.range('A2').value = finally_data1
describe = finally_data.describe()
avg = list(describe.loc['mean', :])
# 大于均值的数在EXCEL位置
red_原煤 = list(finally_data.index[finally_data['原煤产量当期值(万吨)']>avg[0]])
red_position1 = ['B' +str(i+2) for i in red_原煤]
red_原油 = list(finally_data.index[finally_data['原油产量当期值(万吨)']>avg[1]])
red_position2 = ['C' +str(i+2) for i in red_原油]
red_天然气 = list(finally_data.index[finally_data['天然气产量当期值(亿立方米)']>avg[2]])
red_position3 = ['D' +str(i+2) for i in red_天然气]
red = red_position1 + red_position2 + red_position3
# 小于均值的数在EXCEL位置
blue_原煤 = list(finally_data.index[finally_data['原煤产量当期值(万吨)']<avg[0]])
blue_position1 = ['B' +str(i+2) for i in blue_原煤]
blue_原油 = list(finally_data.index[finally_data['原油产量当期值(万吨)']<avg[1]])
blue_position2 = ['C' +str(i+2) for i in blue_原油]
blue_天然气 = list(finally_data.index[finally_data['天然气产量当期值(亿立方米)']<avg[2]])
blue_position3 = ['D' +str(i+2) for i in blue_天然气]
blue = blue_position1 + blue_position2 + blue_position3
# print(red)
# print(blue)
a_range = f'A1:D22'
sht.range(a_range).api.Font.Name='微软雅黑'
sht.range(a_range).api.Borders(8).LineStyle = 1 # 上边框
sht.range(a_range).api.Borders(9).LineStyle = 1 # 下边框
sht.range(a_range).api.Borders(7).LineStyle = 1 # 左边框
sht.range(a_range).api.Borders(10).LineStyle = 1 # 右边框
sht.range(a_range).api.Borders(12).LineStyle = 1 # 内横边框
sht.range(a_range).api.Borders(11).LineStyle = 1 # 内纵边框
# 区域内字体颜色成白色，单元格变成黑色
b_range = f'A1:D1'#区域第一行
sht.range(b_range).api.Font.Color = 0xffffff
sht.range(b_range).color=(0, 0, 0)
# 在excel 表格里改变字体颜色
for i in red:
sht.range(i).api.Font.Color = 0x0000ff
for i in blue:
sht.range(i).api.Font.Color = 0xFF0000
wb.save('结果数据.xlsx')
wb.close()
2、汇总多个工作簿的内容
数据说明：
1）excel文件夹中有两个文件，2018.xlsx 和2019.xls
2）2019的文件表头在第二行，2018的表头在第一行
3）需要的字段不在同一列，并且除了需要的字段可能还有其它不相关的内容
基本步骤：
（1）查看有几个工作簿
（2）打开第一个工作簿，统计表中内容
（3）将工作簿的内容汇总
（4）将汇总的内容保存到工作表中
import xlrd
import xlsxwriter
import os
# 读取文件夹中的文件名 filelist = os.listdir('.\excel文件夹') print(filelist) # 新建一个列表存储汇总的数据，定义好列名 # 此处也可以用dataframe来新建和处理 total = [['部门名称', '招考职位', '职位简介', '招考人数', '专业', '学历','来源']] # 要整理的excel表格 xls、xlsx文件所在文件夹 # 新建一个list存放xlsx文件(包含路径) collect_xlsx = [] # 将excel文件选出来 for file in filelist: fileExpand = os.path.splitext(file)[1] # print(fileExpand) if (fileExpand == '.xlsx')|(fileExpand == '.xls'): # 绝对路径和相对路径 file_path = 'D:/工作文档/03-数据分析分享/excel文件夹/' + file # file_path = "./excel文件夹/" + file collect_xlsx.append(file_path) # elif fileExpand == '.xls': # collect_xlsx.append(file) for file_xlsx in collect_xlsx: if file_xlsx == "D:/工作文档/03-数据分析分享/excel文件夹/2019.xls": wb = xlrd.open_workbook(file_xlsx) sheets = wb.sheet_names() nsheets = len(sheets) for nsht in range(nsheets): table = wb.sheets()[nsht] nrows = table.nrows # 提取标签,2019年的数据从第二行开始提取 label = table.row_values(1) # 将需要的字段通过label提取出来 item1 = label.index("部门名称") item2 = label.index("招考职位") item3 = label.index("职位简介") item4 = label.index("招考人数") item5 = label.index("专业") item6 = label.index("学历") for nr in range(2, nrows): item = [table.row_values(i)[item1], table.row_values(i)[item2], table.row_values(i)[item3],\ table.row_values(i)[item4], table.row_values(i)[item5], table.row_values(i)[item6]] item.append(file_xlsx + sheets[nsht]) total.append(item) else: wb = xlrd.open_workbook(file_xlsx) sheets = wb.sheet_names() nsheets = len(sheets) for nsht in range(nsheets): table = wb.sheets()[nsht] nrows = table.nrows # 提取标签,2018年的数据从第一行开始提取，2018的部门名称叫做招录机关 label = table.row_values(0) # 将需要的字段通过label提取出来 item1 = label.index("招录机关") item2 = label.index("招考职位") item3 = label.index("职位简介") item4 = label.index("招考人数") item5 = label.index("专业") item6 = label.index("学历") for nr in range(1, nrows): item = [table.row_values(i)[item1], table.row_values(i)[item2], table.row_values(i)[item3],\ table.row_values(i)[item4], table.row_values(i)[item5], table.row_values(i)[item6]] item.append(file_xlsx + sheets[nsht]) total.append(item) # 新建一个工作簿 workbook = xlsxwriter.Workbook("total_test.xlsx") worksheet = workbook.add_worksheet() for i in range(len(total)): for j in range(len(total[i])): worksheet.write(i, j, total[i][j]) workbook.close()
浙公网安备 33010602011771号