Python 合并一个Excel文件中格式一样的sheet
一个文件中有多个格式相同的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()