# -*- coding: utf-8 -*-
"""
Create TongMing Certificate Excel Template
Replicates the PDF certificate layout exactly.
"""

import openpyxl
from openpyxl import Workbook
from openpyxl.styles import (
    Font, Alignment, Border, Side, PatternFill, NamedStyle
)
from openpyxl.utils import get_column_letter
from copy import copy

wb = Workbook()
ws = wb.active
ws.title = "Certificate"

# ============================================================
# PAGE SETUP - Letter size, Portrait, margins
# ============================================================
ws.page_setup.paperSize = ws.PAPERSIZE_LETTER  # 8.5 x 11
ws.page_setup.orientation = 'portrait'
ws.page_margins.left = 1.5 / 2.54     # 1.5cm -> inches
ws.page_margins.right = 1.5 / 2.54
ws.page_margins.top = 1.2 / 2.54
ws.page_margins.bottom = 1.0 / 2.54
ws.page_margins.header = 0.3 / 2.54
ws.page_margins.footer = 0.3 / 2.54
ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 1

# ============================================================
# FONTS
# ============================================================
FONT_NAME = 'SimSun'

font_title = Font(name=FONT_NAME, size=14, bold=True, underline='single')
font_8 = Font(name=FONT_NAME, size=8)
font_8b = Font(name=FONT_NAME, size=8, bold=True)
font_9b = Font(name=FONT_NAME, size=9, bold=True)
font_10 = Font(name=FONT_NAME, size=10)
font_10b = Font(name=FONT_NAME, size=10, bold=True)

# ============================================================
# ALIGNMENTS
# ============================================================
align_center = Alignment(horizontal='center', vertical='center', wrap_text=True)
align_left = Alignment(horizontal='left', vertical='center', wrap_text=True)
align_left_top = Alignment(horizontal='left', vertical='top', wrap_text=True)
align_right = Alignment(horizontal='right', vertical='center', wrap_text=True)

# ============================================================
# BORDERS
# ============================================================
thin_side = Side(style='thin')
medium_side = Side(style='medium')
hair_side = Side(style='hair')

border_thin = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)
border_header_bottom = Border(left=thin_side, right=thin_side, top=thin_side, bottom=medium_side)
border_none = Border()

# ============================================================
# COLUMN WIDTHS (12 columns A-L)
# Total printable width ~ 7.5 inches ~ 54 chars approx
# We use columns A through L (12 columns)
# ============================================================
# Layout: 3 groups
# Group 1 (A-D): labels + values for PURCHASER/SIZE/etc  ~35%
# Group 2 (E-H): labels + values for CERT NO/MATERIAL/etc ~35%
# Group 3 (I-L): labels + values for Report Code/ITEM/etc ~30%

col_widths = {
    'A': 14,   # Label col 1
    'B': 19,   # Value col 1
    'C': 3,    # overflow col 1
    'D': 14,   # Label col 2
    'E': 19,   # Value col 2
    'F': 3,    # spacer
    'G': 13,   # Label col 3
    'H': 15,   # Value col 3
}

for col_letter, width in col_widths.items():
    ws.column_dimensions[col_letter].width = width


def set_cell(row, col, value, font=font_8, alignment=align_left, border=None, merge_end_col=None, merge_end_row=None):
    """Helper to set cell value and style, with optional merge."""
    cell = ws.cell(row=row, column=col, value=value)
    cell.font = font
    cell.alignment = alignment
    if border:
        cell.border = border
    if merge_end_col or merge_end_row:
        end_r = merge_end_row if merge_end_row else row
        end_c = merge_end_col if merge_end_col else col
        ws.merge_cells(start_row=row, start_column=col, end_row=end_r, end_column=end_c)
    return cell


def apply_border_to_merged(start_row, start_col, end_row, end_col, border):
    """Apply border to all cells in a merged range."""
    for r in range(start_row, end_row + 1):
        for c in range(start_col, end_col + 1):
            ws.cell(row=r, column=c).border = border


# ============================================================
# ROW 1: TITLE
# ============================================================
r = 1
ws.row_dimensions[r].height = 28
set_cell(r, 1, "TONG  MING  ENTERPRISE  CO., LTD", font_title, align_center, merge_end_col=8)

# ============================================================
# ROW 2: blank spacer
# ============================================================
r = 2
ws.row_dimensions[r].height = 6

