Python Copy Excel Sheet: Openpyxl, a Python package used to read, write, and edit Excel files (with the extension xlsx, xlsm, xltx, xltm) is what we need in order to work with them.
We must first open the source and target Excel files before we may copy one Excel file to another. Next, we determine how many rows and columns there are in the source Excel file, read the value from one cell, save it in a variable, and then write that value to the target Excel file at a cell location similar to the source file’s cell position. In this article, we have provided everything about How to Copy Data from One Excel sheet to Another using Python. Read on to find more.
How to Copy Data from One Excel sheet to Another in Python?
Given two excel files (Source and Destination) the task is to copy the contents of the source file to the destination file in Python.
Prerequisites:
Install the Openpyxl using the below syntax:
pip install openpyxl
Intuition:
To copy one excel file to another, we must first open both the source and destination files. Then we count the number of rows and columns in the source Excel file, read a single cell value, save it in a variable, and send that value to the destination excel file at a cell location similar to the cell in the source file. The destination file has been saved.
SourceExcel File:
| Website name | Name |
| Sheets Tips | Vikram |
| Sheets Tips | Akash |
| Sheets Tips | Vishal |
| Python-Programs | Pavan |
| Python-Programs | Dhoni |
| Python-Programs | Virat |
| BTechGeeks | Devilliers |
| BTechGeeks | Pant |
| PythonArray | Smith |
| PythonArray | Warner |
We will see the step by step approach for Copying Data from One Excel sheet to Another Excel Sheet
1)Importing the modules
- Import the openpyxl using the import and as keyword.
Below is the Implementation:
# import the openpyxl using the import and as keyword import openpyxl as xl
2)Creating Source Worksheet
- Give the source excel filename as static input and store it in a variable.
- Pass the above file name as an argument to the load_workbook() function of openpyxl module to load the above source excel file as a workbook and store it in a variable.
Below is the Implementation:
# give the source excel filename as static input and store it in a variable sourceFilename ="sourceExcel.xlsx" # pass the above file name as argument to the load_workbook() function of openpyxl module to load the # above source excelfile as workbook and store it in a variable sourceWorkbook = xl.load_workbook(sourceFilename) sourceWorksheet = sourceWorkbook.worksheets[0]
3)Creating Destination Worksheet
- Create an empty excel file for the result and pass the excel file name as static input and store it in a variable.
- Pass the above file name as the argument to the load_workbook() function of openpyxl module to load the
above source excel file as a workbook and store it in a variable.
Below is the Implementation:
# Create a empty excel file and pass the excel file name as static input and store it in a variable destinationFilename ="destinationExcel.xlsx" # pass the above file name as argument to the load_workbook() function of openpyxl module to load the # above source excelfile as workbook and store it in a variable destinationWorkbook = xl.load_workbook(destinationFilename) destinationWorksheet = destinationWorkbook.active
4)Getting the Excel File rows and columns size
- Calculate the total number of rows and columns of the source excel files and store them in the corresponding two variables.
- Get the total number of rows by applying max_row attribute to the source worksheet and store it in a variable.
- Get the total number of columns by applying max_row attribute to the source worksheet and store it in a variable.
Below is the Implementation:
#Calculate the total number of rows and columns of the source excel files and store them in corresponding two variables. #Get the total number of rows by applying max_row attribute to the source worksheet and store it in a variable numOfRows = sourceWorksheet.max_row #Get the total number of columns by applying max_row attribute to the source worksheet and store it in a variable numOfColumns = ws1.max_column
5)Copying and saving data from source excel file to destination excel file
- Loop till the number of rows of the source worksheet using the for loop.
- Loop till the number of columns of the source worksheet using another nested for loop.
- Read the value of the cell of the source worksheet by passing the iterator values as arguments to the cell function and applying it to the source worksheet object.
- Store the value in some variable.
- Assigning the destination worksheet cell value with the above cell value.
- Saving the destination excel file.
- Save the destination excel file by passing the destination file name to the save() function and applying it to the destination workbook.
Below is the Implementation:
#Loop till the number of rows of the source worksheet using the for loop
for r in range (1, numOfRows + 1):
#Loop till the number of columns of the source worksheet using another nested for loop
for c in range (1, numOfColumns + 1):
# Read the value of the cell of source worksheet by passing the iterator
# values as arguments to the cell function and apply it to the source worksheet object
# Store the value in some variable
cellValue = sourceWorksheet.cell(row = r, column = c)
# Assinging the destination worksheet cell value with the above cell value
destinationWorksheet.cell(row = r, column = c).value = cellValue.value
# saving the destination excel file
# Save the destination excel file by passing the destination file name to the save() function
# and apply it to the destination workbook
destinationWorkbook.save(str(destinationFilename))
Below is the complete approach for the above program:
Approach:
- Import the openpyxl using the import and as keyword.
- Give the source excel filename as static input and store it in a variable.
- Pass the above file name as an argument to the load_workbook() function of openpyxl module to load the above source excel file as a workbook and store it in a variable.
- Create an empty excel file for the result and pass the excel file name as static input and store it in a variable.
- Pass the above file name as the argument to the load_workbook() function of openpyxl module to load the
above source excel file as a workbook and store it in a variable. - Calculate the total number of rows and columns of the source excel files and store them in the corresponding two variables.
- Get the total number of rows by applying max_row attribute to the source worksheet and store it in a variable.
- Get the total number of columns by applying max_row attribute to the source worksheet and store it in a variable.
- Loop till the number of rows of the source worksheet using the for loop.
- Loop till the number of columns of the source worksheet using another nested for loop.
- Read the value of the cell of the source worksheet by passing the iterator values as arguments to the cell function and applying it to the source worksheet object.
- Store the value in some variable.
- Assigning the destination worksheet cell value with the above cell value.
- Saving the destination excel file.
- Save the destination excel file by passing the destination file name to the save() function and applying it to the destination workbook.
- The Exit of the Program.
Below is the Implementation:
# import the openpyxl using the import and as keyword
import openpyxl as xl
# give the source excel filename as static input and store it in a variable
sourceFilename ="sourceExcel.xlsx"
# pass the above file name as argument to the load_workbook() function of openpyxl module to load the
# above source excelfile as workbook and store it in a variable
sourceWorkbook = xl.load_workbook(sourceFilename)
sourceWorksheet = sourceWorkbook.worksheets[0]
# Create a empty excel file and pass the excel file name as static input and store it in a variable
destinationFilename ="destinationExcel.xlsx"
# pass the above file name as argument to the load_workbook() function of openpyxl module to load the
# above source excelfile as workbook and store it in a variable
destinationWorkbook = xl.load_workbook(destinationFilename)
destinationWorksheet = destinationWorkbook.active
#Calculate the total number of rows and columns of the source excel files and store them in corresponding two variables.
#Get the total number of rows by applying max_row attribute to the source worksheet and store it in a variable
numOfRows = sourceWorksheet.max_row
#Get the total number of columns by applying max_row attribute to the source worksheet and store it in a variable
numOfColumns = ws1.max_column
#Loop till the number of rows of the source worksheet using the for loop
for r in range (1, numOfRows + 1):
#Loop till the number of columns of the source worksheet using another nested for loop
for c in range (1, numOfColumns + 1):
# Read the value of the cell of source worksheet by passing the iterator
# values as arguments to the cell function and apply it to the source worksheet object
# Store the value in some variable
cellValue = sourceWorksheet.cell(row = r, column = c)
# Assinging the destination worksheet cell value with the above cell value
destinationWorksheet.cell(row = r, column = c).value = cellValue.value
# saving the destination excel file
# Save the destination excel file by passing the destination file name to the save() function
# and apply it to the destination workbook
destinationWorkbook.save(str(destinationFilename))
Output:
| Website name | Name |
| Sheets Tips | Vikram |
| Sheets Tips | Akash |
| Sheets Tips | Vishal |
| Python-Programs | Pavan |
| Python-Programs | Dhoni |
| Python-Programs | Virat |
| BTechGeeks | Devilliers |
| BTechGeeks | Pant |
| PythonArray | Smith |
| PythonArray | Warner |
Now that we are provided all the necessary information about how to use Openpyxl to copy data from one Excel sheet to another in Python. If you have any questions related to this page, ping us through the comment box below and we will get back to you as soon as possible.