import openpyxl
from openpyxl.styles import Alignment, PatternFill, Font, Border, Side
from openpyxl.utils import get_column_letter

# 创建新的工作簿
wb = openpyxl.Workbook()

# ========== 1. 创建备料清单（模板）工作表 ==========
ws1 = wb.active
ws1.title = "备料清单（模板）"

# 设置列宽
column_widths = {
    'A': 8, 'B': 15, 'C': 25, 'D': 12, 'E': 12,
    'F': 12, 'G': 12, 'H': 12, 'I': 12
}
for col, width in column_widths.items():
    ws1.column_dimensions[col].width = width

# 设置行高
row_heights = {
    1: 30, 2: 25, 3: 25, 4: 25
}
for row, height in row_heights.items():
    ws1.row_dimensions[row].height = height

# 设置边框样式
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

# 设置颜色
blue_fill = PatternFill(start_color="B4C6E7", end_color="B4C6E7", fill_type="solid")
light_blue_fill = PatternFill(start_color="D9E1F2", end_color="D9E1F2", fill_type="solid")
green_fill = PatternFill(start_color="E2EFDA", end_color="E2EFDA", fill_type="solid")

# 创建标题行
ws1.merge_cells('A1:I1')
ws1['A1'] = '=D2&"-"&H2&"备料清单"'
ws1['A1'].alignment = Alignment(horizontal='center', vertical='center')
ws1['A1'].font = Font(bold=True, size=14)
ws1['A1'].fill = blue_fill
ws1['A1'].border = thin_border

# 第二行
ws1['A2'] = '成品料号：'
ws1['D2'] = '=TRIM(MID(SUBSTITUTE(工单BOM!A3,":",REPT(" ",100)),100,100))'
ws1['F2'] = '计划数量：'
ws1['H2'] = '=TRIM(MID(SUBSTITUTE(工单BOM!D4,":",REPT(" ",100)),100,100))'

for cell in ['A2', 'B2', 'C2', 'D2', 'E2', 'F2', 'G2', 'H2', 'I2']:
    ws1[cell].alignment = Alignment(horizontal='center', vertical='center')
    ws1[cell].font = Font(bold=True)
    ws1[cell].fill = light_blue_fill
    ws1[cell].border = thin_border

# 表头行（第三行）
headers = ['序号', '子件编码', '子件名称', '基本用量', '需求数量', 
           '库存数量', '预估损耗', '差异', '调拨数量']

for col_idx, header in enumerate(headers, 1):
    cell = ws1.cell(row=3, column=col_idx, value=header)
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.font = Font(bold=True)
    cell.fill = green_fill
    cell.border = thin_border

# 创建数据行（第4行到第169行）
for row in range(4, 170):
    row_num = row

    # 序号
    ws1.cell(row=row, column=1, value=row-3)

    # 子件编码
    ws1.cell(row=row, column=2, 
             value=f'=IF(ISBLANK(工单BOM!B{row+4}),"",工单BOM!B{row+4})')

    # 子件名称
    ws1.cell(row=row, column=3, 
             value=f'=IF(ISBLANK(工单BOM!C{row+4}),"",TRIM(MID(SUBSTITUTE(工单BOM!C{row+4},"/",REPT(" ",100)),1,100)))')

    # 基本用量
    ws1.cell(row=row, column=4, 
             value=f'=IF(ISBLANK(工单BOM!D{row+4}),"",工单BOM!D{row+4})')

    # 需求数量
    ws1.cell(row=row, column=5, 
             value=f'=IFERROR(D{row}*H$2,"")')

    # 库存数量
    ws1.cell(row=row, column=6, 
             value=f'=IFERROR(VLOOKUP(B{row},库存数量!B:E,4,0),0)')

    # 预估损耗
    ws1.cell(row=row, column=7, 
             value=f'=IF(LEFT(B{row},3)="413",0,IF(OR(LEFT(B{row},3)="401",LEFT(B{row},3)="402",LEFT(B{row},3)="404",LEFT(B{row},3)="405",LEFT(B{row},3)="406",LEFT(B{row},3)="407",LEFT(B{row},3)="408",LEFT(B{row},3)="409",LEFT(B{row},3)="410"),(E{row}*0.003+D{row}+50),(E{row}*0.003+10)))')

    # 差异
    ws1.cell(row=row, column=8, 
             value=f'=ROUNDUP(F{row}-E{row}-G{row},0)')

    # 调拨数量
    ws1.cell(row=row, column=9, 
             value=f'=IF(-H{row}<0,0,-H{row})')

    # 设置样式
    for col in range(1, 10):
        cell = ws1.cell(row=row, column=col)
        cell.alignment = Alignment(horizontal='center', vertical='center')
        cell.border = thin_border

# ========== 2. 创建工单BOM工作表 ==========
ws2 = wb.create_sheet("工单BOM")
# 这是空的工作表，但保持结构
ws2.sheet_view.showGridLines = False

