添加删除过时UPC代码段后Python脚本无响应故障排查

问题根源 #

新增的删除过时UPC、无UPC及无价格条目的代码段存在三个致命问题：

• 重复条件判断：两个完全相同的barcode_type9 == "Obsolete"分支，导致同一行被多次处理，逻辑混乱
• 无限循环隐患：内部while循环未处理行索引超出工作表范围的情况，当删除到最后一行时，next_row_item_code9始终为None，循环永远无法终止
• 反向遍历的逻辑冲突：直接遍历sheet.iter_rows()生成的行对象，删除行后会导致后续遍历的行索引错位，引发异常或重复处理

修复后的核心代码（替换原第10-56行） #

# * - SECTION DELETE OBSOLETE UPC AND NO UPC AND NO PRICE ITEMS - *

# Get the column names
columns9 = [cell.value for cell in next(sheet.iter_rows(min_row=1))]

# 预计算列索引，避免重复调用index()影响性能
item_code_col = columns9.index('Item Code')
barcode_col = columns9.index('Barcode (row 1 for current use) (Barcodes)')
barcode_type_col = columns9.index('Barcode Type (Barcodes)')
selling_price_col = columns9.index('SELLING PRICE *(Fetched from Selling Price List)*')

# Variable to track if a non-blank "Item Code" row has been encountered
non_blank_row_encountered = False

# 获取所有需要处理的行索引（反向），避免遍历过程中工作表结构变化导致的问题
row_indices = list(range(2, sheet.max_row + 1))[::-1]

for row_idx in row_indices:
    row = sheet[row_idx]
    item_code9 = row[item_code_col].value
    barcode9 = row[barcode_col].value
    barcode_type9 = row[barcode_type_col].value
    selling_price = row[selling_price_col].value

    # 处理无UPC或无价格的条目
    if item_code9 is not None:
        if barcode9 is None or selling_price is None or selling_price == 0:
            sheet.delete_rows(row_idx)
            non_blank_row_encountered = True
        # 处理过时UPC条目
        elif selling_price is not None and barcode_type9 == "Obsolete":
            current_row_index = row_idx
            next_row_index = current_row_index + 1
            # 循环删除下方的过时或空白行，添加边界判断
            while next_row_index <= sheet.max_row:
                next_row_item_code9 = sheet.cell(row=next_row_index, column=item_code_col + 1).value
                if next_row_item_code9 is not None and next_row_item_code9 != "Obsolete":
                    break
                sheet.delete_rows(next_row_index)
            # 删除当前过时行
            sheet.delete_rows(current_row_index)
    # 处理非空白行之后的空白行
    elif non_blank_row_encountered:
        sheet.delete_rows(row_idx)

修复核心调整 #

• 预计算列索引：将列索引查找移到循环外，避免重复调用index()浪费性能，同时减少出错概率
• 预生成行索引列表：先提取所有需要处理的行索引并反向排序，避免遍历过程中工作表行结构变化导致的遍历异常
• 合并重复逻辑分支：将两个相同的Obsolete判断分支合并，逻辑更清晰，避免重复处理同一行
• 添加循环边界校验：在while循环中增加next_row_index <= sheet.max_row的判断，防止索引越界导致无限循环
• 优化删除逻辑顺序：先处理无UPC/无价格的条目，再处理过时条目，避免逻辑冲突

完整修复后脚本 #

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

# Load the workbook
workbook = load_workbook('item.xlsx')

# Select the active sheet (first sheet by default)
sheet = workbook.active

# * - SECTION DELETE OBSOLETE UPC AND NO UPC AND NO PRICE ITEMS - *

# Get the column names
columns9 = [cell.value for cell in next(sheet.iter_rows(min_row=1))]

# 预计算列索引，避免重复调用index()影响性能
item_code_col = columns9.index('Item Code')
barcode_col = columns9.index('Barcode (row 1 for current use) (Barcodes)')
barcode_type_col = columns9.index('Barcode Type (Barcodes)')
selling_price_col = columns9.index('SELLING PRICE *(Fetched from Selling Price List)*')

# Variable to track if a non-blank "Item Code" row has been encountered
non_blank_row_encountered = False

# 获取所有需要处理的行索引（反向），避免遍历过程中工作表结构变化导致的问题
row_indices = list(range(2, sheet.max_row + 1))[::-1]

