from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.worksheet.datavalidation import DataValidation

wb=Workbook()
wb.remove(wb.active)

def make_list(name):
    ws=wb.create_sheet(name)
    headers=["Artikel","Kategorie","Link","Händler","Preis (€)","Priorität","Status","Bewertung","Datum","Notizen"]
    for i,h in enumerate(headers,1):
        c=ws.cell(1,i,h); c.font=Font(bold=True); c.fill=PatternFill("solid",fgColor="D9EAD3")
    dv=DataValidation(type="list",formula1='"Technik,Haushalt,Garten,Reisen,Kleidung,Hobby,Sonstiges"')
    ws.add_data_validation(dv); dv.add("B2:B200")
    for col,vals in [("D",'"Amazon,IKEA,MediaMarkt,Saturn,Otto,Apple,Sonstiges"'),
                     ("F",'"Hoch,Mittel,Niedrig"'),
                     ("G",'"Offen,Bestellt,Gekauft"'),
                     ("H",'"1,2,3,4,5"')]:
        d=DataValidation(type="list",formula1=vals); ws.add_data_validation(d); d.add(f"{col}2:{col}200")
    tab=Table(displayName=name.replace(" ",""),ref="A1:J200")
    tab.tableStyleInfo=TableStyleInfo(name="TableStyleMedium9",showRowStripes=True)
    ws.add_table(tab)
for s in ["Dashboard","Meine Wünsche","Wünsche Frau","Gemeinsame Wünsche","Weihnachten","Geburtstage"]:
    make_list(s)
dash=wb["Dashboard"]
dash["A1"]="Übersicht"; dash["A1"].font=Font(bold=True,size=16)
dash["A3"]="Diese Vorlage ist bereit für Google Sheets."
path="/mnt/data/GoogleSheets_Wunschlisten_Vorlage.xlsx"
wb.save(path)
print(path)