Openpyxl how to check if input duplicates in column and validate?

user_18488337 0
Viewed 153
import openpyxl
from openpyxl.styles import PatternFill
from openpyxl import workbook

wrong_fill_pattern = PatternFill(patternType='solid', fgColor='FF0000')
right_fill_pattern = PatternFill(patternType='solid', fgColor='008000')
starting = (input("Bill Starting Row `A1` : "))
ending = (input("Bill Ending Row `A20`  : "))
wb = openpyxl.load_workbook('test.xlsx')
s = wb["Sheet1"]
billtocheck = input("Enter Bills : ")

billist = [float(item) for item in billtocheck.split(' ')]
for j in s[f'{starting}':f'{ending}']:
    for cel in j:
        for billtocheck in billist:

            if cel.value == billtocheck:
                print(cel.coordinate, cel.value)
                rightcoordinate = cel.coordinate
                s[f'{rightcoordinate}'].fill = right_fill_pattern
                print('+')

                wb.save("TEST2.xlsx")
                break
            if cel.value != billtocheck:
                wrongcoordinate = cel.coordinate
                s[f'{wrongcoordinate}'].fill = wrong_fill_pattern
                print("-")

    print('-------------')
wb.save("TEST2.xlsx")

This code is kind of bill validation program. In this code user enters column and row starting point and ending point and in that range of column and row i wanted to find the bills and if value exist it just fills the bacground green if not red, it is working fine but i got another problem which is sometimes a bill exist multiple times and i want them to validate by their bill number in different row. The file example i am working on as you can see in this excel file bills located in E column and bill number located at H column so as i say i just want to check if E column contains same value multiple times i just want those multiple values to put user in a confirmation section to get an input which is going to be first where bill numbers located column and row range such as H1 to H20 and get multiple inputs which is going to be bill numbers ( if there are 3 times 64,04 and 2 times 52,02 the program going to ask user to enter input 5 times which is going to be bill numbers). So last thing is if bill number not matching with bill it is going to paint the bill red if it matches paint it green.

=IDEA= To confirm duplicated bills and seperate them each other, when user enters bill numbers it can check the cell coordinates and try to match it with rows of duplicated values and if both is in same row it is match and confirmed.

openpyxl excel python
1 Answers

With the current code this will become somewhat complicated since you may not know if a Bill Value occurs more than once in the column until a complete iteration through the given range. I guess you would need to save each Bill Value match to a list then check if the list has more than one element. If it doesn't you can just colour the cell green and move on to the next Bill Value. If it does then ask for the Bill Number cell range and the Bill numbers to check against. You'd have to cycle thru the matched values so you can get the corresponding Bill Number for each matched cell and then check if that compares with the entered Bill numbers, if they do then make the background green.

If you are not familiar with, I think you would benefit to learn about lists and dictionaries this will make things much easier.

Please check your original question, I had given an alternate way to achieve what you were attempting at the time. This code uses dictionaries and lists and will make it much easier to add this additional check.

For example the found_list will immediately determine if there is more than one match for your Bill Value.

found_list = [key for key, value in range_dict.items() if value == btc]
if len(found_list) > 1:

You can the get the Bill Number range and values then run one loop checking the Bill Values from the found_list with the Bill Number against those entered by the User.

I expect also you would only need the Column letter/number for the Bill Number not a cell range. From your example sheet, the Bill Number is the same row as the Value so you just need to obtain the value from the correct cell corresponding to the Bill Value your checking at the time.

user_136641370
Related