In this article, I will tell you how to use the python openpyxl library to copy one excel sheet data to another excel sheet, the two excel sheets can be in the same excel file or different excel file. If you do not know the openpyxl library, you can read the article How To Create / Load Excel File In Python Using Openpyxl first.
1. Copy Excel Sheet Data Example.
Below are the example project files. The python source file is copy_excel_sheet.py. It contains 5 methods, and the source excel file is test_excel.xlsx.
$ tree ./ ./ ├── __init__.py ├── copy_excel_sheet.py ├── create_load_excel_file.py ├── data │ ├── excel-example.xlsx │ ├── financial_sample.xlsx │ ├── financial_sample_freeze_1.xlsx │ ├── reviews_sample.xlsx │ ├── test_default_excel.xlsx │ ├── test_excel.xlsx │ ├── test_excel_1.xlsx │ ├── test_excel_2.xls │ └── ~$financial_sample.xlsx └── freeze_split_excel_pane.py
The copy_excel_sheet.py file contains the below methods.
- if_file_exist(file_path).
- if_excel_sheet_exist(work_book, work_sheet_name).
- copy_excel_sheet_in_same_file(file_path, source_sheet_name, target_sheet_name).
- copy_excel_sheet_in_same_file_by_copy_worksheet(file_path, source_sheet_name, target_sheet_name).
- copy_excel_sheet_in_different_file(source_file_path, source_sheet_name, target_file_path).
Below is the original data of the test_excel.xlsx file.
Below is the excel file after executing the copy_excel_sheet_in_same_file method, it adds a new excel sheet User Account New in test_excel.xlsx.
Below is the excel file after executing the copy_excel_sheet_in_same_file_by_copy_worksheet method, it is similar to the above, but it cloned the entire sheet even the sheet tab color to the new excel sheet.
If you execute the copy_excel_sheet_in_different_file method, it will create a new excel file test_excel_1.xlsx, and there is an excel sheet with the name User Account in the test_excel_1.xlsx.
2. Copy Excel Sheet Data Example Source Code.
'''
@author: zhaosong
'''
import openpyxl, os
from openpyxl import Workbook
from openpyxl import load_workbook
'''
Check whether the file exist or not.
file_path : the input file path with name.
Return True if the file exist otherwise return False.
'''
def if_file_exist(file_path):
if os.path.exists(file_path):
return True
else:
return False
'''
Check whether the excel file contain the excel sheet.
work_book : openpyxl.Workbook instance.
work_sheet_name : excel sheet name.
Return True if the excel file contain the excel sheet otherwise return False.
'''
def if_excel_sheet_exist(work_book, work_sheet_name):
sheet_names_list = work_book.sheetnames
for sheet_name in sheet_names_list:
if sheet_name == work_sheet_name:
return True
else:
return False
'''
Copy one excel sheet data to another excel sheet in same excel file.
file_path : Excel file path.
source_sheet_name : the source excel sheet name that will be copied.
target_sheet_name : the target excel sheet name.
'''
def copy_excel_sheet_in_same_file(file_path, source_sheet_name, target_sheet_name):
if if_file_exist(file_path):
print("File ", file_path, " exist.")
# load the excel file return Workbook object.
work_book = load_workbook(file_path)
if if_excel_sheet_exist(work_book, source_sheet_name):
print("Source excel sheet ", source_sheet_name, " exist.")
# get source Worksheet object.
source_work_sheet = work_book[source_sheet_name]
# initialise the target_work_sheet to None.
target_work_sheet = None
# if target excel sheet exist in the excel file.
if if_excel_sheet_exist(work_book, target_sheet_name):
# assign the target Worksheet.
target_work_sheet = work_book[target_sheet_name]
else:
# create a new Worksheet object.
target_work_sheet = work_book.create_sheet(target_sheet_name)
# loop the source excel sheet rows.
row_number = 1
for row in source_work_sheet.iter_rows():
# loop the cell in the row.
cell_column_number = 1
for cell in row:
# create a cell in target work sheet.
target_cell = target_work_sheet.cell(row = row_number, column = cell_column_number, value = cell.value)
cell_column_number += 1
row_number += 1
# save the excel file.
work_book.save(file_path)
print("Excel sheet has be copied. ")
else:
print("Source excel sheet ", source_sheet_name, " do not exist.")
else:
print("File ", file_path, " do not exist.")
'''
Clone entire excel sheet in same excel file use Workbook object's copy_worksheet method.
'''
def copy_excel_sheet_in_same_file_by_copy_worksheet(file_path, source_sheet_name, target_sheet_name):
if if_file_exist(file_path):
print("File ", file_path, " exist.")
# load the excel file return Workbook object.
work_book = load_workbook(file_path)
if if_excel_sheet_exist(work_book, source_sheet_name):
print("Source excel sheet ", source_sheet_name, " exist.")
source_sheet = work_book[source_sheet_name]
# invoke copy_worksheet method to clone source sheet.
target_sheet = work_book.copy_worksheet(source_sheet)
target_sheet.title = target_sheet_name
# save the excel file.
work_book.save(file_path)
print("Excel sheet has be copied. ")
else:
print("Source excel sheet ", source_sheet_name, " do not exist.")
else:
print("File ", file_path, " do not exist.")
'''
Copy excel sheet data from one excel file to another excel file.
source_file_path : source excel file path with name.
source_sheet_name : source excel sheet name.
target_file_path : target excel file path with name.
'''
def copy_excel_sheet_in_different_file(source_file_path, source_sheet_name, target_file_path):
if if_file_exist(source_file_path):
print("File ", source_file_path, " exist.")
# load Workbook object source excel file.
source_work_book = load_workbook(source_file_path)
if if_excel_sheet_exist(source_work_book, source_sheet_name):
print("Source excel sheet ", source_sheet_name, " exist.")
# get source Worksheet object.
source_work_sheet = source_work_book[source_sheet_name]
target_work_book = None
target_work_sheet = None
target_sheet_name = source_sheet_name
# if target excel file exist then load it.
if if_file_exist(target_file_path):
target_work_book = load_workbook(target_file_path)
# otherwise create a new Workbook object.
else:
target_work_book = Workbook()
# if target excel sheet exist in target excel file the return it.
if if_excel_sheet_exist(target_work_book, target_sheet_name):
target_work_sheet = target_work_book[target_sheet_name]
# otherwise create a new Worksheet object.
else:
target_work_sheet = target_work_book.create_sheet(target_sheet_name)
# loop in the source excel sheet rows.
row_number = 1
for row in source_work_sheet.iter_rows():
# loop in the row cells.
cell_column_number = 1
for cell in row:
# create a target excel cell in target excel sheet.
target_cell = target_work_sheet.cell(row = row_number, column = cell_column_number, value = cell.value)
cell_column_number += 1
row_number += 1
# save the target excel file.
target_work_book.save(target_file_path)
print("Excel sheet has be copied. ")
else:
print("Source excel sheet ", source_sheet_name, " do not exist.")
else:
print("File ", source_file_path, " do not exist.")
if __name__ == '__main__':
source_file_path = './test_excel.xlsx'
source_sheet_name = 'User Account'
target_sheet_name = 'User Account New'
copy_excel_sheet_in_same_file_by_copy_worksheet(source_file_path, source_sheet_name, target_sheet_name)
#copy_excel_sheet_in_same_file(source_file_path, source_sheet_name, target_sheet_name)
#target_file_path = './test_excel_1.xlsx'
#copy_excel_sheet_in_different_file(source_file_path, source_sheet_name, target_file_path)
Reference