# ========== 3. 创建库存数量工作表 ==========
ws3 = wb.create_sheet("库存数量")
# 这是空的工作表
ws3.sheet_view.showGridLines = False

# ========== 4. 创建发料清单工作表 ==========
ws4 = wb.create_sheet("发料清单")
# 这是空的工作表
ws4.sheet_view.showGridLines = False

# ========== 5. 创建料车位置工作表 ==========
ws5 = wb.create_sheet("料车位置")

# 设置列宽
column_widths_ws5 = {
    'A': 15, 'B': 15, 'C': 12, 'D': 2,
    'E': 15, 'F': 15, 'G': 12
}
for col, width in column_widths_ws5.items():
    ws5.column_dimensions[col].width = width

# 标题行
ws5['A1'] = '物料SN编码'
ws5['B1'] = '子件编码'
ws5['C1'] = '料车位置'
ws5['E1'] = '料车位置查询'

# 设置标题样式
for cell in ['A1', 'B1', 'C1', 'E1']:
    ws5[cell].alignment = Alignment(horizontal='center', vertical='center')
    ws5[cell].font = Font(bold=True)
    ws5[cell].fill = blue_fill
    ws5[cell].border = thin_border

# 查询表头
ws5['E3'] = '物料SN编码'
ws5['F3'] = '子件编码'
ws5['G3'] = '料车位置'
for cell in ['E3', 'F3', 'G3']:
    ws5[cell].alignment = Alignment(horizontal='center', vertical='center')
    ws5[cell].font = Font(bold=True)
    ws5[cell].fill = green_fill
    ws5[cell].border = thin_border

# 创建映射数据行（第2行到第121行）
for row in range(2, 122):
    # 子件编码公式
    ws5.cell(row=row, column=2, 
             value=f'=IFERROR(TRIM(MID(SUBSTITUTE(A{row},"|",REPT(" ",100)),1,100)),"")')

    # 料车位置（固定位置，这里简单示例）
    positions = ['A11', 'A12', 'A13', 'A14', 'A15', 'A21', 'A22', 'A23', 'A24', 'A25',
                 'A31', 'A32', 'A33', 'A34', 'A35', 'A41', 'A42', 'A43', 'A44', 'A45',
                 'A51', 'A52', 'A53', 'A54', 'A55', 'A61', 'A62', 'A63', 'A64', 'A65',
                 'B11', 'B12', 'B13', 'B14', 'B15', 'B21', 'B22', 'B23', 'B24', 'B25',
                 'B31', 'B32', 'B33', 'B34', 'B35', 'B41', 'B42', 'B43', 'B44', 'B45',
                 'B51', 'B52', 'B53', 'B54', 'B55', 'B61', 'B62', 'B63', 'B64', 'B65',
                 'C11', 'C12', 'C13', 'C14', 'C15', 'C21', 'C22', 'C23', 'C24', 'C25',
                 'C31', 'C32', 'C33', 'C34', 'C35', 'C41', 'C42', 'C43', 'C44', 'C45',
                 'C51', 'C52', 'C53', 'C54', 'C55', 'C61', 'C62', 'C63', 'C64', 'C65',
                 'D11', 'D12', 'D13', 'D14', 'D15', 'D21', 'D22', 'D23', 'D24', 'D25',
                 'D31', 'D32', 'D33', 'D34', 'D35', 'D41', 'D42', 'D43', 'D44', 'D45',
                 'D51', 'D52', 'D53', 'D54', 'D55', 'D61', 'D62', 'D63', 'D64', 'D65']

    if row-2 < len(positions):
        ws5.cell(row=row, column=3, value=positions[row-2])

# 创建查询区域（第4行到第121行）
for row in range(4, 122):
    # 物料SN编码（用户输入区）
    ws5.cell(row=row, column=5, value="")

    # 子件编码公式
    ws5.cell(row=row, column=6, 
             value=f'=IFERROR(TRIM(MID(SUBSTITUTE(E{row},"|",REPT(" ",100)),1,100)),"")')

    # 料车位置查询公式
    ws5.cell(row=row, column=7, 
             value=f'=IF(LEN(F{row})=10,VLOOKUP(F{row},B:C,2,0),"")')

# 设置数据区域样式
for row in range(2, 122):
    for col in [1, 2, 3]:
        cell = ws5.cell(row=row, column=col)
        cell.alignment = Alignment(horizontal='center', vertical='center')
        cell.border = thin_border

for row in range(4, 122):
    for col in [5, 6, 7]:
        cell = ws5.cell(row=row, column=col)
        cell.alignment = Alignment(horizontal='center', vertical='center')
        cell.border = thin_border

# 保存文件
filename = "备料清单.xlsx"
wb.save(filename)
print(f"Excel文件 '{filename}' 已创建成功！")
print("包含的工作表:")
for sheet in wb.sheetnames:
    print(f"  - {sheet}")