import pandas as pd
import os
import time
from io import StringIO
import numpy as np
import openpyxl
import re
import warnings
from openpyxl.utils import get_column_letter
start = time.perf_counter() # 高精度计时器
def aggregate_loss_usage(df, loss_category, group_column, value_column, output_file):
"""
根据给定的LOSS用分类,筛选出对应的行,并按照指定的分厂列进行分组,
对指定的数值列进行累加,最后保存结果到Excel文件。
参数:
df (DataFrame): 包含数据的DataFrame
loss_category (str): 需要筛选的LOSS用分类名称,如“Glass”
group_column (str): 用于分组的列名,如“分厂”
value_column (str): 需要累加的数值列名,如“用量产能折算”
output_file (str): 输出Excel文件的路径和名称
"""
# 确保输入的参数都是字符串
if not isinstance(loss_category, str) or \
not isinstance(group_column, str) or \
not isinstance(value_column, str):
raise TypeError("loss_category、group_column和value_column必须是字符串类型。")
try:
# 筛选出指定LOSS分类下的数据
filtered_df = df[df['LOSS用分类'] == loss_category].copy()
# 检查是否有有效的数据记录
if len(filtered_df) == 0:
print(f"没有找到'{loss_category}'类别下的任何数据。")
return None
# 按照分厂进行分组,并计算数值列的总和
grouped = filtered_df.groupby(group_column)[value_column].sum().reset_index()
# 将结果保存到Excel文件
grouped.to_excel(output_file, index=False)
print(f"已将'{loss_category}'类别下的数据按分厂累加,结果保存至:{output_file}")
except KeyError as e:
print(f"错误:DataFrame中缺少列'{e.args[0]}'。")
except Exception as e:
print(f"发生了意外错误:{str(e)}")
return grouped
# 按照 'LOSS用分类' 和 '分厂' 分组,并对数值列进行累加
def aggregate_loss_categories(df, output_file, factory_divisor_map):
try:
# 验证必要列是否存在于 DataFrame 中
required_columns = ['LOSS用分类', '分厂',
'实际金额', '标准金额',
'固定实际', '固定标准']
if not all(col in df.columns for col in required_columns):
raise ValueError("DataFrame 中缺少必要的列")
# 按照 'LOSS用分类' 和 '分厂' 分组,并对数值列进行累加
grouped = (df.groupby(['分厂', 'LOSS用分类' ]).agg
({
'固定实际': 'sum',
'固定标准': 'sum',
'实际金额': 'sum',
'标准金额': 'sum'
}).reset_index())
# 将结果保存到 Excel 文件
grouped.to_excel(output_file, index=False)
print(f"累加的结果保存至:{output_file}")
except Exception as e:
print(f"发生了错误:{str(e)}")
return grouped
#按分厂分离数据并保存为独立Excel文件
def process_and_export(data, output_dir="H:\\yz\\BOM计算"):
"""
按分厂分离数据并保存为独立Excel文件
参数:
data : DataFrame - 包含"分厂"列的处理后数据
output_dir : str - 输出目录(默认创建"分厂数据"文件夹)
"""
# 创建输出目录
os.makedirs(output_dir, exist_ok=True)
try:
# 按分厂分组
grouped = data.groupby("分厂")
# 遍历每个分厂
for factory_name, factory_data in grouped:
# 生成安全文件名(替换特殊字符)
safe_name = factory_name.replace("/", "_").replace("\\", "_")
file_path = os.path.join(output_dir, f"{safe_name}_生产数据.xlsx")
# 保存Excel文件
factory_data.to_excel(file_path, index=False, engine='openpyxl')
print(f"成功生成:{file_path}")
return True
except KeyError:
print("错误:数据中未找到'分厂'列")
return False
except Exception as e:
print(f"保存失败:{str(e)}")
return False
#筛选并剔除Cell指定编号
def filter_and_export(data, exclude_components, output_file):
# 检查必要列是否存在
required_columns = ["LOSS用分类", "组件"]
if not set(required_columns).issubset(data.columns):
missing = set(required_columns) - set(data.columns)
raise KeyError(f"数据缺少必要列:{missing}")
# 步骤1:筛选分类(Array和Cell)
filtered = data[data["LOSS用分类"].isin(["Array"])]
# 步骤2:剔除指定组件号
filtered = filtered[~filtered["组件"].isin(exclude_components)]
# 步骤3:导出Excel
filtered.to_excel(output_file, index=False, engine='openpyxl')
print(f"数据已导出至:{os.path.abspath(output_file)}")
try:
series = pd.to_numeric(filtered["用量产能折算"], errors='coerce') # 无法转换的值变为 NaN
total = series.sum(skipna=True) # 自动跳过 NaN
print(f" '{"用量产能折算"}' 总和: {total:.2f}")
except KeyError:
print(f"错误: 列 '{"用量产能折算"}' 不存在")
return total, filtered
#按照顺序输出Array+CF+Cell表格
def preprocess_Array(data1, data2):
# 定义分类顺序
categories_order = [
'PR胶', 'Cu靶材', 'ITO靶材', '钼靶', 'MTD靶材',
'Cl2', 'He', 'NF3', 'NH3', 'H2', 'SiH4',
'Cu剥离液', 'Cu刻蚀液', 'ITO刻蚀液', '补充液',
'清洗液', '稀释液', '显影液'
]
# 步骤1:获取除数
try:
divisor = data2.iloc[0, 1] # 取第二列第一行
except IndexError:
raise ValueError("数据二格式错误:至少需要两列且第一行有数据")
# 步骤2:筛选有效分类
filtered = data1[data1['LOSS用分类'].isin(categories_order)].copy()
# 步骤3:按指定顺序排序
filtered['排序键'] = filtered['LOSS用分类'].map(lambda x: categories_order.index(x))
filtered = filtered.sort_values('排序键').drop(columns='排序键')
# 步骤4:处理数值列(后四列)
if len(filtered.columns) < 4:
raise ValueError("数据至少需要四列数值数据")
# 获取后四列列名
numeric_cols = filtered.columns[-4:]
filtered[numeric_cols] = filtered[numeric_cols] / divisor
return filtered
def preprocess_CF(data1, data2):
# 定义分类顺序
categories_order = [
'Blue胶',
'BM胶',
'GREEN胶',
'OC胶',
'PS胶',
'Red胶',
'ITO靶材',
'显影液',
'清洗液',
'稀释液'
]
# 步骤1:获取除数
try:
divisor = data2.iloc[1, 1] # 取第二列第一行
except IndexError:
raise ValueError("数据二格式错误:至少需要两列且第一行有数据")
# 步骤2:筛选有效分类
filtered = data1[data1['LOSS用分类'].isin(categories_order)].copy()
# 步骤3:按指定顺序排序
filtered['排序键'] = filtered['LOSS用分类'].map(lambda x: categories_order.index(x))
filtered = filtered.sort_values('排序键').drop(columns='排序键')
# 步骤4:处理数值列(后四列)
if len(filtered.columns) < 4:
raise ValueError("数据至少需要四列数值数据")
# 获取后四列列名
numeric_cols = filtered.columns[-4:]
filtered[numeric_cols] = filtered[numeric_cols] / divisor
return filtered
def preprocess_Cell(data1, data2):
# 定义分类顺序
categories_order = \
[
'液晶',
'PI液',
'封框胶',
'小球'
]
# 步骤1:获取除数
try:
divisor = data2 # 取第二列第一行
except IndexError:
raise ValueError("数据二格式错误:至少需要两列且第一行有数据")
# 步骤2:筛选有效分类
filtered = data1[data1['LOSS用分类'].isin(categories_order)].copy()
# 步骤3:按指定顺序排序
filtered['排序键'] = filtered['LOSS用分类'].map(lambda x: categories_order.index(x))
filtered = filtered.sort_values('排序键').drop(columns='排序键', axis=1)
# 步骤4:处理数值列(后四列)
if len(filtered.columns) < 4:
raise ValueError("数据至少需要四列数值数据")
# 获取后四列列名
numeric_cols = filtered.columns[-4:]
filtered[numeric_cols] = filtered[numeric_cols] / divisor
return filtered
#读取 Excel 文件,去除第二列末尾的“-P”
def remove_p_suffix(s):
"""
去除字符串末尾的“-P”
@param s: 输入字符串
@return: 处理后的字符串
"""
try:
if isinstance(s, str) and len(s) >= 2 and s.endswith("-P"):
return s[:-2]
else:
return s
except Exception as e:
print(f"处理出错:{e}")
return s# #
def process_excel_file(file_path):
"""
读取 Excel 文件,去除第二列末尾的“-P”
@param file_path: Excel 文件路径(支持 .xlsx 和 .xls)
@return: 处理后的 DataFrame
"""
try:
# 读取 Excel 文件
df = file_path
# 检查是否有数据
if df.empty:
print("Excel 文件为空。")
return None
# 获取第一列的列名(假设是 'Column1')
first_column_name = df.columns[1]
# 对每个单元格应用 remove_p_suffix 函数
df[first_column_name] = df[first_column_name].apply(remove_p_suffix)
return df
except Exception as e:
print(f"读取或处理 Excel 文件出错:{e}")
return None
#建立汇总BOM报表
def process_bom_data(bom_file_path, price_file_path, output_file_path):
"""
处理BOM数据并计算相关金额。
:param bom_file_path: BOM表文件路径
:param price_file_path: 固定价格表文件路径
:param output_file_path: 输出文件路径
:return: 处理后的DataFrame
"""
# 读取Excel文件
df_old = pd.read_excel(bom_file_path)
df_new = pd.read_excel(price_file_path , sheet_name='固定价格')
df_old = process_excel_file(df_old)
df2 = pd.read_excel(price_file_path , sheet_name='对应编号')
# 重命名df2的列(除了产出品代码),避免合并后列名冲突
df2_columns_rename = {col: f'{col}' for col in df2.columns if col != '产出品代码'}
df2_renamed = df2.rename(columns=df2_columns_rename)
df_old = (pd.merge
(
df_old,
df2_renamed,
on='产出品代码',
how='left'
))
# 创建价格字典(组件:均价)
price_mapping = df_new.set_index('组件')['均价'].to_dict()
# 在最后一列创建均价列
df_old.insert(
len(df_old.columns),
'均价',
df_old['组件'].map(price_mapping).fillna(0)
)
# 新增1:固定价实际消耗量金额
df_old.insert(
len(df_old.columns) - 1, # 倒数第二列位置
'固定实际',
df_old['实际消耗数量'] * df_old['均价']
)
# 新增2:固定价标准消耗量金额
df_old.insert(
len(df_old.columns) - 1,
'固定标准',
df_old['标准消耗数量'] * df_old['均价']
)
# 新增3:实际消耗金额
df_old.insert(
len(df_old.columns) - 1,
'实际金额',
df_old['实际消耗数量'] * df_old['月度投入均价']
)
# 新增4:标准消耗金额
df_old.insert(
len(df_old.columns) - 1,
'标准金额',
df_old['标准消耗数量'] * df_old['月度投入均价']
)
# 新增5:用量产能折算
df_old.insert(
len(df_old.columns) - 1,
'用量产能折算',
df_old['标准消耗数量'] / (df_old['标准单耗量-折算前'] * df_old['切片'])
)
# 保存结果到新文件
df_old.to_excel(output_file_path, index=False)
# 返回处理后的DataFrame
return pd.DataFrame(df_old)
#按照产出品代码累加
def process_production_data(data, output_file_path):
#确保输入是DataFrame格式
if not isinstance(data, pd.DataFrame):
data = pd.DataFrame(data)
grouped = {}
# 正确遍历DataFrame行数据
for index, row in data.iterrows(): # 使用iterrows遍历
code = row['产出品代码'] # 从行对象获取值
# 初始化分组数据
if code not in grouped:
grouped[code] = {
'分厂': row['分厂'],
'LOSS用分类': row['LOSS用分类'],
'产品别': row['产品别'],
'产出品代码': code,
'说明': row['说明'],
'用量产能折算': float(row['用量产能折算']) # 确保数值类型
}
else:
# 累计计算
grouped[code]['用量产能折算'] += float(row['用量产能折算'])
# 将字典转换为DataFrame保存
result_df = pd.DataFrame(list(grouped.values()))
# 按指定格式保存
result_df.to_excel(output_file_path, index=False)
return result_df
#根据指定列筛选数据并保存为Excel文件
def filter_and_save_to_excel(data,output_file_path,target_column="LOSS用分类",filter_value="Glass"):
"""
根据指定列筛选数据并保存为Excel文件
参数:
data (str): 输入数据
output_file_path (str): 输出Excel文件路径,默认当前目录filtered_glass_data.xlsx
target_column (str): 筛选依据列名,默认"LOSS用分类"
filter_value (str): 筛选的目标值,默认"Glass"
返回:
int: 筛选出的数据行数
"""
try:
# 读取数据
df = data
# 验证目标列存在
if target_column not in df.columns:
available_cols = ", ".join(df.columns)
raise ValueError(f"目标列 '{target_column}' 不存在,可用列:{available_cols}")
# 数据预处理(处理空格和大小写)
filtered = df[
df[target_column].astype(str).str.strip().str.lower() == filter_value.lower().strip()
]
# 保存结果
filtered.to_excel(output_file_path, index=False, engine="openpyxl")
return filtered
# 返回筛选结果数量
print(f"成功保存 {len(filtered)} 条数据到 {output_file_path}")
except PermissionError:
print(f"错误:无权限写入 {output_file_path},请关闭文件或检查权限")
except Exception as e:
print(f"未知错误:{str(e)}")
#※※※※※※※※※※※※※※※※※※※※※※※※传入文件路径※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※================================#
# 调用函数,传入文件路径
bom_file_ALLData = 'H:\\yz\\测试\\B9消耗明细-202508.xlsx'
input_file_AllPrice = 'H:\\yz\\测试\\固定价格.xlsx'
input_file_MOVE = "H:\\yz\\群学习资料\\Array单耗核算\\Array_分站点MOVE最新(2025.05.26)_(优化后).xlsx"
df_oee = pd.read_excel('H:\\yz\\群学习资料\\Array单耗核算\\工序OEE.xlsx')
#※※※※※※※※※※※※※※※※※※※※※※※※※传入文件路径※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※===============================#
# 执行函数并获取结果
output_file = 'H:\\yz\\BOM计算\\BOM计算汇总.xlsx'
df_old1 = process_bom_data(bom_file_ALLData, input_file_AllPrice, output_file)
output_file_path = 'H:\\yz\\BOM计算\\BOM产能Glass.xlsx'
channeng_glass = filter_and_save_to_excel(df_old1,output_file_path,target_column="LOSS用分类",filter_value="Glass")
output_file_path = 'H:\\yz\\BOM计算\\BOM产能_Array+CF.xlsx'
channeng_alldata = process_production_data(channeng_glass, output_file_path) #Array+CF的产能表格
# 调用函数执行聚合操作
output_path = "H:\\yz\\BOM计算\\BOM计算GLASS.xlsx"
grouped=aggregate_loss_usage(df_old1, 'Glass', '分厂', '用量产能折算', output_path)
output_path = "H:\\yz\\BOM计算\\BOM计算投料.xlsx"
grouped1=aggregate_loss_categories(df_old1, output_path,grouped)
process_and_export(grouped1)
# 执行处理===============================================================================================================================#
exclude_list = ["B9P286FH5VP01-P", "B9P483FB5VP01-P"] #剔除组件号
output_file = "H:\\yz\\BOM计算\\CELL筛选结果.xlsx" #输出文件
success, filtered_cell = filter_and_export(df_old1, exclude_list,output_file) #记录总计
output_file_path = 'H:\\yz\\BOM计算\\BOM产能_cell.xlsx'
channeng_cell = process_production_data(filtered_cell, output_file_path) #cell的产能表格
#Array
data1_Array = pd.read_excel('H:\\yz\\BOM计算\\Array_生产数据.xlsx')
data2_Array = pd.read_excel('H:\\yz\\BOM计算\\BOM计算GLASS.xlsx')
processed_Array = preprocess_Array(data1_Array, data2_Array)
# print(processed_Array)
#CF
data1_CF = pd.read_excel('H:\\yz\\BOM计算\\CF_生产数据.xlsx')
data2_CF = pd.read_excel('H:\\yz\\BOM计算\\BOM计算GLASS.xlsx')
processed_CF = preprocess_CF(data1_CF, data2_CF)
# print(processed_CF)
# #CELL
data1_Cell = pd.read_excel('H:\\yz\\BOM计算\\Cell_生产数据.xlsx')
data2_Cell = success
processed_Cell = preprocess_Cell(data1_Cell, data2_Cell)
# print(processed_Cell)
combined_df = pd.concat([processed_Array, processed_CF, processed_Cell],ignore_index=True)
combined_channeng = pd.concat([channeng_alldata, channeng_cell],ignore_index=True)
#===添加汇总sheet小计 + 保留两位小数=================================================================================================================================#
# 读取数据为DataFrame
df = combined_df
# 定义数值列
numeric_cols = [ '固定实际', '固定标准', '实际金额', '标准金额']
# 将数值列转为float类型
df[numeric_cols] = df[numeric_cols].astype(float)
result_df = pd.DataFrame(columns=df.columns)
subtotals = []
for factory in ['Array', 'CF', 'Cell']:
factory_df = df[df['分厂'] == factory]
subtotal = factory_df[numeric_cols].sum().to_dict()
subtotal['分厂'] = f'{factory}小计'
subtotals.append(subtotal)
result_df = pd.concat([result_df, pd.DataFrame(subtotals)], ignore_index=True)
total_sum = pd.DataFrame(subtotals)[numeric_cols].sum().to_dict()
total_row = {'分厂': '总合计'}
total_row.update(total_sum)
result_df = pd.concat([result_df, pd.DataFrame([total_row])], ignore_index=True)
result_df = pd.concat([result_df, df], ignore_index=True)
result_df.reset_index(drop=True, inplace=True)
#两位有效数字
result_df[numeric_cols] = result_df[numeric_cols].round(2)
print(result_df.to_string(index=False))
combined_df = result_df
#===添加产能小计 + 保留两位小数=================================================================================================================================#
df = combined_channeng
# 定义数值列
numeric_cols = [ '用量产能折算' ]
# 将数值列转为float类型
df[numeric_cols] = df[numeric_cols].astype(float)
result_df = pd.DataFrame(columns=df.columns)
subtotals = []
for factory in ['Array', 'CF', 'Cell']:
factory_df = df[df['分厂'] == factory]
subtotal = factory_df[numeric_cols].sum().to_dict()
subtotal['分厂'] = f'{factory}小计'
subtotals.append(subtotal)
result_df = pd.concat([result_df, pd.DataFrame(subtotals)], ignore_index=True)
total_sum = pd.DataFrame(subtotals)[numeric_cols].sum().to_dict()
total_row = {'分厂': '总合计'}
total_row.update(total_sum)
result_df = pd.concat([result_df, pd.DataFrame([total_row])], ignore_index=True)
result_df = pd.concat([result_df, df], ignore_index=True)
result_df.reset_index(drop=True, inplace=True)
#两位有效数字
result_df[numeric_cols] = result_df[numeric_cols].round(2)
# print(result_df.to_string(index=False))
combined_channeng = result_df
#===靶材产品别MOV数 + 靶材膜厚统计=================================================================================================================================#
# 忽略openpyxl的警告
warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl')
def extract_and_merge_data(file_path ,input_file_MOVE):
try:
# 加载整个工作簿
wb = openpyxl.load_workbook(file_path, data_only=True)
ws = openpyxl.load_workbook(input_file_MOVE, data_only=True)
# 提取"靶材产品别MOV数"工作表数据
ws_move = ws["靶材产品别MOV数"]
extracted_data = []
# 搜索以"B9A"开头的单元格
start_row, start_col = None, None
for row in range(1, ws_move.max_row + 1):
for col in range(1, ws_move.max_column + 1):
cell_value = ws_move.cell(row=row, column=col).value
if cell_value and isinstance(cell_value, str) and re.match(r'^B9A', cell_value):
start_row, start_col = row, col
# print(f"找到起始单元格: [{row}, {col}], 值: '{cell_value}'")
break
if start_row: break
if not start_row:
print("未找到以'B9A'开头的单元格")
return None
# 查找同列的合计行
total_row = next((r for r in range(start_row + 1, ws_move.max_row + 1)
if ws_move.cell(r, start_col).value and "合计" in str(ws_move.cell(r, start_col).value)),
None)
if not total_row:
print(f"未找到'合计'行")
return None
# 提取原始数据并填充空值为0
rows_to_extract = [start_row - 1, start_row] + list(range(start_row + 1, total_row + 1))
extracted_data = [
[ws_move.cell(row_idx, col_idx).value or 0
for col_idx in range(start_col, start_col + 5)]
for row_idx in rows_to_extract
]
# print(f"提取{len(extracted_data)}行原始数据")
# 提取"靶材膜厚统计"工作表数据
ws_thickness = wb["靶材膜厚统计"]
# print(f"膜厚表尺寸:行={ws_thickness.max_row}, 列={ws_thickness.max_column}")
# 查找"型号"列索引
model_col = next((c for c in range(1, ws_thickness.max_column + 1)
if ws_thickness.cell(1, c).value and "型号" in str(ws_thickness.cell(1, c).value)),
None)
if not model_col:
print("未找到'型号'列")
return None
# 提取膜厚数据并填充空值为0
thickness_headers = [ws_thickness.cell(1, c).value for c in range(1, ws_thickness.max_column + 1) if
c != model_col]
thickness_data = [
[ws_thickness.cell(row, col).value or 0
for col in range(1, ws_thickness.max_column + 1) if col != model_col]
for row in range(2, ws_thickness.max_row + 1)
]
model_values = [ws_thickness.cell(row, model_col).value for row in range(2, ws_thickness.max_row + 1)]
# print(f"提取{len(thickness_data)}行膜厚数据")
# 合并数据
merged_data = [extracted_data[0] + thickness_headers]
matched, unmatched = 0, 0
for i in range(1, len(extracted_data)):
model_value = extracted_data[i][0]
try:
match_idx = model_values.index(model_value)
merged_row = extracted_data[i] + thickness_data[match_idx]
matched += 1
except ValueError:
merged_row = extracted_data[i] + [0] * len(thickness_headers)
if model_value: # 忽略空值警告
print(f"警告: 型号 '{model_value}' 未找到匹配")
unmatched += 1
merged_data.append(merged_row)
# print(f"\n===== 匹配统计 =====")
# print(f"匹配行数: {matched}/{len(extracted_data) - 1}")
# if unmatched: print(f"未匹配型号数: {unmatched}")
# 添加计算列
# print("\n===== 添加计算列 =====")
calc_columns = ['ITO Dep', 'CU Dep', 'MoNb Dep', 'MTD Dep']
merged_data[0] += calc_columns
# 四组计算规则
calc_rules = [
{'factors': (1, 2), 'denominators': (5, 6)},
{'factors': (3, 4), 'denominators': (7, 8)},
{'factors': (3, 4), 'denominators': (9, 10)},
{'factors': (3, 4), 'denominators': (11, 12)}
]
for i in range(1, len(merged_data)):
row = merged_data[i]
calc_values = []
for rule in calc_rules:
col1, col2 = rule['factors']
col5, col6 = rule['denominators']
try:
denominator = float(row[col5]) + float(row[col6])
denominator = denominator if abs(denominator) > 1e-6 else float('inf')
value = (float(row[col1]) * (float(row[col5]) / denominator) +
float(row[col2]) * (float(row[col6]) / denominator))
calc_values.append(value)
except (ValueError, TypeError, IndexError):
calc_values.append(0)
merged_data[i] += calc_values
# print(f"成功添加{len(calc_columns)}个计算列")
return merged_data
except Exception as e:
print(f"处理错误: {str(e)}")
return None
def save_to_excel(data, output_file):
if not data:
print("无数据可保存")
return False
try:
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "最终结果"
# 写入数据
for r, row in enumerate(data, 1):
for c, value in enumerate(row, 1):
ws.cell(r, c, value=value)
if isinstance(value, (int, float)):
ws.cell(r, c).number_format = '#,##0.00'
# 调整列宽
for col in range(1, len(data[0]) + 1):
max_len = max(
(len(str(ws.cell(r, col).value or '')) for r in range(1, len(data) + 1)),
default=0
)
ws.column_dimensions[get_column_letter(col)].width = (max_len + 2) * 1.2
# 创建输出目录
os.makedirs(os.path.dirname(output_file), exist_ok=True)
wb.save(output_file)
print(f"结果保存至: {output_file}")
return True
except Exception as e:
print(f"保存错误: {str(e)}")
return False
# input_file_AllPrice = "H:\\yz\\测试\\固定价格.xlsx"
# input_file_MOVE = "H:\\yz\\群学习资料\\Array单耗核算\\Array_分站点MOVE最新(2025.05.26)_(优化后).xlsx"
output_file = "H:\\yz\\BOM计算2\\最终结果.xlsx"
result = extract_and_merge_data(input_file_AllPrice ,input_file_MOVE)
if result:
print(f"\n合并数据: {len(result)}行, {len(result[0])}列")
print("表头样例:", result[0][:5])
print("数据样例:", result[1][:5])
if save_to_excel(result, output_file):
print("1")
else:
print("数据处理失败")
result_BOM_Array = pd.read_excel("H:\\yz\\BOM计算2\\最终结果.xlsx")
#====================================================================================================================================#
def aggregate_loss_usage(df, loss_category, group_column, value_column, output_file):
"""
根据给定的LOSS用分类,筛选出对应的行,并按照指定的分厂列进行分组,
对指定的数值列进行累加,最后保存结果到Excel文件。
参数:
df (DataFrame): 包含数据的DataFrame
loss_category (str): 需要筛选的LOSS用分类名称,如“Glass”
group_column (str): 用于分组的列名,如“分厂”
value_column (str): 需要累加的数值列名,如“用量产能折算”
output_file (str): 输出Excel文件的路径和名称
"""
# 确保输入的参数都是字符串
if not isinstance(loss_category, str) or \
not isinstance(group_column, str) or \
not isinstance(value_column, str):
raise TypeError("loss_category、group_column和value_column必须是字符串类型。")
try:
# 筛选出指定LOSS分类下的数据
filtered_df = df[df['LOSS用分类'] == loss_category].copy()
# 检查是否有有效的数据记录
if len(filtered_df) == 0:
print(f"没有找到'{loss_category}'类别下的任何数据。")
return None
# 按照分厂进行分组,并计算数值列的总和
grouped = filtered_df.groupby(group_column)[value_column].sum().reset_index()
# 将结果保存到Excel文件
grouped.to_excel(output_file, index=False)
print(f"已将'{loss_category}'类别下的数据按分厂累加,结果保存至:{output_file}")
except KeyError as e:
print(f"错误:DataFrame中缺少列'{e.args[0]}'。")
except Exception as e:
print(f"发生了意外错误:{str(e)}")
return grouped
# 按照 'LOSS用分类' 和 '分厂' 分组,并对数值列进行累加
def aggregate_loss_categories(df, output_file, factory_divisor_map):
try:
# 验证必要列是否存在于 DataFrame 中
required_columns = ['LOSS用分类', '分厂',
'实际金额', '标准金额',
'固定实际', '固定标准']
if not all(col in df.columns for col in required_columns):
raise ValueError("DataFrame 中缺少必要的列")
# 按照 'LOSS用分类' 和 '分厂' 分组,并对数值列进行累加
grouped = (df.groupby(['分厂', 'LOSS用分类' ]).agg
({
'固定实际': 'sum',
'固定标准': 'sum',
'实际金额': 'sum',
'标准金额': 'sum'
}).reset_index())
# 将结果保存到 Excel 文件
grouped.to_excel(output_file, index=False)
print(f"累加的结果保存至:{output_file}")
except Exception as e:
print(f"发生了错误:{str(e)}")
return grouped
#按分厂分离数据并保存为独立Excel文件
def process_and_export(data, output_dir="H:\\yz\\BOM计算"):
"""
按分厂分离数据并保存为独立Excel文件
参数:
data : DataFrame - 包含"分厂"列的处理后数据
output_dir : str - 输出目录(默认创建"分厂数据"文件夹)
"""
# 创建输出目录
os.makedirs(output_dir, exist_ok=True)
try:
# 按分厂分组
grouped = data.groupby("分厂")
# 遍历每个分厂
for factory_name, factory_data in grouped:
# 生成安全文件名(替换特殊字符)
safe_name = factory_name.replace("/", "_").replace("\\", "_")
file_path = os.path.join(output_dir, f"{safe_name}_生产数据.xlsx")
# 保存Excel文件
factory_data.to_excel(file_path, index=False, engine='openpyxl')
print(f"成功生成:{file_path}")
return True
except KeyError:
print("错误:数据中未找到'分厂'列")
return False
except Exception as e:
print(f"保存失败:{str(e)}")
return False
#筛选并剔除Cell指定编号
def filter_and_export(data, exclude_components, output_file):
# 检查必要列是否存在
required_columns = ["LOSS用分类", "组件"]
if not set(required_columns).issubset(data.columns):
missing = set(required_columns) - set(data.columns)
raise KeyError(f"数据缺少必要列:{missing}")
# 步骤1:筛选分类(Array和Cell)
filtered = data[data["LOSS用分类"].isin(["Array"])]
# 步骤2:剔除指定组件号
filtered = filtered[~filtered["组件"].isin(exclude_components)]
# 步骤3:导出Excel
filtered.to_excel(output_file, index=False, engine='openpyxl')
print(f"数据已导出至:{os.path.abspath(output_file)}")
try:
series = pd.to_numeric(filtered["用量产能折算"], errors='coerce') # 无法转换的值变为 NaN
total = series.sum(skipna=True) # 自动跳过 NaN
print(f" '{"用量产能折算"}' 总和: {total:.2f}")
except KeyError:
print(f"错误: 列 '{"用量产能折算"}' 不存在")
return total, filtered
#按照顺序输出Array+CF+Cell表格
def preprocess_Array(data1, data2):
# 定义分类顺序
categories_order = [
'PR胶', 'Cu靶材', 'ITO靶材', '钼靶', 'MTD靶材',
'Cl2', 'He', 'NF3', 'NH3', 'H2', 'SiH4',
'Cu剥离液', 'Cu刻蚀液', 'ITO刻蚀液', '补充液',
'清洗液', '稀释液', '显影液'
]
# 步骤1:获取除数
try:
divisor = data2.iloc[0, 1] # 取第二列第一行
except IndexError:
raise ValueError("数据二格式错误:至少需要两列且第一行有数据")
# 步骤2:筛选有效分类
filtered = data1[data1['LOSS用分类'].isin(categories_order)].copy()
# 步骤3:按指定顺序排序
filtered['排序键'] = filtered['LOSS用分类'].map(lambda x: categories_order.index(x))
filtered = filtered.sort_values('排序键').drop(columns='排序键')
# 步骤4:处理数值列(后四列)
if len(filtered.columns) < 4:
raise ValueError("数据至少需要四列数值数据")
# 获取后四列列名
numeric_cols = filtered.columns[-4:]
filtered[numeric_cols] = filtered[numeric_cols] / divisor
return filtered
def preprocess_CF(data1, data2):
# 定义分类顺序
categories_order = [
'Blue胶',
'BM胶',
'GREEN胶',
'OC胶',
'PS胶',
'Red胶',
'ITO靶材',
'显影液',
'清洗液',
'稀释液'
]
# 步骤1:获取除数
try:
divisor = data2.iloc[1, 1] # 取第二列第一行
except IndexError:
raise ValueError("数据二格式错误:至少需要两列且第一行有数据")
# 步骤2:筛选有效分类
filtered = data1[data1['LOSS用分类'].isin(categories_order)].copy()
# 步骤3:按指定顺序排序
filtered['排序键'] = filtered['LOSS用分类'].map(lambda x: categories_order.index(x))
filtered = filtered.sort_values('排序键').drop(columns='排序键')
# 步骤4:处理数值列(后四列)
if len(filtered.columns) < 4:
raise ValueError("数据至少需要四列数值数据")
# 获取后四列列名
numeric_cols = filtered.columns[-4:]
filtered[numeric_cols] = filtered[numeric_cols] / divisor
return filtered
def preprocess_Cell(data1, data2):
# 定义分类顺序
categories_order = \
[
'液晶',
'PI液',
'封框胶',
'小球'
]
# 步骤1:获取除数
try:
divisor = data2 # 取第二列第一行
except IndexError:
raise ValueError("数据二格式错误:至少需要两列且第一行有数据")
# 步骤2:筛选有效分类
filtered = data1[data1['LOSS用分类'].isin(categories_order)].copy()
# 步骤3:按指定顺序排序
filtered['排序键'] = filtered['LOSS用分类'].map(lambda x: categories_order.index(x))
filtered = filtered.sort_values('排序键').drop(columns='排序键', axis=1)
# 步骤4:处理数值列(后四列)
if len(filtered.columns) < 4:
raise ValueError("数据至少需要四列数值数据")
# 获取后四列列名
numeric_cols = filtered.columns[-4:]
filtered[numeric_cols] = filtered[numeric_cols] / divisor
return filtered
#读取 Excel 文件,去除第二列末尾的“-P”
def remove_p_suffix(s):
"""
去除字符串末尾的“-P”
@param s: 输入字符串
@return: 处理后的字符串
"""
try:
if isinstance(s, str) and len(s) >= 2 and s.endswith("-P"):
return s[:-2]
else:
return s
except Exception as e:
print(f"处理出错:{e}")
return s# #
def process_excel_file(file_path):
"""
读取 Excel 文件,去除第二列末尾的“-P”
@param file_path: Excel 文件路径(支持 .xlsx 和 .xls)
@return: 处理后的 DataFrame
"""
try:
# 读取 Excel 文件
df = file_path
# 检查是否有数据
if df.empty:
print("Excel 文件为空。")
return None
# 获取第一列的列名(假设是 'Column1')
first_column_name = df.columns[1]
# 对每个单元格应用 remove_p_suffix 函数
df[first_column_name] = df[first_column_name].apply(remove_p_suffix)
return df
except Exception as e:
print(f"读取或处理 Excel 文件出错:{e}")
return None
#建立汇总BOM报表
def process_bom_data(bom_file_path, price_file_path, output_file_path):
"""
处理BOM数据并计算相关金额。
:param bom_file_path: BOM表文件路径
:param price_file_path: 固定价格表文件路径
:param output_file_path: 输出文件路径
:return: 处理后的DataFrame
"""
# 读取Excel文件
df_old = pd.read_excel(bom_file_path)
df_new = pd.read_excel(price_file_path , sheet_name='固定价格')
df_old = process_excel_file(df_old)
df2 = pd.read_excel(price_file_path , sheet_name='对应编号')
# 重命名df2的列(除了产出品代码),避免合并后列名冲突
df2_columns_rename = {col: f'{col}' for col in df2.columns if col != '产出品代码'}
df2_renamed = df2.rename(columns=df2_columns_rename)
df_old = (pd.merge
(
df_old,
df2_renamed,
on='产出品代码',
how='left'
))
# 创建价格字典(组件:均价)
price_mapping = df_new.set_index('组件')['均价'].to_dict()
# 在最后一列创建均价列
df_old.insert(
len(df_old.columns),
'均价',
df_old['组件'].map(price_mapping).fillna(0)
)
# 新增1:固定价实际消耗量金额
df_old.insert(
len(df_old.columns) - 1, # 倒数第二列位置
'固定实际',
df_old['组件投入数量'] * df_old['均价']
)
# 新增2:固定价标准消耗量金额
df_old.insert(
len(df_old.columns) - 1,
'固定标准',
df_old['组件投入数量'] * df_old['均价']
)
# 新增3:实际消耗金额
df_old.insert(
len(df_old.columns) - 1,
'实际金额',
df_old['组件投入数量'] * df_old['月度投入均价']
)
# 新增4:标准消耗金额
df_old.insert(
len(df_old.columns) - 1,
'标准金额',
df_old['组件投入数量'] * df_old['月度投入均价']
)
# 新增5:用量产能折算
df_old.insert(
len(df_old.columns) - 1,
'用量产能折算',
df_old['标准消耗数量'] / (df_old['标准单耗量-折算前'] * df_old['切片'])
)
# 保存结果到新文件
df_old.to_excel(output_file_path, index=False)
# 返回处理后的DataFrame
return pd.DataFrame(df_old)
#按照产出品代码累加
def process_production_data(data, output_file_path):
#确保输入是DataFrame格式
if not isinstance(data, pd.DataFrame):
data = pd.DataFrame(data)
grouped = {}
# 正确遍历DataFrame行数据
for index, row in data.iterrows(): # 使用iterrows遍历
code = row['产出品代码'] # 从行对象获取值
# 初始化分组数据
if code not in grouped:
grouped[code] = {
'分厂': row['分厂'],
'LOSS用分类': row['LOSS用分类'],
'产品别': row['产品别'],
'产出品代码': code,
'说明': row['说明'],
'用量产能折算': float(row['用量产能折算']) # 确保数值类型
}
else:
# 累计计算
grouped[code]['用量产能折算'] += float(row['用量产能折算'])
# 将字典转换为DataFrame保存
result_df = pd.DataFrame(list(grouped.values()))
# 按指定格式保存
result_df.to_excel(output_file_path, index=False)
return result_df
#根据指定列筛选数据并保存为Excel文件
def filter_and_save_to_excel(data,output_file_path,target_column="LOSS用分类",filter_value="Glass"):
try:
# 读取数据
df = data
# 验证目标列存在
if target_column not in df.columns:
available_cols = ", ".join(df.columns)
raise ValueError(f"目标列 '{target_column}' 不存在,可用列:{available_cols}")
# 数据预处理(处理空格和大小写)
filtered = df[
df[target_column].astype(str).str.strip().str.lower() == filter_value.lower().strip()
]
# 保存结果
filtered.to_excel(output_file_path, index=False, engine="openpyxl")
return filtered
# 返回筛选结果数量
print(f"成功保存 {len(filtered)} 条数据到 {output_file_path}")
except PermissionError:
print(f"错误:无权限写入 {output_file_path},请关闭文件或检查权限")
except Exception as e:
print(f"未知错误:{str(e)}")
# 定义需要统计的分类顺序
target_categories = [
'PR胶', 'Cu靶材', 'ITO靶材', '钼靶', 'MTD靶材',
'Cl2', 'He', 'NF3', 'NH3', 'H2', 'SiH4',
'Cu剥离液', 'Cu刻蚀液', 'ITO刻蚀液',
'补充液', '清洗液', '稀释液', '显影液'
]
# 读取文件并筛选"分厂"列为"Array"的行
df = pd.read_excel(bom_file_ALLData) # 替换为实际文件名
array_data = df[df['分厂'] == 'Array'].copy()
# 按指定顺序筛选并累加"组件投入数量"
result_data = []
for category in target_categories:
category_data = array_data[array_data['LOSS用分类'] == category]
total_quantity = category_data['组件投入数量'].sum()
result_data.append({
'LOSS用分类': category,
'组件投入总数量': total_quantity
})
# 创建结果DataFrame
result_df = pd.DataFrame(result_data)
# 读取第一个Excel文件
# df_oee = pd.read_excel('H:\\yz\\群学习资料\\Array单耗核算\\工序OEE.xlsx')
# 读取第二个Excel文件(代码二的结果)
df_bom = pd.read_excel('H:\\yz\\BOM计算2\\最终结果.xlsx') # 注意路径与代码二结果一致
# 按行索引计算各项值
row_1 = df_oee.iloc[1, 16] - df_oee.iloc[1, 17]
row_6 = (df_oee.iloc[14, 16] + df_oee.iloc[15, 16] + df_oee.iloc[17, 16]) / 3
row_7 = row_6
row_8 = (df_oee.iloc[4, 16] + df_oee.iloc[5, 16] + df_oee.iloc[6, 16]) / 3
row_9 = row_8
row_10 = df_oee.iloc[5, 16]
row_11 = (df_oee.iloc[4, 16] + df_oee.iloc[5, 16] + df_oee.iloc[6, 16]) / 3
row_12 = (df_oee.iloc[9, 16] + df_oee.iloc[13, 16]) - (df_oee.iloc[9, 17] + df_oee.iloc[13, 17])
row_13 = df_oee.iloc[11, 16] - df_oee.iloc[11, 17]
row_14 = df_oee.iloc[12, 16] - df_oee.iloc[12, 17]
row_15 = row_14
row_16 = df_oee.iloc[10, 16] - df_oee.iloc[10, 17]
row_17 = row_1
row_18 = row_1
row_2 = sum(df_bom['CU Dep'])
row_3 = sum(df_bom['ITO Dep'])
row_4 = sum(df_bom['MoNb Dep'])
row_5 = sum(df_bom['Gate Dep']) / 2
# 组合所有计算结果
L_1 = [row_1, row_2, row_3, row_4, row_5, row_6, row_7,
row_8, row_9, row_10, row_11, row_12, row_13,
row_14, row_15, row_16, row_17, row_18]
# ======== 整合结果 ========
# 确保代码二的DataFrame行数与代码一的结果数量匹配
if len(result_df) != len(L_1):
# 如果行数不匹配,添加空行补齐
result_df = result_df.iloc[:len(L_1)]
# 将L_1的结果转换为"Move"列插入到第二列位置
result_df.insert(1, 'Move', L_1 ) # 在索引位置1(第二列)插入新列
row_1 = df_oee.iloc[1, 18]
row_12 = (df_oee.iloc[9, 18] + df_oee.iloc[13, 18])
row_14 = df_oee.iloc[12, 18]
L_2 = [row_1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 ,
row_12, row_12, row_14, row_14, 1, row_1, row_1]
# ======== 整合结果 ========
# 确保结果数量匹配
if len(result_df) != len(L_2):
# 如果行数不匹配,添加空行补齐
result_df = result_df.iloc[:len(L_2)]
# 将L_2的结果转换为"Step"列插入到第二列位置
result_df.insert(2, 'Step', L_2) # 在索引位置1(第三列)插入新列
#====================================================================================================================================#
# 调用函数,传入文件路径
# bom_file_ALLData = 'H:\\yz\\测试\\B9消耗明细-202508.xlsx'
# input_file_AllPrice = 'H:\\yz\\测试\\固定价格.xlsx'
# 执行函数并获取结果
#====================================================================================================================================#
output_file = 'H:\\yz\\BOM计算\\BOM计算汇总.xlsx'
df_old1 = process_bom_data(bom_file_ALLData, input_file_AllPrice, output_file)
output_file_path = 'H:\\yz\\BOM计算\\BOM产能Glass.xlsx'
channeng_glass = filter_and_save_to_excel(df_old1,output_file_path,target_column="LOSS用分类",filter_value="Glass")
output_file_path = 'H:\\yz\\BOM计算\\BOM产能_Array+CF.xlsx'
channeng_alldata = process_production_data(channeng_glass, output_file_path) #Array+CF的产能表格
# 调用函数执行聚合操作
output_path = "H:\\yz\\BOM计算\\BOM计算GLASS.xlsx"
grouped=aggregate_loss_usage(df_old1, 'Glass', '分厂', '用量产能折算', output_path)
output_path = "H:\\yz\\BOM计算\\BOM计算投料.xlsx"
grouped1=aggregate_loss_categories(df_old1, output_path,grouped)
process_and_export(grouped1)
# 定义分类顺序
categories_order = [
'PR胶', 'Cu靶材', 'ITO靶材', '钼靶', 'MTD靶材',
'Cl2', 'He', 'NF3', 'NH3', 'H2', 'SiH4',
'Cu剥离液', 'Cu刻蚀液', 'ITO刻蚀液', '补充液',
'清洗液', '稀释液', '显影液'
]
data1 = pd.read_excel('H:\\yz\\BOM计算\\Array_生产数据.xlsx')
# 步骤2:筛选有效分类
filtered = data1[data1['LOSS用分类'].isin(categories_order)].copy()
# 步骤3:按指定顺序排序
filtered['排序键'] = filtered['LOSS用分类'].map(lambda x: categories_order.index(x))
filtered = filtered.sort_values('排序键').drop(columns='排序键')
columns = ['分厂', 'LOSS用分类', '实际金额', '固定实际']
result = filtered[columns]
# 在指定位置插入行(例如在第2行后插入)
insert_index = 4 # 在0-based索引位置2插入(即第3行)
new_data = {"分厂": "Array", "LOSS用分类": "MTD靶材", "实际金额": 0, "固定实际": 0 } # 列名需匹配原表格
# 拆分DataFrame并插入新行
df_top = result.iloc[:insert_index] # 插入位置前的行
df_bottom = result.iloc[insert_index:] # 插入位置后的行
result = pd.concat([df_top, pd.DataFrame([new_data]), df_bottom], ignore_index=True)
new_column_names = {
'实际金额' : '实际组件投入金额',
'固定实际' : '固定组件投入金额'
}
result = result.rename(columns=new_column_names)
c = result['实际组件投入金额'].reset_index(drop = True)
# print(c)
# 将L_2的结果转换为"Step"列插入到第二列位置
result_df.insert(4, '实际组件投入金额', c ) # 在索引位置1(第三列)插入新列
# 将L_2的结果转换为"Step"列插入到第二列位置
c = result['固定组件投入金额'].reset_index(drop = True)
# print(c)
result_df.insert(5, '固定组件投入金额', c) # 在索引位置1(第三列)插入新列
#====================================================================================================================================#
result_df.insert(
len(result_df.columns) ,
'实际单耗',
result_df['实际组件投入金额'] / result_df['Move'] * result_df['Step']
)
result_df.insert(
len(result_df.columns) ,
'固定单耗',
result_df['固定组件投入金额'] / result_df['Move'] * result_df['Step']
)
#======更新产品结构 + 异常消除 =============================================================================================================================#
import pandas as pd
import os
import time
from io import StringIO
import numpy as np
import openpyxl
import re
import warnings
from openpyxl.utils import get_column_letter
from openpyxl import load_workbook
def filter_and_save_to_excel(data,output_file_path,target_column="LOSS用分类",filter_value="Cl2"):
"""
根据指定列筛选数据并保存为Excel文件
参数:
data (str): 输入数据
output_file_path (str): 输出Excel文件路径,默认当前目录filtered_glass_data.xlsx
target_column (str): 筛选依据列名,默认"LOSS用分类"
filter_value (str): 筛选的目标值,默认"Glass"
返回:
int: 筛选出的数据行数
"""
try:
# 读取数据
df = data
# 验证目标列存在
if target_column not in df.columns:
available_cols = ", ".join(df.columns)
raise ValueError(f"目标列 '{target_column}' 不存在,可用列:{available_cols}")
# 数据预处理(处理空格和大小写)
filtered = df[
df[target_column].astype(str).str.strip().str.lower() == filter_value.lower().strip()
]
# 保存结果
filtered.to_excel(output_file_path, index=False, engine="openpyxl")
return filtered
# 返回筛选结果数量
print(f"成功保存 {len(filtered)} 条数据到 {output_file_path}")
except PermissionError:
print(f"错误:无权限写入 {output_file_path},请关闭文件或检查权限")
except Exception as e:
print(f"未知错误:{str(e)}")
input_file = "H:\\yz\\群学习资料\\Array单耗核算\\输出文件.xlsx"
# input_file_AllPrice = 'H:\\yz\\测试\\固定价格.xlsx'
bom_file = "H:\\yz\\BOM计算\\BOM产能_Array+CF.xlsx"
bom_summary_file = 'H:\\yz\\BOM计算\\BOM计算汇总.xlsx'
output_cl2_file = 'H:\\yz\\BOM计算\\BOM产能Cl2.xlsx'
# 读取数据
df1 = pd.read_excel(input_file)
dff1 = df1.iloc[7:36, 2].dropna().astype(str).str.strip().str.lower()
df2 = pd.read_excel(input_file_AllPrice, sheet_name="靶材膜厚统计")
dff2 = df2.iloc[0:56, 0].dropna().astype(str).str.strip().str.lower()
# 合并所有有效代码
dff1_valid_codes = set(dff1)
dff2_valid_codes = set(dff2)
# 读取BOM数据
df = pd.read_excel(bom_file)
array_data = df[df['分厂'] == 'Array'].copy()
# 检查array_data的产出品代码
array_codes = array_data['产出品代码'].astype(str).str.strip().str.lower()
missing_array_dff1 = set(array_codes) - dff1_valid_codes
missing_array_dff2 = set(array_codes) - dff2_valid_codes
if missing_array_dff1:
raise ValueError(f"array_data中存在“产品结构”中无效产出品代码: {', '.join(missing_array_dff1)}")
if missing_array_dff2:
raise ValueError(f"array_data中存在“膜厚统计”中无效产出品代码: {', '.join(missing_array_dff2)}")
# 筛选Cl2数据
df_old1 = pd.read_excel(bom_summary_file)
channeng_Cl2 = filter_and_save_to_excel(df_old1, output_cl2_file, target_column="LOSS用分类", filter_value="Cl2")
# 检查channeng_Cl2的产出品代码
Cl2_codes = array_data['产出品代码'].astype(str).str.strip().str.lower()
missing_array_dff1 = set(Cl2_codes) - dff1_valid_codes
missing_array_dff2 = set(Cl2_codes) - dff2_valid_codes
if missing_array_dff1:
raise ValueError(f"Cl2_codes中存在“产品结构”中无效产出品代码: {', '.join(missing_array_dff1)}")
if missing_array_dff2:
raise ValueError(f"Cl2_codes中存在“膜厚统计”中无效产出品代码: {', '.join(missing_array_dff2)}")
# 使用openpyxl加载输出文件以保留格式
wb = load_workbook(input_file)
ws = wb.active
code_to_row = {}
for row in range(9, 42):
code = str(ws.cell(row=row, column=3).value).strip().lower()
code_to_row[code] = row
for row in range(9, 42):
ws.cell(row=row, column=26).value = 0
for row in range(9, 42):
ws.cell(row=row, column=25).value = 0
ALL_ARRAY = 0
for _, row in array_data.iterrows():
code = str(row['产出品代码']).strip().lower()
if code in code_to_row:
# 检查代码是否在有效代码集中
if code in dff1_valid_codes:
ws.cell(row=code_to_row[code], column=26).value = row['用量产能折算']
ALL_ARRAY = ALL_ARRAY + row['用量产能折算']
else:
ws.cell(row=code_to_row[code], column=26).value = 0
ws.cell(row=8, column=26).value = ALL_ARRAY
ALL_CL2 = 0
for _, row in channeng_Cl2.iterrows():
code = str(row['产出品代码']).strip().lower()
if code in code_to_row:
# 检查代码是否在有效代码集中
if code in dff1_valid_codes:
ws.cell(row=code_to_row[code], column=25).value = row['用量产能折算']
ALL_CL2 = ALL_CL2 + row['用量产能折算']
else:
ws.cell(row=code_to_row[code], column=25).value = 0
ws.cell(row=8, column=25).value = ALL_CL2
for i in range(23) :
print(ws.cell(row=4, column=(i+5)).value)
# 保存修改后的Excel
wb.save("H:\\yz\\群学习资料\\Array单耗核算\\输出文件2.xlsx")
print("数据更新完成!")
input_file2 = "H:\\yz\\群学习资料\\Array单耗核算\\输出文件2.xlsx"
# 使用openpyxl加载输出文件以保留格式
wb = openpyxl.load_workbook(input_file2)
ws = wb.active
# 定义需要处理的列范围
start_row, end_row = 9, 42
columns_to_save = ['E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'X', 'Y',
'Z']
# 保存修改前的原始值
original_values = {}
for row in range(start_row, end_row):
row_values = {}
for col_letter in columns_to_save:
col_idx = openpyxl.utils.column_index_from_string(col_letter)
row_values[col_letter] = ws.cell(row=row, column=col_idx).value
original_values[row] = row_values
# 初始化累加器
sum_products = [0] * 18
sum_Products = [0] * 18
x8_value = 0
# 计算SUMPRODUCT部分
for row in range(9, 41):
row_data = original_values[row]
x_value = row_data.get('X', 0) or 0
x8_value += x_value
# 处理每列的计算
for i, col_letter in enumerate(
['E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V']):
e_value = row_data.get(col_letter, 0) or 0
# 确定乘数列(第一列用Y,其他用Z)
multiplier_col = 'Y' if i == 0 else 'Z'
multiplier_value = row_data.get(multiplier_col, 0) or 0
sum_products[i] += e_value * multiplier_value
sum_Products[i] += e_value * x_value
# 获取第7行的值
row7_values = []
for col_idx in range(5, 23): # E列(5)到V列(22)
row7_values.append(float(ws.cell(row=7, column=col_idx).value or 0))
# 获取Z8的值
z8_value = float(ws.cell(row=8, column=26).value or 1) # 避免除以0错误
# 计算并存储结果
results = []
extra_additions = [0] * 18
extra_additions[3] = 1.2 # H列(第4列)
extra_additions[6] = 0.5 # K列(第7列)
for i in range(18):
result = ((sum_products[i] * row7_values[i]) / z8_value) - \
((sum_Products[i] * row7_values[i]) / x8_value) + \
extra_additions[i]
results.append(result)
# 将结果写入第4行
for col_idx, result in enumerate(results, start=5): # 从E列(5)开始
ws.cell(row=4, column=col_idx).value = result
# 保存修改后的Excel
output_file = "H:\\yz\\群学习资料\\Array单耗核算\\输出文件3.xlsx"
wb.save(output_file)
print(f"数据更新完成!第一个计算结果:{results[0]}")
dfaaa = pd.read_excel("H:\\yz\\群学习资料\\Array单耗核算\\输出文件3.xlsx", engine='openpyxl')
dff = dfaaa.iloc[2, 4:22]
print("====ARRAY + CF产品结构核算=============================================================================================================================")
print(dff)
# 原始列名(按实际提取顺序)
original_columns = [
'Cl2', 'Cu靶材', 'Cu剥离液', 'Cu刻蚀液', 'H2', 'He', 'ITO靶材',
'ITO刻蚀液', 'MTD靶材', 'NF3', 'NH3', 'PR胶', 'SiH4',
'补充液', '钼靶', '清洗液', '稀释液', '显影液'
]
# 定义新的分类顺序
categories_order = [
'PR胶', 'Cu靶材', 'ITO靶材', '钼靶', 'MTD靶材',
'Cl2', 'He', 'NF3', 'NH3', 'H2', 'SiH4',
'Cu剥离液', 'Cu刻蚀液', 'ITO刻蚀液', '补充液',
'清洗液', '稀释液', '显影液'
]
# 创建列名到值的映射(确保数据类型为数值)
values_dict = {}
for idx, col in enumerate(original_columns):
value = dff.iloc[idx]
# 处理NaN值 - 替换为0
if pd.isna(value):
values_dict[col] = 0.0
# 处理文本型数值
elif isinstance(value, str):
try:
# 尝试转换字符串为浮点数
values_dict[col] = float(value)
except ValueError:
# 无法转换的设为0
values_dict[col] = 0.0
else:
values_dict[col] = float(value)
# 按新顺序提取数值
ordered_values = [values_dict[col] for col in categories_order]
# 转换为单列DataFrame
ordered_values = pd.DataFrame(ordered_values, columns=["单耗数值"])
# 重置索引
ordered_values.reset_index(drop=True, inplace=True)
# print("转换为一列的结果:")
# print(ordered_values )
result_df.insert(8, '产品结构折算', ordered_values)
#====================================================================================================================================#
result_df.insert(
len(result_df.columns) ,
'KPI考核单耗',
result_df['固定单耗'] - result_df['产品结构折算']
)
result_df.loc[ 7 , 'KPI考核单耗' ] = result_df.loc[ 7 , 'KPI考核单耗' ]
# print(result_df.loc[ 7 , 'KPI考核单耗' ])
result_df.loc[ 12 , 'KPI考核单耗' ] = result_df.loc[ 12 , 'KPI考核单耗' ]
# print(result_df.loc[ 12 , 'KPI考核单耗' ])
result_BOM_Array_Result = result_df
# ======== 导出最终结果 ========
# result_df.to_excel('H:\\yz\\BOM计算2\\最终表格L_3_整合结果.xlsx', index=False)
# print("处理完成!整合结果已保存为'最终表格L_3_整合结果.xlsx'")
# print(f"最终表格包含 {len(result_df.columns)} 列: {list(result_df.columns)}")
# end = time.perf_counter()
# elapsed = end - start
# print(f"代码运行耗时: {elapsed:.6f} 秒")
#====产能汇总================================================================================================================================#
# 读取三张Excel表
df1 = pd.read_excel('H:\\yz\\BOM计算2\\B9\\202411.xlsx' )
df2 = pd.read_excel('H:\\yz\\BOM计算2\\B9\\202412.xlsx' )
df3 = pd.read_excel('H:\\yz\\BOM计算2\\B9\\202501.xlsx' )
df4 = pd.read_excel('H:\\yz\\BOM计算2\\B9\\202502.xlsx' )
# 前五列的列名
front_columns = ['分厂', 'LOSS用分类', '产品别', '产出品代码', '说明']
# 产出品代码列名
code_col = '产出品代码'
# 产能折算列名
capacity_col = '用量产能折算'
# 分别提取关键列
df1_cap = df1[[code_col, capacity_col]].rename(columns={capacity_col: '202411'})
df2_cap = df2[[code_col, capacity_col]].rename(columns={capacity_col: '202412'})
df3_cap = df3[[code_col, capacity_col]].rename(columns={capacity_col: '202501'})
df4_cap = df4[[code_col, capacity_col]].rename(columns={capacity_col: '202502'})
# 使用全外连接融合产能折算列
merged_cap = pd.merge(df1_cap, df2_cap, on=code_col, how='outer')
merged_cap = pd.merge(merged_cap, df3_cap, on=code_col, how='outer')
merged_cap = pd.merge(merged_cap, df4_cap, on=code_col, how='outer')
# 构建前五列数据,按产出品代码去重
front_data = pd.concat([
df1[front_columns],
df2[front_columns],
df3[front_columns],
df4[front_columns]
]).drop_duplicates(subset=[code_col], keep='first')
# 合并前五列和产能折算数据
final_df = pd.merge(front_data, merged_cap, on=code_col, how='outer')
# 按列重排序:前五列 + 三张表的折算列
column_order = front_columns + ['202411', '202412', '202501', '202502']
final_df = final_df[column_order]
# 空值填充
final_df.fillna('', inplace=True)
# 输出结果
# final_df.to_excel('H:\\yz\\BOM计算2\\B9\\合并结果表.xlsx', index=False)
# print("融合完成!结果已保存至'H:\\yz\\BOM计算2\\B9\\合并结果表.xlsx'")
#====================================================================================================================================#
#结果输出位置
output_file_path = 'H:\\yz\\测试\\总结果.xlsx'
#====================================================================================================================================#
# combined_df.to_excel(output_file_path,sheet_name="单耗" ,index=False)
# combined_channeng.to_excel(output_file_path,sheet_name="产能" ,index=False)
# 导出到Excel的不同Sheet
with pd.ExcelWriter(output_file_path) as writer:
combined_df.to_excel(writer, sheet_name='单耗', index=False)
combined_channeng.to_excel(writer, sheet_name='产能', index=False)
# result_BOM_Array.to_excel(writer, sheet_name='BOM_Array', index=False)
result_BOM_Array_Result.to_excel(writer, sheet_name='总结果', index=False)
final_df.to_excel(writer, sheet_name='汇总', index=False)
end = time.perf_counter()
elapsed = end - start
print(f"代码运行耗时: {elapsed:.6f} 秒")
优化代码