for row_idx in row_indices:
    row = sheet[row_idx]
    item_code9 = row[item_code_col].value
    barcode9 = row[barcode_col].value
    barcode_type9 = row[barcode_type_col].value
    selling_price = row[selling_price_col].value

    # 处理无UPC或无价格的条目
    if item_code9 is not None:
        if barcode9 is None or selling_price is None or selling_price == 0:
            sheet.delete_rows(row_idx)
            non_blank_row_encountered = True
        # 处理过时UPC条目
        elif selling_price is not None and barcode_type9 == "Obsolete":
            current_row_index = row_idx
            next_row_index = current_row_index + 1
            # 循环删除下方的过时或空白行，添加边界判断
            while next_row_index <= sheet.max_row:
                next_row_item_code9 = sheet.cell(row=next_row_index, column=item_code_col + 1).value
                if next_row_item_code9 is not None and next_row_item_code9 != "Obsolete":
                    break
                sheet.delete_rows(next_row_index)
            # 删除当前过时行
            sheet.delete_rows(current_row_index)
    # 处理非空白行之后的空白行
    elif non_blank_row_encountered:
        sheet.delete_rows(row_idx)

# Save the modified workbook
workbook.save('delete7.xlsx')

# * - SECTION BARCODES TYPE SEPARATION - *

# Load the workbook
workbook = load_workbook('delete7.xlsx')

# Select the active sheet (first sheet by default)
sheet = workbook.active

# Find the column index for the "Barcode Type (Barcodes)" column
barcodetype_column_index = None
for column in sheet.iter_cols():
    if column[0].value == "Barcode Type (Barcodes)":
        barcodetype_column_index = column[0].column
        break

if barcodetype_column_index is None:
    print("Barcode Type (Barcodes) column not found!")
    exit()

# Find the column index for the "Barcode (row 1 for current use) (Barcodes)" column
barcode_column_index = None
for column in sheet.iter_cols():
    if column[0].value == "Barcode (row 1 for current use) (Barcodes)":
        barcode_column_index = column[0].column
        break

if barcode_column_index is None:
    print("Barcode (row 1 for current use) (Barcodes) column not found!")
    exit()

# Get all unique non-blank values except "Obsolete" from the "Barcode Type (Barcodes)" column
barcodetypes = set()
for cell in sheet[get_column_letter(barcodetype_column_index)][1:]:
    if cell.value is not None and cell.value != "" and cell.value != "Obsolete":
        barcodetypes.add(cell.value)

# Create new columns next to the "barcodetype" column and copy values from the "Barcode (row 1 for current use) (Barcodes)" column
new_columns = []
column_index = barcodetype_column_index + 1

# Check if "UPC-A" column exists
if "UPC-A" in barcodetypes:
    # Insert "UPC-A" column first
    column_letter = get_column_letter(column_index)
    sheet.insert_cols(column_index)
    sheet[column_letter + '1'] = "UPC-A_py"  # Add suffix "_py" to the new column header

    # Copy values from the "Barcode (row 1 for current use) (Barcodes)" column
    for row in range(2, sheet.max_row + 1):
        barcode_value = sheet[get_column_letter(barcode_column_index) + str(row)].value
        barcodetype_value = sheet[get_column_letter(barcodetype_column_index) + str(row)].value

        if barcodetype_value == "UPC-A":
            sheet[column_letter + str(row)] = barcode_value

    new_columns.append(column_letter)
    column_index += 1

# Insert other barcode type columns
for barcodetype in barcodetypes:
    if barcodetype != "UPC-A":
        column_letter = get_column_letter(column_index)
        sheet.insert_cols(column_index)
        sheet[column_letter + '1'] = barcodetype + "_py"  # Add suffix "_py" to the new column header

        # Copy values from the "Barcode (row 1 for current use) (Barcodes)" column
        for row in range(2, sheet.max_row + 1):
            barcode_value = sheet[get_column_letter(barcode_column_index) + str(row)].value
            barcodetype_value = sheet[get_column_letter(barcodetype_column_index) + str(row)].value

            if barcodetype_value == barcodetype:
                sheet[column_letter + str(row)] = barcode_value

        new_columns.append(column_letter)
        column_index += 1

# Update matching values in the "Barcode Type (Barcodes)" column with suffix "_py"
for row in range(2, sheet.max_row + 1):
    barcodetype_value = sheet[get_column_letter(barcodetype_column_index) + str(row)].value

    if barcodetype_value in barcodetypes:
        updated_value = barcodetype_value 
        sheet[get_column_letter(barcodetype_column_index) + str(row)].value = updated_value

# Delete the "Barcode Type (Barcodes)" column
sheet.delete_cols(barcodetype_column_index)

# Delete the "Barcode (row 1 for current use) (Barcodes)" column
sheet.delete_cols(barcode_column_index)

# * - SECTION SUPPLIER ABBREVIATIONS CONSOLIDATION - *

# Find the column index of "Supplier Abbr. (Supplier Items)"
header_row = 1
supplier_abbr_column = None
for column in range(1, sheet.max_column + 1):
    header_value = sheet.cell(row=header_row, column=column).value
    if header_value == "Supplier Abbr. (Supplier Items)":
        supplier_abbr_column = column
        break

