import pandas as pd
import os

# 查找当前文件夹.xlsx文件
fullpath = [f for f in os.listdir('.') if not f.startswith("~$") and f.endswith(".xlsx")]

# 读取表格信息
sheet2 = pd.read_excel(fullpath,sheet_name='Sheet2')
sheet3 = pd.read_excel(fullpath,sheet_name='Sheet3')

# 提取工单号和产品料号的对应关系，并进行去重
order_material = sheet3[['工单','产品料号']].drop_duplicates()

# 汇总每个料号的入库数量，重新排序
material_summary = sheet2.groupby('产品料号')['入库数量'].sum().reset_index()

# 合并两个表，并按产品料号排序
result = pd.merge(order_material,material_summary,on='产品料号',how='right')

# 重置索引
result = result.sort_values('产品料号').reset_index(drop=True)

# 重命名列名
result.columns = ['工单号','产品料号','入库数量']

# 将结果追加保存到新表
with pd.ExcelWriter(fullpath,mode='a') as writer:
    result.to_excel(writer,sheet_name='Sheet4',index=False)