#!/usr/bin/env python3 """ Builds Glowly_Product_Database_v1.xlsx — a sample product catalogue. This is a portfolio sample for a fictional skincare store, not client work. It exists to demonstrate the things a client actually checks when they open a delivered spreadsheet: * a frozen header row and a working filter, so the file is usable at 400 rows * data validation on every column, so the next person cannot break the format * conditional formatting that encodes a rule ("re-order below 10"), not decoration * a summary built from live formulas, so it stays correct when a row changes * a data dictionary sheet, so the conventions survive being handed over Usage: .venv/bin/python scripts/build_glowly_workbook.py public/downloads """ import sys from pathlib import Path from openpyxl import Workbook from openpyxl.formatting.rule import FormulaRule from openpyxl.styles import Alignment, Border, Font, PatternFill, Side from openpyxl.utils import get_column_letter from openpyxl.worksheet.datavalidation import DataValidation CATEGORIES = ["Cleanser", "Toner", "Serum", "Moisturizer", "Sunscreen", "Exfoliant", "Mask", "Eye Care", "Lip Care", "Treatment"] REORDER_LEVEL = 10 LOW_LEVEL = 25 # SKU, name, category, normal price (IDR), discount price, stock, weight (g), description PRODUCTS = [ ("GLW-CLN-001", "Gentle Rice Foam Cleanser", "Cleanser", 89000, 71200, 142, 150, "Low-pH daily foam with rice bran extract for normal to dry skin."), ("GLW-CLN-002", "Green Tea Balancing Gel Wash", "Cleanser", 79000, 63200, 96, 120, "Lightweight gel wash for oily and combination skin."), ("GLW-CLN-003", "Centella Cream Cleanser", "Cleanser", 95000, 0, 38, 130, "Non-foaming cream cleanser for sensitive, reactive skin."), ("GLW-CLN-004", "Micellar Cleansing Water 300ml", "Cleanser", 110000, 93500, 7, 300, "No-rinse micellar water for light makeup removal."), ("GLW-CLN-005", "Deep Cleansing Oil", "Cleanser", 135000, 108000, 54, 200, "First-step cleansing oil that emulsifies with water."), ("GLW-TNR-001", "Hydrating Rose Toner", "Toner", 92000, 78200, 118, 200, "Alcohol-free hydrating toner with rose water and glycerin."), ("GLW-TNR-002", "BHA 2% Clarifying Toner", "Toner", 125000, 100000, 23, 150, "Salicylic acid toner for congested pores. Evening use."), ("GLW-TNR-003", "Rice Ferment Essence Toner", "Toner", 148000, 0, 61, 180, "Fermented rice essence for texture and glow."), ("GLW-TNR-004", "Soothing Cica Mist", "Toner", 68000, 54400, 4, 100, "Portable centella mist for midday hydration."), ("GLW-SER-001", "Niacinamide 10% + Zinc Serum", "Serum", 165000, 132000, 204, 30, "Brightening serum for uneven tone and visible pores."), ("GLW-SER-002", "Vitamin C 15% Brightening Serum", "Serum", 215000, 172000, 87, 30, "Stabilised L-ascorbic acid serum. Morning use with sunscreen."), ("GLW-SER-003", "Hyaluronic Acid B5 Serum", "Serum", 145000, 116000, 156, 30, "Multi-weight hyaluronic acid for layered hydration."), ("GLW-SER-004", "Retinal 0.05% Night Serum", "Serum", 289000, 231200, 19, 30, "Encapsulated retinaldehyde for fine lines. Start twice weekly."), ("GLW-SER-005", "Azelaic Acid 10% Suspension", "Serum", 175000, 0, 42, 30, "Azelaic acid for redness and post-acne marks."), ("GLW-SER-006", "Peptide Firming Ampoule", "Serum", 265000, 212000, 8, 30, "Signal peptide complex for elasticity and firmness."), ("GLW-SER-007", "Snail Mucin Repair Essence", "Serum", 158000, 126400, 73, 100, "96% snail secretion filtrate for barrier repair."), ("GLW-MST-001", "Ceramide Barrier Cream", "Moisturizer",178000, 142400, 131, 50, "Ceramide and cholesterol cream for compromised barriers."), ("GLW-MST-002", "Oil-Free Gel Moisturiser", "Moisturizer",112000, 89600, 94, 50, "Water-gel moisturiser for oily skin in humid climates."), ("GLW-MST-003", "Squalane Overnight Mask", "Moisturizer",195000, 0, 27, 80, "Occlusive sleeping mask with olive-derived squalane."), ("GLW-MST-004", "Panthenol Recovery Balm", "Moisturizer",132000, 105600, 6, 40, "Thick balm for dry patches and post-procedure skin."), ("GLW-MST-005", "Rich Nourishing Night Cream", "Moisturizer",210000, 168000, 49, 50, "Shea and ceramide night cream for dry skin."), ("GLW-SUN-001", "Daily Fluid Sunscreen SPF50+ PA++++","Sunscreen",139000, 111200, 312, 50, "Lightweight chemical filter fluid with no white cast."), ("GLW-SUN-002", "Mineral Sunscreen SPF40 PA+++", "Sunscreen", 155000, 124000, 68, 50, "Zinc oxide sunscreen for sensitive and reactive skin."), ("GLW-SUN-003", "Sunscreen Stick SPF50 PA++++", "Sunscreen", 118000, 0, 9, 20, "Reapplication stick for over-makeup top-ups."), ("GLW-EXF-001", "AHA 8% Glow Peeling Solution", "Exfoliant", 168000, 134400, 56, 30, "Glycolic and lactic acid weekly peel. Evening use only."), ("GLW-EXF-002", "Enzyme Powder Wash", "Exfoliant", 125000, 100000, 31, 70, "Papain enzyme powder for gentle daily polishing."), ("GLW-EXF-003", "PHA Gentle Exfoliating Pads", "Exfoliant", 142000, 113600, 3, 90, "Pre-soaked gluconolactone pads for sensitive skin."), ("GLW-MSK-001", "Clay Detox Mask", "Mask", 108000, 86400, 77, 100, "Kaolin and bentonite mask for the T-zone."), ("GLW-MSK-002", "Honey Hydrating Wash-Off Mask", "Mask", 98000, 0, 44, 100, "Honey and glycerin mask for dehydrated skin."), ("GLW-MSK-003", "Sheet Mask Variety Box (10pcs)", "Mask", 145000, 116000, 188, 250, "Ten-sheet assortment across hydrating and soothing types."), ("GLW-EYE-001", "Caffeine Eye Serum", "Eye Care", 135000, 108000, 62, 15, "Caffeine and peptide roll-on for puffiness."), ("GLW-EYE-002", "Retinol Eye Cream", "Eye Care", 188000, 150400, 15, 20, "Low-dose retinol formulated for the eye area."), ("GLW-EYE-003", "Hydrogel Eye Patches (60pcs)", "Eye Care", 125000, 0, 5, 100, "Cooling hydrogel patches with niacinamide."), ("GLW-LIP-001", "Vitamin E Lip Sleeping Mask", "Lip Care", 85000, 68000, 121, 20, "Overnight lip mask with vitamin E and shea."), ("GLW-LIP-002", "Tinted Lip Balm SPF20", "Lip Care", 72000, 57600, 93, 15, "Sheer tinted balm with sun protection."), ("GLW-LIP-003", "Lip Scrub Sugar Polish", "Lip Care", 65000, 0, 2, 15, "Sugar and jojoba scrub for flaky lips."), ("GLW-TRT-001", "Spot Treatment Gel 2% BHA", "Treatment", 78000, 62400, 147, 15, "Targeted drying gel for active breakouts."), ("GLW-TRT-002", "Hydrocolloid Patches (36pcs)", "Treatment", 55000, 44000, 265, 10, "Absorbing patches for surfaced blemishes."), ("GLW-TRT-003", "Tranexamic Acid Dark Spot Serum", "Treatment", 245000, 196000, 34, 30, "Tranexamic acid and niacinamide for stubborn marks."), ("GLW-TRT-004", "Barrier Repair Ampoule", "Treatment", 198000, 0, 1, 30, "Intensive lipid ampoule for over-exfoliated skin."), ] HEADERS = [ ("sku", "SKU", 14), ("name", "Product Name", 38), ("category", "Category", 14), ("price", "Price Normal (IDR)", 18), ("sale", "Price Discount (IDR)", 20), ("discount", "Discount %", 12), ("stock", "Stock", 9), ("weight", "Weight (g)", 11), ("description", "Short Description", 62), ] INK = "1F2937" HEADER_FILL = PatternFill("solid", fgColor="111827") RED_FILL = PatternFill("solid", fgColor="FEE2E2") AMBER_FILL = PatternFill("solid", fgColor="FEF3C7") HAIRLINE = Border(*[Side(style="thin", color="E5E7EB")] * 4) def style_header(sheet, row=1): for index, (_, label, width) in enumerate(HEADERS, start=1): cell = sheet.cell(row=row, column=index, value=label) cell.font = Font(bold=True, color="FFFFFF", size=11) cell.fill = HEADER_FILL cell.alignment = Alignment(vertical="center", wrap_text=True) sheet.column_dimensions[get_column_letter(index)].width = width sheet.row_dimensions[row].height = 30 def build_products(workbook): sheet = workbook.active sheet.title = "Products" style_header(sheet) for offset, product in enumerate(PRODUCTS): row = offset + 2 sku, name, category, price, sale, stock, weight, description = product sheet.cell(row=row, column=1, value=sku) sheet.cell(row=row, column=2, value=name) sheet.cell(row=row, column=3, value=category) sheet.cell(row=row, column=4, value=price).number_format = "#,##0" # An empty discount cell means "not on promotion" — clearer than a zero, # which reads as a free product when the column is summed. sale_cell = sheet.cell(row=row, column=5, value=sale if sale else None) sale_cell.number_format = "#,##0" # Derived, never typed: recalculates if either price is edited. discount = sheet.cell(row=row, column=6, value=f'=IF(E{row}="","",1-E{row}/D{row})') discount.number_format = "0%" sheet.cell(row=row, column=7, value=stock).number_format = "#,##0" sheet.cell(row=row, column=8, value=weight).number_format = "#,##0" sheet.cell(row=row, column=9, value=description).alignment = Alignment(wrap_text=True) for column in range(1, 10): sheet.cell(row=row, column=column).border = HAIRLINE last = len(PRODUCTS) + 1 sheet.freeze_panes = "A2" sheet.auto_filter.ref = f"A1:I{last}" # --- validation: the format cannot be broken by whoever edits this next --- category_rule = DataValidation( type="list", formula1='"' + ",".join(CATEGORIES) + '"', allow_blank=False, showErrorMessage=True, errorTitle="Unknown category", error="Pick a category from the list. To add a new one, update the Data Dictionary sheet first.") sheet.add_data_validation(category_rule) category_rule.add(f"C2:C{last}") sku_rule = DataValidation( type="custom", formula1=f'=AND(LEN(A2)=11,COUNTIF($A$2:$A${last},A2)=1)', showErrorMessage=True, errorTitle="Invalid SKU", error="SKU must match GLW-XXX-000 (11 characters) and must be unique.") sheet.add_data_validation(sku_rule) sku_rule.add(f"A2:A{last}") for column, title, message in [ ("D", "Invalid price", "Price must be a whole number above zero."), ("G", "Invalid stock", "Stock must be zero or a positive whole number."), ("H", "Invalid weight", "Shipping weight in grams must be above zero."), ]: rule = DataValidation(type="whole", operator="greaterThanOrEqual", formula1="0" if column == "G" else "1", showErrorMessage=True, errorTitle=title, error=message) sheet.add_data_validation(rule) rule.add(f"{column}2:{column}{last}") discount_rule = DataValidation( type="custom", formula1=f"=OR(ISBLANK(E2),AND(E2>0,E2"")*Products!E2:E{last}*Products!G2:G{last})' f'+SUMPRODUCT((Products!E2:E{last}="")*Products!D2:D{last}*Products!G2:G{last})', '"Rp"#,##0'), ("Products on promotion", f'=COUNTIF(Products!E2:E{last},">0")', "#,##0"), ("Average discount", f'=IFERROR(AVERAGEIF(Products!F2:F{last},">0"),0)', "0.0%"), (f"Below re-order level ({REORDER_LEVEL})", f'=COUNTIF(Products!G2:G{last},"<{REORDER_LEVEL}")', "#,##0"), ("Out of stock", f'=COUNTIF(Products!G2:G{last},0)', "#,##0"), ("Heaviest item (g)", f"=MAX(Products!H2:H{last})", "#,##0"), ] for offset, (label, formula, fmt) in enumerate(metrics): row = offset + 4 label_cell = sheet.cell(row=row, column=1, value=label) label_cell.font = Font(size=11, color=INK) value = sheet.cell(row=row, column=2, value=formula) value.number_format = fmt value.font = Font(bold=True, size=11, color=INK) if label.startswith("Below re-order"): value.font = Font(bold=True, size=11, color="991B1B") heading = sheet.cell(row=4, column=4, value="By category") heading.font = Font(bold=True, color="FFFFFF") heading.fill = HEADER_FILL for column, label in [(5, "Items"), (6, "Stock value")]: cell = sheet.cell(row=4, column=column, value=label) cell.font = Font(bold=True, color="FFFFFF") cell.fill = HEADER_FILL for offset, category in enumerate(CATEGORIES): row = offset + 5 sheet.cell(row=row, column=4, value=category) sheet.cell(row=row, column=5, value=f'=COUNTIF(Products!$C$2:$C${last},D{row})').number_format = "#,##0" sheet.cell(row=row, column=6, value=f'=SUMPRODUCT((Products!$C$2:$C${last}=D{row})*Products!$D$2:$D${last}*Products!$G$2:$G${last})' ).number_format = '"Rp"#,##0' def build_dictionary(workbook, last): sheet = workbook.create_sheet("Data Dictionary") for column, width in zip("ABCD", (22, 14, 30, 62)): sheet.column_dimensions[column].width = width title = sheet.cell(row=1, column=1, value="Data Dictionary & Conventions") title.font = Font(bold=True, size=14, color=INK) sheet.cell(row=2, column=1, value="Hand this sheet to whoever maintains the file next.").font = Font( italic=True, size=9, color="6B7280") for index, label in enumerate(["Column", "Type", "Rule", "Notes"], start=1): cell = sheet.cell(row=4, column=index, value=label) cell.font = Font(bold=True, color="FFFFFF") cell.fill = HEADER_FILL spec = [ ("SKU", "Text", "GLW-XXX-000, unique", "Three-letter category code. Never reused, even after a product is delisted."), ("Product Name", "Text", "Required", "Sentence case. Size goes in the name only when the same product ships in several sizes."), ("Category", "List", "One of 10 values", "Locked to a dropdown. Adding a category means updating this sheet and the validation rule."), ("Price Normal (IDR)", "Whole number", "> 0", "Rupiah, no decimals. Excludes VAT."), ("Price Discount (IDR)", "Whole number", "Blank or < normal", "Blank means not on promotion. Zero is not used, because a zero sums as a free product."), ("Discount %", "Formula", "Derived", "=1-discount/normal. Never typed by hand, so it cannot drift from the prices."), ("Stock", "Whole number", ">= 0", f"Rows below {REORDER_LEVEL} highlight red (re-order now); below {LOW_LEVEL} amber (watch)."), ("Weight (g)", "Whole number", "> 0", "Shipping weight including packaging. Drives courier rate bands."), ("Short Description", "Text", "Required, <= 120 chars", "One sentence. Used for marketplace listing sync."), ] for offset, row_values in enumerate(spec): row = offset + 5 for column, value in enumerate(row_values, start=1): cell = sheet.cell(row=row, column=column, value=value) cell.alignment = Alignment(wrap_text=True, vertical="top") cell.border = HAIRLINE notes_row = len(spec) + 7 sheet.cell(row=notes_row, column=1, value="Conventions").font = Font(bold=True, size=12, color=INK) for offset, line in enumerate([ "File naming: Glowly_Product_Database_v1.xlsx — version suffix increments on every delivery.", "Header row is frozen and a filter is applied, so the file stays usable past a few hundred rows.", "Validation is enforced on SKU, Category, prices, Stock and Weight — bad input is rejected at entry.", "Empty means unknown. Nothing in this file is estimated or filled in to look complete.", "The Summary sheet contains no pasted values, only formulas, so it cannot go stale.", f"Sample file: 40 products, {last - 1} data rows. Structure is unchanged at 4,000 rows.", ], start=1): cell = sheet.cell(row=notes_row + offset, column=1, value="• " + line) cell.font = Font(size=10, color="374151") def main(output_dir): output_dir = Path(output_dir) output_dir.mkdir(parents=True, exist_ok=True) workbook = Workbook() last = build_products(workbook) build_summary(workbook, last) build_dictionary(workbook, last) target = output_dir / "Glowly_Product_Database_v1.xlsx" workbook.save(target) low = sum(1 for p in PRODUCTS if p[5] < REORDER_LEVEL) print(f"wrote {target}") print(f" products {len(PRODUCTS)}") print(f" categories {len(CATEGORIES)}") print(f" below re-order {low}") print(f" sheets {', '.join(workbook.sheetnames)}") if __name__ == "__main__": main(sys.argv[1] if len(sys.argv) > 1 else "public/downloads")