import sys
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from PyQt5.QtWidgets import (QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, QPushButton,
QTableWidget, QTableWidgetItem, QFileDialog, QTabWidget, QLabel,
QLineEdit, QComboBox, QMessageBox, QHeaderView, QDialog,
QGroupBox, QTextEdit, QScrollArea, QSplitter, QFormLayout)
from PyQt5.QtCore import Qt
from sklearn.linear_model import LinearRegression
from datetime import datetime
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
import re
import os
class MaterialSystem(QMainWindow):
def init(self):
super().init()
self.setWindowTitle(“辅料管理系统”)
self.setGeometry(100, 100, 1200, 800)
# 初始化变量 self.file_path = None self.df = None self.original_df = None self.production_data = {} self.historical_data = [] # 存储历史数据用于预测模型 # 创建主部件和布局 main_widget = QWidget() main_layout = QVBoxLayout() # 顶部按钮区域 top_layout = QHBoxLayout() self.load_button = QPushButton("加载Excel文件") self.load_button.clicked.connect(self.load_file) self.save_button = QPushButton("保存修改") self.save_button.clicked.connect(self.save_changes) self.save_button.setEnabled(False) self.predict_button = QPushButton("预测功能") self.predict_button.clicked.connect(self.show_prediction_dialog) self.predict_button.setEnabled(False) top_layout.addWidget(self.load_button) top_layout.addWidget(self.save_button) top_layout.addWidget(self.predict_button) top_layout.addStretch() # 标签显示文件路径 self.file_label = QLabel("未加载文件") top_layout.addWidget(self.file_label) main_layout.addLayout(top_layout) # 创建标签页 self.tabs = QTabWidget() self.table_tab = QWidget() self.production_tab = QWidget() self.history_tab = QWidget() # 新增历史数据标签页 # 表格标签页布局 table_layout = QVBoxLayout(self.table_tab) self.table_widget = QTableWidget() self.table_widget.setEditTriggers(QTableWidget.DoubleClicked) table_layout.addWidget(self.table_widget) # 产量数据标签页布局 production_layout = QVBoxLayout(self.production_tab) production_form_layout = QHBoxLayout() production_form_layout.addWidget(QLabel("阴极锌产量:")) self.zn_input = QLineEdit() production_form_layout.addWidget(self.zn_input) production_form_layout.addWidget(QLabel("电锌产量:")) self.dx_input = QLineEdit() production_form_layout.addWidget(self.dx_input) production_form_layout.addWidget(QLabel("渣处理量:")) self.zl_input = QLineEdit() production_form_layout.addWidget(self.zl_input) production_form_layout.addWidget(QLabel("硫酸产量:")) self.ls_input = QLineEdit() production_form_layout.addWidget(self.ls_input) production_form_layout.addWidget(QLabel("小窑焙砂处理量:")) self.by_input = QLineEdit() production_form_layout.addWidget(self.by_input) self.save_production_button = QPushButton("保存产量数据") self.save_production_button.clicked.connect(self.save_production_data) production_form_layout.addWidget(self.save_production_button) production_layout.addLayout(production_form_layout) production_layout.addStretch() # 历史数据标签页布局 history_layout = QVBoxLayout(self.history_tab) # 历史数据导入区域 history_group = QGroupBox("历史数据管理") history_group_layout = QVBoxLayout() self.import_button = QPushButton("导入历史数据") self.import_button.clicked.connect(self.import_historical_data) history_group_layout.addWidget(self.import_button) self.history_list = QTextEdit() self.history_list.setReadOnly(True) history_group_layout.addWidget(self.history_list) history_group.setLayout(history_group_layout) history_layout.addWidget(history_group) # 公式编辑区域 formula_group = QGroupBox("计算公式管理") formula_layout = QVBoxLayout() self.formula_edit = QTextEdit() self.formula_edit.setPlaceholderText("在此编辑计算公式...\n示例: 实际单耗 = 本月消耗 / 本月产量 * 1000") formula_layout.addWidget(self.formula_edit) self.save_formula_button = QPushButton("保存公式") self.save_formula_button.clicked.connect(self.save_formulas) formula_layout.addWidget(self.save_formula_button) formula_group.setLayout(formula_layout) history_layout.addWidget(formula_group) # 添加标签页 self.tabs.addTab(self.table_tab, "辅料数据") self.tabs.addTab(self.production_tab, "产量数据") self.tabs.addTab(self.history_tab, "历史数据与公式") # 新增标签页 main_layout.addWidget(self.tabs) main_widget.setLayout(main_layout) self.setCentralWidget(main_widget) # 初始化产量数据 self.init_production_data() # 初始化公式 self.formulas = { "实际单耗": "本月消耗 / 本月产量 * 1000", "定额单耗": "上月实际单耗 * 0.98", "定额消耗": "定额单耗 * 下月计划产量 / 1000" } self.update_formula_display() def update_formula_display(self): """更新公式显示""" text = "" for name, formula in self.formulas.items(): text += f"{name}: {formula}\n" self.formula_edit.setText(text) def save_formulas(self): """保存公式""" text = self.formula_edit.toPlainText() lines = text.split('\n') new_formulas = {} for line in lines: if ':' in line: parts = line.split(':', 1) name = parts[0].strip() formula = parts[1].strip() if name and formula: new_formulas[name] = formula if new_formulas: self.formulas = new_formulas QMessageBox.information(self, "成功", "公式已保存！") else: QMessageBox.warning(self, "警告", "未检测到有效的公式！") def import_historical_data(self): """导入历史数据""" file_paths, _ = QFileDialog.getOpenFileNames( self, "选择历史数据文件", "", "Excel Files (*.xlsx *.xls)" ) if not file_paths: return success_count = 0 for file_path in file_paths: try: # 读取历史数据 wb = openpyxl.load_workbook(file_path) sheet = wb.active # 提取数据 data = [] for row in sheet.iter_rows(values_only=True): data.append(row) # 提取产量数据 production_info = {} for row in data: if any(keyword in str(row[0]) for keyword in ["阴极锌", "电锌", "渣处理", "硫酸", "焙砂"]): parts = str(row[0]).split('；') for part in parts: if ':' in part: key_value = part.split(':', 1) key = key_value[0].strip() value = key_value[1].strip() # 提取数值部分 numbers = re.findall(r"[-+]?\d*\.\d+|\d+", value) if numbers: production_info[key] = float(numbers[0]) # 提取辅料消耗数据 material_consumption = {} for i, row in enumerate(data): if i >= 4: # 跳过标题行 if len(row) > 1 and row[1] and str(row[1]).strip() != "": material_name = str(row[1]).strip() # 确保有足够的列 if len(row) > 12 and row[12] is not None: try: consumption = float(row[12]) material_consumption[material_name] = consumption except (ValueError, TypeError): pass # 添加到历史数据 if production_info and material_consumption: history_entry = { "file": os.path.basename(file_path), "date": datetime.now().strftime("%Y-%m"), "production": production_info, "materials": material_consumption } self.historical_data.append(history_entry) success_count += 1 except Exception as e: QMessageBox.warning(self, "导入错误", f"文件 {os.path.basename(file_path)} 导入失败: {str(e)}") # 更新历史数据显示 self.update_history_display() QMessageBox.information(self, "导入完成", f"成功导入 {success_count}/{len(file_paths)} 个历史数据文件！") def update_history_display(self): """更新历史数据显示""" text = "已导入的历史数据:\n\n" for i, entry in enumerate(self.historical_data, 1): text += f"{i}. {entry['file']} ({entry['date']})\n" text += f" 产量: {entry['production']}\n" text += f" 辅料数量: {len(entry['materials'])}\n\n" self.history_list.setText(text) def init_production_data(self): """初始化产量数据""" self.production_data = { "阴极锌": 9293.226, "电锌": 8635.059, "渣处理量": 6514.5, "硫酸产量": 13143.5, "小窑焙砂处理量": 1095.9 } self.zn_input.setText(str(self.production_data["阴极锌"])) self.dx_input.setText(str(self.production_data["电锌"])) self.zl_input.setText(str(self.production_data["渣处理量"])) self.ls_input.setText(str(self.production_data["硫酸产量"])) self.by_input.setText(str(self.production_data["小窑焙砂处理量"])) def load_file(self): """加载Excel文件""" file_path, _ = QFileDialog.getOpenFileName( self, "打开Excel文件", "", "Excel Files (*.xlsx *.xls)" ) if file_path: self.file_path = file_path self.file_label.setText(f"已加载: {file_path}") try: # 使用openpyxl加载工作簿 wb = openpyxl.load_workbook(file_path) sheet = wb.active # 将工作表转换为DataFrame data = sheet.values headers = next(data) self.df = pd.DataFrame(data, columns=headers) self.original_df = self.df.copy() # 显示数据 self.display_table() # 启用按钮 self.save_button.setEnabled(True) self.predict_button.setEnabled(True) # 提取产量数据 self.extract_production_data() except Exception as e: QMessageBox.critical(self, "错误", f"加载文件失败: {str(e)}") # 重置状态 self.file_path = None self.df = None self.save_button.setEnabled(False) self.predict_button.setEnabled(False) self.file_label.setText("未加载文件") def extract_production_data(self): """从表格中提取产量数据 - 改进版本""" # 查找包含产量数据的行 for _, row in self.df.iterrows(): row_content = str(row[0]) # 检查是否包含产量关键词 if any(keyword in row_content for keyword in ["阴极锌", "电锌", "渣处理", "硫酸", "焙砂"]): # 使用更稳健的解析方法 parts = row_content.split('；') for part in parts: # 使用正则表达式提取数值 numbers = re.findall(r"[-+]?\d*\.\d+|\d+", part) if numbers: value = float(numbers[0]) if "阴极锌" in part: self.production_data["阴极锌"] = value elif "电锌" in part: self.production_data["电锌"] = value elif "渣处理" in part: self.production_data["渣处理量"] = value elif "硫酸" in part: self.production_data["硫酸产量"] = value elif "焙砂" in part: self.production_data["小窑焙砂处理量"] = value # 更新输入框 self.zn_input.setText(str(self.production_data["阴极锌"])) self.dx_input.setText(str(self.production_data["电锌"])) self.zl_input.setText(str(self.production_data["渣处理量"])) self.ls_input.setText(str(self.production_data["硫酸产量"])) self.by_input.setText(str(self.production_data["小窑焙砂处理量"])) break def save_production_data(self): """保存产量数据""" try: self.production_data["阴极锌"] = float(self.zn_input.text()) self.production_data["电锌"] = float(self.dx_input.text()) self.production_data["渣处理量"] = float(self.zl_input.text()) self.production_data["硫酸产量"] = float(self.ls_input.text()) self.production_data["小窑焙砂处理量"] = float(self.by_input.text()) QMessageBox.information(self, "成功", "产量数据已保存！") except ValueError: QMessageBox.warning(self, "错误", "请输入有效的数字！") def display_table(self): """在表格中显示数据 - 改进版本""" if self.df is not None: self.table_widget.clear() # 设置行列数 n_rows, n_cols = self.df.shape self.table_widget.setRowCount(n_rows) self.table_widget.setColumnCount(n_cols) # 设置表头 headers = self.df.columns.tolist() self.table_widget.setHorizontalHeaderLabels(headers) # 填充数据 for i in range(n_rows): for j in range(n_cols): # 安全获取单元格值 cell_value = self.df.iloc[i, j] if pd.isna(cell_value): cell_value = "" item = QTableWidgetItem(str(cell_value)) # 设置不可编辑的单元格 - 改进索引计算 # 仅当列数足够时才应用不可编辑 if j < len(headers): col_name = headers[j] # 对特定列设置为不可编辑 if any(keyword in col_name for keyword in ["单耗", "定额", "消耗量", "结果"]): item.setFlags(item.flags() & ~Qt.ItemIsEditable) item.setBackground(Qt.lightGray) self.table_widget.setItem(i, j, item) # 调整列宽 self.table_widget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) def save_changes(self): """保存修改到Excel文件 - 改进版本""" if self.df is None or self.file_path is None: return try: # 从表格中获取修改后的数据 for i in range(self.table_widget.rowCount()): for j in range(self.table_widget.columnCount()): if self.table_widget.item(i, j) is not None: new_value = self.table_widget.item(i, j).text() # 仅当列存在时才更新 if j < self.df.shape[1]: self.df.iloc[i, j] = new_value # 使用openpyxl保存，保留公式 wb = openpyxl.load_workbook(self.file_path) sheet = wb.active # 清除现有内容 for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column): for cell in row: cell.value = None # 写入新数据 for r_idx, row in enumerate(dataframe_to_rows(self.df, index=False, header=True), 1): for c_idx, value in enumerate(row, 1): if c_idx <= sheet.max_column: # 确保列索引有效 sheet.cell(row=r_idx, column=c_idx, value=value) # 保存工作簿 wb.save(self.file_path) QMessageBox.information(self, "成功", "修改已保存到Excel文件！") except Exception as e: QMessageBox.critical(self, "错误", f"保存文件失败: {str(e)}") def show_prediction_dialog(self): """显示预测对话框""" if self.df is None: return dialog = PredictionDialog(self.df, self.production_data, self.historical_data, self.formulas, self) dialog.exec_()
class PredictionDialog(QDialog):
def init(self, df, production_data, historical_data, formulas, parent=None):
super().init(parent)
self.setWindowTitle(“辅料消耗预测”)
self.setGeometry(200, 200, 1000, 800)
self.df = df self.production_data = production_data self.historical_data = historical_data self.formulas = formulas self.material_models = {} # 存储每个辅料的预测模型 layout = QVBoxLayout() # 预测设置区域 settings_layout = QHBoxLayout() settings_layout.addWidget(QLabel("预测月份:")) self.month_combo = QComboBox() months = [f"{i}月" for i in range(1, 13)] current_month = datetime.now().month self.month_combo.addItems(months) self.month_combo.setCurrentIndex(current_month % 12) settings_layout.addWidget(self.month_combo) settings_layout.addWidget(QLabel("预测方法:")) self.method_combo = QComboBox() methods = ["基于产量比例", "线性回归", "移动平均"] if historical_data: methods.insert(0, "历史数据模型") self.method_combo.addItems(methods) settings_layout.addWidget(self.method_combo) self.predict_button = QPushButton("执行预测") self.predict_button.clicked.connect(self.run_prediction) settings_layout.addWidget(self.predict_button) layout.addLayout(settings_layout) # 产量输入区域 production_layout = QFormLayout() production_layout.addRow(QLabel("阴极锌产量:"), self.zn_pred_input := QLineEdit(str(production_data["阴极锌"]))) production_layout.addRow(QLabel("电锌产量:"), self.dx_pred_input := QLineEdit(str(production_data["电锌"]))) production_layout.addRow(QLabel("渣处理量:"), self.zl_pred_input := QLineEdit(str(production_data["渣处理量"]))) production_layout.addRow(QLabel("硫酸产量:"), self.ls_pred_input := QLineEdit(str(production_data["硫酸产量"]))) production_layout.addRow(QLabel("小窑焙砂处理量:"), self.by_pred_input := QLineEdit(str(production_data["小窑焙砂处理量"]))) layout.addLayout(production_layout) # 创建分割器用于结果和公式显示 splitter = QSplitter(Qt.Vertical) # 预测结果表格 result_widget = QWidget() result_layout = QVBoxLayout(result_widget) result_layout.addWidget(QLabel("预测结果:")) self.result_table = QTableWidget() self.result_table.setColumnCount(5) self.result_table.setHorizontalHeaderLabels(["车间", "辅料名称", "单位", "预测消耗量", "预测方法"]) self.result_table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) result_layout.addWidget(self.result_table) # 公式显示区域 formula_widget = QWidget() formula_layout = QVBoxLayout(formula_widget) formula_layout.addWidget(QLabel("当前使用的公式:")) self.formula_display = QTextEdit() self.formula_display.setReadOnly(True) self.update_formula_display() formula_layout.addWidget(self.formula_display) splitter.addWidget(result_widget) splitter.addWidget(formula_widget) splitter.setSizes([500, 200]) layout.addWidget(splitter) # 图表按钮 self.chart_button = QPushButton("生成预测图表") self.chart_button.clicked.connect(self.generate_chart) layout.addWidget(self.chart_button) self.setLayout(layout) # 初始化时执行预测 self.run_prediction() def update_formula_display(self): """更新公式显示""" text = "" for name, formula in self.formulas.items(): text += f"{name}: {formula}\n" self.formula_display.setText(text) def run_prediction(self): """执行预测计算 - 增强版本""" # 清空表格 self.result_table.setRowCount(0) # 获取预测设置 prediction_method = self.method_combo.currentText() # 获取预测产量 try: zn_production = float(self.zn_pred_input.text()) dx_production = float(self.dx_pred_input.text()) zl_production = float(self.zl_pred_input.text()) ls_production = float(self.ls_pred_input.text()) by_production = float(self.by_pred_input.text()) except ValueError: QMessageBox.warning(self, "错误", "请输入有效的产量数据！") return # 筛选有效的辅料数据行 valid_rows = [] for idx, row in self.df.iterrows(): if idx >= 4: # 跳过标题行 workshop = row[0] material = row[1] unit = row[2] if len(row) > 2 else "" # 检查是否有消耗量数据 if len(row) > 12 and pd.notna(row[12]) and str(row[12]).strip() != "": try: consumption = float(row[12]) valid_rows.append((workshop, material, unit, consumption)) except (ValueError, TypeError): continue # 训练模型（如果需要） if prediction_method == "历史数据模型" and self.historical_data: self.train_models() # 执行预测 predictions = [] for workshop, material, unit, consumption in valid_rows: method_used = prediction_method predicted = None # 根据预测方法选择预测逻辑 if prediction_method == "历史数据模型" and material in self.material_models: # 使用历史数据训练的模型 try: # 根据车间确定使用哪个产量数据 if "浸出" in workshop: input_data = [[zn_production]] elif "熔铸" in workshop: input_data = [[dx_production]] elif "渣处理" in workshop: input_data = [[zl_production]] elif "硫酸" in workshop: input_data = [[ls_production]] elif "焙砂" in workshop: input_data = [[by_production]] else: input_data = [[(zn_production + dx_production + zl_production) / 3]] predicted = self.material_models[material].predict(input_data)[0] except: predicted = consumption * 1.05 # 模型失败时使用默认方法 elif prediction_method == "线性回归": # 使用线性回归模型（简化） predicted = consumption * 1.05 elif prediction_method == "移动平均": # 移动平均法 predicted = consumption * 1.03 else: # 基于产量比例 # 根据产量变化调整 if "浸出" in workshop: ratio = zn_production / self.production_data["阴极锌"] predicted = consumption * ratio elif "熔铸" in workshop: ratio = dx_production / self.production_data["电锌"] predicted = consumption * ratio elif "渣处理" in workshop: ratio = zl_production / self.production_data["渣处理量"] predicted = consumption * ratio elif "硫酸" in workshop: ratio = ls_production / self.production_data["硫酸产量"] predicted = consumption * ratio elif "焙砂" in workshop: ratio = by_production / self.production_data["小窑焙砂处理量"] predicted = consumption * ratio else: predicted = consumption * 1.02 # 默认增长2% method_used = "默认增长" # 应用自定义公式（如果有） if "定额消耗" in self.formulas: try: # 在实际应用中应使用更安全的公式解析 predicted = eval(self.formulas["定额消耗"], { "定额单耗": consumption / (self.production_data["阴极锌"] / 1000), "下月计划产量": zn_production }) except: pass if predicted is not None: predictions.append((workshop, material, unit, predicted, method_used)) # 显示预测结果 self.result_table.setRowCount(len(predictions)) for i, (workshop, material, unit, predicted, method) in enumerate(predictions): self.result_table.setItem(i, 0, QTableWidgetItem(str(workshop))) self.result_table.setItem(i, 1, QTableWidgetItem(material)) self.result_table.setItem(i, 2, QTableWidgetItem(unit)) self.result_table.setItem(i, 3, QTableWidgetItem(f"{predicted:.2f}")) self.result_table.setItem(i, 4, QTableWidgetItem(method)) def train_models(self): """使用历史数据训练预测模型""" if not self.historical_data: return self.material_models = {} # 收集每个辅料的历史数据 material_data = {} for entry in self.historical_data: for material, consumption in entry["materials"].items(): if material not in material_data: material_data[material] = [] # 根据辅料类型确定使用哪个产量数据 production_key = self.detect_production_key(material) if production_key in entry["production"]: production_value = entry["production"][production_key] material_data[material].append((production_value, consumption)) # 为每个辅料训练模型 for material, data in material_data.items(): if len(data) > 2: # 至少需要3个数据点 try: X = np.array([d[0] for d in data]).reshape(-1, 1) y = np.array([d[1] for d in data]) model = LinearRegression() model.fit(X, y) self.material_models[material] = model except: # 模型训练失败，跳过该辅料 pass def detect_production_key(self, material): """根据辅料名称确定关联的产量类型""" material_lower = material.lower() if "浸出" in material_lower or "阴极锌" in material_lower: return "阴极锌" elif "熔铸" in material_lower or "电锌" in material_lower: return "电锌" elif "渣处理" in material_lower or "渣量" in material_lower: return "渣处理量" elif "硫酸" in material_lower: return "硫酸产量" elif "焙砂" in material_lower: return "小窑焙砂处理量" else: return "阴极锌" # 默认 def generate_chart(self): """生成预测图表 - 增强版本""" if self.result_table.rowCount() == 0: QMessageBox.warning(self, "警告", "没有可用的预测数据！") return # 获取预测结果 data = [] for i in range(self.result_table.rowCount()): workshop = self.result_table.item(i, 0).text() material = self.result_table.item(i, 1).text() consumption = float(self.result_table.item(i, 3).text()) method = self.result_table.item(i, 4).text() data.append((f"{workshop}-{material}", consumption, method)) # 按消耗量排序 data = sorted(data, key=lambda x: x[1], reverse=True)[:15] # 创建图表 plt.figure(figsize=(12, 8)) names = [item[0] for item in data] values = [item[1] for item in data] methods = [item[2] for item in data] # 为不同方法分配颜色 color_map = { "历史数据模型": "green", "线性回归": "blue", "移动平均": "orange", "基于产量比例": "purple", "默认增长": "red" } colors = [color_map.get(method, "gray") for method in methods] plt.barh(names, values, color=colors) plt.xlabel('预测消耗量') plt.title('辅料消耗预测 (前15项)') # 添加图例 from matplotlib.patches import Patch legend_elements = [Patch(facecolor=color, label=method) for method, color in color_map.items()] plt.legend(handles=legend_elements, loc='lower right') plt.tight_layout() plt.show()
if name == “main”:
app = QApplication(sys.argv)
window = MaterialSystem()
window.show()
sys.exit(app.exec_())帮我检查该代码中有什么问题
最新发布
本文介绍了当用户更改输入面板状态时，操作系统如何通过发送WM_SETTINGCHANGE消息通知活跃应用程序。此外，还讨论了输入语言更改时，输入法管理器(IMM)如何发送WM_INPUTLANGCHANGE消息到应用程序的活动窗口。
扫一扫
6169
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
