Copy Values/Styles of Excel Sheet by openpyxl

Copying data from one sheet to another by Python openpyxl

So, if you have not already installed openpyxl, use the following command in the CMD or PowerShell:

Copying data from one sheet to another

Though there is no straightforward copy method in openpyxl (like copy/paste etc.), we need to write the logic for that.

Following is the source sheet that we will copy:

First of all, specify the source Workbook and its sheet that you want to copy:

import openpyxl as copy_ex;

wb_src = copy_ex.load_workbook("test_Excel.xlsx")

sheet_src = wb_src["Product Information"]

Now load Workbook and specify the sheet where you want to copy i.e. destination Workbook:

wb_dest = copy_ex.load_workbook("test_copy.xlsx")

sheet_dest = wb_dest.active

Using Outer and Inner for loop to get data from the source and write to the destination sheets:

for i in range (1, sheet_src.max_row + 1):

    for j in range (1, sheet_src.max_column + 1):

        # Assign source cell value

        cell_src = sheet_src.cell(row = i, column = j)


        # paste to destination cell

        sheet_dest.cell(row = i, column = j).value = cell_src.value

Save the destination Workbook after completing the operation:

Combining all the above stuff with the output

import openpyxl as copy_ex;


# Loading source File - Where you want to copy from and specify sheet name

wb_src = copy_ex.load_workbook("test_Excel.xlsx")

sheet_src = wb_src["Product Information"]


# Loading Destination Workbook and specify sheet

wb_dest = copy_ex.load_workbook("test_copy.xlsx")

sheet_dest = wb_dest.active

for i in range (1, sheet_src.max_row + 1):

    for j in range (1, sheet_src.max_column + 1):

        # Assign source cell value

        cell_src = sheet_src.cell(row = i, column = j)


        # paste to destination cell

        sheet_dest.cell(row = i, column = j).value = cell_src.value


# Save the file after copying data

wb_dest.save("test_copy.xlsx")

Result:

You can see all data is copied to the destination sheet of test_copy.xlsx Workbook

How to keep style to the destination sheet

The above program works well if you only require data from the source to the destination sheet in plain style.

What if you require to keep the style copied – including:

• Fill (background color)
• Border style
• Font style
• Bold, italic, underlined
• Hyperlinks

We have a solution for keeping the styles in the destination sheet below. It is almost the same solution as above except we added a few lines to copy styles.

First, have a look at the code and output.

Python Code:

import openpyxl as copy_ex;

from copy import copy


# Loading source File - Where you want to copy from and specify sheet name

wb_src = copy_ex.load_workbook("test_Excel.xlsx")

sheet_src = wb_src["Product Information"]

# Loading Destination Workbook and specify sheet

wb_dest = copy_ex.load_workbook("test_copy.xlsx")

sheet_dest = wb_dest.active


for i in range (1, sheet_src.max_row + 1):

    for j in range (1, sheet_src.max_column + 1):

        # Assign source cell value

        cell_src = sheet_src.cell(row = i, column = j)

        #print(cell_src.fill)


        # paste values and styles to the destination sheet

        sheet_dest.cell(row = i, column = j).value = cell_src.value

        sheet_dest.cell(row = i, column = j).fill = copy(cell_src.fill)

        sheet_dest.cell(row = i, column = j).font  = copy(cell_src.font )

        sheet_dest.cell(row = i, column = j).border  = copy(cell_src.border )

        sheet_dest.cell(row = i, column = j).alignment  = copy(cell_src.alignment )


# Save the file after copying data

wb_dest.save("test_copy.xlsx")

Output:

You can see that the destination sheet cells have the same style as our source Workbook’s sheet (shown above).