Python | How to copy data from one excel sheet to another

In this article, we will learn how to copy data from one excel sheet to destination excel workbook using openpyxl module in Python.

For working with excel files, we require openpyxl, which is a Python library that is used for reading, writing and modifying excel (with extension xlsx/xlsm/xltx/xltm) files. It can be installed using the following command:

Sudo pip3 install openpyxl

For copying one excel file to another, we first open both the source and destination excel files. Then we calculate the total number of rows and columns in the source excel file and read a single cell value and store it in a variable and then write that value to the destination excel file at a cell position similar to that of the cell in source file. The destination file is saved.



Procedure –

1) Import openpyxl library as xl.
2) Open the source excel file using the path in which it is located.

Note: The path should be a string and have double backslashes (\\) instead of single backslash (\). Eg: Path should be C:\\Users\\Desktop\\source.xlsx Instead of C:\Users\Admin\Desktop\source.xlsx

3) Open the required worksheet to copy using the index of it. The index of worksheet ‘n’ is ‘n-1’. For example, the index of worksheet 1 is 0.
4) Open the destination excel file and the active worksheet in it.
5) Calculate the total number of rows and columns in source excel file.
6) Use two for loops (one for iterating through rows and another for iterating through columns of the excel file) to read the cell value in source file to a variable and then write it to a cell in destination file from that variable.
7) Save the destination file.

filter_none

edit
close

play_arrow

link
brightness_4
code

# importing openpyxl module
import openpyxl as xl;
  
# opening the source excel file
filename ="C:\\Users\\Admin\\Desktop\\trading.xlsx"
wb1 = xl.load_workbook(filename)
ws1 = wb1.worksheets[0]
  
# opening the destination excel file 
filename1 ="C:\\Users\\Admin\\Desktop\\test.xlsx"
wb2 = xl.load_workbook(filename1)
ws2 = wb2.active
  
# calculate total number of rows and 
# columns in source excel file
mr = ws1.max_row
mc = ws1.max_column
  
# copying the cell values from source 
# excel file to destination excel file
for i in range (1, mr + 1):
    for j in range (1, mc + 1):
        # reading cell value from source excel file
        c = ws1.cell(row = i, column = j)
  
        # writing the read value to destination excel file
        ws2.cell(row = i, column = j).value = c.value
  
# saving the destination excel file
wb2.save(str(filename1))

chevron_right


Source File:

Output:



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.