I'm new to Python and I'm trying to adapt some of my VBA code to it using the openpyxl library. On this particular case, I'm trying to copy 468 rows in a single column from a workbook according to the string in the header and to paste them in another workbook in a particular column that has another specific string as a header. I can't simply select the range of cells I want to copy because this is part of a report automation and the headers change positions from file to file.
What's the function I need to use to copy each of the 468 cells from one workbook into the 468 cells of the second workbook? Or alternatively how can I copy a range of cells and then paste them in another workbook? Here is my code and I know exactly what's wrong: I'm copying one cell (the last from the first workbook) repeatedly into the 468 cells of the second workbook.
#!/usr/bin/python3
import pdb
import openpyxl
from openpyxl.utils import column_index_from_string
wb1 = openpyxl.load_workbook('.../Extraction.xlsx')
wb2 = openpyxl.load_workbook('.../Template.xlsx')
ws1 = wb1.active
first_row1 = list(ws1.rows)[0] #to select the first row (header)
for cell in first_row1:
if cell.value == "email":
x = cell.column #to get the column
y = column_index_from_string(x) #to get the column's index
for i in range(2, 469):
cell_range1 = ws1.cell(i, y) #the wrong part
ws2 = wb2.active
first_row2 = list(ws2.rows)[0]
for cell in first_row2:
if cell.value == "emailAddress":
w = cell.column
z = column_index_from_string(w)
for o in range(2, 469):
cell_range2 = ws2.cell(o, z)
cell_range2.value = cell_range1.value
path = '.../Test.xlsx'
wb2.save(path)
You may have to flip the input to .cell(), I guess it is .cell(column, row). Or just use the keywords .cell(column=z, row=o)
You need a dynamic index for both of the row iterators, while keeping the column indices where you found them:
for o in range(2, 469):
#note the common o for both, could also be o+1 for one if there is an offset
ws2.cell(o, z).value = ws1.cell(o, y).value