Openpyxl: How to copy a row after checking if a cell contains specific value

Question

I have a worksheet that is updated every week with thousands of rows and would need to transfer rows from this worksheet after filtering. I am using the current code to find the cells which has the value I need and then transfer the entire row to another sheet but after saving the file, I get the "IndexError: list index out of range" exception.

The code I use is as follows:

import openpyxl

wb1 = openpyxl.load_workbook('file1.xlsx')
wb2 = openpyxl.load_workbook('file2.xlsx')

ws1 = wb1.active
ws2 = wb2.active

for row in ws1.iter_rows():
    for cell in row:
        if cell.value == 'TrueValue':
            n = 'A' + str(cell.row) + ':' + ('GH' + str(cell.row))
            for row2 in ws1.iter_rows(n):
                ws2.append(row2)

wb2.save("file2.xlsx")

The original code I used that used to work is below and has to be modified because of the large files which causes MS Excel not to open them (over 40mb).

n = 'A3' + ':' + ('GH'+ str(ws1.max_row))
for row in ws1.iter_rows(n):
    ws2.append(row)

Thanks.

Answer 1

I'm not entirely sure what you're trying to do but I suspect the problem is that you have nested your copy loop.

Try the following:

row_nr = 1
for row in ws1:
    for cell in row:
        if cell.value == "TrueValue":
            row_nr = cell.row
            break
    if row_nr > 1:
        break

for row in ws1.iter_rows(min_row=row_nr, max_col=190):
    ws2.append((cell.value for cell in row))

Answer 2

Question: I get the "IndexError: list index out of range" exception.

---

I get, from ws1.iter_rows(n)

UserWarning: Using a range string is deprecated. Use ws[range_string]

and from ws2.append(row2).

ValueError: Cells cannot be copied from other worksheets

The Reason are row2 does hold a list of Cell objects instead of a list of Values

---

Question: ... need to transfer rows from this worksheet after filtering

The following do what you want, for instance:

# If you want to Start at Row 2 to append Row Data
# Set Private self._current_row to 1
ws2.cell(row=1, column=1).value = ws2.cell(row=1, column=1).value

# Define min/max Column Range to copy
from openpyxl.utils import range_boundaries
min_col, min_row, max_col, max_row = range_boundaries('A:GH')

# Define Cell Index (0 Based) used to Check Value
check = 0 # == A

for row in ws1.iter_rows():
    if row[check].value == 'TrueValue':
        # Copy Row Values
        # We deal with Tuple Index 0 Based, so min_col must have to be -1
        ws2.append((cell.value for cell in row[min_col-1:max_col]))

Tested with Python: 3.4.2 - openpyxl: 2.4.1 - LibreOffice: 4.3.3.2