2

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)
Mahle
  • 23
  • 1
  • 1
  • 4
  • What's the question? – Charlie Clark Mar 28 '18 at 07:36
  • 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? As you can see, I found a way to copy one cell (the last from the first workbook) repeatedly 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? – Mahle Mar 28 '18 at 07:53
  • There isn't such a function, you'll have to write it yourself. – Charlie Clark Mar 28 '18 at 08:02
  • Thanks for the answer, Charlie, but I'm struggling with the writing of this function. – Mahle Mar 28 '18 at 08:26

2 Answers2

3

It is actually quite easy to create such a function:

from openpyxl.utils import rows_from_range

def copy_range(range_str, src, dst):

    for row in rows_from_range(range_str):
        for cell in row:
            dst[cell].value = src[cell].value

    return

Note that range_str is a regular string such as "A1:B2" and src and dest both have to be valid sheet objects. However, if you are copying large ranges, this might take a while, as the read/writes seem to be rather time-consuming.

rt87
  • 1,123
  • 7
  • 8
1

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
Joe
  • 6,758
  • 2
  • 26
  • 47
  • That's not the part of the code that doesn't work. The question is: 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? As you can see, I found a way to copy one cell (the last from the first workbook) repeatedly 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? – Mahle Mar 28 '18 at 08:24
  • The function to copy is just assign to `.value` of a cell I guess, at least I have used it that way. Try to assign something using `.cell(column=z, row=o).value`, with some values for `z` and `o`. – Joe Mar 28 '18 at 12:26
  • Of course you need the indice also running on the left side: `ws2.cell(o, z).value = ws1.cell(i, y).value`. Did you do it like that? – Joe Mar 28 '18 at 12:46
  • But need to use a dynamic index for both of them, I add a an example above. – Joe Mar 28 '18 at 14:03
  • Yes, that is equivalent to what I wrote above. You don't need to assign to the range. – Joe Mar 28 '18 at 14:10
  • `for i in range(2, 469): cell_range1 = ws1.cell(i, y) cell_range2 = ws2.cell(i, z) cell_range2.value = cell_range1.value` This code worked! Thanks! – Mahle Mar 28 '18 at 14:10