上周五，生产的同事在进行生产计划时，提出了希望有个工具可以进行 MRP，由于目前公司处在起步阶段，虽然有ERP，也只是使用了该系统的财务部分的模块，仓库以及采购等业务实际都由相关同事以 Excel 进行了大量的比对和管理，对于 MRP 确实需要一个工具。
构想1：采用网页上传 Bom 表格和库存表格，转存进数据库，再进行计算，虽然用户体验稍微好一些，但对于开发而言会需要更多的时间，以及额外的部署开销；
构想2：写一个桌面应用，桌面应用比如 WPF 这个也非常耗时啊，思来想去开发一个控制台应用足够了，Python 去开发，然后再打包编译成一个可执行的 exe；
构想3：完整的开发一个集成库存管理、物料清册、采购与供应商管理、生产管理…不能再想了，太多内容，系统需要有人去使用，公司缺的不是系统，它还不是需要全套系统的阶段。
构想2显得更实际，也符合简单原则，把一个复杂的问题简单解决，这就是最佳的选择。
项目使用了 openpyxl 包来读取和写入 Excel，使用 auto-py-to-exe 打包成 exe 文件，项目目录如下图：excel 目录用于添加以 bom 开头的 Bom 表以及以 w 开头的库存表，result 目录用于输出 MRP 结果，output 是编译成控制台应用的目录；
Bom 表模板只需要两列(型号、用量)，其实应该使用物料编码的，用量是单套用量或者是生产的套数，我在控制台中加入了可输入的 Input，如果 Bom 是单套用量，那在 Input 中要输入生产的套数，或者输入 1，由于表格有表头，所以代码取数从第 2 行开始。
库存表格其实只需要型号和数量，同事提供的表格内容较多，我在代码中定位了型号所在的列 6 以及库存所在的列 20，原本考虑复杂场景应该还有多仓库的概念，额…稍微复杂一些，暂时不往代码里面加逻辑，下图中增加T列是因为 R 列数据由公式计算得来，它不是 int 类。
项目可实现读取指定目录下的多份 Bom，以及一份库存表，再依次对单份 Bom 所需物料进行库存校验，然后虚拟库存占用，再进行下一份的 Bom 所需物料校验，然后将结果输出给不同的 sheet。
完整代码如下：
import openpyxl
import os
# 获取表格目录下有多少个表格，形成文件名的 List
files = os.listdir("excel")
# 获取表格目录下所有的 Bom 表，形成文件名的 List
bom_files = [f for f in files if f.startswith('bom') and f.endswith('.xlsx')] # 获取前缀为 bom 的表格
# 获取表格目录下所有的库存表，形成文件名 List
material_inventory_files = [f for f in files if f.startswith('w') and f.endswith('.xlsx')] # 获取前缀为 w 的表格
# 读取 Bom 表
def get_bom(bn: str, su: int):
"""
获取 Bom Excel 表中的物料数据
:param su: 套数，用于乘以 Bom 表物料的一套用量
:param bn: Bom Excel 表的名称
:return: 返回 Bom Excel 的物料和需求量字典
"""
return read_excel(bn, False, su)
# 读取库存表
def get_material_inventory(mn: str):
"""
获取库存 Excel 表中的物料数据
:param mn: 库存 Excel 表的名称
:return: 返回库存 Excel 的物料和库存量字典
"""
return read_excel(mn, True)
# 公用读取表格函数
def read_excel(excel: str, stock: bool = False, suite: int = 1):
"""
公用的读取 Excel 表方法
:param suite: Bom 表中的一套用量需要乘的套数
:param stock: 一个 bool 参数，用于判断是读取库存还是读取 Bom，由于数据列不同
:param excel: 需要读取的 Excel 表的名称
:return: 返回一个 Bom 或库存的字典数据
"""
workbook = openpyxl.load_workbook(f'excel/{excel}')
sheet = workbook.active
data = {}
if stock:
for row in range(2, sheet.max_row + 1):
cell_1 = sheet.cell(row, 6).value
cell_2 = sheet.cell(row, 20).value
data.update({cell_1: cell_2})
else:
for row in range(2, sheet.max_row + 1):
cell_1 = sheet.cell(row, 1).value
cell_2 = sheet.cell(row, 2).value * suite
data.update({cell_1: cell_2})
return data
# 比对需求物料和库存并输出结果
def calculator(bd: dict, sd: dict):
"""
计算 Bom 用量需求和库存的 MRP
:param sd: 库存字典
:param bd: Bom 需求量字典
:return: 返回一个 MRP 结果的列表
"""
res = []
for bk, bv in bd.items():
if bk in sd:
if bv > sd[bk]:
shortage = bv - sd[bk]
row = {'type': bk, 'dosage': bv, 'inventory': sd[bk], 'shortage': shortage, 'remain': 0}
res.append(row)
sd[bk] = 0
else:
remain = sd[bk] - bv
row = {'type': bk, 'dosage': bv, 'inventory': sd[bk], 'shortage': 0, 'remain': remain}
res.append(row)
sd[bk] = remain
else:
row = {'type': bk, 'dosage': bv, 'inventory': 0, 'shortage': bv, 'remain': 0}
res.append(row)
return res
# 生成结果
def generate_result(sheet: str, mrp: list):
"""
将 MRP 结果输出到 Excel
:param sheet: 按 Bom 名称给 sheet 命名
:param mrp: MRP 的结果列表
:return: 输出 Excel，无返回值
"""
_sheet = wb.create_sheet(sheet)
_sheet['A1'] = '型号'
_sheet['B1'] = '用量'
_sheet['C1'] = '库存'
_sheet['D1'] = '欠缺'
_sheet['E1'] = '剩余'
for row in mrp:
_sheet.append([row['type'], row['dosage'], row['inventory'], row['shortage'], row['remain']])
# 库存的内存变量
material_inventory = {}
# 公用的结果对象
wb = openpyxl.Workbook()
if __name__ == '__main__':
_bwl = {}
_mwl = {}
if len(bom_files) > 1:
while True:
for w in material_inventory_files:
_mwl = get_material_inventory(w)
material_inventory = _mwl
break
for b in bom_files:
while True:
try:
su = int(input(f"请输入 {b} 生产数量，只能是整数："))
break
except ValueError:
print("输入错误，请重新输入整数")
_bwl = get_bom(b, su)
generate_result(b, calculator(_bwl, _mwl))
print(f"{b} MRP 运算结束")
wb.save('result/res.xlsx') # 保存输出到 Excel
break
else:
print("未检测到 Bom 表格，请确保 excel 目录下有以 bom 开头的 Excel 表格")
input("按任意键结束...")