如何实现Excel记录批量遍历与结果写入的循环逻辑？解决现有代码仅处理单条记录的问题

Got it, let's get your code to process every record in your input Excel file instead of just the first one. Here's how to fix it, with explanations of key changes:

Key Issues in Your Original Code #

• You only accessed a single cell (worksheet.cell(1,0).value) instead of looping through all rows
• You hardcoded the output to row 2 instead of using a counter to increment rows as you write results
• You reused the sheet variable for both input and output, which can cause confusion or accidental overwrites

Optimized Code Implementation #

import xlrd
import openpyxl

# ----------------------
# Step 1: Read Input Excel
# ----------------------
input_workbook = xlrd.open_workbook("URL of input file")
input_worksheet = input_workbook.sheet_by_index(0)  # Use sheet_by_name("sheet name") if you prefer named sheets

# ----------------------
# Step 2: Set Up Output Excel
# ----------------------
output_workbook = openpyxl.Workbook()
output_sheet = output_workbook.create_sheet("NIL Records", 1)  # Create target sheet at index 1
output_sheet["A1"].value = "Registration Number"
output_sheet["B1"].value = "Status"

# Remove default empty "Sheet" if you don't need it
if "Sheet" in output_workbook.sheetnames:
    output_workbook.remove(output_workbook["Sheet"])

# ----------------------
# Step 3: Loop Through All Records
# ----------------------
current_output_row = 2  # Start writing right after the header row (row 1)

# Loop from row 1 to skip input header (adjust to row 0 if your input has no header)
for row_index in range(1, input_worksheet.nrows):
    # Get the registration number from column 0 of the current row
    registration_number = input_worksheet.cell(row_index, 0).value
    
    # ----------------------
    # Replace this with your actual "record found" check logic
    # Example: Query a database, cross-reference another Excel file, etc.
    # ----------------------
    record_found = False  # Default to not found for demo; update this based on your real check
    
    # If record is not found, write to output sheet
    if not record_found:
        output_sheet[f"A{current_output_row}"].value = registration_number
        output_sheet[f"B{current_output_row}"].value = "Registration Number Not Registered"
        current_output_row += 1  # Move to the next row for the next result

# Save the final output file
output_workbook.save("path to the saved location file")

Key Improvements Explained #

• Full Row Looping: Uses input_worksheet.nrows to get the total number of rows, then loops through each row starting from index 1 (skip header; change to 0 if your input has no header row)
• Dynamic Row Writing: The current_output_row variable tracks where to write the next result, and increments after each write to avoid overwriting existing entries
• Clear Variable Names: Renamed variables like input_workbook/output_workbook to eliminate confusion between input and output data structures
• Explicit Logic Placeholder: Left a clear section for you to insert your actual "record found" check (e.g., querying an external database or another spreadsheet)

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