# ============================================================
# INFO SECTION: Rows 3-12
# ============================================================
# Row 3: PURCHASER / CERT NO / Report Code
r = 3
ws.row_dimensions[r].height = 15
set_cell(r, 1, "PURCHASER:", font_8b, align_left)
set_cell(r, 2, "MECSU JOINT STOCK", font_8, align_left, merge_end_col=3)
set_cell(r, 4, "CERT NO\uff1a", font_8b, align_left)
set_cell(r, 5, "965712", font_8, align_left)
set_cell(r, 7, "Report Code\uff1a", font_8b, align_left)
set_cell(r, 8, "", font_8, align_left)

# Row 4: ADDRESS / CUST P/N / PO# NO
r = 4
ws.row_dimensions[r].height = 15
set_cell(r, 1, "ADDRESS\uff1a", font_8b, align_left_top)
set_cell(r, 2, "39 Tran Hung Dao St,", font_8, align_left_top, merge_end_col=3)
set_cell(r, 4, "CUST P/N\uff1a", font_8b, align_left)
set_cell(r, 5, "D0127B3T1X0P0800", font_8, align_left)
set_cell(r, 7, "PO# NO\uff1a", font_8b, align_left)
set_cell(r, 8, "", font_8, align_left)

# Row 5: address continued
r = 5
ws.row_dimensions[r].height = 15
set_cell(r, 2, "Tan Thanh Ward,Tan Phu", font_8, align_left_top, merge_end_col=3)

# Row 6: address continued
r = 6
ws.row_dimensions[r].height = 15
set_cell(r, 2, "District, Ho Chi Minh", font_8, align_left_top, merge_end_col=3)

# Row 7: address continued
r = 7
ws.row_dimensions[r].height = 15
set_cell(r, 2, "City, Vietnam", font_8, align_left_top, merge_end_col=3)

# Row 8: SIZE / MATERIAL TYPE / ITEM NO
r = 8
ws.row_dimensions[r].height = 15
set_cell(r, 1, "SIZE\uff1a", font_8b, align_left)
set_cell(r, 2, "M8", font_8, align_left)
set_cell(r, 4, "MATERIAL TYPE\uff1a", font_8b, align_left)
set_cell(r, 5, "A2(304)", font_8, align_left)
set_cell(r, 7, "ITEM NO:", font_8b, align_left)
set_cell(r, 8, "94457", font_8, align_left)

# Row 9: MARKING / SHIP QTY / HEAT NO
r = 9
ws.row_dimensions[r].height = 15
set_cell(r, 1, "MARKING\uff1a", font_8b, align_left)
set_cell(r, 2, "NO MARK", font_8, align_left)
set_cell(r, 4, "SHIP QTY\uff1a", font_8b, align_left)
set_cell(r, 5, "30000 PC", font_8, align_left)
set_cell(r, 7, "HEAT NO:", font_8b, align_left)
set_cell(r, 8, "S241114AA20", font_8, align_left)

# Row 10: MFR. / COMMODITY / LOT NO
r = 10
ws.row_dimensions[r].height = 15
set_cell(r, 1, "MFR.\uff1a", font_8b, align_left)
set_cell(r, 2, "TONG MING ENTERPRISE", font_8, align_left, merge_end_col=3)
set_cell(r, 4, "COMMODITY:", font_8b, align_left)
set_cell(r, 5, "DIN127-1987", font_8, align_left)
set_cell(r, 7, "LOT NO\uff1a", font_8b, align_left)
set_cell(r, 8, "C20250402W741", font_8, align_left)

# Row 11: MFR.DATE / Product Name / Surface
r = 11
ws.row_dimensions[r].height = 15
set_cell(r, 1, "MFR.DATE\uff1a", font_8b, align_left)
set_cell(r, 2, "2025/03/27", font_8, align_left)
set_cell(r, 4, "Product Name:", font_8b, align_left)
set_cell(r, 5, "Lock Washers", font_8, align_left)
set_cell(r, 7, "Surface:", font_8b, align_left)
set_cell(r, 8, "Passivated", font_8, align_left)

# Row 12: Sampling Date / Test Date / Sample Plan spec
r = 12
ws.row_dimensions[r].height = 15
set_cell(r, 1, "Sampling Date\uff1a", font_8b, align_left)
set_cell(r, 2, "2025/04/02", font_8, align_left)
set_cell(r, 4, "Test Date\uff1a", font_8b, align_left)
set_cell(r, 5, "2025/04/02", font_8, align_left)
set_cell(r, 7, "Sample Plan spec\uff1a", font_8b, align_left)
set_cell(r, 8, "ANSI/ASME B18.18", font_8, align_left)

# Row 13: blank spacer
r = 13
ws.row_dimensions[r].height = 6

