利用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'處理完成')