利用python将多页sheet多张excel合并为一张表格

任务目标：上期讲到报表的拆分，本次采用逆向工作思路，将各区域模块下的多个sheet镇街excel表（格式一致），按照各单位区域情况，合并为一张excel

直接上代码

#!/usr/bin/python
# -*- coding:utf-8 -*-

import os
import xlrd
import pandas as pd
from pandas import DataFrame

# 生成将要汇总的文件
outputfile = r'***\区域汇总表.xlsx'
writer = pd.ExcelWriter(outputfile, engine='openpyxl')

# 合并文件目录下每张excel下的sheet表
xls_names = [x for x in os.listdir() if x.endswith(".xls")]
for xls_name in xls_names:
wb = xlrd.open_workbook(xls_name)
sheets = wb.sheet_names()
data = DataFrame()
for i in range(len(sheets)):
    df = pd.read_excel(xls_name, sheet_name=i, skiprows=1)
    data = data.append(df)
data.to_excel(excel_writer=writer, sheet_name=xls_name, encoding='utf-8', index=False)
writer.save()
writer.close()

# 合并所有excel表
wb = xlrd.open_workbook(outputfile)
sheets1 = wb.sheet_names()
data1 = DataFrame()
for i in range(len(sheets1)):
    df = pd.read_excel(outputfile, sheet_name=i)
    data1 = data1.append(df)
data1.to_excel(excel_writer=writer, sheet_name=u'区域汇总表', encoding='utf-8', index=False)
writer.save()
writer.close()
print(u'处理完成')