Insulated Buffer Water Tank 60L - 500L Improving HVAC System Stability

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

# Create workbook
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Buffer Tank Specifications"

# Ensure grid lines are visible
ws.views.sheetView[0].showGridLines = True

# Data extraction from image
headers = ["Capacity (L)", "60L", "100L", "150L", "200L", "250L", "300L", "400L", "500L"]

data = [
    ["Inner tank material", "SUS304 | 316L | duplex2205"],
    ["Outer case material", "Galvanized steel with painting | SUS304"],
    ["Insulation", "HCFC free foam"],
    ["Insulation thickness (mm)", "50", "50", "50", "50", "45", "45", "50", "50"],
    ["Max. working pressure(bar)", "8"],
    ["Circulating inlet/outlet", "G1" | G5/4" | G6/4" | G2""],
    ["Electric heater", "1.5kW | 3kW | 4kW (Optional)"],
    ["Thermostat", "Optional"],
    ["Product size (mm)", "φ470x705", "φ470x1085", "φ470x1525", "φ520x1575", "φ560x1605", "φ560x1885", "φ700x1605", "φ700x1845"],
    ["Package size (mm)", "550x550x740", "550x550x1120", "550x550x1550", "605x605x1610", "645x645x1670", "645x645x1950", "790x790x1690", "790x790x1950"]
]

# Header Row Styling (Gold/Yellow theme as in image)
ws.row_dimensions[1].height = 30
header_fill = PatternFill(start_color="D4AF37", end_color="D4AF37", fill_type="solid") # Gold
header_font = Font(name="Arial", size=11, bold=True, color="FFFFFF")

for col_num, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col_num, value=header)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)

# Thin border style
thin_border = Border(
    left=Side(style='thin', color='CCCCCC'),
    right=Side(style='thin', color='CCCCCC'),
    top=Side(style='thin', color='CCCCCC'),
    bottom=Side(style='thin', color='CCCCCC')
)

white_fill = PatternFill(start_color="FFFFFF", end_color="FFFFFF", fill_type="solid")

row_idx = 2
for row_item in data:
    ws.row_dimensions[row_idx].height = 24
    label = row_item[0]
    
    # Check if row spans all columns (merged) or has distinct columns
    if len(row_item) == 2 and row_item[1] not in ["50", "45", "φ470x705", "550x550x740"]: 
        # This is a full-width property (like Inner tank material, Max pressure, etc.)
        # We put label in col 1, merge B to I for value
        cell_label = ws.cell(row=row_idx, column=1, value=label)
        cell_label.font = Font(name="Arial", size=10, bold=True)
        cell_label.fill = white_fill
        cell_label.border = thin_border
        cell_label.alignment = Alignment(horizontal="left", vertical="center")
        
        ws.merge_cells(start_row=row_idx, start_column=2, end_row=row_idx, end_column=9)
        cell_val = ws.cell(row=row_idx, column=2, value=row_item[1])
        cell_val.font = Font(name="Arial", size=10)
        cell_val.fill = white_fill
        cell_val.alignment = Alignment(horizontal="center", vertical="center")
        
        for c in range(2, 10):
            ws.cell(row=row_idx, column=c).border = thin_border
            ws.cell(row=row_idx, column=c).fill = white_fill
            
    else:
        # Row with individual columns per capacity (Insulation thickness, Product size, Package size)
        cell_label = ws.cell(row=row_idx, column=1, value=label)
        cell_label.font = Font(name="Arial", size=10, bold=True)
        cell_label.fill = white_fill
        cell_label.border = thin_border
        cell_label.alignment = Alignment(horizontal="left", vertical="center")
        
        for col_idx in range(2, 10):
            val = row_item[col_idx - 1] if (col_idx - 1) < len(row_item) else ""
            cell = ws.cell(row=row_idx, column=col_idx, value=val)
            cell.font = Font(name="Arial", size=10)
            cell.fill = white_fill
            cell.border = thin_border
            cell.alignment = Alignment(horizontal="center", vertical="center")

    row_idx += 1

# Auto-adjust column widths
for col in ws.columns:
    max_len = 0
    col_letter = get_column_letter(col[0].column)
    for cell in col:
        val_str = str(cell.value or '')
        if len(val_str) > max_len:
            max_len = len(val_str)
    ws.column_dimensions[col_letter].width = max(max_len + 4, 16)

# Save file
file_path = "Buffer_Tank_Specifications.xlsx"
wb.save(file_path)
print(file_path)

Buffer_Tank_Specifications.xlsx