96 lines
3.1 KiB
Python
96 lines
3.1 KiB
Python
from io import BytesIO
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import Alignment, Font
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
def build_overview_xlsx(period_name: str, rows: list):
|
|
"""
|
|
Build an Excel workbook for the time period overview.
|
|
|
|
period_name: e.g. "Dec 1..Dec 15, 2025" (or your custom name)
|
|
rows: list of dicts per employee:
|
|
{
|
|
"employee_name": str,
|
|
"regular": float,
|
|
"overtime": float,
|
|
"pto": float,
|
|
"holiday": float,
|
|
"paid_total": float,
|
|
"reimbursement": float,
|
|
"additional_payroll": float,
|
|
"notes": str,
|
|
}
|
|
"""
|
|
wb = Workbook()
|
|
ws = wb.active
|
|
ws.title = "Overview"
|
|
|
|
# Title row
|
|
ws["A1"] = period_name
|
|
ws["A1"].font = Font(bold=True, size=14)
|
|
|
|
headers = [
|
|
"Employee",
|
|
"Regular Hours",
|
|
"Overtime Hours",
|
|
"PTO Hours",
|
|
"Holiday",
|
|
"Reimbursement",
|
|
"Additional Payroll Changes",
|
|
"Total hours for pay period",
|
|
"Notes",
|
|
]
|
|
# Header row at row 2 (keep space for title row)
|
|
for col, text in enumerate(headers, start=1):
|
|
cell = ws.cell(row=2, column=col, value=text)
|
|
cell.font = Font(bold=True)
|
|
|
|
# Freeze the header row (row 2)
|
|
ws.freeze_panes = "A3"
|
|
|
|
# Data
|
|
row_idx = 3
|
|
for r in rows:
|
|
ws.cell(row=row_idx, column=1, value=r.get("employee_name", ""))
|
|
ws.cell(row=row_idx, column=2, value=r.get("regular", 0.0))
|
|
ws.cell(row=row_idx, column=3, value=r.get("overtime", 0.0))
|
|
ws.cell(row=row_idx, column=4, value=r.get("pto", 0.0))
|
|
ws.cell(row=row_idx, column=5, value=r.get("holiday", 0.0))
|
|
ws.cell(row=row_idx, column=6, value=r.get("reimbursement", 0.0) or 0.0)
|
|
ws.cell(row=row_idx, column=7, value=r.get("additional_payroll", 0.0) or 0.0)
|
|
ws.cell(row=row_idx, column=8, value=r.get("paid_total", 0.0))
|
|
ws.cell(row=row_idx, column=9, value=r.get("notes", "") or "")
|
|
row_idx += 1
|
|
|
|
# Number formats and alignment
|
|
num_cols = [2, 3, 4, 5, 8] # hour columns and total hours
|
|
currency_cols = [6, 7] # reimbursement, additional payroll
|
|
wrap_cols = [9] # notes
|
|
|
|
max_row = ws.max_row
|
|
for r in range(3, max_row + 1):
|
|
for c in num_cols:
|
|
cell = ws.cell(row=r, column=c)
|
|
cell.number_format = "0.00"
|
|
cell.alignment = Alignment(horizontal="right")
|
|
for c in currency_cols:
|
|
cell = ws.cell(row=r, column=c)
|
|
cell.number_format = "$#,##0.00"
|
|
cell.alignment = Alignment(horizontal="right")
|
|
for c in wrap_cols:
|
|
cell = ws.cell(row=r, column=c)
|
|
cell.alignment = Alignment(wrap_text=True)
|
|
|
|
# Autosize columns (basic heuristic)
|
|
for col in range(1, len(headers) + 1):
|
|
letter = get_column_letter(col)
|
|
max_len = len(headers[col - 1])
|
|
for cell in ws[letter]:
|
|
val = "" if cell.value is None else str(cell.value)
|
|
max_len = max(max_len, len(val))
|
|
ws.column_dimensions[letter].width = min(max_len + 2, 40)
|
|
|
|
buf = BytesIO()
|
|
wb.save(buf)
|
|
buf.seek(0)
|
|
return buf.getvalue() |