"""Excel output generation for attendance reports."""
from datetime import datetime
import datetime as dt
import tempfile
import os
from typing import List, Dict
try:
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.styles import Alignment
from openpyxl.styles.fonts import Font as xlfont
OPENPYXL_AVAILABLE = True
except ImportError:
OPENPYXL_AVAILABLE = False
class ExcelGenerator:
"""Generate Excel attendance reports."""
IML_ROOM_A = [
"01", "02", "03", "04", "05", "06",
"11", "12", "13", "14", "15", "16",
"21", "22", "23", "24", "25", "26",
"31", "32", "33", "34", "35", "36",
"41", "42", "43", "44",
"51", "52", "53", "54",
"K01", "K03", "61"
]
def __init__(self, template_path: str):
"""
Initialize Excel generator.
Args:
template_path: Path to Excel template file
"""
if not OPENPYXL_AVAILABLE:
raise ImportError("openpyxl is required for Excel generation")
self.template_path = template_path
def generate(self, records: List[List], from_time: datetime,
to_time: datetime, room_filters: Dict[str, bool]) -> str:
"""
Generate Excel file from attendance records.
Args:
records: List of attendance records
from_time: Start time for report
to_time: End time for report
room_filters: Dict of enabled room filters
Returns:
Path to generated temporary Excel file
"""
wb = load_workbook(self.template_path)
# Sort records by user_id + login time
records.sort(key=lambda x: x[4] + x[1])
# Process each room
for room in ["GRL", "ML1", "ML2", "IML"]:
# Remove existing sheet
sheet_name = room + "出席表"
if sheet_name in wb.sheetnames:
wb.remove(wb[sheet_name])
if room in room_filters and room_filters[room]:
self._create_room_sheet(wb, room, records, from_time, to_time, room_filters)
else:
# Also remove seating chart if room not selected
seating_name = room + "座席表"
if seating_name in wb.sheetnames:
wb.remove(wb[seating_name])
# Save to temporary file
filename = "attendancebook" + from_time.strftime("%Y%m%d%H%M") + ".xlsx"
tname = tempfile.mktemp(suffix=filename)
wb.save(tname)
return tname
def _create_room_sheet(self, wb, room: str, records: List[List],
from_time: datetime, to_time: datetime,
room_filters: Dict[str, bool]):
"""Create attendance sheet for a specific room."""
ws = wb.create_sheet(title=room + "出席表")
ws.sheet_properties.pageSetUpPr.fitToPage = True
# Determine room prefix
if room == "IML":
prefix = "IML"
table_prefix = "HSJ"
else:
prefix = room
table_prefix = room
# Filter records for this room
extract = []
for record in records:
host, logon, logoff, _, _, student_no, name, reading, dep, pos, status = record
if host is None or not host.startswith(prefix):
continue
# Handle IML sub-rooms
if room == "IML":
machine_num = host[4:]
in_room_a = machine_num in self.IML_ROOM_A
if in_room_a and not room_filters.get("IML-A", False):
continue
if not in_room_a and not room_filters.get("IML-B", False):
continue
extract.append([
host, str(student_no), logon[:5], logoff[-5:],
name, reading, dep, "", pos
])
# Set column widths
ws.column_dimensions["A"].width = 8.5
ws.column_dimensions["B"].width = 10.0
ws.column_dimensions["C"].width = 7.0
ws.column_dimensions["D"].width = 7.0
ws.column_dimensions["E"].width = 16.0
ws.column_dimensions["F"].width = 18.0
ws.column_dimensions["G"].width = 17.8
ws.column_dimensions["H"].width = 17.0
# Add header rows
ws.append([
"学生", "", "", "",
from_time.strftime("%Y/%m/%d"),
from_time.strftime("%H:%M") + "〜" + to_time.strftime("%H:%M")
])
ws.append(["ホスト名", "学籍番号", "開始", "終了", "名前", "よみ", "所属", "備考"])
# Process student records
stafflist = []
prev = None
align = Alignment(wrap_text=True, vertical="center", horizontal="center")
for e in extract:
try:
login = dt.time.fromisoformat(e[2])
logout = dt.time.fromisoformat(e[3])
font = None
# Check for missing logoff
if dt.time(18, 45) <= login and logout >= dt.time(19, 50):
e[3] = "記録無"
e[7] = "ログオフ記録無"
font = xlfont(color="b00000")
except (ValueError, TypeError):
# Handle invalid time formats
font = None
# Separate students from staff
if e[8] == "01" and not e[1].startswith("M"):
# Check if same user as previous (multiple logins)
if prev == e[1]:
row = str(ws.max_row)
ws['C' + row] = ws['C' + row].value + "\n" + str(e[2])
ws['D' + row] = ws['D' + row].value + "\n" + e[3]
else:
ws.append(e[:-1])
row = str(ws.max_row)
# Apply alignment to all cells
for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H']:
ws[col + row].alignment = align
if font:
ws[col + row].font = font
else:
stafflist.append(e[:-1])
prev = e[1]
# Create student table
if ws.max_row > 2:
tab = Table(
displayName="StudentTable" + table_prefix,
ref="A2:H" + str(ws.max_row)
)
style = TableStyleInfo(
name="TableStyleLight1",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False
)
tab.tableStyleInfo = style
ws.add_table(tab)
# Add student count
ws.append([])
ws.append(['合計', '=COUNTA(StudentTable' + table_prefix + '[学籍番号])', '人'])
# Add staff section
ws.append([])
ws.append(["スタッフ"])
ws.append(["ホスト名", "ユーザ名", "開始", "終了", "名前", "よみ", "所属", "ステータス"])
staff_start = ws.max_row
for e in stafflist:
ws.append(e)
row = str(ws.max_row)
for col in ['A', 'B', 'C', 'D', 'E', 'F', 'G']:
ws[col + row].alignment = align
if ws.max_row == staff_start:
ws.append(["-", "-", "-", "-", "-", "-", "-"])
# Create staff table
tab = Table(
displayName="StaffTable" + table_prefix,
ref="A" + str(staff_start) + ":G" + str(ws.max_row)
)
style = TableStyleInfo(
name="TableStyleLight2",
showFirstColumn=False,
showLastColumn=False,
showRowStripes=True,
showColumnStripes=False
)
tab.tableStyleInfo = style
ws.add_table(tab)
# Hide status column
ws.column_dimensions['H'].hidden = True
