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.xlsxInstead ofC:\Users\Admin\Desktop\source.xlsx3) 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.
# importing openpyxl moduleimport openpyxl as xl; # opening the source excel filefilename ="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 filemr = ws1.max_rowmc = ws1.max_column # copying the cell values from source # excel file to destination excel filefor 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 filewb2.save(str(filename1)) |
Source File:
Output:
Please Login to comment…