# If the "Supplier Abbr. (Supplier Items)" column is found, proceed with modification
if supplier_abbr_column is not None:
    # Find the last column index
    last_column = sheet.max_column

    # Insert a new column "Supplier_py" to the right of "Supplier Abbr. (Supplier Items)"
    new_column_index = supplier_abbr_column + 1
    sheet.insert_cols(new_column_index)

    # Update the header for the new column
    sheet.cell(row=header_row, column=new_column_index, value="Supplier_py")

    # Iterate over each row in the sheet
    for row in range(2, sheet.max_row + 1):
        item_code = sheet.cell(row=row, column=1).value
        supplier_abbr = sheet.cell(row=row, column=supplier_abbr_column).value

        if item_code and supplier_abbr:
            supplier_py = supplier_abbr
            current_row = row + 1

            # Fetch the values from the following rows until the next non-blank row in both columns
            while current_row <= sheet.max_row:
                current_item_code = sheet.cell(row=current_row, column=1).value
                current_supplier_abbr = sheet.cell(row=current_row, column=supplier_abbr_column).value

                if current_item_code and current_supplier_abbr:
                    break

                if current_supplier_abbr:
                    supplier_py += " " + current_supplier_abbr

                current_row += 1

            # Set the value in the "Supplier_py" column
            sheet.cell(row=row, column=new_column_index, value=supplier_py)

    # Remove the "Supplier Abbr. (Supplier Items)" column
sheet.delete_cols(supplier_abbr_column)

# * - SECTION CODE MEDICAMENT SEPERATION - *

# Get the header values from the columns A_py, B_py, D_py, X_py, H_py, and E_py
headers = [sheet.cell(row=1, column=column_index).value for column_index in range(1, sheet.max_column + 1)]

# Find the index of the "Code list (Code Médicament)" column
code_column_index = headers.index('Code list (Code Médicament)') + 1
item_column_index = headers.index('Item Code') + 1

# Insert new columns A_py, B_py, D_py, X_py, H_py, and E_py adjacent to the "Item Code" column
sheet.insert_cols(code_column_index + 1, 6)

# Update the headers list to include the new columns
headers = headers[:code_column_index] + ['A_py', 'B_py', 'D_py', 'X_py', 'H_py', 'E_py'] + headers[code_column_index:]

# Set the header values for the new columns
for column_index, header_value in enumerate(headers, start=1):
    sheet.cell(row=1, column=column_index).value = header_value

# Iterate over each row starting from the second row
for row in range(2, sheet.max_row + 1):
    code_value = sheet.cell(row=row, column=code_column_index).value
    item_value = sheet.cell(row=row, column=item_column_index).value

    if item_value is not None:
        # Iterate over the header columns (excluding the "Code" column)
        for column_index in range(1, sheet.max_column + 1):
            if column_index != code_column_index:
                header_value = sheet.cell(row=1, column=column_index).value

                # Check if the code value is not None and the header matches the code value + "_py"
                if code_value is not None and header_value == code_value + "_py":
                    # Copy the code value to the respective column in the same row
                    sheet.cell(row=row, column=column_index).value = code_value
    else:
        # Find the nearest non-blank row above
        nearest_row = row - 1
        while nearest_row > 1 and sheet.cell(row=nearest_row, column=item_column_index).value is None:
            nearest_row -= 1

        # Iterate over the header columns (excluding the "Code" column)
        for column_index in range(1, sheet.max_column + 1):
            if column_index != code_column_index:
                header_value = sheet.cell(row=1, column=column_index).value

                # Check if the code value is not None and the header matches the code value + "_py"
                if code_value is not None and header_value == code_value + "_py":
                    # Copy the code value to the respective column in the nearest non-blank row above
                    sheet.cell(row=nearest_row, column=column_index).value = code_value

# Remove the "Code" column
sheet.delete_cols(code_column_index)

# * - SECTION REMOVE EMPTY ROWS - *

# Create a list to store the row indices that should be deleted
rows_to_delete = []

# Iterate over each row starting from the second row
for row in range(2, sheet.max_row + 1):
    # Check if all values in the row are blank
    is_blank_row = all(sheet.cell(row=row, column=column_index).value is None for column_index in range(1, sheet.max_column + 1))

    if is_blank_row:
        rows_to_delete.append(row)

# Iterate over the rows in reverse order and delete them
for row in reversed(rows_to_delete):
    sheet.delete_rows(row)

# Save the modified workbook
workbook.save('Upload for Barcodes3.xlsx')

内容的提问来源于stack exchange，提问作者l4cky