How to search an Excel column for a range of values

I have 2 Excel files, one containing a list of emails of people that need to be removed (i.e. removal file), and a second Excel file that contains the active list of people (i.e. roster file).

I want to search the email column of the roster file for the list of emails to be removed, and then copy the new list, minus the removed people, to a new file.

My code so far is as follows:

import openpyxl, pprint
print('Opening Removals workbook...')

# Open Removal file
wb1 = openpyxl.load_workbook('c:python27deleteRemovals.xlsx')
removalsheet = wb1.get_sheet_by_name('Removals')
removalData = {}
# TODO: Fill in removalData with each worker that we want to remove
print('Reading removal rows...')

# Open All Candidates file
print('Opening All Candidates workbook...')
wb3 = openpyxl.load_workbook('c:python27deleteAll Candidates.xlsx')
oldlivesheet = wb3.get_sheet_by_name('Live')

# Create a New Roster file
wb2 = openpyxl.Workbook()
wb2.save('c:python27deleteNew Roster.xlsx')
wb2 = openpyxl.load_workbook('c:python27deleteNew Roster.xlsx')
newlivesheet = wb2.get_sheet_by_name('Sheet')

# Iterate over old live sheet
for row in range(1, oldlivesheet.get_highest_row() + 1):
    workername = removalsheet.cell(row=row, column=1).value
    if workername != oldlivesheet.cell(row=row, column=5).value:
        newlivesheet['A' + str(row)] = oldlivesheet['A' + str(row)].value
        newlivesheet['B' + str(row)] = oldlivesheet['B' + str(row)].value
        newlivesheet['C' + str(row)] = oldlivesheet['C' + str(row)].value
        newlivesheet['D' + str(row)] = oldlivesheet['D' + str(row)].value
        newlivesheet['E' + str(row)] = oldlivesheet['E' + str(row)].value
        newlivesheet['F' + str(row)] = oldlivesheet['F' + str(row)].value
        newlivesheet['G' + str(row)] = oldlivesheet['G' + str(row)].value
        newlivesheet['H' + str(row)] = oldlivesheet['H' + str(row)].value
        newlivesheet['I' + str(row)] = oldlivesheet['I' + str(row)].value
        newlivesheet['J' + str(row)] = oldlivesheet['J' + str(row)].value
        newlivesheet['K' + str(row)] = oldlivesheet['K' + str(row)].value

wb2.save('c:python27deleteNew Roster.xlsx')

I’m hoping that I’m close, all help appreciated. Note that I have to preserve the files in Excel format, so no option to use CSV format unfortunately.


Source: python

Leave a Reply