针对打包后exe文件无法读取Excel的问题,怎么动态获取资源路径,请显示修改后的全部代码
import os
import json
import re
import pandas as pd
import numpy as np
import win32com.client
from PyQt6 import QtCore, QtGui, QtWidgets
from PyQt6.QtWidgets import QFileDialog, QMessageBox
class Ui_MainWindow(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(513, 462)
self.centralwidget = QtWidgets.QWidget(parent=MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.formLayout = QtWidgets.QFormLayout(self.centralwidget)
self.formLayout.setObjectName("formLayout")
# Initialize labels and line edits
self.label = QtWidgets.QLabel(parent=self.centralwidget)
font = QtGui.QFont()
font.setPointSize(13)
self.label.setFont(font)
self.label.setObjectName("label")
self.formLayout.setWidget(0, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label)
self.lineEdit = QtWidgets.QLineEdit(parent=self.centralwidget)
self.lineEdit.setObjectName("lineEdit")
self.lineEdit.textChanged.connect(self.save_data) # Connect to save data
self.formLayout.setWidget(0, QtWidgets.QFormLayout.ItemRole.FieldRole, self.lineEdit)
self.label_2 = QtWidgets.QLabel(parent=self.centralwidget)
font = QtGui.QFont()
font.setPointSize(13)
self.label_2.setFont(font)
self.label_2.setObjectName("label_2")
self.formLayout.setWidget(2, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_2)
self.lineEdit_2 = QtWidgets.QLineEdit(parent=self.centralwidget)
self.lineEdit_2.setObjectName("lineEdit_2")
self.lineEdit_2.textChanged.connect(self.save_data) # Connect to save data
self.formLayout.setWidget(2, QtWidgets.QFormLayout.ItemRole.FieldRole, self.lineEdit_2)
self.label_3 = QtWidgets.QLabel(parent=self.centralwidget)
font = QtGui.QFont()
font.setPointSize(13)
self.label_3.setFont(font)
self.label_3.setObjectName("label_3")
self.formLayout.setWidget(4, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_3)
self.lineEdit_3 = QtWidgets.QLineEdit(parent=self.centralwidget)
self.lineEdit_3.setObjectName("lineEdit_3")
self.lineEdit_3.textChanged.connect(self.save_data) # Connect to save data
self.formLayout.setWidget(4, QtWidgets.QFormLayout.ItemRole.FieldRole, self.lineEdit_3)
self.label_4 = QtWidgets.QLabel(parent=self.centralwidget)
font = QtGui.QFont()
font.setPointSize(13)
self.label_4.setFont(font)
self.label_4.setObjectName("label_4")
self.formLayout.setWidget(6, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_4)
self.lineEdit_4 = QtWidgets.QLineEdit(parent=self.centralwidget)
self.lineEdit_4.setObjectName("lineEdit_4")
self.lineEdit_4.textChanged.connect(self.save_data) # Connect to save data
self.formLayout.setWidget(6, QtWidgets.QFormLayout.ItemRole.FieldRole, self.lineEdit_4)
self.label_5 = QtWidgets.QLabel(parent=self.centralwidget)
font = QtGui.QFont()
font.setPointSize(13)
self.label_5.setFont(font)
self.label_5.setObjectName("label_5")
self.formLayout.setWidget(8, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_5)
self.lineEdit_5 = QtWidgets.QLineEdit(parent=self.centralwidget)
self.lineEdit_5.setObjectName("lineEdit_5")
self.lineEdit_5.textChanged.connect(self.save_data) # Connect to save data
self.formLayout.setWidget(8, QtWidgets.QFormLayout.ItemRole.FieldRole, self.lineEdit_5)
self.label_6 = QtWidgets.QLabel(parent=self.centralwidget)
font = QtGui.QFont()
font.setPointSize(13)
self.label_6.setFont(font)
self.label_6.setObjectName("label_6")
self.formLayout.setWidget(10, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_6)
self.lineEdit_6 = QtWidgets.QLineEdit(parent=self.centralwidget)
self.lineEdit_6.setObjectName("lineEdit_6")
self.lineEdit_6.textChanged.connect(self.save_data) # Connect to save data
self.formLayout.setWidget(10, QtWidgets.QFormLayout.ItemRole.FieldRole, self.lineEdit_6)
self.label_7 = QtWidgets.QLabel(parent=self.centralwidget)
font = QtGui.QFont()
font.setPointSize(13)
self.label_7.setFont(font)
self.label_7.setObjectName("label_7")
self.formLayout.setWidget(12, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_7)
self.lineEdit_7 = QtWidgets.QLineEdit(parent=self.centralwidget)
self.lineEdit_7.setObjectName("lineEdit_7")
self.lineEdit_7.setReadOnly(True) # Make it read-only to prevent manual input
self.lineEdit_7.mousePressEvent = self.open_folder_dialog # Set click event
self.formLayout.setWidget(12, QtWidgets.QFormLayout.ItemRole.FieldRole, self.lineEdit_7)
self.label_8 = QtWidgets.QLabel(parent=self.centralwidget)
font = QtGui.QFont()
font.setPointSize(13)
self.label_8.setFont(font)
self.label_8.setObjectName("label_8")
self.formLayout.setWidget(14, QtWidgets.QFormLayout.ItemRole.LabelRole, self.label_8)
self.lineEdit_8 = QtWidgets.QLineEdit(parent=self.centralwidget)
self.lineEdit_8.setObjectName("lineEdit_8")
self.lineEdit_8.textChanged.connect(self.save_data) # Connect to save data
self.formLayout.setWidget(14, QtWidgets.QFormLayout.ItemRole.FieldRole, self.lineEdit_8)
self.pushButton = QtWidgets.QPushButton(parent=self.centralwidget)
self.pushButton.setObjectName("pushButton")
self.pushButton.setText("执行")
self.pushButton.clicked.connect(self.process_data) # Connect to process data
self.formLayout.setWidget(16, QtWidgets.QFormLayout.ItemRole.FieldRole, self.pushButton)
MainWindow.setCentralWidget(self.centralwidget)
self.menubar = QtWidgets.QMenuBar(parent=MainWindow)
self.menubar.setGeometry(QtCore.QRect(0, 0, 513, 22))
self.menubar.setObjectName("menubar")
MainWindow.setMenuBar(self.menubar)
self.statusbar = QtWidgets.QStatusBar(parent=MainWindow)
self.statusbar.setObjectName("statusbar")
MainWindow.setStatusBar(self.statusbar)
self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
# Load previous data
self.load_data()
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
self.label.setText(_translate("MainWindow", "付款清单开始日期"))
self.label_2.setText(_translate("MainWindow", "付款清单结束日期"))
self.label_3.setText(_translate("MainWindow", "ZFIR08_N"))
self.label_4.setText(_translate("MainWindow", "ZFIR08_Y"))
self.label_5.setText(_translate("MainWindow", "ZFIR08_D"))
self.label_6.setText(_translate("MainWindow", "FBL5N_Date"))
self.label_7.setText(_translate("MainWindow", "文件地址"))
self.label_8.setText(_translate("MainWindow", "输入公司代码"))
def open_folder_dialog(self, event):
folder = QFileDialog.getExistingDirectory(None, "选择文件夹")
if folder:
self.lineEdit_7.setText(folder)
self.save_data() # Save data when folder is selected
def load_data(self):
# Load the previous input data from a JSON file
if os.path.exists('data.json'):
with open('data.json', 'r', encoding='utf-8') as f:
data = json.load(f)
self.lineEdit.setText(data.get("lineEdit", ""))
self.lineEdit_2.setText(data.get("lineEdit_2", ""))
self.lineEdit_3.setText(data.get("lineEdit_3", ""))
self.lineEdit_4.setText(data.get("lineEdit_4", ""))
self.lineEdit_5.setText(data.get("lineEdit_5", ""))
self.lineEdit_6.setText(data.get("lineEdit_6", ""))
self.lineEdit_7.setText(data.get("lineEdit_7", ""))
self.lineEdit_8.setText(data.get("lineEdit_8", ""))
def save_data(self):
# Save the current input data to a JSON file
data = {
"lineEdit": self.lineEdit.text(),
"lineEdit_2": self.lineEdit_2.text(),
"lineEdit_3": self.lineEdit_3.text(),
"lineEdit_4": self.lineEdit_4.text(),
"lineEdit_5": self.lineEdit_5.text(),
"lineEdit_6": self.lineEdit_6.text(),
"lineEdit_7": self.lineEdit_7.text(),
"lineEdit_8": self.lineEdit_8.text(),
}
with open('data.json', 'w', encoding='utf-8') as f:
json.dump(data, f)
def process_data(self):
start_date = self.lineEdit.text()
end_date = self.lineEdit_2.text()
ZFIR08_N = self.lineEdit_3.text()
ZFIR08_Y = self.lineEdit_4.text()
ZFIR08_D = self.lineEdit_5.text()
FBL5N_Date = self.lineEdit_6.text()
Company_Code = self.lineEdit_8.text()
folder = self.lineEdit_7.text()
# 定义正则表达式模式
pattern = r'^\w+_付款清单留存及检核\.xlsx$'
# 获取当前目录下的所有文件
files = os.listdir('.')
# 找到符合模式的文件
matching_files = [f for f in files if re.match(pattern, f)]
if matching_files:
# 如果找到符合条件的文件,读取第一个匹配的文件
df_FK = pd.read_excel(matching_files[0], sheet_name="代发")
print(f"成功读取文件: {matching_files[0]}")
# 将“过账日期”列从object类型转换为日期类型
df_FK["过账日期"] = pd.to_datetime(df_FK["过账日期"], errors='coerce')
# 清洗数据,去掉过账日期为空的行
df_FK = df_FK.dropna(subset=["过账日期"])
# 格式化“过账日期”为年月日格式
df_FK["过账日期"] = df_FK["过账日期"].dt.strftime('%Y-%m-%d')
# 筛选需要的字段并过滤“过账日期”在输入的日期范围内的数据
df_FK = df_FK[["公司代码", "凭证编号", "分配", "金额", "客户编码", "过账日期"]]
df_FK = df_FK.query("@start_date <= 过账日期 <= @end_date")
# # 导出txt - 修改为使用folder路径
# df_FK[["凭证编号"]].to_csv("./重复检核结果凭证编号.txt", index=False, sep='\t', encoding='utf-8')
df_FK[["凭证编号"]].to_csv(f"{folder}/重复检核结果凭证编号.txt", index=False, sep='\t', encoding='utf-8')
print("重复检核结果凭证编号已保存为 '重复检核结果凭证编号.TXT'")
# SAP下载
SapGuiAuto_1 = win32com.client.GetObject("SAPGUI")
application_1 = SapGuiAuto_1.GetScriptingEngine
connections_1 = application_1.Children
session = connections_1[0].Children(0)
session.findById("wnd[0]").maximize()
session.findById("wnd[0]/tbar[0]/okcd").text = "ZFIR08"
session.findById("wnd[0]").sendVKey(0)
session.findById("wnd[0]/usr/ctxtP_BUKRS").text = Company_Code
session.findById("wnd[0]/usr/txtS_GJAHR-LOW").text = ZFIR08_N
session.findById("wnd[0]/usr/txtS_MONAT1-LOW").text = ZFIR08_Y
session.findById("wnd[0]/usr/txtS_MONAT1-HIGH").text = ZFIR08_D
session.findById("wnd[0]/usr/txtS_MONAT1-HIGH").setFocus()
session.findById("wnd[0]/usr/txtS_MONAT1-HIGH").caretPosition = 0
session.findById("wnd[0]/usr/btn%_S_BELNR_%_APP_%-VALU_PUSH").press()
session.findById("wnd[1]/tbar[0]/btn[23]").press()
session.findById("wnd[2]/usr/ctxtDY_PATH").text = folder
session.findById("wnd[2]/usr/ctxtDY_FILENAME").text = "重复检核结果凭证编号.TXT"
session.findById("wnd[2]/usr/ctxtDY_FILENAME").caretPosition = 34
session.findById("wnd[2]/tbar[0]/btn[0]").press()
session.findById("wnd[1]/tbar[0]/btn[8]").press()
session.findById("wnd[0]/tbar[1]/btn[8]").press()
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").select()
session.findById("wnd[1]/tbar[0]/btn[0]").press()
session.findById("wnd[1]/usr/ctxtDY_PATH").text = folder
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = f"{Company_Code}ZFIR08.XLSX"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 24
session.findById("wnd[1]/tbar[0]/btn[0]").press()
# 创建Excel应用程序对象
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False # 设置Excel应用为不可见
# 关闭所有打开的Excel工作簿
workbooks = excel.Workbooks
for wb in workbooks:
try:
wb.Close(SaveChanges=False) # 关闭工作簿,不保存更改
except Exception as e:
print(f"关闭工作簿时出错: {e}")
# 退出Excel应用程序
excel.Quit()
session.findById("wnd[0]/tbar[0]/btn[3]").press()
print(f"{Company_Code}ZFIR08已经下载完")
# FBL5N
session.findById("wnd[0]/tbar[0]/btn[15]").press()
SapGuiAuto_1 = win32com.client.GetObject("SAPGUI")
application_1 = SapGuiAuto_1.GetScriptingEngine
connections_1 = application_1.Children
session = connections_1[0].Children(0)
session.findById("wnd[0]").maximize()
session.findById("wnd[0]/tbar[0]/okcd").text = "FBL5N"
session.findById("wnd[0]").sendVKey(0)
session.findById("wnd[0]/tbar[1]/btn[17]").press()
session.findById("wnd[1]/usr/txtENAME-LOW").text = ""
session.findById("wnd[1]/usr/txtENAME-LOW").setFocus()
session.findById("wnd[1]/usr/txtENAME-LOW").caretPosition = 0
session.findById("wnd[1]/tbar[0]/btn[8]").press()
QtCore.QThread.sleep(1) # Sleep for a moment to allow loading
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").setCurrentCell(58, "TEXT")
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").firstVisibleRow = 45
session.findById("wnd[1]/usr/cntlALV_CONTAINER_1/shellcont/shell").selectedRows = "58"
session.findById("wnd[1]/tbar[0]/btn[2]").press()
session.findById("wnd[0]/usr/ctxtDD_BUKRS-LOW").text = Company_Code
session.findById("wnd[0]/usr/ctxtPA_STIDA").text = FBL5N_Date
session.findById("wnd[0]/usr/ctxtPA_STIDA").setFocus()
session.findById("wnd[0]/usr/ctxtPA_STIDA").caretPosition = 10
session.findById("wnd[0]/tbar[1]/btn[8]").press()
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[1]").select()
session.findById("wnd[1]/tbar[0]/btn[0]").press()
session.findById("wnd[1]/usr/ctxtDY_PATH").text = folder
session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = f"{Company_Code}FBL5N.XLSX"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 2
session.findById("wnd[1]/tbar[0]/btn[0]").press()
# 创建Excel应用程序对象
excel = win32com.client.Dispatch("Excel.Application")
excel.Visible = False # 设置Excel应用为不可见
# 关闭所有打开的Excel工作簿
workbooks = excel.Workbooks
for wb in workbooks:
try:
wb.Close(SaveChanges=False) # 关闭工作簿,不保存更改
except Exception as e:
print(f"关闭工作簿时出错: {e}")
# 退出Excel应用程序
excel.Quit()
session.findById("wnd[0]/tbar[0]/btn[3]").press()
print(f"{Company_Code}FBL5N已经下载完")
session.findById("wnd[0]/tbar[0]/btn[15]").press()
# 验证文本和行数第一个条件正确 ZFIR08
df_FK["辅助数据"] = df_FK["公司代码"].astype(str) + "_" + df_FK["金额"].astype(str) + "_" + df_FK["客户编码"].astype(str)
# # 新增“重复检核结果”列,标识辅助数据中的重复项
df_FK["辅助数据重复检核结果"] = df_FK.duplicated(subset=["辅助数据"], keep=False).replace({True: '重复', False: '唯一'})
# # 验证文凭证编号第二个条件正确
df_ZFIR08 = pd.read_excel(f"./{Company_Code}FIR08.XLSX")
df_ZFIR08.rename(columns={'凭证编码': '凭证编号'}, inplace=True)
df_ZFIR08['行项目文本'] = df_ZFIR08.groupby('凭证编号')['行项目文本'].ffill()
df_ZFIR08['凭证编号'] = df_ZFIR08['凭证编号'].astype(str).str.lstrip('0')
df_FK['凭证编号'] = df_FK['凭证编号'].astype(str).str.lstrip('0')
df_ZFIR08_凭证编号14 = df_ZFIR08[df_ZFIR08['凭证编号'].str.startswith('14')]
df_ZFIR08 = df_ZFIR08[df_ZFIR08["借贷标识"] == "S"]
df_ZFIR08 = df_ZFIR08.drop_duplicates(subset=["凭证编号"], keep='last')
# 6. 合并两个 DataFrame(14 开头的凭证 + 借贷标识为 "S" 的去重数据)
df_combined = pd.concat([df_ZFIR08_凭证编号14, df_ZFIR08], ignore_index=True)
df_combined = pd.merge(df_FK, df_combined[["凭证编号", "行项目文本"]], on='凭证编号', how='left')
df_combined["重复检核结果"] = df_combined.duplicated(subset=["行项目文本"], keep=False).replace({True: '重复', False: '唯一'})
# FBL5N
df_FBL5N = pd.read_excel(f"./{Company_Code}FBL5N.XLSX")
df_FBL5N.rename(columns={'客户': '客户编码'}, inplace=True)
df_FBL5N = df_FBL5N.drop_duplicates(subset=["客户编码"], keep='last')
df_FBL5N['客户编码'] = df_FBL5N['客户编码'].astype(str)
# 合并数据
df_FK_5N = pd.merge(df_combined,df_FBL5N[["客户编码", "文本"]], on='客户编码', how='left')
# 更推荐使用NumPy的where函数代替apply,效率更高:
df_FK_5N['FBL5N数据检验'] = np.where(df_FK_5N['行项目文本'].astype(str) == df_FK_5N['文本'].astype(str), '重复', '唯一')
with pd.ExcelWriter(os.path.join(folder, "付款排查检核记录.xlsx")) as writer:
df_greater_than_10000 = df_FK[df_FK['金额'] > 10000]
df_greater_than_10000.to_excel(writer, index=False, sheet_name='大于10000')
df_combined.to_excel(writer, index=False,sheet_name='ZFIR08付款排查表重复和唯一结果')
df_FK_5N.to_excel(writer, index=False,sheet_name='FBL5N重复检核结果')
# 保存全部检核结果
print("数据已保存为 '付款排查重复检核结果.xlsx'")
# 显示完成提示
self.show_message("处理完成", "数据处理已完成,结果已保存。")
def show_message(self, title, message):
msg_box = QMessageBox()
msg_box.setWindowTitle(title)
msg_box.setText(message)
msg_box.exec()
if __name__ == "__main__":
import sys
app = QtWidgets.QApplication(sys.argv)
MainWindow = QtWidgets.QMainWindow()
ui = Ui_MainWindow()
ui.setupUi(MainWindow)
MainWindow.show()
sys.exit(app.exec())
最新发布