# ============================================================
# DIMENSIONAL INSPECTIONS TABLE 1 (Incision width)
# ============================================================
# Row 14: Table 1 Header
r = 14
ws.row_dimensions[r].height = 18
set_cell(r, 1, "DIMENSIONAL INSPECTIONS    (mm)", font_9b, align_left, merge_end_col=4)
set_cell(r, 5, "SAMPLE STANDARD\uff1a", font_9b, align_right, merge_end_col=8)

# Row 15: Sub-header
r = 15
ws.row_dimensions[r].height = 18
set_cell(r, 1, "CHARACTERISTICS", font_9b, align_center, border_header_bottom, merge_end_col=2)
apply_border_to_merged(r, 1, r, 2, border_header_bottom)
set_cell(r, 3, "SPECIFICATION", font_9b, align_center, border_header_bottom, merge_end_col=4)
apply_border_to_merged(r, 3, r, 4, border_header_bottom)
set_cell(r, 5, "ACTUAL RESULT", font_9b, align_center, border_header_bottom, merge_end_col=6)
apply_border_to_merged(r, 5, r, 6, border_header_bottom)
set_cell(r, 7, "SAMPLE DETERMINANT", font_9b, align_center, border_header_bottom, merge_end_col=8)
apply_border_to_merged(r, 7, r, 8, border_header_bottom)

# Row 16: Data - Incision width
r = 16
ws.row_dimensions[r].height = 16
set_cell(r, 1, "Incision width", font_8, align_center, border_thin, merge_end_col=2)
apply_border_to_merged(r, 1, r, 2, border_thin)
set_cell(r, 3, "1.05 max", font_8, align_center, border_thin, merge_end_col=4)
apply_border_to_merged(r, 3, r, 4, border_thin)
set_cell(r, 5, "1.05 <---> 1.05", font_8, align_center, border_thin, merge_end_col=6)
apply_border_to_merged(r, 5, r, 6, border_thin)
set_cell(r, 7, "Qualified", font_8, align_center, border_thin, merge_end_col=8)
apply_border_to_merged(r, 7, r, 8, border_thin)

# Row 17: blank spacer
r = 17
ws.row_dimensions[r].height = 6

# ============================================================
# DIMENSIONAL INSPECTIONS TABLE 2 (Main dimensions)
# ============================================================
# Row 18: Table 2 Header
r = 18
ws.row_dimensions[r].height = 18
set_cell(r, 1, "DIMENSIONAL INSPECTIONS    (mm)", font_9b, align_left, merge_end_col=4)
set_cell(r, 5, "SAMPLE STANDARD\uff1aDIN127-1987", font_9b, align_right, merge_end_col=8)

# Row 19: Sub-header
r = 19
ws.row_dimensions[r].height = 18
set_cell(r, 1, "CHARACTERISTICS", font_9b, align_center, border_header_bottom, merge_end_col=2)
apply_border_to_merged(r, 1, r, 2, border_header_bottom)
set_cell(r, 3, "SPECIFICATION", font_9b, align_center, border_header_bottom, merge_end_col=4)
apply_border_to_merged(r, 3, r, 4, border_header_bottom)
set_cell(r, 5, "ACTUAL RESULT", font_9b, align_center, border_header_bottom, merge_end_col=6)
apply_border_to_merged(r, 5, r, 6, border_header_bottom)
set_cell(r, 7, "SAMPLE DETERMINANT", font_9b, align_center, border_header_bottom, merge_end_col=8)
apply_border_to_merged(r, 7, r, 8, border_header_bottom)

# Data rows
dim_data = [
    ("INNER DIAMETER", "8.1 <---> 8.5", "8.33 <---> 8.35", "Qualified"),
    ("OUTER DIAMETER", "14.8 max", "14.22 <---> 14.34", "Qualified"),
    ("THICKNESS", "1.9 <---> 2.1", "2.03 <---> 2.1", "Qualified"),
    ("Height", "4 <---> 4.7", "4.16 <---> 4.26", "Qualified"),
]

for i, (char, spec, actual, det) in enumerate(dim_data):
    r = 20 + i
    ws.row_dimensions[r].height = 16
    set_cell(r, 1, char, font_8, align_center, border_thin, merge_end_col=2)
    apply_border_to_merged(r, 1, r, 2, border_thin)
    set_cell(r, 3, spec, font_8, align_center, border_thin, merge_end_col=4)
    apply_border_to_merged(r, 3, r, 4, border_thin)
    set_cell(r, 5, actual, font_8, align_center, border_thin, merge_end_col=6)
    apply_border_to_merged(r, 5, r, 6, border_thin)
    set_cell(r, 7, det, font_8, align_center, border_thin, merge_end_col=8)
    apply_border_to_merged(r, 7, r, 8, border_thin)

