一、前言
前几天在Python最强王者交流群【钟爱一生】问了一个Python自动化办公的问题，问题如下：not well-formed (invalid token): line 3, column 74593各位老师，读取excel文件时，有这个报错，应该怎么解决？
代码如下：
代码语言：javascript复制import os
import pandas as pd
import tkinter as tk
from tkinter import filedialog
from tkinter import messagebox
from tkinter import ttk
import subprocess
import 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) # 将选择的文件路径插入到Listbox中
else:
entry_listbox.insert(tk.END, file_paths[0]) # 只显示第一个选择的文件路径
# 设置Listbox的高度为1，无论选择了多少文件
entry_listbox.config(height=1)
# def execute():
# inventory_df_paths = inventory_df_entry.get(0, tk.END).strip().split("n")
# transactions_df = transactions_df_entry.get(0, tk.END)
# output_file_path = output_entry.get(0, tk.END)
#
# if not inventory_df_paths or not transactions_df or not output_file_path:
# messagebox.showwarning("警告", "请提供所有必要的文件和文件夹路径！")
# else:
# # 读取所有库存现有量文件的数据
# inventory_dfs = []
# for inventory_df_path in inventory_df_paths:
# inventory_df = pd.read_excel(inventory_df_path, parse_dates=['生产日期'])
# inventory_dfs.append(inventory_df)
def execute():
inventory_df_paths = inventory_df_entry.get(0, tk.END)
inventory_df_paths = [path.strip() for path in inventory_df_paths]
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 path in selected_paths:
path = path.strip()
print(f"正在读取文件：{path}")
try:
pd.ExcelFile = pd.ExcelFile.__module__ ".openpyxl" #添加此语句，使pandas默认采用openpyxl作为Excel解析
df = pd.read_excel(path, parse_dates=['生产日期'], engine="openpyxl")
inventory_df_paths.append(df)
print("读取成功")
except Exception as e:
print(f"读取文件时出现错误：{str(e)}")
transactions_df = transactions_df_entry.get(0, tk.END)
output_file_path = output_entry.get(0, tk.END)
if not inventory_df_paths or not transactions_df or not output_file_path:
messagebox.showwarning("警告", "请提供所有必要的文件和文件夹路径！")
else:
progressbar["value"] = 0 # 将进度条重置为0
root.update_idletasks()
check_fifo_rules(inventory_df_paths, transactions_df, output_file_path)
# 合并所有库存现有量数据为一个 DataFrame
inventory_df = pd.concat(inventory_df_paths)
# 函数：检查FIFO规则
def check_fifo_rules(inventory_df, transactions_df, output_file_path):
try:
# 确保操作日期和生产日期仅包含日期部分
transactions_df['操作时间'] = transactions_df['操作时间'].dt.date
transactions_df['生产日期'] = transactions_df['生产日期'].dt.date
# 初始化检查列
transactions_df['出入库FIFO'] = True
transactions_df['库存FIFO'] = True
# Step 1: 检查出入库表中的FIFO规则
for (material, warehouse), group in transactions_df.groupby(['物料编码', '仓库编码']):
group = group[group['操作代码'] == '出库'].sort_values(by=['操作时间', '生产日期'])
sliding_window = []
indices_to_update_false = set()
indices_to_update_true = set()
for i, row in group.iterrows():
row_operation_date = row['操作时间']
row_production_date = row['生产日期']
for j in range(1, len(sliding_window)):
prev_record = sliding_window[-j]
second_prev_record = sliding_window[-j - 1] if j < len(sliding_window) else None
if not pd.isnull(row_production_date) and second_prev_record is not None:
if row_production_date == prev_record['生产日期'] == second_prev_record['生产日期']:
indices_to_update_true.update({i, prev_record['index'], second_prev_record['index']})
if (row_operation_date == prev_record['操作时间'] > second_prev_record['操作时间'] and
row_production_date == prev_record['生产日期'] < second_prev_record['生产日期']):
indices_to_update_false.update({i, prev_record['index'], second_prev_record['index']})
if sliding_window:
previous_record = sliding_window[-1]
if row_operation_date > previous_record['操作时间']:
if not pd.isnull(row_production_date) and not pd.isnull(
previous_record['生产日期']) and row_production_date < previous_record['生产日期']:
indices_to_update_false.update({i, previous_record['index']})
if row_operation_date == previous_record['操作时间']:
if row_production_date == previous_record['生产日期']:
for k in range(len(sliding_window) - 1):
prev_record = sliding_window[-k - 2]
if not pd.isnull(row_production_date) and not pd.isnull(prev_record['生产日期']):
if row_production_date < prev_record['生产日期']:
indices_to_update_false.update({i, previous_record['index']})
if row_operation_date != previous_record['操作时间']:
if row_production_date == previous_record['生产日期']:
for k in range(len(sliding_window) - 1):
prev_record = sliding_window[-k - 2]
if not pd.isnull(row_production_date) and not pd.isnull(prev_record['生产日期']):
if row_production_date == prev_record['生产日期']:
indices_to_update_true.update({i, previous_record['index']})
sliding_window.append({
'index': i,
'操作时间': row_operation_date,
'生产日期': row_production_date
})
if len(sliding_window) > 3:
sliding_window.pop(0)
transactions_df.loc[indices_to_update_false, '出入库FIFO'] = False
transactions_df.loc[indices_to_update_true, '出入库FIFO'] = True
# Step 2: 检查出库记录与库存表中的FIFO规则
for (material, warehouse), group in transactions_df.groupby(['物料编码', '仓库编码']):
fifo_queue = inventory_df[(inventory_df['物料编码'] == material) & (inventory_df['仓库编码'] == warehouse)]
fifo_queue = fifo_queue.sort_values(by='生产日期')
earliest_production_date = fifo_queue['生产日期'].min()
for i, row in group[group['操作代码'] == '出库'].sort_values(by='操作时间').iterrows():
if not pd.isnull(earliest_production_date) and not pd.isnull(row['生产日期']) and row[
'生产日期'] > earliest_production_date:
transactions_df.loc[i, '库存FIFO'] = False
# 合并两个检查结果
transactions_df['是否符合FIFO'] = transactions_df['出入库FIFO'] & transactions_df['库存FIFO']
# 标红不符合FIFO规则的记录
def highlight_rule(val):
return 'background-color: red' if not val else ''
styled_transactions_df = transactions_df.style.applymap(highlight_rule, subset=['是否符合FIFO'])
# 函数：保存标红后的表格为Excel文件
def save_to_excel(filename, transactions_df):
styled_transactions_df = transactions_df.style.apply(lambda x: [highlight_rule(v) for v in x],
subset=['是否符合FIFO'])
output_folder = os.path.dirname(output_file_path)
if not os.path.exists(output_folder):
os.makedirs(output_folder)
styled_transactions_df.to_excel(output_file_path, index=True)
update_progress(100) # 设置进度条为100
messagebox.showinfo("完成", "核对完成，祝工作顺利！")
except Exception as e:
messagebox.showerror("错误", f"发生错误：{str(e)}")
# def browse_file(entry_var):
# file_path = filedialog.askopenfilename()
# entry_var.delete(0, tk.END) # 清空Text控件内容
# entry_var.insert(tk.END, file_path) # 将选择的文件路径插入到Text控件中
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) # 清空Text控件内容
entry_var.insert(tk.END, file_path) # 将选择的文件路径插入到Text控件中
# def execute():
# inventory_df = 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)
#
# if not inventory_df or not transactions_df or not output_file_path:
# messagebox.showwarning("警告", "请提供所有必要的文件和文件夹路径！")
# else:
# progressbar["value"] = 0 # 将进度条重置为0
# root.update_idletasks()
# check_fifo_rules(inventory_df, transactions_df, output_file_path)
# 创建主窗口
root = tk.Tk()
root.title("先进先出对比工具")
# 创建UI组件
inventory_df_label = tk.Label(root, text="选择库存现有量文件:")
inventory_df_entry = tk.Listbox(root, selectmode=tk.MULTIPLE, width=40, height=1)
inventory_df_button = tk.Button(root, text="浏览", command=lambda: browse_file(inventory_df_entry))
transactions_df_label = tk.Label(root, text="选择出入库文件：")
transactions_df_entry = tk.Listbox(root, selectmode=tk.MULTIPLE, width=40, height=1)
transactions_df_button = tk.Button(root, text="浏览", command=lambda: browse_file(transactions_df_entry))
output_label = tk.Label(root, text="选择输出文件:")
output_entry = tk.Listbox(root, selectmode=tk.MULTIPLE, width=40, height=1)
output_button = tk.Button(root, text="浏览", command=lambda: browse_output_file(output_entry))
execute_button = tk.Button(root, text="开始执行", command=execute)
progressbar = ttk.Progressbar(root, mode="determinate")
# 布局UI组件
inventory_df_label.grid(row=1, column=0, padx=10, pady=10, sticky=tk.W)
inventory_df_entry.grid(row=1, column=1, padx=10, pady=10)
inventory_df_button.grid(row=1, column=2, padx=10, pady=10)
transactions_df_label.grid(row=0, column=0, padx=10, pady=10, sticky=tk.W)
transactions_df_entry.grid(row=0, column=1, padx=10, pady=10)
transactions_df_button.grid(row=0, column=2, padx=10, pady=10)
output_label.grid(row=2, column=0, padx=10, pady=10, sticky=tk.W)
output_entry.grid(row=2, column=1, padx=10, pady=10)
output_button.grid(row=2, column=2, padx=10, pady=10)
execute_button.grid(row=3, column=0, columnspan=3, pady=20)
progressbar.grid(row=4, column=0, columnspan=3, padx=10, pady=10, sticky=tk.W tk.E)
# 运行主循环
root.mainloop()
二、实现过程
这里【莫生气】给了个思路如下：第三行看看有点问题，是表格报错还是 Python 报错，这看不出来。
后来【隔壁
