Imitate the copy function of Excel or LibreOffice Calc with openpyxl and python3 (copy with properties to new position)

Question

is there a way to imitate the copy function of "Excel" or "LibreOffice Calc" using openpyxl and python3?

I would like to specify a certain range (e.g. "A1:E5") and copy the "border", "alignment", "number_format", "value", "merged_cells", ... properties of each cell to an other position (and probably to another worksheet), whereby the used formulas should be updated automatically to the new position. The new formulas are intended to refer to cells within the target worksheet and not to the old cells in the original worksheet.

My project:

I generate a workbook for every month. This workbook contains yield monitoring tables that list all working days. Although the tables differ from month to month, all have the same structure within a workbook, so I would like to create a template and paste it into the individual worksheets.

Copying the entire worksheet is not really a solution because I also like to specify the position of the table individually for every worksheet. So the position in the target sheet could differ from the postion in the template.

My current code (where the formulas are not automatically updated):

import copy

# The tuple "topLeftCell" represents the assumed new position in the target worksheet. It is zero-based. (e.g. (0,0) or (7,3))
# "templateSheet" is the template from which to copy.
# "worksheet" is the target worksheet

# Create the same "merged_cells" as in the template at the new positioin
for cell_range in templateSheet.merged_cells.ranges:
    startCol, startRow, endCol, endRow = cell_range.bounds
    worksheet.merge_cells(start_column=topLeftCell[0] + startCol,
                           start_row=topLeftCell[1] + startRow,
                           end_column=topLeftCell[0] + endCol,
                           end_row=topLeftCell[1] + endRow)

colNumber = topLeftCell[0] + 1         # 1 is added up because topLeftCell is zero based.
rowNumber = topLeftCell[1] + 1         # 1 is added up because topLeftcell is zero based.

# Copy the properties of the old cells into the target worksheet
for row in templateSheet[templateSheet.dimensions]:
    tmpCol = colNumber                 # sets the column back to the original value
    for cell in row:
        ws_cell = worksheet.cell(column=tmpCol, row=rowNumber)

        ws_cell.alignment = copy.copy(cell.alignment)
        ws_cell.border = copy.copy(cell.border)
        ws_cell.font = copy.copy(cell.font)
        ws_cell.number_format = copy.copy(cell.number_format)
        ws_cell.value = cell.value

        tmpCol += 1                    # move one column further
    rowNumber += 1                     # moves to the next line

Since copying ranges is actually a common task, I assumed that openpyxl provides a function or method for doing so. Unfortunately, I could not find one so far.
I'm using openpyxl version 2.5.1 (and Python 3.5.2).

Best regards AFoeee

Answer 1

Since it seems that openpyxl does not provide a general solution for my problem, I proceeded as follows:

I created a template with the properties of the cells set (borders, alignment, number format, etc.). Although the formulas are entered in the respective cells, columns and rows are replaced by placeholders. These placeholders indicate the offset to the "zero point".

The template area is copied as described above, but when copying "cell.value", the placeholder is used to calculate the actual position in the target worksheet.

Best regards
AFoeee