# Row 24: blank spacer
r = 24
ws.row_dimensions[r].height = 6

# ============================================================
# MECHANICAL PROPERTIES TABLE
# ============================================================
# Row 25: Header
r = 25
ws.row_dimensions[r].height = 18
set_cell(r, 1, "MECHANICAL PROPERTIES", font_9b, align_left, merge_end_col=4)
set_cell(r, 5, "SAMPLE STANDARD\uff1aDIN127 1987 TYPE B", font_9b, align_right, merge_end_col=8)

# Row 26: Sub-header
r = 26
ws.row_dimensions[r].height = 18
set_cell(r, 1, "CHARACTERISTICS", font_9b, align_center, border_header_bottom, merge_end_col=2)
apply_border_to_merged(r, 1, r, 2, border_header_bottom)
set_cell(r, 3, "SPECIFICATION", font_9b, align_center, border_header_bottom, merge_end_col=4)
apply_border_to_merged(r, 3, r, 4, border_header_bottom)
set_cell(r, 5, "ACTUAL RESULT", font_9b, align_center, border_header_bottom, merge_end_col=6)
apply_border_to_merged(r, 5, r, 6, border_header_bottom)
set_cell(r, 7, "SAMPLE DETERMINANT", font_9b, align_center, border_header_bottom, merge_end_col=8)
apply_border_to_merged(r, 7, r, 8, border_header_bottom)

# Row 27: Data - Free Height
r = 27
ws.row_dimensions[r].height = 16
set_cell(r, 1, "Free Height", font_8, align_center, border_thin, merge_end_col=2)
apply_border_to_merged(r, 1, r, 2, border_thin)
set_cell(r, 3, "3.2 min", font_8, align_center, border_thin, merge_end_col=4)
apply_border_to_merged(r, 3, r, 4, border_thin)
set_cell(r, 5, "3.2 <---> 3.2", font_8, align_center, border_thin, merge_end_col=6)
apply_border_to_merged(r, 5, r, 6, border_thin)
set_cell(r, 7, "Qualified", font_8, align_center, border_thin, merge_end_col=8)
apply_border_to_merged(r, 7, r, 8, border_thin)

# Row 28: blank spacer
r = 28
ws.row_dimensions[r].height = 6

# ============================================================
# CHEMICAL COMPOSITION% TABLE
# ============================================================
# Row 29: Header
r = 29
ws.row_dimensions[r].height = 18
set_cell(r, 1, "CHEMICAL COMPOSITION%", font_8b, align_left, merge_end_col=8)

# Row 30: Column headers for chemical table
# We need more columns here - use A through H creatively
# Heat Number | C | Si | Mn | P | S | Ni | Cr | Mo | Cu | N | SAMPLE DETERMINANT
# This needs 12 sub-columns. We'll use individual cells A-H but split differently.
# Actually, let's use a different approach: put it in a condensed format

# For chemical composition, we need many narrow columns.
# We'll use the 8 columns but format text to fit.
# Best approach: merge some cells for the heat number and determinant.

r = 30
ws.row_dimensions[r].height = 20
# Headers across - we'll abbreviate to fit in 8 columns
# Col 1: Heat Number, Col 2: C, Col 3: Si/Mn, Col 4: P/S, Col 5: Ni, Col 6: Cr, Col 7: Mo/Cu/N, Col 8: DETERMINANT
# Actually better to just list all in a text-based table row

# Let me reconsider: use 8 columns as:
# A: Heat Number | B: C/Si/Mn | C: P/S | D: Ni | E: Cr | F: Mo/Cu | G: N | H: DETERMINANT
# This won't look right. Let's put proper headers.

# Alternative: Use a compact text representation in merged cells
# OR: Add more detail columns. Let's keep it readable.

# I'll make the chemical headers span 2 rows: first row is column names, second is data
chem_headers = ["Heat Number", "C", "Si", "Mn", "P", "S", "Ni", "Cr", "Mo", "Cu", "N", "DETERMINANT"]
chem_values = ["S241114AA20", "0.053", "0.31", "1.01", "0.034", "0.002", "8.01", "18.1", "", "00", "00", "Qualified"]

# For chemical table we need to handle 12 columns in 8 Excel columns
# Solution: put chemical table content as a formatted string in merged cells
# Actually, let's properly handle it by putting multiple items per cell

