SoFunction
Updated on 2024-10-29

Checking weights in excel with python

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!