Copy a worksheet (data+style) from a workbook to another in Python using openpyxl

user_14655190 0
Viewed 3266

I have a large amount of XLS files (more than 500), and I need to replace in all just the first sheet. Needs to be a perfect copy of data+style (font, background, borders, cell alignment and even images) of the 1st worksheet.

All the solutions that I found in Python using openpyxl only allow it to copy data, without the style. And using xlwings it's not an option because the Linux target machine doesn't have MS Office.

import openpyxl as pyxl
import re

basereportworkbook = pyxl.load_workbook(filename="base_template.xlsx")
testreportworkbook = pyxl.load_workbook(filename="file1_to_correct.xlsx")

sheetbase = basereportworkbook.get_sheet_by_name("Coverpage")
sheetreport = basereportworkbook.get_sheet_by_name("Coverpage")

# Remove the 1st page from the file to correct
testreportworkbook.remove(testreportworkbook["Coverpage"])
testreportworkbook.create_sheet("Coverpage")
sheetreport = testreportworkbook.get_sheet_by_name("Coverpage")


# Copying the cell values from template excel file to destination excel file, one by one
mr = sheetbase.max_row 
mc = sheetbase.max_column 

for i in range (1, mr + 1): 
    for j in range (1, mc + 1): 
        # reading cell value from source excel file 
        c = sheetbase.cell(row = i, column = j) 
  
        # writing the read value to destination excel file 
        sheetreport.cell(row = i, column = j).value = c.value 

# Save the XLS file in the disk
testreportworkbook.save(filename="output.xlsx")

This is my code so far, it works just to copy data without formatting style. Thanks!

worksheet openpyxl excel python
1 Answers

When you use c.value, you specify that you only want to copy the value and not any other cell attributes (the formatting, etc). You could use copy to move all _style formatting over, eg:

from copy import copy

sheetreport.cell(row = i, column = j).value = c.value
if cell.has_style:
    sheetreport.cell(row = i, column = j)._style = copy(c._style)

...et cetera.

However, if you want to just copy the entire worksheet, I would probably just copy the entire workbook and then delete all the other worksheets, rather than iterate over every cell.

shutil.copyfile('base_template.xlsx', 'file1_to_correct.xlsx')
testreportworkbook = pyxl.load_workbook(filename='file1_to_correct.xlsx')

for ws in testreportworkbook.worksheets[1:]:
    testreportworkbook.remove_sheet(ws)

Also note, from the docs: "You also cannot copy worksheets between workbooks. You cannot copy a worksheet if the workbook is open in read-only or write-only mode."

user_118464570
Related