Sep-07-2021, 09:55 AM
Hello everyone!
I have 2 xlsx files and I need to search some words from one file in file two and write down founded information in third xlsx file. The problem is that when I make two xlsx verification files by my own all works great but when I take information from other xlsx files my program didn't work good.
Here is my program:
I have 2 xlsx files and I need to search some words from one file in file two and write down founded information in third xlsx file. The problem is that when I make two xlsx verification files by my own all works great but when I take information from other xlsx files my program didn't work good.
Here is my program:
from openpyxl import load_workbook, Workbook import re from xlsxwriter import Workbook as XlsxWb def reader(file_name, file_name2): book1 = load_workbook(file_name) book2 = load_workbook(file_name2) book_new = XlsxWb('pizza.xlsx') line = book1.active line2 = book2.active line_new = book_new.add_worksheet("Pakkumine") input_list = [] one_piece_list = [] one_piece_km_list = [] i = 0 j = 0 h = 0 for value in range(1, line.max_row + 1): # I take values from first file highest_count = 0 counter = 0 input_list.clear() one_piece_list.clear() one_piece_km_list.clear() item = line[value][1].value pos = line[value][0].value unit = line[value][2].value amount = line[value][3].value if item is None: continue else: lower_case_item = item.lower() text_re = re.sub(r'[\([{})\]]', "", lower_case_item) item_to_list = list(text_re.strip().split(' ')) # I make list with words that containing in value for value2 in range(1, line2.max_row + 1): # Now we take value from second file item2 = line2[value2][1].value one_piece = line2[value2][2].value one_piece_km = line2[value2][3].value if item2 is None: continue else: lower_case_item2 = item2.lower() text_re2 = re.sub(r'[\([{})\]]', "", lower_case_item2) for word in item_to_list: text = re.search(r'\b{}\b'.format(word), text_re2) #Here we are searching words from second file in words from first file if text: counter += 1 #Counting how much coincidences we have in this case else: continue if counter > highest_count: #This piece of code we need to write value with biggest amount of coincidence highest_count = counter input_list.append(item2) one_piece_list.append(one_piece) one_piece_km_list.append(one_piece_km) counter = 0 line_new.write(i, 1, item) line_new.write(i, 0, pos) elif counter > 0 and counter == highest_count: input_list.append(item2) one_piece_list.append(one_piece) one_piece_km_list.append(one_piece_km) counter = 0 line_new.write(i, 1, item) line_new.write(i, 0, pos) else: continue for x in input_list: #We are writing information about our coincidence line_new.write(i, 2, x) #Name of coincidence from second file line_new.write(i, 3, unit) #quantity name line_new.write(i, 4, amount) #How many we need i += 1 for y in one_piece_list: if type(y) == str: #Check for string continue elif y is None: #Check for zero j += 1 continue else: line_new.write(j, 5, float(y)) #Numbers about cost of one piece line_new.write(j, 7, float(y) * float(amount)) #Numbers about cost of all products j += 1 for z in one_piece_km_list: if type(z) == str: continue elif z is None: h += 1 continue else: line_new.write(h, 6, float(z)) line_new.write(h, 8, float(z) * float(amount)) h += 1 book_new.close() if __name__ == '__main__': reader('newin.xlsx', 'sqr.xlsx')Can someone explain why my code didnt work properly? I attach my xlsx files too.
Attached Files