How To Copy Cell Range Styles Using OpenPyXL?

Method

Copying styles:

from copy import copy

cr=CellRange(‘D5:F7’)

cr2=CellRange(‘D12:F14’)

r=cr2.max_row-cr.max_row

c=cr2.max_col-cr.max_col

for cl in cr.cells:

      cl2=ws.cell(row=cl[0],column=cl[1])

      ws.cell(row=cl[0]+r,column=cl[1]+c,value=cl2.value)

      if cl2.has_style:

            nc=ws.cell(row=cl[0]+r,column=cl[1]+c)

            nc.font=copy(cl2.font)

            nc.border=copy(cl2.border)

            nc.fill=copy(cl2.fill)

            nc.number_format=copy(cl2.number_format)

            nc.alignment=copy(cl2.alignment)

Sample Code

#Copy the Style of a Cell Range

#Import load_workbook function
from openpyxl import Workbook

#Create a new workbook
wb=Workbook()
#Get the active worksheet
ws=wb.active

#Import CellRange class and copy function
from openpyxl.worksheet.cell_range import CellRange
from copy import copy

#Create two cell ranges
cr=CellRange('D5:F7')
cr2=CellRange('D12:F14')

#Copy the style
r=cr2.max_row-cr.max_row
c=cr2.max_col-cr.max_col
for cl in cr.cells:
      cl2=ws.cell(row=cl[0],column=cl[1])
      ws.cell(row=cl[0]+r,column=cl[1]+c,value=cl2.value)
      #If there are styles, use the `copy` function to copy them
      if cl2.has_style:
            nc=ws.cell(row=cl[0]+r,column=cl[1]+c)
            nc.font=copy(cl2.font)
            nc.border=copy(cl2.border)
            nc.fill=copy(cl2.fill)
            nc.number_format=copy(cl2.number_format)
            nc.alignment=copy(cl2.alignment)

wb.save('test.xlsx')
wb.close()