2 Examples to Copy Values/Styles of Excel Sheet by openpyxl

Copying data from one sheet to another by Python openpyxl

In this tutorial, we will show you how to copy data from one Workbook sheet to another sheet in Excel by using openpyxl library.

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

pip install openpyxl

If pip is working properly then it should install the openpyxl library.

Otherwise, follow the instructions by visiting the link below:

https://pypi.org/project/openpyxl

Copying data from one sheet to another

Though there is no straight-forward 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:

Step 1:

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"]

Step 2:

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

Step 3:

Using Outer and Inner for loops 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

Step 4:

Save the destination Workbook after completing the operation:

wb_dest.save("test_copy.xlsx")

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

Well, 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: