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



To work with Excel files, we need openpyxl , a Python library that is used to read, write and modify Excel files (with the extension xlsx / xlsm / xltx / xltm) … It can be installed using the following command:

 Sudo pip3 install openpyxl 

To copy one Excel file to another, we first open the source and target Excel files. We then calculate the total number of rows and columns in the source Excel file, read the value of one cell and store it in a variable, and then write that value to the target Excel file at a cell position similar to the cell position in the source file. , The destination file has been 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:UsersAdminDesktopsource.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.

# import openpyxl module

import openpyxl as xl; 

 
# open the original Excel file

filename = "C: Users Admin Desktop trading.xlsx"

wb1 = xl.load_workbook (filename )

ws1 = wb1.worksheets [ 0 ]

 
# open target Excel file

filename1 = "C: Users Admin Desktop test.xlsx"

wb2 = xl.load_workbook (filename1)

ws2 = wb2.active

 
# calculate the total rows and
# columns in the Excel source file

mr = ws1.max_row

mc = ws1.max_column

 
# copy cell values ​​from source
# Excel file to target Excel file

for i in range ( 1 , mr + 1 ):

for j in range ( 1 , mc + 1 ):

  # read cell value from Excel source file

c = ws1.cell (row = i, column = j)

 

# writing the read value to the target Excel file

ws2.cell (row = i, column = j) .value = c.value

 
# save target Excel file

wb2.save ( str (filename1))

Source file :

Output: