coi/test_xls.py

from openpyxl import load_workbook
from datetime import datetime

# Define the data
data = {
    "customer": "xxxx",
    "inspect_date": "2025-01-15",
    "lot_no": "12345",
    "size": "Large",
    "pcs": "10 pcs",
    "spec": "Spec-A",
    "d1_act": "10",
    "d2_act": "0",  # Adjust this to test hiding `No.2` table
}

# Load the Excel template
template_path = "./hardness.xlsx"  # Replace with your template file path
workbook = load_workbook(template_path)
sheet = workbook.active

# Replace placeholders with actual values
for row in sheet.iter_rows():
    for cell in row:
        if cell.value and isinstance(cell.value, str) and cell.value.startswith("<") and cell.value.endswith(">"):
            placeholder = cell.value.strip("<>")
            if placeholder in data:
                cell.value = data[placeholder]

# Hide `No.2` table if `d2_act` is 0
if data.get("d2_act") == "0":
    # Adjust the range to the actual No.2 table location in your template
    table_start_row = 20  # Replace with the actual start row of the No.2 table
    table_end_row = 25    # Replace with the actual end row of the No.2 table
    table_start_col = 1   # Starting column for the table
    table_end_col = 5     # Ending column for the table
    
    # Clear only non-merged cells

    sheet.row_dimensions.group(24, 28, hidden=True)


# Save the updated file with a timestamp
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
output_path = f"hardness_filled_{timestamp}.xlsx"
workbook.save(output_path)

print(f"File saved as {output_path}")