ChatGPT generated Spreadsheet styled with Colours

Colours.py

import pandas as pd
import numpy as np
from random import randint
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Border, Side
from openpyxl.utils import get_column_letter
import colorsys
import hashlib

# Define the colours and their popularity (1-10)
colours = {
    "Red": 8,
    "Blue": 9,
    "Green": 7,
    "Yellow": 6,
    "Purple": 5,
    "Orange": 4,
    "Pink": 10,
    "Brown": 3,
    "Black": 2,
    "White": 1
}

# Create a DataFrame
df = pd.DataFrame({
    "product": list(colours.keys()),
    "price": [randint(10, 100) for _ in range(10)],
    "sales": [randint(1, 400) for _ in range(10)],
    "rank": list(colours.values())
})

# Sort by rank (Z to A)
df.sort_values(by="rank", ascending=False, inplace=True)

# Calculate subtotals
df["subtotal"] = df["price"] * df["sales"]

# Write to an Excel file
df.to_excel("colours.xlsx", index=False)

# Load the workbook
book = load_workbook("colours.xlsx")
writer = pd.ExcelWriter("colours.xlsx", engine='openpyxl')
writer.book = book

# Select the default sheet
sheet = writer.book.active

# Hide gridlines
sheet.sheet_view.showGridLines = False

# Apply styles to headers
header_font = Font(bold=True, color="FFFFFF", size=14)
header_fill = PatternFill(start_color="0000FF", end_color="0000FF", fill_type="solid")
header_border = Border(bottom=Side(border_style="thin"))
for cell in sheet[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.border = header_border

# Apply styles to data rows
for index, row in df.iterrows():
    # Generate a color in HSL space, then convert to RGB
    h = int(hashlib.md5(row['product'].encode()).hexdigest(), 16) / float(16**32)  # Create a unique hue based on the product name
    s = 0.5  # Keep saturation constant
    l = 0.5 if row['product'] != 'Black' else 0.1  # Adjust lightness based on product
    r, g, b = [int(x * 255) for x in colorsys.hls_to_rgb(h, l, s)]
    fill_color = "{:02X}{:02X}{:02X}".format(r, g, b)
    text_color = "000000" if l > 0.5 else "FFFFFF"
    fill = PatternFill(start_color=fill_color, end_color=fill_color, fill_type="solid")
    font = Font(color=text_color, size=14)
    for cell in list(sheet[index+2]):
        cell.fill = fill
        cell.font = font

# Format price and subtotal columns as currency
for cell in sheet['B'] + sheet['E']:
    cell.number_format = '"$"#,##0.00'

# Calculate and insert total
total_cell = sheet.cell(row=len(df)+3, column=5, value=sum(df["subtotal"]))
total_cell.number_format = '"$"#,##0.00'
total_cell.font = Font(size=14)
total_label = sheet.cell(row=len(df)+3, column=4, value="TOTAL")
total_label.font = Font(size=14)

# Auto-size columns
for column in sheet.columns:
    max_length = 0
    column = [cell for cell in column]
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(cell.value)
        except:
            pass
    adjusted_width = (max_length + 2)
    sheet.column_dimensions[get_column_letter(column[0].column)].width = adjusted_width

writer.save()