基础代码

# 导入模块
from openpyxl import Workbook,load_workbook
from copy import copy

def load_worksheet_to_new_workbook(file_path,ws):
    # 加载表数据
    wb1 = load_workbook(file_path)
    ws1 = wb1.active
    # 写入到新的excel中，每个表作为一个worksheet,复制表样式
    max_row = ws1.max_row
    max_column = ws1.max_column
    for i in range(1,max_row+1):
        for j in range(1,max_column+1):
            cell = ws.cell(row=i,column=j) # 新的excel的每一个单元格
            load_cell = ws1.cell(row=i,column=j) # 加载的excel数据的单元格
            cell.value = load_cell.value
            cell.font = copy(load_cell.font)
            cell.fill = copy(load_cell.fill)
            cell.border = copy(load_cell.border)
            cell.number_format = copy(load_cell.number_format)
    # 设置列宽
    ws.column_dimensions['A'].width = 15
    ws.column_dimensions['G'].width = 15


if __name__ == '__main__':
    # 新建excel
    wb = Workbook()
    ws_huadong = wb.create_sheet('华东',0)
    ws_huanan = wb.create_sheet('华南',1)
    ws_huabei = wb.create_sheet('华北',2)
    # 调用函数
    load_worksheet_to_new_workbook('../data/表1-华东.xlsx',ws_huadong)
    load_worksheet_to_new_workbook('../data/表2-华南.xlsx',ws_huanan)
    load_worksheet_to_new_workbook('../data/表3-华北.xlsx',ws_huabei)
    # 保存excel
    wb.save('综合数据.xlsx')
    wb.close()

整合代码

# 导入模块
from openpyxl import Workbook,load_workbook
from copy import copy
# 新建excel
wb = Workbook()
ws_huadong = wb.create_sheet('华东',0)
ws_huanan = wb.create_sheet('华南',1)
ws_huabei = wb.create_sheet('华北',2)

# 加载表数据
wb1 = load_workbook('../data/表1-华东.xlsx')
ws1 = wb1.active
# 写入到新的excel中，每个表作为一个worksheet,复制表样式
max_row = ws1.max_row
max_column = ws1.max_column
for i in range(1,max_row+1):
    for j in range(1,max_column+1):
        cell = ws_huadong.cell(row=i,column=j) # 新的excel的每一个单元格
        load_cell = ws1.cell(row=i,column=j) # 加载的excel数据的单元格
        cell.value = load_cell.value
        cell.font = copy(load_cell.font)
        cell.fill = copy(load_cell.fill)
        cell.border = copy(load_cell.border)
        cell.number_format = copy(load_cell.number_format)
# 设置列宽
ws_huadong.column_dimensions['A'].width = 15
ws_huadong.column_dimensions['G'].width = 15
# 保存excel
wb.save('综合数据.xlsx')
wb.close()

说明:本文章为大熊自动化办公课程的学习笔记