#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
生成出货单Excel表格
"""

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

# 数据
items = [
    ("ZD260429050001", 0.528, 204), ("ZD260429050002", 0.578, 204), ("ZD260429050003", 0.628, 204),
    ("ZD260429050004", 0.678, 204), ("ZD260429050005", 0.728, 204), ("ZD260429050006", 0.778, 204),
    ("ZD260429050007", 0.828, 204), ("ZD260429050008", 0.878, 204), ("ZD260429050009", 0.928, 204),
    ("ZD260429050010", 0.978, 204), ("ZD260429050011", 1.028, 204), ("ZD260429050012", 1.078, 204),
    ("ZD260429050013", 1.128, 204), ("ZD260429050014", 1.178, 204), ("ZD260429050015", 1.228, 204),
    ("ZD260429050016", 1.278, 204), ("ZD260429050017", 1.328, 204), ("ZD260429050018", 1.378, 204),
    ("ZD260429050019", 1.428, 204), ("ZD260429050020", 1.478, 204), ("ZD260429050021", 1.528, 614),
    ("ZD260429050022", 2.028, 614), ("ZD260429050023", 3.000, 614)
]

# 创建工作簿
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "出货单数据"

# 定义样式
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
header_font = Font(bold=True, color="FFFFFF", size=11)
title_font = Font(bold=True, size=14)
summary_fill = PatternFill(start_color="FFF2CC", end_color="FFF2CC", fill_type="solid")
border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

# 标题
ws.merge_cells('A1:D1')
ws['A1'] = "智耀星空出货单 - 长度与数量汇总"
ws['A1'].font = title_font
ws['A1'].alignment = Alignment(horizontal='center', vertical='center')
ws.row_dimensions[1].height = 25

# 单据信息
ws['A2'] = "单号:"
ws['B2'] = "CK26053015"
ws['C2'] = "日期:"
ws['D2'] = "2026-05-30"
ws['A3'] = "客户:"
ws['B3'] = "智耀星空"
ws['C3'] = "型号:"
ws['D3'] = "ZH-10X10SJ"

for row in [2, 3]:
    ws[f'A{row}'].font = Font(bold=True)
    ws[f'C{row}'].font = Font(bold=True)

# 表头
headers = ['序号', '项目编号', '长度(m)', '数量(支)']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=5, column=col, value=header)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.border = border

ws.row_dimensions[5].height = 22

# 数据行
for idx, (code, length, qty) in enumerate(items, 1):
    row = idx + 5
    
    # 序号
    cell = ws.cell(row=row, column=1, value=idx)
    cell.alignment = Alignment(horizontal='center')
    cell.border = border
    
    # 项目编号
    cell = ws.cell(row=row, column=2, value=code)
    cell.alignment = Alignment(horizontal='left')
    cell.border = border
    
    # 长度
    cell = ws.cell(row=row, column=3, value=length)
    cell.number_format = '0.000'
    cell.alignment = Alignment(horizontal='center')
    cell.border = border
    
    # 数量
    cell = ws.cell(row=row, column=4, value=qty)
    cell.alignment = Alignment(horizontal='center')
    cell.border = border
    
    # 高亮614数量的行
    if qty == 614:
        for col in range(1, 5):
            ws.cell(row=row, column=col).fill = PatternFill(start_color="FCE4D6", end_color="FCE4D6", fill_type="solid")

# 空行
empty_row = len(items) + 6

# 汇总区域
summary_start = empty_row + 1
ws.merge_cells(f'A{summary_start}:D{summary_start}')
ws[f'A{summary_start}'] = "汇总统计"
ws[f'A{summary_start}'].font = Font(bold=True, size=12)
ws[f'A{summary_start}'].alignment = Alignment(horizontal='center')
ws[f'A{summary_start}'].fill = summary_fill

# 汇总数据
summary_data = [
    ("总项数", "23", "总数量", "5,922 支"),
    ("204支规格", "20 项", "614支规格", "3 项"),
]

for i, (label1, val1, label2, val2) in enumerate(summary_data):
    row = summary_start + 1 + i
    ws[f'A{row}'] = label1
    ws[f'B{row}'] = val1
    ws[f'C{row}'] = label2
    ws[f'D{row}'] = val2
    ws[f'A{row}'].font = Font(bold=True)
    ws[f'C{row}'].font = Font(bold=True)
    ws[f'B{row}'].font = Font(bold=True, color="4472C4")
    ws[f'D{row}'].font = Font(bold=True, color="4472C4")

# 设置列宽
ws.column_dimensions['A'].width = 8
ws.column_dimensions['B'].width = 22
ws.column_dimensions['C'].width = 12
ws.column_dimensions['D'].width = 12

# 保存文件
output_path = "/mnt/data/we-smart/users/limaolin/exports/出货单_CK26053015_长度数量汇总.xlsx"
wb.save(output_path)
print(f"Excel文件已保存: {output_path}")