Copy data from one excel sheet to another using openpyxl in Python

In this openpyxl tutorial, we will learn how to copy data from one Excel sheet to another using openpyxl in Python.

We will use the openpyxl library which is basically used for modifying, reading, and writing Excel files, in our Python program.

Program to Copy data from one Excel sheet to another using openpyxl library

Here is the sample Excel sheet with some data in it. Our aim is to copy all the contents of the Sheet1 to Sheet2 of the Excel Workbook.

openpyxl

 

Step1: Import the openpyxl library.

import openpyxl

Step2: Connect/load the Excel file to the program.

path ="//home//sanamsahoo0876//Documents//Book1.xlsx"
workbook = openpyxl.load_workbook(path)

Step3: Initialize variables with the source sheet name and destination sheet name.

sheet1 = workbook["Sheet1"]
sheet2 = workbook["Sheet2"]

Step4: Create two variables to initialize with the total number of rows and columns in the source Excel sheet.

maxr = sheet1.max_row
maxc = sheet1.max_column

Step5: Now, copy all the cell values from the source Excel sheet to the destination Excel sheet.

for r in range (1, maxr + 1):
    for c in range (1, maxc + 1):
        sheet2.cell(row=r,column=c).value = sheet1.cell(row=r,column=c).value

Step6: Lastly, save the changes to the Excel File.

workbook.save("//home//sanamsahoo0876//Documents//Book1.xlsx")

Here is the complete Python Program:

import openpyxl;
  
path ="//home//sanamsahoo0876//Documents//Book1.xlsx"
workbook = openpyxl.load_workbook(path)

sheet1 = workbook["Sheet1"]
sheet2 = workbook["Sheet2"]
    
maxr = sheet1.max_row
maxc = sheet1.max_column
  
workbook.save("//home//sanamsahoo0876//Documents//Book1.xlsx")

Output:

Copy data from one excel sheet to another using openpyxl in Python

As we can see from the output, the cell values of sheet1 have successfully been copied to sheet2.

Hope you have enjoyed this tutorial and learned how to copy cell values from one sheet to another sheet by using openpyxl in Python.

Happy Coding!!

You can also read, How to get sheet names using openpyxl in Python

One response to “Copy data from one excel sheet to another using openpyxl in Python”

  1. Aditya says:

    Thanks for the code.
    I tried this same in my project and it worked. However, the code hasn’t picked the format. Could you help me with this please.

Leave a Reply

Your email address will not be published.