TEMPLATE_PELAYANAN_SURAT.xlsx

[Amirullah Saleh]

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.styles import Font, Alignment
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "DATA"
master = wb.create_sheet("MASTER")

# Header
headers = ["No", "Nomor Surat", "Tanggal", "Nama", "Tanggal Lahir",
           "Jenis Kelamin", "Nama Orang Tua", "Pekerjaan",
           "Alamat", "Keperluan", "Keterangan"]

ws.append(headers)

# Format header
for col in range(1, len(headers) + 1):
    cell = ws.cell(row=1, column=col)
    cell.font = Font(bold=True)
    cell.alignment = Alignment(horizontal="center", vertical="center")
    ws.column_dimensions[get_column_letter(col)].width = 20

# Nomor otomatis + format tanggal
for i in range(2, 102):
    ws.cell(row=i, column=1).value = f'=IF(B{i}="","",ROW()-1)'
    ws.cell(row=i, column=3).number_format = 'DD/MM/YYYY'
    ws.cell(row=i, column=5).number_format = 'DD/MM/YYYY'
    ws.cell(row=i, column=9).alignment = Alignment(wrap_text=True)

# MASTER data
master["A1"] = "L"
master["A2"] = "P"

keperluan_list = [
    "SURAT KETERANGAN BELUM MENIKAH",
    "SURAT KETERANGAN PENGHASILAN",
    "SURAT KETERANGAN DOMISILI TEMPAT TINGGAL",
    "SURAT KETERANGAN DOMISILI",
    "SURAT KETERANGAN JANDA/DUDA",
    "SURAT KETERANGAN PEMEKARAN WILAYAH",
    "SURAT KETERANGAN SATU ORANG YANG SAMA",
    "SURAT KETERANGAN NIKAH",
    "SURAT KETERANGAN PERNYATAAN AHLI WARIS",
    "PELAYANAN LAIN-LAIN"
]

for idx, item in enumerate(keperluan_list, start=1):
    master[f"B{idx}"] = item

# Dropdown Jenis Kelamin
dv_jk = DataValidation(type="list", formula1="=MASTER!$A$1:$A$2", allow_blank=True)
ws.add_data_validation(dv_jk)
dv_jk.add("F2:F101")

# Dropdown Keperluan
dv_kep = DataValidation(type="list", formula1="=MASTER!$B$1:$B$10", allow_blank=True)
ws.add_data_validation(dv_kep)
dv_kep.add("J2:J101")

# Freeze header
ws.freeze_panes = "A2"

wb.save("TEMPLATE_PELAYANAN_SURAT.xlsx")
print("File berhasil dibuat: TEMPLATE_PELAYANAN_SURAT.xlsx")