0

Using python 3, I'm trying to append a sheet from an existing excel file to another excel file.

I have conditional formats in this excel file so I can't just use pandas.

from openpyxl import load_workbook

final_wb = load_workbook("my_final_workbook_with_lots_of_sheets.xlsx")
new_wb = load_workbook("workbook_with_one_sheet.xlsx")

worksheet_new = new_wb['Sheet1']
final_wb.create_sheet(worksheet_new)

final_wb.save("my_final_workbook_with_lots_of_sheets.xlsx")

This code doesn't work because the .create_sheet method only makes a new blank sheet, I want to insert my worksheet_new that I loaded from the other file into the final_wb

Jeffyx
  • 78
  • 2
  • 9
  • 1
    You can't do it. Worksheets contain workbook-specific information that means that they cannot be copied or moved between workbooks. – Charlie Clark Aug 19 '19 at 17:07

2 Answers2

0

See this issue on openpyxl discussing this same feature: https://bitbucket.org/openpyxl/openpyxl/issues/171/

It is not currently a builtin function of openpyxl, but that thread has some workarounds available that may suit your needs.

EDIT: Also found a likely duplicate question here: Copy worksheet from one workbook to another one using Openpyxl

TheKewlStore
  • 304
  • 1
  • 6
0

I found a solution using pandas.ExcelWriter, which also uses xlrd and openpyxl in the background:

I created two sample excel-files:

  • test1.xlsx
  • test2.xlsx

and append the first sheet of test1.xlsx to test2.xlsx:

In [1]: import pandas as pd                                                                              

In [2]: from pandas import ExcelWriter                                                                   

In [3]: with ExcelWriter("test2.xlsx", mode="a") as writer: 
   ...:     df1 = pd.read_excel("test1.xlsx", sheet_name=0) 
   ...:     df1.to_excel(writer, sheet_name="New sheet name")

Important is the mode="a", which toggles the append-mode.

Urhengulas
  • 11
  • 5
  • sorry, i was jokingly referring to pandas as an actual bear. Like the animal. In my comment `bear == pandas` – Jeffyx Aug 19 '19 at 18:20