Python 从Excel中抽出sheet再合并

import copy
import openpyxl
from openpyxl.utils import get_column_letter
import os
import warnings
warnings.filterwarnings("ignore")
'''
reference:
https://www.pudn.com/news/62d14a1f864d5c73acb2494e.html
https://w0714.blog.csdn.net/article/details/122816165
'''

def main():
    #指定sheet的名称
    specific_sheet_name = 'sheet_specific' # 要复制的sheet的名称
    #execl文件存放位置
    file_pdir = r'xx\input'
    scr_file_path = r'xx\output\merge.xlsx'
    wb2 = openpyxl.Workbook()
    for file in os.listdir(file_pdir):
        if os.path.splitext(file)[-1] == '.xlsx':
            print('file name is : {}'.format(file))
            file_path = os.path.join(file_pdir, file)
            wb = openpyxl.load_workbook(file_path)
            sheet = wb[specific_sheet_name]
            # 创建新的sheet
            sheet2 = wb2.create_sheet(os.path.basename(file_path).split('.')[0])
            # tab颜色
            sheet2.sheet_properties.tabColor = sheet.sheet_properties.tabColor
            wm = list(sheet.merged_cells)
            if len(wm) > 0:
                for i in range(0, len(wm)):
                    cell2 = str(wm[i]).replace('(<CellRange ', '').replace('>,)', '')
                    sheet2.merge_cells(cell2)

            for i, row in enumerate(sheet.iter_rows()):
                sheet2.row_dimensions[i+1].height = sheet.row_dimensions[i+1].height
                for j, cell in enumerate(row):
                    sheet2.column_dimensions[get_column_letter(j+1)].width = sheet.column_dimensions[get_column_letter(j+1)].width
                    sheet2.cell(row=i + 1, column=j + 1, value=cell.value)
                    # 设置单元格格式
                    source_cell = sheet.cell(i+1, j+1)
                    target_cell = sheet2.cell(i+1, j+1)
                    target_cell.fill = copy.copy(source_cell.fill)
                    if source_cell.has_style:
                        target_cell._style = copy.copy(source_cell._style)
                        target_cell.font = copy.copy(source_cell.font)
                        target_cell.border = copy.copy(source_cell.border)
                        target_cell.fill = copy.copy(source_cell.fill)
                        target_cell.number_format = copy.copy(source_cell.number_format)
                        target_cell.protection = copy.copy(source_cell.protection)
                        target_cell.alignment = copy.copy(source_cell.alignment)
    if 'Sheet' in wb2.sheetnames:
        del wb2['Sheet']
    wb2.save(scr_file_path)

    wb.close()
    wb2.close()
    print('Done.')
    
if __name__ == '__main__':
    main()
          

Reference1
Reference2
Reference3