Recently, my daughter-in-law encountered a repetitive labor on the job, excel table weight check, repeat mark up, ask me if I can write a program to make it automatic weight check marking
have to be arranged
The first time I wrote python properly, while googling and writing
It finally worked.
For the record.
First install the xlwings library
pip install xlwings
write code
import xlwings as xw # Input table name title = input() # Specify to open Excel without displaying, read Excel files app = (visible=False, add_book=False) wb = (title) # Open Excel files sheet = [0] # Select form 0 # Get the number of table rows sheetInfo = sheet.used_range maxRow = sheetInfo.last_cell.row # maxColumn = sheetInfo.last_cell.column # print('Number of table rows:',maxRow) # Document number num = [] # Type of reimbursement baoxiaoType = [] # Department department = [] # Receiving party name = [] # Amount reimbursed money = [] # Read and save the required data for row in range(2, maxRow): value = ("A" + str(row)).value (value) value = ("C" + str(row)).value (value) value = ("H" + str(row)).value (value) value = ("N" + str(row)).value (value) value = ("K" + str(row)).value (value) # print(num) # print(baoxiaoType) # print(department) # print(name) # print(money) # Save line numbers marked as duplicates flag = [] # Determine if a duplicate has been marked # Repeatedly return to Ture # Otherwise return False def isRepeat(index): for num in flag: if num == index: return True else: continue return False # Iterate over each line and do a lookup for row in range(0, len(money)): # Determine if a duplicate has been marked # If repeated without judgment, end this loop # Otherwise, it's broken down if True == isRepeat(row + 2): continue elif False == isRepeat(row + 2): # Get the current row of data current = money[row] # Iterate over subsequent rows to see if they duplicate the data in the current row. for subRow in range(1, len(money)): # Get the next line of data subCur = money[subRow] # Determine whether the contents of the current line and the contents of the comparison line are equal. if current == subCur: # Then determine if the numbered lines are equal if num[row] == num[subRow]: continue else: # Compare other elements for equality if ( (department[row] == department[subRow]) and (baoxiaoType[row] == baoxiaoType[subRow]) and (name[row] == name[subRow]) ): # Save the duplicate row number, the header of the table, and the header row number starts at 1, so the row number is equal to the current index + 2 (subRow + 2) # Set the first column cell color of two repeating rows cell = ("A" + str(row + 2)) = 0, 255, 255 subcell = ("A" + str(subRow + 2)) = 0, 255, 255 # Print tips print("Repeat starting line:", row + 2, "Repeat line", subRow + 2) # Save the current workbook () # Close the current workbook () # Exit the excel program () # Blocking without exiting input("Press Any Key")
Given the daughter-in-law office computer is not convenient to install python environment, so packaged into an exe executable program, using pyinstaller tool
mounting
pip install pyinstaller
ask for a doggy bag (at a restaurant)
# -F Package as a single file # -i Specify the icon pyinstaller -F *.py -i *.ico
Above is the details of excel weight check with python, for more information about python excel weight check, please pay attention to my other related articles!