Created
July 19, 2023 01:02
-
-
Save prestonw/5b69a35878ac432d94753bc5b539cfe7 to your computer and use it in GitHub Desktop.
ChatGPT generated Spreadsheet styled with Colours
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment