how to add the worksheet object in openpyxl to the new sheet of the existing xlsx document

Question

I have done a lot of research and failed to solve this problem, so I am coming here to ask for help. I read the worksheet object from a file below,

sheet_2_workbook = openpyxl. load_workbook(sheet_2_path)
sheet_2 = sheet_2_workbook.worksheets\[0\]

As described in the title, I want to add it to the new sheet of the existing .xlsx document, how should I do it?

I tried to realize this as below, but the new document obtained by this method will lose some of the original formatting, including the cell background color and merged cells

     old_wb = openpyxl. load_workbook(file_list[i])
     old_sheet_name = old_wb. get_sheet_names()[0]
     old_ws = old_wb[old_sheet_name]
     ws2 = combined_wb.create_sheet(sheet_name)
     for row in old_ws.values:
         ws2.append(row)

I am sure that the worksheet object read in the file contains these formats, because the .xlsx document I dumped with the following code has the format mentioned above

sheet_2_workbook. save(filename = temp_save_path)

Openpyxl won't copy formatting like font, bordering, number format etc with the cell value. This has to be done separately for each cell. If you are running windows with Excel installed locally it is easier to copy sheets from one workbook to another using Xlwings as this duplicates the move/copy sheet option within excel, all formatting is preserved. See this answer for how to copy styles https://stackoverflow.com/questions/61892135/how-to-copy-cell-from-one-workbook-to-an-other-workbook-with-its-set-style — moken, Dec 24 '22 at 10:38

score 1 · Answer 1 · edited Dec 24 '22 at 22:06

How I would go about your task of adding a worksheet object to a new sheet in an existing Excel document using openpyxl:

Import the openpyxl module
Load the existing Excel document using the load_workbook() function
Create a new sheet using the create_sheet() method of the Workbook object
Alternatively, you can specify the name of the new sheet as a string when calling the create_sheet() method
Save changes using the save() method

(To preserve the formatting you can use the copy_worksheet() to create a copy of the original worksheet and add it to the workbook)

import openpyxl
    
workbook = openpyxl.load_workbook('existing_document.xlsx')
    
sheet_2_workbook = openpyxl.load_workbook(sheet_2_path)
sheet_2 = sheet_2_workbook.worksheets[0]
    
new_sheet = workbook.copy_worksheet(sheet_2)
// Alternatively, you can specify the name of the new sheet as a string
// new_sheet = workbook.copy_worksheet(sheet_2, 'Sheet2')
    
workbook.save('existing_document.xlsx')

Thank you, really get inspired by the idea of `copy_worksheet`, but get `ValueError: Cannot copy between worksheets from different workbooks` — Allen.L, Dec 24 '22 at 09:05

how to add the worksheet object in openpyxl to the new sheet of the existing xlsx document

1 Answers1