基于Python实现手持扫码枪扫描条码并更新Excel仓库商品计数的代码请求

Got it, let's tackle this barcode counting task efficiently. The perfect tool for this job is openpyxl—a lightweight Python library designed for reading and writing .xlsx files. Here's a concise, robust solution that fits all your requirements:

Step 1: Install Dependencies #

First, install the required library if you haven't already:

pip install openpyxl

Step 2: Full Working Code #

from openpyxl import load_workbook

# Replace this with your actual Excel file path
INVENTORY_FILE = "your_warehouse_inventory.xlsx"

def update_inventory_count():
    # Load the Excel workbook (supports read/write by default)
    wb = load_workbook(INVENTORY_FILE)
    ws = wb.active  # Use the first sheet; change to wb["SheetName"] if needed
    
    # Pre-build a barcode-to-row mapping for fast lookups (avoids looping 4000+ rows every scan)
    barcode_to_row = {}
    for row_idx in range(1, ws.max_row + 1):
        barcode = ws[f"A{row_idx}"].value
        if barcode:
            # Convert barcode to string to handle both numeric and text-based barcodes
            barcode_to_row[str(barcode)] = row_idx
    
    print("Scanner ready! Scan a barcode, or type 'exit' to stop.")
    
    while True:
        scanned_code = input("Scan > ").strip()
        
        # Exit condition
        if scanned_code.lower() == "exit":
            print("Saving changes and exiting...")
            wb.save(INVENTORY_FILE)
            break
        
        # Check if barcode exists in our inventory
        if scanned_code not in barcode_to_row:
            print(f"Error: Barcode '{scanned_code}' not found in the inventory list.")
            continue
        
        # Get the corresponding row number
        target_row = barcode_to_row[scanned_code]
        count_cell = ws[f"B{target_row}"]
        
        # Update the count value
        if not count_cell.value:
            # Cell is empty, set to 1
            count_cell.value = 1
        else:
            # Try to increment the existing count; handle non-numeric values gracefully
            try:
                count_cell.value = int(count_cell.value) + 1
            except ValueError:
                print(f"Warning: Cell B{target_row} has non-numeric content. Resetting count to 1.")
                count_cell.value = 1
        
        # Save immediately after each update to prevent data loss
        wb.save(INVENTORY_FILE)
        print(f"Success! Count for '{scanned_code}' is now {count_cell.value}")

if __name__ == "__main__":
    update_inventory_count()

Key Features Explained #

• Fast Lookups: We pre-load all barcodes into a dictionary once at startup, so each scan only takes O(1) time instead of looping through 4000+ rows every time.
• Type Safety: Converts barcodes to strings to match both numeric barcodes stored as numbers and text-based barcodes in Excel.
• Error Handling: Gracefully handles missing barcodes and non-numeric values in column B.
• Data Persistence: Saves the Excel file after every update, so you won't lose counts if the program closes unexpectedly.

How to Use #

1. Replace INVENTORY_FILE with the full path to your .xlsx file (e.g., C:/warehouse/inventory.xlsx on Windows or /home/user/inventory.xlsx on Linux/macOS).
2. Run the script.
3. Scan barcodes using your handheld scanner (it will input the barcode text automatically, just like typing).
4. When you're done, type exit to save changes and close the program.

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