# -*- coding: utf-8 -*- """Build the on-site confirmation workbook for 佰奥莱博 SKU change (基灵西安抬头).""" import os from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, Protection from openpyxl.worksheet.datavalidation import DataValidation from openpyxl.formatting.rule import CellIsRule from openpyxl.worksheet.properties import PageSetupProperties from openpyxl.worksheet.pagebreak import Break from openpyxl.drawing.image import Image as XLImage from PIL import Image ROOT = os.path.expanduser('~/Desktop/Hermes输出-工作类') OUT_DIR = os.path.join(ROOT, '对外物料', '佰奥莱博货号变更_20261104') OUT = os.path.join(OUT_DIR, '佰奥莱博货号变更执行_基灵西安抬头专用_现场确认表_20261105_V01.xlsx') LOGO = os.path.expanduser('~/work/公司资料/logo_transparent.png') LOGO_WHITE = '/tmp/biori_logo_white_bg_confirmation.png' # Brand colours NAVY = '0F3D6E' TEAL = '0E9F9A' LIGHT_BLUE = 'EAF3FB' PALE_BLUE = 'F6F9FD' PALE_YELLOW = 'FFF2CC' GREEN_FILL = PatternFill('solid', fgColor='E2EFDA') AMBER_FILL = PatternFill('solid', fgColor='FCE4D6') RED_FILL = PatternFill('solid', fgColor='F8CBAD') WHITE = 'FFFFFF' GRAY = '64748B' BORDER_CLR = 'B7C9DD' thin = Side(style='thin', color=BORDER_CLR) medium = Side(style='medium', color=NAVY) BORDER = Border(left=thin, right=thin, top=thin, bottom=thin) def font(size=9, bold=False, color='1F2937', name='宋体'): return Font(name=name, size=size, bold=bold, color=color) def fill(hex_color): return PatternFill('solid', fgColor=hex_color) def align(wrap=True, horizontal='left', vertical='center'): return Alignment(wrap_text=wrap, horizontal=horizontal, vertical=vertical) def put(ws, row, col, value, size=9, bold=False, color='1F2937', bg=None, horizontal='left', vertical='center', wrap=True, border=True, name='宋体'): c = ws.cell(row=row, column=col, value=value) c.font = font(size=size, bold=bold, color=color, name=name) c.alignment = align(wrap=wrap, horizontal=horizontal, vertical=vertical) if bg: c.fill = fill(bg) if border: c.border = BORDER return c def merge_put(ws, r1, c1, r2, c2, value, **kwargs): ws.merge_cells(start_row=r1, start_column=c1, end_row=r2, end_column=c2) for r in range(r1, r2 + 1): for c in range(c1, c2 + 1): cell = ws.cell(row=r, column=c) if kwargs.get('border', True): cell.border = BORDER if kwargs.get('bg'): cell.fill = fill(kwargs['bg']) c = put(ws, r1, c1, value, **kwargs) return c def band(ws, row, ncols, text, size=10, bg=NAVY, color=WHITE): ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=ncols) for c in range(1, ncols + 1): cell = ws.cell(row=row, column=c) cell.fill = fill(bg) cell.border = BORDER cell = ws.cell(row=row, column=1, value=text) cell.font = font(size=size, bold=True, color=color) cell.alignment = align(horizontal='left', vertical='center') ws.row_dimensions[row].height = 24 def table_header(ws, row, headers, widths=None, size=8.2): for i, h in enumerate(headers, 1): c = put(ws, row, i, h, size=size, bold=True, color=WHITE, bg=NAVY, horizontal='center', vertical='center') c.border = Border(left=thin, right=thin, top=thin, bottom=medium) ws.row_dimensions[row].height = 34 if widths: for i, w in enumerate(widths, 1): ws.column_dimensions[chr(64 + i)].width = w def input_cell(ws, row, col, value='', size=8.8, horizontal='center'): c = put(ws, row, col, value, size=size, bg=PALE_YELLOW, horizontal=horizontal, vertical='center') c.protection = Protection(locked=False) return c def add_confirmation_validation(ws, rng, allow_blank=True): dv = DataValidation(type='list', formula1='"确认,需调整,不确认,待确认"', allow_blank=allow_blank) dv.error = '请选择：确认 / 需调整 / 不确认 / 待确认' dv.errorTitle = '客户确认' dv.prompt = '请选择客户对本项的实际确认结果' dv.promptTitle = '客户确认' ws.add_data_validation(dv) dv.add(rng) # Conditional formatting: green / amber / red from openpyxl.formatting.rule import FormulaRule ws.conditional_formatting.add(rng, FormulaRule(formula=[f'{rng.split(":")[0]}="确认"'], fill=GREEN_FILL)) ws.conditional_formatting.add(rng, FormulaRule(formula=[f'{rng.split(":")[0]}="需调整"'], fill=AMBER_FILL)) ws.conditional_formatting.add(rng, FormulaRule(formula=[f'{rng.split(":")[0]}="待确认"'], fill=AMBER_FILL)) ws.conditional_formatting.add(rng, FormulaRule(formula=[f'{rng.split(":")[0]}="不确认"'], fill=RED_FILL)) def add_consistency_validation(ws, rng): dv = DataValidation(type='list', formula1='"一致,不一致,待核"', allow_blank=True) dv.error = '请选择：一致 / 不一致 / 待核' dv.errorTitle = '核对结果' ws.add_data_validation(dv) dv.add(rng) from openpyxl.formatting.rule import FormulaRule ws.conditional_formatting.add(rng, FormulaRule(formula=[f'{rng.split(":")[0]}="一致"'], fill=GREEN_FILL)) ws.conditional_formatting.add(rng, FormulaRule(formula=[f'{rng.split(":")[0]}="不一致"'], fill=RED_FILL)) ws.conditional_formatting.add(rng, FormulaRule(formula=[f'{rng.split(":")[0]}="待核"'], fill=AMBER_FILL)) def page_setup(ws, print_area, title_rows='1:6', fit_height=2): ws.sheet_view.showGridLines = False ws.sheet_view.zoomScale = 85 ws.page_setup.orientation = 'landscape' ws.page_setup.paperSize = ws.PAPERSIZE_A4 ws.page_setup.fitToWidth = 1 ws.page_setup.fitToHeight = fit_height ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True) ws.page_margins.left = 0.25 ws.page_margins.right = 0.25 ws.page_margins.top = 0.45 ws.page_margins.bottom = 0.45 ws.page_margins.header = 0.18 ws.page_margins.footer = 0.18 ws.print_options.horizontalCentered = True ws.print_area = print_area ws.print_title_rows = title_rows ws.oddHeader.left.text = '宝锐生物 BIORI BIOTECH' ws.oddHeader.left.size = 8 ws.oddHeader.left.color = '0F3D6E' ws.oddHeader.center.text = '佰奥莱博货号变更执行 · 基灵西安抬头专用 · 现场确认表' ws.oddHeader.center.size = 8 ws.oddHeader.center.color = '0F3D6E' ws.oddHeader.right.text = 'V01 / 2026-11-05' ws.oddHeader.right.size = 8 ws.oddHeader.right.color = '64748B' ws.oddFooter.left.text = '守护生命健康，缔造美好生活' ws.oddFooter.left.size = 8 ws.oddFooter.left.color = '0F3D6E' ws.oddFooter.center.text = '第 &P 页 / 共 &N 页' ws.oddFooter.center.size = 8 ws.oddFooter.center.color = '64748B' ws.oddFooter.right.text = '客户签字版' ws.oddFooter.right.size = 8 ws.oddFooter.right.color = '64748B' def make_logo(): img = Image.open(LOGO).convert('RGBA') bg = Image.new('RGB', img.size, (255, 255, 255)) bg.paste(img, mask=img.split()[3]) bg.save(LOGO_WHITE, 'PNG') PRODUCTS = [ { 'kit': 'FM513-007701-5P', 'actual_sku': 'FMD5084-000901\n+ 4×冻干保护剂-9', 'actual_name': '5×Fast Direct RT Premix-UNG(Probe qRT-PCR) (DG) (000901)', 'label_name': '5×Neoscript RT Premix(DG)(Probe qRT-PCR)', 'coa_actual': '批号：主酶（FMD5084-000901）-1 组分批次（现场填）\nCOA货号：FM513-007701-5P\nCOA品名：套件品名', 'coa_label': '佰奥莱博专用COA（新增）\n批号：同实际\n货号：FM513-007701-5P\n品名：套件品名', 'manual_actual': '按 FM513-007701-5P 说明书执行\n（佰奥莱博专用说明书，新增）', 'manual_label': 'FM513-007701-5P 套件说明书\n（佰奥莱博专用）', 'main_enzyme': 'FMD5084-000901', 'batch_note': '取 FMD5084-000901-1 组分批号', }, { 'kit': 'FM507-007701', 'actual_sku': 'FMD2011', 'actual_name': '2×Superstart Direct Premix-UNG (Probe qPCR) (DG)', 'label_name': '2×Superstart Direct Premix-UNG (Probe qPCR) (DG)', 'coa_actual': '批号：主酶（FMD2011）批次（按 -1 组分规则，现场填）\nCOA货号：FM507-007701\nCOA品名：主酶品名', 'coa_label': '佰奥莱博专用COA（新增）\n批号：同实际\n货号：FM507-007701\n品名：主酶品名', 'manual_actual': '按 FM507-007701 说明书执行\n（佰奥莱博专用说明书，新增）', 'manual_label': 'FM507-007701 套件说明书\n（佰奥莱博专用）', 'main_enzyme': 'FMD2011', 'batch_note': '单组分按主酶批号填写', }, { 'kit': 'FM513-007702-FP', 'actual_sku': 'FM5144\n+ 4×冻干保护剂-9', 'actual_name': '5×FastAmpli RT Premix-UNG (Probe qRT-PCR) (DG)', 'label_name': '5×Neoscript RT Premix (DG)(Probe qRT-PCR)', 'coa_actual': '批号：主酶（FM5144）-1 组分批次（现场填）\nCOA货号：FM513-007702-FP\nCOA品名：套件品名', 'coa_label': '佰奥莱博专用COA（新增）\n批号：同实际\n货号：FM513-007702-FP\n品名：套件品名', 'manual_actual': '按 FM513-007702-FP 说明书执行\n（佰奥莱博专用说明书，新增）', 'manual_label': 'FM513-007702-FP 套件说明书\n（佰奥莱博专用）', 'main_enzyme': 'FM5144', 'batch_note': '取 FM5144-1 组分批号', }, ] def build_main_sheet(wb): ws = wb.active ws.title = '01_现场确认表' ws.sheet_properties.tabColor = NAVY widths = [4.5, 18, 24, 18, 30, 30, 12, 22] for i, w in enumerate(widths, 1): ws.column_dimensions[chr(64 + i)].width = w # Header block ws.row_dimensions[1].height = 40 ws.row_dimensions[2].height = 26 ws.row_dimensions[3].height = 20 ws.merge_cells('B1:H1') ws.merge_cells('A1:A2') if os.path.exists(LOGO_WHITE): img = XLImage(LOGO_WHITE) img.width = 95 img.height = 28 ws.add_image(img, 'A1') put(ws, 1, 2, '佰奥莱博货号变更执行 · 现场确认表', size=16, bold=True, color=NAVY, bg=WHITE, border=False) ws.merge_cells('B2:H2') put(ws, 2, 2, '基灵（西安）生物科技有限公司 · 客户签字版', size=10, bold=True, color=WHITE, bg=TEAL, border=False) ws.merge_cells('A3:H3') put(ws, 3, 1, '适用范围：佰奥莱博货号变更执行；本表仅覆盖下列 3 个套件货号，不涉及其他货号或其他客户。', size=8.5, color='374151', bg=LIGHT_BLUE, border=False) # Info grid labels = [ (4, 1, '客户抬头', 4, 2, 'B4:C4', '基灵（西安）生物科技有限公司', 4, 4, '客户/集团', 4, 5, 'E4:F4', '佰奥莱博集团', 4, 7, '确认日期', 4, 8, None, ''), ] # Row 4 ws.merge_cells('B4:C4'); ws.merge_cells('E4:F4') put(ws, 4, 1, '客户抬头', size=8.5, bold=True, bg=PALE_BLUE) put(ws, 4, 2, '基灵（西安）生物科技有限公司', size=8.8, bg=PALE_YELLOW) put(ws, 4, 4, '客户/集团', size=8.5, bold=True, bg=PALE_BLUE) put(ws, 4, 5, '佰奥莱博集团', size=8.8, bg=PALE_YELLOW) put(ws, 4, 7, '确认日期', size=8.5, bold=True, bg=PALE_BLUE) input_cell(ws, 4, 8, '') # Row 5 ws.merge_cells('B5:D5'); ws.merge_cells('F5:H5') put(ws, 5, 1, '变更依据', size=8.5, bold=True, bg=PALE_BLUE) put(ws, 5, 2, '佰奥莱博货号变更执行（基灵西安抬头专用）', size=8.8, bg=PALE_YELLOW) put(ws, 5, 5, '适用货号', size=8.5, bold=True, bg=PALE_BLUE) put(ws, 5, 6, 'FM513-007701-5P / FM507-007701 / FM513-007702-FP', size=8.5, bg=PALE_YELLOW) # Row 6 ws.merge_cells('B6:C6'); ws.merge_cells('E6:F6') put(ws, 6, 1, '我方参会', size=8.5, bold=True, bg=PALE_BLUE) input_cell(ws, 6, 2, '') put(ws, 6, 4, '客户参会', size=8.5, bold=True, bg=PALE_BLUE) input_cell(ws, 6, 5, '') put(ws, 6, 7, '版本/日期', size=8.5, bold=True, bg=PALE_BLUE) put(ws, 6, 8, 'V01 / 2026-11-05', size=8.5) # Section 1: SKU / name band(ws, 8, 8, '一、货号与品名：实际执行 vs 标签/对外', size=10) table_header(ws, 9, ['序号', '套件货号（对客）', '实际货号（生产/实物）', '标签货号', '实际品名（主酶/配方）', '标签品名', '客户确认', '客户备注'], widths) for i, p in enumerate(PRODUCTS, 1): r = 9 + i put(ws, r, 1, str(i), horizontal='center') put(ws, r, 2, p['kit'], bold=True, color=NAVY, name='Times New Roman') put(ws, r, 3, p['actual_sku'], name='Times New Roman') put(ws, r, 4, p['kit'], name='Times New Roman') put(ws, r, 5, p['actual_name'], name='Times New Roman') put(ws, r, 6, p['label_name'], name='Times New Roman') input_cell(ws, r, 7, '') input_cell(ws, r, 8, '') ws.row_dimensions[r].height = 52 add_confirmation_validation(ws, 'G10:G12') # Section 2: COA / manual band(ws, 14, 8, '二、COA 与说明书：实际执行 vs 标签/对外', size=10) table_header(ws, 15, ['序号', '套件货号（对客）', '实际 COA（批号来源/货号/品名）', '标签/对外 COA', '实际说明书', '标签/对外说明书', '客户确认', '客户备注'], widths) for i, p in enumerate(PRODUCTS, 1): r = 15 + i put(ws, r, 1, str(i), horizontal='center') put(ws, r, 2, p['kit'], bold=True, color=NAVY, name='Times New Roman') put(ws, r, 3, p['coa_actual'], size=8.2) put(ws, r, 4, p['coa_label'], size=8.2) put(ws, r, 5, p['manual_actual'], size=8.2) put(ws, r, 6, p['manual_label'], size=8.2) input_cell(ws, r, 7, '') input_cell(ws, r, 8, '') ws.row_dimensions[r].height = 68 add_confirmation_validation(ws, 'G16:G18') # Section 3: conclusion / signature band(ws, 20, 8, '三、客户结论与签字', size=10) ws.merge_cells('B21:H21') put(ws, 21, 1, '客户结论', size=8.8, bold=True, bg=PALE_BLUE) put(ws, 21, 2, '□ 全部确认，按变更后执行 □ 部分确认，按备注调整 □ 不确认，维持原执行 □ 待内部评审', size=8.8, bg=PALE_YELLOW) ws.row_dimensions[21].height = 28 put(ws, 22, 1, '差异/调整要求', size=8.8, bold=True, bg=PALE_BLUE) ws.merge_cells('B22:H23') input_cell(ws, 22, 2, '') ws.row_dimensions[22].height = 22 ws.row_dimensions[23].height = 22 # signature row 24 ws.merge_cells('B24:C24'); ws.merge_cells('E24:F24') put(ws, 24, 1, '客户确认人', size=8.8, bold=True, bg=PALE_BLUE) input_cell(ws, 24, 2, '') put(ws, 24, 4, '职务', size=8.8, bold=True, bg=PALE_BLUE) input_cell(ws, 24, 5, '') put(ws, 24, 7, '日期', size=8.8, bold=True, bg=PALE_BLUE) input_cell(ws, 24, 8, '') # signature row 25 ws.merge_cells('B25:C25'); ws.merge_cells('E25:F25') put(ws, 25, 1, '宝锐参会人', size=8.8, bold=True, bg=PALE_BLUE) input_cell(ws, 25, 2, '') put(ws, 25, 4, '日期', size=8.8, bold=True, bg=PALE_BLUE) input_cell(ws, 25, 5, '') put(ws, 25, 7, '客户盖章', size=8.8, bold=True, bg=PALE_BLUE) input_cell(ws, 25, 8, '') for r in (24, 25): ws.row_dimensions[r].height = 28 ws.merge_cells('A26:H26') put(ws, 26, 1, '说明：本表一式两份，双方各执一份；扫描件与原件等效。实际批号以现场实物/COA 为准，本表不预填批号。', size=8, color=GRAY, bg=PALE_BLUE, border=False) ws.row_dimensions[26].height = 22 # Keep the tables on page 1 and the conclusion/signature block on page 2. ws.row_breaks.append(Break(id=19)) page_setup(ws, 'A1:H26', '1:6', fit_height=2) ws.print_title_rows = '1:6' return ws def build_coa_sheet(wb): ws = wb.create_sheet('02_COA与执行确认') ws.sheet_properties.tabColor = TEAL widths = [4.5, 18, 18, 26, 26, 14, 14, 22] for i, w in enumerate(widths, 1): ws.column_dimensions[chr(64 + i)].width = w ws.merge_cells('A1:H1') put(ws, 1, 1, 'COA 批号现场核对', size=14, bold=True, color=WHITE, bg=NAVY) ws.row_dimensions[1].height = 30 ws.merge_cells('A2:H2') put(ws, 2, 1, '客户抬头：基灵（西安）生物科技有限公司 ｜ 适用：佰奥莱博货号变更执行 ｜ 本页用于现场填写批号，不预填。', size=8.5, color='374151', bg=LIGHT_BLUE) ws.row_dimensions[2].height = 22 table_header(ws, 3, ['序号', '套件货号（对客）', '主酶货号（实际）', '主酶 -1 组分批号（现场填）', 'COA 显示批号（现场填）', '是否一致', '客户确认', '客户备注'], widths) for i, p in enumerate(PRODUCTS, 1): r = 3 + i put(ws, r, 1, str(i), horizontal='center') put(ws, r, 2, p['kit'], bold=True, color=NAVY, name='Times New Roman') put(ws, r, 3, p['main_enzyme'], name='Times New Roman') input_cell(ws, r, 4, p['batch_note']) input_cell(ws, r, 5, '') input_cell(ws, r, 6, '') input_cell(ws, r, 7, '') input_cell(ws, r, 8, '') ws.row_dimensions[r].height = 40 add_consistency_validation(ws, 'F4:F6') add_confirmation_validation(ws, 'G4:G6') # Execution confirmation table ws.merge_cells('A9:H9') put(ws, 9, 1, '变更执行口径确认', size=12, bold=True, color=WHITE, bg=NAVY) ws.row_dimensions[9].height = 26 ws.merge_cells('A10:H10') put(ws, 10, 1, '请客户逐项确认；如不确认，请在备注写明实际看到的内容和期望执行方式。', size=8.5, color='374151', bg=LIGHT_BLUE) ws.row_dimensions[10].height = 20 # merge columns for wide text: B-C item, D-G execution, H confirm? Let's use 1..8 with merges # Header: A序号, B确认事项, C-F执行口径, G客户确认, H客户备注 table_header(ws, 11, ['序号', '确认事项', '执行口径（宝锐）', '', '', '', '客户确认', '客户备注'], widths) ws.merge_cells('C11:F11') put(ws, 11, 3, '执行口径（宝锐）', size=8.2, bold=True, color=WHITE, bg=NAVY, horizontal='center') for c in range(4, 7): ws.cell(row=11, column=c).fill = fill(NAVY) ws.cell(row=11, column=c).border = BORDER actions = [ ('实物配制配方', 'FM513-007701-5P：FMD5084-000901 + 4×冻干保护剂-9；\n' 'FM507-007701：FMD2011；\n' 'FM513-007702-FP：FM5144 + 4×冻干保护剂-9。'), ('标签货号', '按套件货号打印：FM513-007701-5P / FM507-007701 / FM513-007702-FP；\n' '实际主酶货号不打印在客户标签上。'), ('标签品名', 'FM507-007701 用主酶品名；\n' 'FM513-007701-5P、FM513-007702-FP 用套件品名。'), ('COA 批号', '新增 3 个佰奥莱博专用 COA；\n' '批号套用主酶 -1 组分批次，不含各组分信息；\n' 'COA 货号用套件货号。'), ('COA 品名', 'FM507-007701 用主酶品名；\n' 'FM513-007701-5P、FM513-007702-FP 用套件品名。'), ('说明书', '新增 3 个佰奥莱博专用说明书；\n' '随货按对应套件说明书执行。'), ('生效规则', '变更通过后 +1 批；\n' '以质量放行批次为准。'), ('适用范围', '仅限上述 3 个货号、基灵（西安）抬头；\n' '不涉及其他货号/客户。'), ] for i, (item, detail) in enumerate(actions, 1): r = 11 + i put(ws, r, 1, str(i), horizontal='center') put(ws, r, 2, item, bold=True, bg=PALE_BLUE) ws.merge_cells(start_row=r, start_column=3, end_row=r, end_column=6) put(ws, r, 3, detail, size=8.4) for c in range(4, 7): ws.cell(row=r, column=c).border = BORDER input_cell(ws, r, 7, '') input_cell(ws, r, 8, '') ws.row_dimensions[r].height = 44 add_confirmation_validation(ws, 'G12:G19') # signature / conclusion ws.merge_cells('A21:H21') put(ws, 21, 1, '本页客户结论：□ 全部确认 □ 部分确认（见备注） □ 不确认 □ 待内部评审', size=8.8, bold=True, bg=PALE_YELLOW) ws.row_dimensions[21].height = 26 ws.merge_cells('A22:D22'); ws.merge_cells('E22:H22') put(ws, 22, 1, '客户确认人（签字）：________________', size=8.8) put(ws, 22, 5, '日期：________________', size=8.8) ws.merge_cells('A23:D23'); ws.merge_cells('E23:H23') put(ws, 23, 1, '宝锐参会人（签字）：________________', size=8.8) put(ws, 23, 5, '日期：________________', size=8.8) for r in (22, 23): ws.row_dimensions[r].height = 26 page_setup(ws, 'A1:H23', '1:3', fit_height=1) return ws def build_notes_sheet(wb): ws = wb.create_sheet('03_填写说明') ws.sheet_properties.tabColor = GRAY ws.sheet_view.showGridLines = False ws.column_dimensions['A'].width = 4 ws.column_dimensions['B'].width = 115 ws.merge_cells('A1:B1') put(ws, 1, 1, '填写说明与执行口径', size=14, bold=True, color=WHITE, bg=NAVY) ws.row_dimensions[1].height = 30 notes = [ ('01', '本表用于现场核对，不替代质量协议、变更审批或放行文件。'), ('02', '实际货号 = 生产/实物使用的主酶或配方货号；标签货号 = 客户签收时看到的套件货号。两者不是同一个编码体系。'), ('03', '批号以现场实物和 COA 为准，表中不预填；请客户核对后填写“主酶 -1 组分批号”和“COA 显示批号”。'), ('04', 'COA 批号规则：3 个产品均套用主酶 -1 组分批次，不含各组分信息；COA 货号用套件货号。'), ('05', 'COA 品名规则：FM507-007701 用主酶品名；FM513-007701-5P、FM513-007702-FP 用套件品名。'), ('06', '标签品名规则：FM507-007701 用主酶品名；两个 FM513 套件用套件品名。'), ('07', '说明书：新增 3 个佰奥莱博专用说明书；随货按对应套件说明书执行。'), ('08', '生效规则：变更通过后 +1 批，以质量放行批次为准。'), ('09', '适用范围：仅限佰奥莱博货号变更（基灵西安抬头）；不涉及其他客户。'), ('10', '签署：客户确认人签字 + 日期；如部分确认，必须在备注写清“哪个货号、哪个维度、实际看到什么、期望怎么执行”。'), ('11', '份数：一式两份，双方各执一份；扫描件与原件等效。'), ] r = 3 for no, text in notes: put(ws, r, 1, no, size=8.5, bold=True, color=TEAL, horizontal='center', bg=PALE_BLUE) put(ws, r, 2, text, size=9.2, bg=WHITE) ws.row_dimensions[r].height = 34 r += 1 ws.merge_cells(start_row=r + 1, start_column=1, end_row=r + 1, end_column=2) put(ws, r + 1, 1, '数据来源：佰奥莱博货号变更执行表（基灵西安抬头专用）+ 宝锐产品主数据 BOM。', size=8, color=GRAY, border=False) ws.page_setup.orientation = 'portrait' ws.page_setup.paperSize = ws.PAPERSIZE_A4 ws.page_setup.fitToWidth = 1 ws.page_setup.fitToHeight = 1 ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True) ws.print_area = f'A1:B{r+1}' return ws def main(): os.makedirs(OUT_DIR, exist_ok=True) make_logo() wb = Workbook() build_main_sheet(wb) build_coa_sheet(wb) build_notes_sheet(wb) wb.properties.title = '佰奥莱博货号变更执行 · 基灵西安抬头专用 · 现场确认表' wb.properties.subject = '客户现场确认表（货号/品名/COA/说明书）' wb.properties.creator = '宝锐生物 营销中心' wb.properties.description = 'V01 / 2026-11-05；内部及客户现场使用。' wb.save(OUT) print('WROTE', OUT, os.path.getsize(OUT)) print('sheets', wb.sheetnames) if __name__ == '__main__': main()