Python带格式合并多个Excel工作簿

把要合并的工作簿放到一个文件夹内，把这个Python代码也复制保存到同一文件夹内运行即可。

openpyxl只支持xlsx文件，所以本程序不会合并xls文件（Excel 2007之前的版本）。

from openpyxl import load_workbook
from openpyxl import Workbook
from copy import copy
from openpyxl.utils import get_column_letter
import os

path = os.path.dirname(__file__)
dest = os.path.join('合并后.xlsx')

# 复制一个单元格
def copy_cell(source_cell, target_cell):
    # 复制值
    target_cell.value = source_cell.value     
    # 复制格式
    if source_cell.has_style:
        target_cell.font = copy(source_cell.font)
        target_cell.border = copy(source_cell.border)
        target_cell.fill = copy(source_cell.fill)
        target_cell.number_format = copy(source_cell.number_format)
        target_cell.protection = copy(source_cell.protection)
        target_cell.alignment = copy(source_cell.alignment)
    return 0

# 复制一张工作表
def copy_a_sheet(source_ws, target_ws):
    area = source_ws[source_ws.dimensions]
    # 复制值
    for row in area:
        for cell in row:
            r = cell.row
            c = cell.column
            copy_cell(cell, target_ws.cell(r,c))
    # 重设列宽
    for c_index in range(source_ws.min_column,source_ws.max_column):
        c_name = get_column_letter(c_index)
        target_ws.column_dimensions[c_name].width = source_ws.column_dimensions[c_name].width
    return 0


if __name__ == '__main__':
    # 读取文件列表
    fileList = []
    for r,ds,fs in os.walk(path):
        for f in fs:
            if ".xlsx" not in f:continue
            fileList.append(os.path.join(r,f))

    # 新建工作簿
    combined_workbook = Workbook()
    combined_workbook.remove(combined_workbook['Sheet'])

    # 遍历文件列表
    for i in range(len(fileList)):
        filepath = fileList[i]
        sheetname = filepath.split('/')[-1].split('.')[0]
        combined_workbook.create_sheet(sheetname)
        combined_worksheet = combined_workbook[sheetname] 
        
        wb = load_workbook(filepath)
        ws = wb.active 
        copy_a_sheet(ws, combined_worksheet)

    # 保存工作簿    
    combined_workbook.save(dest)