"""Generate a sample demo.xlsx for testing."""
import random
from datetime import datetime, timedelta
from pathlib import Path
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
random.seed(42)
wb = openpyxl.Workbook()
# ---------------------------------------------------------------------------
# Sheet 1: Sales
# ---------------------------------------------------------------------------
ws = wb.active
ws.title = "Sales"
header_font = Font(bold=True, color="FFFFFF", size=11)
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
thin_border = Border(
bottom=Side(style="thin", color="D9E2F3"),
)
headers = ["Invoice ID", "Date", "Customer", "Product", "Quantity", "Unit Price", "Total", "Region", "Status"]
for col, h in enumerate(headers, 1):
cell = ws.cell(row=1, column=col, value=h)
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal="center")
customers = ["Acme Corp", "Globex Inc", "Initech", "Umbrella LLC", "Stark Industries",
"Wayne Enterprises", "Pied Piper", "Hooli", "Dunder Mifflin", "Sterling Cooper"]
products = ["Widget A", "Widget B", "Gadget Pro", "Gadget Lite", "Service Pack", "Premium Bundle"]
regions = ["North", "South", "East", "West"]
statuses = ["Paid", "Paid", "Paid", "Pending", "Overdue"]
base_date = datetime(2025, 1, 1)
for i in range(1, 201):
date = base_date + timedelta(days=random.randint(0, 365))
customer = random.choice(customers)
product = random.choice(products)
qty = random.randint(1, 50)
price = round(random.uniform(25, 500), 2)
total = round(qty * price, 2)
region = random.choice(regions)
status = random.choice(statuses)
row = [f"INV-{i:04d}", date.strftime("%Y-%m-%d"), customer, product, qty, price, total, region, status]
for col, val in enumerate(row, 1):
cell = ws.cell(row=i + 1, column=col, value=val)
cell.border = thin_border
# Column widths
for col, width in enumerate([12, 12, 18, 14, 10, 12, 12, 10, 10], 1):
ws.column_dimensions[openpyxl.utils.get_column_letter(col)].width = width
# ---------------------------------------------------------------------------
# Sheet 2: Employees
# ---------------------------------------------------------------------------
ws2 = wb.create_sheet("Employees")
emp_headers = ["Employee ID", "Name", "Department", "Title", "Hire Date", "Salary", "Performance Score"]
for col, h in enumerate(emp_headers, 1):
cell = ws2.cell(row=1, column=col, value=h)
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal="center")
departments = ["Engineering", "Sales", "Marketing", "Finance", "HR", "Operations"]
titles = {
"Engineering": ["Software Engineer", "Senior Engineer", "Tech Lead", "Engineering Manager"],
"Sales": ["Sales Rep", "Account Executive", "Sales Manager", "VP Sales"],
"Marketing": ["Marketing Coordinator", "Content Manager", "Marketing Director"],
"Finance": ["Analyst", "Senior Analyst", "Finance Manager", "CFO"],
"HR": ["HR Coordinator", "HR Manager", "VP People"],
"Operations": ["Operations Analyst", "Operations Manager", "COO"],
}
first_names = ["James", "Emma", "Liam", "Olivia", "Noah", "Ava", "Mason", "Sophia", "Ethan", "Isabella",
"Lucas", "Mia", "Logan", "Charlotte", "Jack", "Amelia", "Aiden", "Harper", "Owen", "Evelyn"]
last_names = ["Smith", "Johnson", "Williams", "Brown", "Jones", "Garcia", "Miller", "Davis", "Rodriguez", "Martinez",
"Hernandez", "Lopez", "Gonzalez", "Wilson", "Anderson", "Thomas", "Taylor", "Moore", "Jackson", "Martin"]
for i in range(1, 51):
dept = random.choice(departments)
name = f"{random.choice(first_names)} {random.choice(last_names)}"
title = random.choice(titles[dept])
hire_date = (base_date - timedelta(days=random.randint(30, 2000))).strftime("%Y-%m-%d")
salary = round(random.uniform(50000, 180000), -3)
score = round(random.uniform(2.5, 5.0), 1)
row = [f"EMP-{i:03d}", name, dept, title, hire_date, salary, score]
for col, val in enumerate(row, 1):
ws2.cell(row=i + 1, column=col, value=val).border = thin_border
for col, width in enumerate([12, 20, 14, 22, 12, 12, 16], 1):
ws2.column_dimensions[openpyxl.utils.get_column_letter(col)].width = width
# ---------------------------------------------------------------------------
# Save
# ---------------------------------------------------------------------------
out = Path(__file__).parent / "demo.xlsx"
wb.save(out)
print(f"Created {out}")
