[SCRIPT] export data kegiatan dengan pandas - fourslickz/notes GitHub Wiki

import pandas as pd
import mysql.connector
from openpyxl.styles import Font, PatternFill

# =============================
# Koneksi DB
# =============================
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="PASSWORD_DB",
    database="produksi"
)

query = """
SELECT
    p.nama  AS nama_provinsi,
    kb.nama AS nama_kabupaten,
    kc.nama AS nama_kecamatan,
    s.nama  AS nama_sekolah,
    k.*
FROM tt_kegiatan k
JOIN tm_ref_propinsi p ON p.id = k.provinsi_id
LEFT JOIN tm_ref_kabupaten kb ON kb.id = k.kabupaten_id
LEFT JOIN tm_ref_kecamatan kc ON kc.id = k.kecamatan_id
LEFT JOIN tm_data_sekolah s ON s.id = k.sekolah_id
ORDER BY p.nama, kb.nama, kc.nama, k.plan_jadwal_mulai
"""

df = pd.read_sql(query, conn)
conn.close()

# =============================
# SUMMARY DATA
# =============================
summary_df = (
    df.groupby(["nama_provinsi", "nama_kabupaten", "nama_kecamatan"])
      .size()
      .reset_index(name="jumlah_kegiatan")
      .sort_values(["nama_provinsi", "nama_kabupaten", "nama_kecamatan"])
)

# =============================
# Export Excel
# =============================
output_file = "export_tt_kegiatan_per_provinsi.xlsx"
header_fill = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid")
header_font = Font(color="FFFFFF", bold=True)

with pd.ExcelWriter(output_file, engine="openpyxl") as writer:

    # ===== Sheet SUMMARY =====
    summary_df.to_excel(
        writer,
        sheet_name="SUMMARY",
        index=False
    )

    ws_summary = writer.sheets["SUMMARY"]
    ws_summary.freeze_panes = "A2"
    ws_summary.auto_filter.ref = ws_summary.dimensions

    for cell in ws_summary[1]:
        cell.font = header_font
        cell.fill = header_fill

    # ===== Sheet per Provinsi =====
    for provinsi, data in df.groupby("nama_provinsi"):
        sheet_name = provinsi[:31]
        export_df = data.drop(columns=["nama_provinsi"])

        export_df.to_excel(
            writer,
            sheet_name=sheet_name,
            index=False
        )

        ws = writer.sheets[sheet_name]
        ws.freeze_panes = "A2"
        ws.auto_filter.ref = ws.dimensions

        for cell in ws[1]:
            cell.font = header_font
            cell.fill = header_fill

        # Auto width kolom
        for col in ws.columns:
            max_length = max(len(str(cell.value)) if cell.value else 0 for cell in col)
            ws.column_dimensions[col[0].column_letter].width = max_length + 2

print("Export selesai:", output_file)