一个文件中有多个格式相同的sheet，我们想要批量合并这些sheet，然后将处理好的文件放到一个新的文件中去：

# -*- coding: utf-8 -*-
"""
Created on Wed Sep 22 11:18:51 2021

@author: xxx
"""

import xlrd
import pandas as pd
from pandas import DataFrame
from openpyxl import load_workbook

excel_name = r"xx1概览.xlsx"
wb = xlrd.open_workbook(excel_name)
sheets = wb.sheet_names()

alldata = DataFrame()
for i in sheets[2:]: #取格式一样的sheet的名字
    df = pd.read_excel(excel_name, sheet_name=i)
    df['variable'] = i  #这1列主要是识别来自于哪个sheet
    df['product'] = 'xx'  #自己添加的，你们可以按需添加
    alldata = alldata.append(df)  #保存成一个df     

writer = pd.ExcelWriter(r"xx1概览副本.xlsx",engine='openpyxl')

#下面2个是不需要合并的，我们直接读取出来
detect = pd.read_excel("xx1概览.xlsx",sheet_name='detect')
quality = pd.read_excel("xx1概览.xlsx",sheet_name='quality')
detect['product'] = 'xx'
quality['product'] = 'xx'
detect.to_excel(writer,sheet_name='detect')
quality.to_excel(writer,sheet_name='quality')

alldata.to_excel(excel_writer=writer,sheet_name="ALLDATA")
writer.save()
writer.close()