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

# Path to the workbook
path = './Test_Output.xlsx'
wb = load_workbook(path)

# Define corporate style

header_font = Font(name='Arial', size=11, bold=True, color="FFFFFF")
subheader_font = Font(name='Arial', size=10.5, bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="0070C0", end_color="0070C0", fill_type="solid")  # Corporate blue
header_alignment = Alignment(horizontal="left", vertical="center")
subheader_alignment = Alignment(horizontal="left", vertical="center", wrapText=True)
header_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                      top=Side(style='thin'), bottom=Side(style='thin'))
header_style = NamedStyle(name="header_style")
header_style.font = header_font
header_style.fill = header_fill
header_style.alignment = header_alignment
header_style.border = header_border

cell_font = Font(name='Arial', size=9)
cell_alignment = Alignment(horizontal="left", vertical="center")
cell_border = Border(left=Side(style='thin'), right=Side(style='thin'),
                     top=Side(style='thin'), bottom=Side(style='thin'))
cell_style = NamedStyle(name="cell_style")
cell_style.font = cell_font
cell_style.alignment = cell_alignment
cell_style.border = cell_border

no_fill = PatternFill(fill_type=None)  # No fill for data cells

wb.add_named_style(header_style)

# Find the data area (col_max and row_max)
def find_max_cell(ws):
    max_col = ws.max_column
    max_row = ws.max_row
    while ws.cell(max_row, max_col).value is None and max_row > 1:
        max_row -= 1
    while ws.cell(max_row, max_col).value is None and max_col > 1:
        max_col -= 1
    return max_col, max_row

MAX_TOTAL_COLUMN_WIDTH = 240

# Worksheet processing
for sheet_name in wb.sheetnames[1:]:
    ws = wb[sheet_name]

    col_max, row_max = find_max_cell(ws)

    # Apply header style to the top row
    for col in range(1, col_max + 1):
        cell = ws.cell(row=1, column=col)
        cell.style = header_style  # Applying named style to cells

        # cell.font = header_font
        # cell.fill = header_fill
        # cell.alignment = header_alignment
        # cell.border = header_border

    # Apply subheader style for the rest top rows
    header_row_indices = []

    for row in range(1, row_max + 1):
        # Check if the first cell (and more if needed) is empty, indicating a header row
        if all(ws.cell(row=row, column=col).value is None for col in range(1, 3)):  # Adjust range(1, 4) as needed
            header_row_indices.append(row)  # Store the header row index
            print(header_row_indices)

    # The first header has a different height
    ws.row_dimensions[1].height = 48            
    if header_row_indices:
        start_row = header_row_indices[-1] + 1
      
        # Apply header styles and row heights
        for row_index in header_row_indices[1:]:
            for col in range(1, col_max + 1):
                cell = ws.cell(row=row_index, column=col)
                cell.font = subheader_font
                cell.fill = header_fill
                cell.alignment = subheader_alignment
                cell.border = header_border
                ws.row_dimensions[row_index].height = 36
    else:
        start_row = 2
              
    
    for row in range(start_row, row_max + 1):  # header_row_indices[-1] is the last header row
        for col in range(1, col_max + 1):
            cell = ws.cell(row=row, column=col)
            cell = ws.cell(row=row, column=col)
            cell.font = cell_font
            cell.alignment = cell_alignment
            cell.border = cell_border
            cell.fill = no_fill 
            
    # Set the width of the first column to 16
    ws.column_dimensions[get_column_letter(1)].width = 18
    for row in range(1, row_max + 1):
        ws.cell(row=row, column=1).alignment = Alignment(horizontal="left", vertical="center", wrapText=True)
# Iterate over each column in the worksheet
for col_index in range(1, col_max + 1):
    max_length = 0  # Reset max_length for each column
    column = ws[get_column_letter(col_index)]
    for cell in column:
        # Check if the cell has content
        if cell.value:
            cell_length = len(str(cell.value))
            # Adjust cell content length for visible characters length approximation
            adjustment = 1.2  # Amend the adjustment factor as needed
            cell_length = int(cell_length * adjustment)
            max_length = max(max_length, cell_length)
            
    # Adding a bit more space to max_length for aesthetics
    adjusted_width = max_length 
    # Set the column width
    ws.column_dimensions[get_column_letter(col_index)].width = adjusted_width 
    

    # Calculate the total columns' width
    total_width = sum(ws.column_dimensions[get_column_letter(col)].width
                     for col in range(1, col_max + 1))

    # Check if total width is greater than the maximum allowed width
    if total_width > MAX_TOTAL_COLUMN_WIDTH:
        # Calculate the proportional scale and apply it
        scale = MAX_TOTAL_COLUMN_WIDTH / total_width
        
        for col in range(1, col_max + 1):
            column_letter = get_column_letter(col)
            ws.column_dimensions[column_letter].width *= scale
        
# Save the changes to the workbook
wb.save(path)