How can I add an existing workbook sheet to another existing workbook with openpyxl?

Question

I am trying to create an new excel that contains the first sheet of a list of excel files.

I tried the following but of course line wb.create_sheet(sheet) doesn't work.

wb = Workbook('new.xlsx')

for i in range(len(file_list)):
    excel = load_workbook(file_list[i], read_only=False)
    sheet = excel[excel.sheetnames[0]]
    sheet.Name = tab_names_list[i]

    wb.create_sheet(sheet)


wb.save('new.xlsx')

At the same time replacing wb.create_sheet(sheet) with wb.copy_worksheet(sheet) gives the error ValueError: Cannot copy worksheets in read-only or write-only mode

Does this answer your question? [Copy whole worksheet with openpyxl](https://stackoverflow.com/questions/27101024/copy-whole-worksheet-with-openpyxl) — Tomerikoo, Nov 10 '20 at 14:39
No, because I get `ValueError: Cannot copy worksheets in read-only or write-only mode ` — Charalamm, Nov 10 '20 at 14:51

Vink · Accepted Answer · 2020-05-30T15:28:52.380

0

This may not work for you but this is what I use in a similar application. It does require pandas dataframes, I'm not sure if you're using them or not.

file_list = [file_1, file_2]
sheet_names = ['file1_name', 'file2_name']

writer = pd.ExcelWriter("workbook_name.xlsx", engine='xlsxwriter')

for file, s_names in zip(file_list, sheet_names):
    file.to_excel(writer, sheet_name=s_names)
writer.save()

edited May 30 '20 at 15:28

answered May 30 '20 at 13:39

Vink

571
4
15

This works for me, although it is not an openpyxl solution. Thanks – Charalamm Jun 08 '20 at 12:00

How can I add an existing workbook sheet to another existing workbook with openpyxl?

1 Answers1