r = 30
ws.row_dimensions[r].height = 16
# Use a condensed layout: split into logical groups
set_cell(r, 1, "Heat Number", font_8b, align_center, border_header_bottom)
set_cell(r, 2, "C", font_8b, align_center, border_header_bottom)
set_cell(r, 3, "Si    Mn", font_8b, align_center, border_header_bottom)
set_cell(r, 4, "P    S", font_8b, align_center, border_header_bottom)
set_cell(r, 5, "Ni", font_8b, align_center, border_header_bottom)
set_cell(r, 6, "Cr", font_8b, align_center, border_header_bottom)
set_cell(r, 7, "Mo  Cu  N", font_8b, align_center, border_header_bottom)
set_cell(r, 8, "DETERMINANT", font_8b, align_center, border_header_bottom)

r = 31
ws.row_dimensions[r].height = 16
set_cell(r, 1, "S241114AA20", font_8, align_center, border_thin)
set_cell(r, 2, "0.053", font_8, align_center, border_thin)
set_cell(r, 3, "0.31  1.01", font_8, align_center, border_thin)
set_cell(r, 4, "0.034  0.002", font_8, align_center, border_thin)
set_cell(r, 5, "8.01", font_8, align_center, border_thin)
set_cell(r, 6, "18.1", font_8, align_center, border_thin)
set_cell(r, 7, "    00  00", font_8, align_center, border_thin)
set_cell(r, 8, "Qualified", font_8, align_center, border_thin)

# Row 32-33: blank spacer
r = 32
ws.row_dimensions[r].height = 20
r = 33
ws.row_dimensions[r].height = 20

# ============================================================
# SIGNATURE SECTION
# ============================================================
# Row 34: Signature values
r = 34
ws.row_dimensions[r].height = 22
set_cell(r, 1, "(stamp)", font_10, align_center, merge_end_col=2)
set_cell(r, 3, "(signature)", font_10, align_center, merge_end_col=4)
set_cell(r, 5, "Xiaoyang.W", font_10, align_center, merge_end_col=6)
set_cell(r, 7, "2025/07/28", font_10, align_center, merge_end_col=8)

# Row 35: Signature labels
r = 35
ws.row_dimensions[r].height = 18
set_cell(r, 1, "Authorization", font_10, align_center, merge_end_col=2)
set_cell(r, 3, "Verification", font_10, align_center, merge_end_col=4)
set_cell(r, 5, "Sampler", font_10, align_center, merge_end_col=6)
set_cell(r, 7, "Date", font_10, align_center, merge_end_col=8)

# Row 36: blank spacer
r = 36
ws.row_dimensions[r].height = 8

# ============================================================
# DISCLAIMER
# ============================================================
r = 37
ws.row_dimensions[r].height = 14
set_cell(r, 1, "This Lot fit the demands of EN10204 3.1", font_8, align_left, merge_end_col=8)

r = 38
ws.row_dimensions[r].height = 14
set_cell(r, 1, "INSPECTION RESULT:SAMPLES TESTED CONFORM TO ALL OF SPECIFICATION AS ABOVE THIS REPORT MUST NOT BE REPRODUCED EXCEPT IN FULL.", font_8, align_left, merge_end_col=8)

r = 39
ws.row_dimensions[r].height = 14
set_cell(r, 1, "a\u3001HRC for Screw & Bolt with partial thread;HRB(S) for Nut\u3001Thread rod and Bolt & Screw with full thread.", font_8, align_left, merge_end_col=8)

# Row 40: blank spacer
r = 40
ws.row_dimensions[r].height = 8

# ============================================================
# FOOTER
# ============================================================
r = 41
ws.row_dimensions[r].height = 14
set_cell(r, 1, "Tel\uff1a+86-573-82203125  Fax\uff1a+86-573-82207588  Web\uff1awww.tongming.com.cn  E-mail\uff1aexport@tongming.com.cn", font_8, align_center, merge_end_col=8)

r = 42
ws.row_dimensions[r].height = 14
set_cell(r, 1, "ADD\uff1a88 ChangSheng RD., E.D. Zone,JiaXing,ZheJiang,China  Zip\uff1a314003", font_8, align_center, merge_end_col=8)

r = 43
ws.row_dimensions[r].height = 14
set_cell(r, 1, "Page 1  Toal 1 Page", font_8, align_center, merge_end_col=8)

# ============================================================
# SET PRINT AREA
# ============================================================
ws.print_area = 'A1:H43'

# ============================================================
# SAVE
# ============================================================
output_path = r"D:\12\templates\tongming_template.xlsx"
wb.save(output_path)
print(f"Template saved to: {output_path}")
print("Done!")