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

user_6662601 0
Viewed 22441

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.

openpyxl python
4 Answers

I was able to solve this with lists for my project.

import openpyxl
#load data file
wb1 = openpyxl.load_workbook('original.xlsx')
sheet1 = wb1.active
print("loaded 1st file")    
#new template file
wb2 = openpyxl.load_workbook('blank.xlsx') 
sheet2 = wb2.active
print("loaded 2nd file")
header = sheet1[1:1] #grab header row
listH =[]
for h in header:
    listH.append(h.value)
sheet2.append(listH)
colOfInterest= 11 # this is my col that contains the value I'm checking against
for rowNum in range(2, sheet1.max_row +1):  #iterate over each row, starting with 2 to skipping header from original file
    if sheet1.cell(row=rowNum, column=colOfInterest).value is not None: #interested in non blank values in column 11
        listA = [] # list which will hold my data
        row = sheet1[rowNum:rowNum] #creates a tuple of row's data
        #print (str(rowNum))  # for debugging to show what rows are copied
        for cell in row:  # for each cell in the row
            listA.append(cell.value) # add each cell's data as an element in the list
        if listA[10]  == 1:  # condition1 I'm checking for by looking up the index in the list
            sheet2.append(listA)  # appending the sheet2's next available row
        elif listA[10] > 1:  # condition2 I'm checking for by looking up the index in the list
            # do something else and store it in bar
            sheet2.append(bar) # appending the sheet2's next available row

print("saving file...")
wb2.save('result.xlsx')  # save file
print("Done!")

Tested with: Python 3.7 openpyxl 2.5.4

user_100824570
Related