Openpyxl save multiple sheets in one workbook

Question

I want to read multiple excel files separately and save them in one workbook as sheets using Openpyxl, I am not sure if there is any atributes for .save() function :

df1_wb = load_workbook(path +'df1_.xlsx')
df1_ws = df1_wb.get_active_sheet()

df2_wb = load_workbook(path +'df2_.xlsx')
df2_ws = df2_wb.get_active_sheet()

df3_wb = load_workbook(path +'df3_.xlsx')
df3_ws = df3_wb.get_active_sheet()


wb.save(path+'master_file.xlsx') ??

[Openpyxl's documentation](https://openpyxl.readthedocs.io/en/stable/tutorial.html) says _You also cannot copy worksheets between workbooks._ — Gustave Coste, Aug 25 '20 at 14:40

score 0 · Answer 1 · answered Jul 09 '22 at 20:21

# import required library
# tested on openpyxl 3.0.9 and python >3.6
from openpyxl import load_workbook, Workbook
from copy import copy

def copy_worksheet_cells(new_ws, old_ws):
    # first umnerge all merged cells, if not it create problem while copying
    # reference: https://stackoverflow.com/questions/69891944/unmerge-every-cell-in-an-excel-worksheet-using-openpyxl
    for merge in list(old_ws.merged_cells):
        old_ws.unmerge_cells(range_string=str(merge))

    # copy value and style
    # reference: https://stackoverflow.com/questions/23332259/copy-cell-style-openpyxl
    for row in old_ws.rows:
        for cell in row:
            new_cell = new_ws.cell(row=cell.row, column=cell.col_idx,
                value= cell.value)
        if cell.has_style:
            new_cell.font = copy(cell.font)
            new_cell.border = copy(cell.border)
            new_cell.fill = copy(cell.fill)
            new_cell.number_format = copy(cell.number_format)
            new_cell.protection = copy(cell.protection)
            new_cell.alignment = copy(cell.alignment)
    return new_ws

wb1 = load_workbook(r'wb1.xlsx')
ws1 = wb1.active
wb2 = load_workbook(r"wb2.xlsx")
ws2 = wb2.active
wb3 = load_workbook(r"wb3.xlsx")
ws3 = wb3.active

wb = Workbook()

wb.encoding = 'utf_8_sig'
title1 = wb1.active.title + "wb1"
new_ws1 = wb.create_sheet(title1)
new_ws1 = copy_worksheet_cells(new_ws1, ws1)

title2 = wb2.active.title + "wb2"
new_ws2 = wb.create_sheet(title2)
new_ws2 = copy_worksheet_cells(new_ws2, ws2)

title3 = wb3.active.title + "wb3"
new_ws3 = wb.create_sheet(title3)
new_ws3 = copy_worksheet_cells(new_ws3, ws3)

wb.save('master_file.xlsx')

Openpyxl save multiple sheets in one workbook

1 Answers1