How to Build a Python FIFO Checker for Excel Inventory with Tkinter

1. Introduction

The author, a Python enthusiast, received a question in a community chat about an error that occurs when reading an Excel file with pandas ("not well‑formed (invalid token): line 3, column 74593"). The discussion highlights that the issue often stems from the source Excel file and can be resolved by copying a problematic cell as a value or re‑saving the file.

2. Implementation

A complete Tkinter‑based GUI application is provided to automate the FIFO (first‑in‑first‑out) validation process. The tool lets users select one or more inventory Excel files and transaction logs, choose an output location, and then runs a series of checks:

It reads each inventory file with pandas.read_excel using the openpyxl engine.

It normalises date columns to contain only the date part.

It adds boolean columns 出入库FIFO and 库存FIFO to flag FIFO violations in the transaction data.

Step 1 checks FIFO consistency within the transaction log by sliding‑window comparison of operation dates and production dates.

Step 2 compares each outbound transaction against the earliest production date available in the inventory queue.

The results are merged into a final column 是否符合FIFO and rows that violate the rule are highlighted in red.

A helper function saves the highlighted dataframe back to an Excel file.

import os
import pandas as pd
import tkinter as tk
from tkinter import filedialog, messagebox, ttk
import subprocess, sys
from collections import deque
from openpyxl.utils.exceptions import InvalidFileException

def install_package(package):
    subprocess.check_call([sys.executable, "-m", "pip", "install", package])

try:
    import openpyxl
except ImportError:
    install_package("openpyxl")
    import openpyxl

def update_progress(progress):
    progressbar["value"] = progress
    root.update_idletasks()

def browse_file(entry_listbox):
    file_paths = filedialog.askopenfilenames()
    entry_listbox.delete(0, tk.END)
    if len(file_paths) > 1:
        merged_path = '...'.join(file_paths[:2]) + f" ({len(file_paths)} files)"
        entry_listbox.insert(tk.END, merged_path)
    else:
        entry_listbox.insert(tk.END, file_paths[0])
    entry_listbox.config(height=1)

def execute():
    inventory_df_paths = [p.strip() for p in inventory_df_entry.get(0, tk.END)]
    transactions_df = transactions_df_entry.get(0, tk.END)
    output_file_path = output_entry.get(0, tk.END)
    selected_paths = inventory_df_entry.get(0, tk.END)
    inventory_df_paths = []
    for i, path in enumerate(selected_paths):
        path = path.strip()
        print(f"Reading file: {path}")
        try:
            pd.ExcelFile = pd.ExcelFile.__module__ + ".openpyxl"
            df = pd.read_excel(path, parse_dates=['生产日期'], engine="openpyxl")
            inventory_df_paths.append(df)
            print("Read success")
        except Exception as e:
            print(f"Error reading file: {str(e)}")
    # ... further processing ...

def check_fifo_rules(inventory_df, transactions_df, output_file_path):
    # Detailed FIFO checking logic (sliding window, date comparisons)
    pass

def highlight_rule(val):
    return 'background-color: red' if not val else ''

def save_to_excel(filename, transactions_df):
    # Save the styled dataframe to Excel
    pass

def browse_output_file(entry_var):
    file_path = filedialog.asksaveasfilename(defaultextension=".xlsx",
        filetypes=[("Excel files", "*.xlsx"), ("Excel files", "*.xls")])
    entry_var.delete(0, tk.END)
    entry_var.insert(tk.END, file_path)

# GUI layout
root = tk.Tk()
root.title("先进先出对比工具")
# ... create labels, listboxes, buttons, progress bar ...
root.mainloop()

3. Summary

The solution demonstrates how to combine pandas data processing with a Tkinter interface to automatically verify FIFO rules in inventory management, providing clear visual feedback and a saved Excel report, while also offering practical tips for handling common Excel‑reading errors.