app / export /excel_exporter.py
Ryo_Shimano
Claude Sonnet 4.6
朝のみ可・晩のみ可の中央セル黄色塗りを除去
e809d1f
Raw History Blame Contribute Delete
25.1 kB
"""Excel出力モジュール(SDU_shift テンプレート流し込み方式)
export/templates/SDU_shift_template.xlsx を読み込み、生成済みシフトデータを
値として流し込む。書式・マージ・列幅・印刷設定はテンプレートのものを保持する。
テンプレートのレイアウト(1日 = 3列: 開始時刻 / 区切り("-")・備考 / 終了時刻):
行1-2 : 日付・曜日(16ブロック、B〜AW列)
行3-5 : メモ1(日付ごと縦3行マージ)← 日付メモ(schedule_notes)を流し込み
行6 : メモ2(空欄のまま)
行7-8 : 朝食見込・夜予約 ← 予約客数(reservation_counts)を流し込み
行9-10 : キッチン社員 / 行11-28: キッチンA・P
行29-32 : 2ブロック目ヘッダー(日付・曜日・朝食見込・夜予約=1ブロック目参照式)
行33-35 : ホール社員 / 行36-51: ホールA・P
行52-53 : フッター(日付・曜日)
AX列 : 右側氏名 / AY列: 勤務時間合計式 / AZ列: 人件費式(時給×時間)
従業員数が枠数を超える場合はグループ末尾に行を挿入する(行高・マージも追従)。
日付ブロックは期間日数(月によって13〜16日)に合わせて余りを列削除し、空白を作らない。
"""
from __future__ import annotations
from copy import copy
from datetime import date
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Alignment, Font
from openpyxl.utils import get_column_letter
from models.employee import Employee
from models.schedule import SchedulePeriod
from utils.constants import TimeSlot, EmploymentType, PrimaryPosition
from utils.holidays import holiday_set
TEMPLATE_PATH = Path(__file__).parent / "templates" / "SDU_shift_template.xlsx"
DAY_JP = ["月", "火", "水", "木", "金", "土", "日"]
# ── テンプレートのレイアウト定数 ─────────────────────────────────────────
N_BLOCKS = 16 # テンプレートの日付ブロック数(B〜AW列)。期間日数に合わせて余りは列削除する
COLS_PER_DAY = 3 # 1日 = 3列(開始 / 区切り・備考 / 終了)
C_DATA = 2 # データ先頭列(B)
C_NAME_L = 1 # 氏名列(左、A)
NAME_W = 17.63 # 氏名列の幅
DAY_W = 5.75 # 日付1列の幅
R_DATE1, R_DOW1 = 1, 2 # 1ブロック目: 日付・曜日
R_MEMO1, R_MEMO2 = 3, 6 # メモ1(3-5行マージ)・メモ2
R_BF, R_DIN = 7, 8 # 朝食見込・夜予約
K_FT_START, K_FT_SLOTS = 9, 2 # キッチン社員
K_PT_START, K_PT_SLOTS = 11, 18 # キッチンA・P
R_DATE2 = 29 # 2ブロック目ヘッダー(日付/曜日/朝食見込/夜予約)
H_FT_START, H_FT_SLOTS = 33, 3 # ホール社員
H_PT_START, H_PT_SLOTS = 36, 16 # ホールA・P
R_DATE3 = 52 # フッター(日付/曜日)
# ── ステータス別のセル装飾(完成イメージ SDU_Shift_full.xlsx に準拠)────
FILL_NONE = PatternFill(fill_type=None)
FILL_NOTE = PatternFill("solid", fgColor="FFFF00") # 黄: 備考メモ
FILL_LEAVE = PatternFill("solid", fgColor="B3E5A1") # 緑: 有給(3セル全体)
FILL_FTOFF = PatternFill("solid", fgColor="FF0000") # 赤: 社員の休み・指定休(3セル全体)
FILL_SAT = PatternFill("solid", fgColor="60CBF3") # 水色: 土曜の日付・曜日
FILL_SUN = PatternFill("solid", fgColor="F6C6AC") # オレンジ: 日曜・祝日の日付・曜日
# 全セル縦横中央揃え(テンプレートの氏名列は標準=左寄せのため明示的に上書き)
ALIGN_CC = Alignment(horizontal="center", vertical="center")
# ── ユーティリティ ────────────────────────────────────────────────────────
def _to_decimal(time_str: str) -> str:
"""'HH:MM' → 小数時刻文字列 ('5.75', '22.5', '13' など)"""
if not time_str:
return ""
try:
h, m = map(int, time_str.split(":"))
if m == 0:
return str(h)
val = h + m / 60.0
return f"{val:.2f}".rstrip("0").rstrip(".")
except Exception:
return time_str
def _get_shift_cells(
emp_id: int,
date_str: str,
assignments: dict,
req_map: dict,
cell_notes: dict,
) -> tuple[str, str, str, str]:
"""
(開始時刻セル, 区切り/備考セル, 終了時刻セル, スタイル種別) を返す。
スタイル種別: 'assigned' | 'assigned_note' | 'leave' | 'off' | 'ft_off' | 'am_only' | 'pm_only'
表示フォーマット:
通常シフト : ("13", "-", "22.5")
備考付きシフト: ("6.5", "オムレツ", "15")
有給 : ("", "有給", "")
休み : ("", "-", "")
assignments の値は (position_value, is_reinforcement, reinf_start, reinf_end) の4タプル。
"""
from utils.shift_patterns import PATTERN_MAP
req = req_map.get((emp_id, date_str))
note = (req.note or "").strip() if req else ""
# スタッフ個別コメント(セル単位のメモ)があれば優先して区切りセルに表示
cnote = (cell_notes.get((emp_id, date_str)) or "").strip()
if cnote:
note = cnote
# 正社員希望休
if req and req.pattern_id == "off_request":
return "", "-", "", "ft_off"
# 有給チェック(paid_leave パターン or メモに「有給」)
if (req and req.pattern_id == "paid_leave") or "有給" in note:
return "", "有給", "", "leave"
# アサイン確認
b_raw = assignments.get((emp_id, date_str, TimeSlot.BREAKFAST.value))
d_raw = assignments.get((emp_id, date_str, TimeSlot.DINNER.value))
if not b_raw and not d_raw:
if req and req.pattern_id == "am_only":
return "6", "-", "15", "am_only"
if req and req.pattern_id == "pm_only":
return "14", "-", "23", "pm_only"
if cnote:
return "", cnote, "", "assigned_note"
return "", "-", "", "off"
# 4タプルから位置情報を展開
b_pos, _b_is_reinf, b_rs, b_re = b_raw if b_raw else (None, False, None, None)
d_pos, _d_is_reinf, d_rs, d_re = d_raw if d_raw else (None, False, None, None)
# 応援・希望時間からの変更は reinf_start/reinf_end を優先して使用
if (b_rs and b_re) or (d_rs and d_re):
if b_raw and not d_raw and b_rs and b_re:
s, e = _to_decimal(b_rs), _to_decimal(b_re)
elif d_raw and not b_raw and d_rs and d_re:
s, e = _to_decimal(d_rs), _to_decimal(d_re)
elif b_raw and d_raw:
s = _to_decimal(b_rs) if b_rs else "6"
e = _to_decimal(d_re) if d_re else "23"
else:
s, e = _slot_default(b_pos, d_pos)
if note:
return s, note, e, "assigned_note"
return s, "-", e, "assigned"
# 時刻取得(パターンから)
# 「ダブル」は朝食・ディナー両方が割当済みの場合のみ通し表示にする。
# 片方のみの割当の場合は実際の割当時間帯を表示する。
if req and req.pattern_id == "double" and b_raw and d_raw:
s, e = "6", "23"
elif req and req.pattern_id == "custom" and req.custom_start and req.custom_end:
s = _to_decimal(req.custom_start)
e = _to_decimal(req.custom_end)
elif req and req.pattern_id and req.pattern_id != "double":
p = PATTERN_MAP.get(req.pattern_id)
if p and p.start and p.end:
s = _to_decimal(p.start)
e = _to_decimal(p.end)
else:
s, e = _slot_default(b_pos, d_pos)
else:
s, e = _slot_default(b_pos, d_pos)
# 備考を時刻の間(区切りセル)に挟む形式
if note:
return s, note, e, "assigned_note"
return s, "-", e, "assigned"
def _slot_default(b_pos, d_pos) -> tuple[str, str]:
"""ポジション別のデフォルト勤務時間を返す(HH:MM を小数時刻に変換)"""
from db import repositories as repo
pos_key = b_pos or d_pos or "hall" # ポジション名 ("hall" or "kitchen")
if b_pos and d_pos:
s = _to_decimal(repo.get_app_setting(f"ft_{pos_key}_breakfast_start", "06:00"))
e = _to_decimal(repo.get_app_setting(f"ft_{pos_key}_dinner_end", "22:00"))
elif b_pos:
s = _to_decimal(repo.get_app_setting(f"ft_{pos_key}_breakfast_start", "06:00"))
e = _to_decimal(repo.get_app_setting(f"ft_{pos_key}_breakfast_end", "11:00"))
else:
s = _to_decimal(repo.get_app_setting(f"ft_{pos_key}_dinner_start", "17:00"))
e = _to_decimal(repo.get_app_setting(f"ft_{pos_key}_dinner_end", "22:00"))
return s, e
def _num_or_text(v: str):
"""数値に変換できれば float/int、できなければ文字列のまま返す"""
if v == "":
return None
try:
f = float(v)
return int(f) if f.is_integer() else f
except ValueError:
return v
def _insert_rows_keep_layout(ws, idx: int, amount: int, max_col: int):
"""idx 行の直前に amount 行挿入する。
openpyxl の insert_rows はマージセル・行高を追従させないため手動でずらし、
挿入行のスタイル・行高は直前の行(同グループの枠)からコピーする。"""
if amount <= 0:
return
# マージ範囲の退避とシフト
to_shift = [r for r in list(ws.merged_cells.ranges) if r.min_row >= idx]
for r in to_shift:
ws.unmerge_cells(str(r))
ws.insert_rows(idx, amount)
for r in to_shift:
ws.merge_cells(start_row=r.min_row + amount, end_row=r.max_row + amount,
start_column=r.min_col, end_column=r.max_col)
# 行高のシフト(下から処理して上書きを防ぐ)
heights = {r: dim.height for r, dim in ws.row_dimensions.items()
if r >= idx and dim.height is not None}
for r in sorted(heights, reverse=True):
ws.row_dimensions[r + amount].height = heights[r]
# 挿入行: 直前の行からスタイル・行高をコピー
donor = idx - 1
donor_h = ws.row_dimensions[donor].height
for r in range(idx, idx + amount):
if donor_h:
ws.row_dimensions[r].height = donor_h
for c in range(1, max_col + 1):
ws.cell(r, c)._style = copy(ws.cell(donor, c)._style)
def _delete_trailing_blocks(ws, n: int):
"""期間日数 n に合わせて余った日付ブロック列を削除する(空白ブロックを作らない)。
openpyxl の delete_cols はマージセルを追従させないため手動でずらす。"""
extra = N_BLOCKS - n
if extra <= 0:
return
first = C_DATA + n * COLS_PER_DAY # 削除開始列
amount = extra * COLS_PER_DAY
for rng in list(ws.merged_cells.ranges):
if rng.min_col >= first + amount:
# 削除範囲より右(氏名列など)→ 左へシフト
ws.unmerge_cells(str(rng))
ws.merge_cells(start_row=rng.min_row, end_row=rng.max_row,
start_column=rng.min_col - amount,
end_column=rng.max_col - amount)
elif rng.min_col >= first:
# 削除範囲内のブロックマージ → 解除
ws.unmerge_cells(str(rng))
ws.delete_cols(first, amount)
# 列幅を再構築(テンプレートのグループ幅指定は削除に追従しないため)
for letter in list(ws.column_dimensions.keys()):
del ws.column_dimensions[letter]
c_name_r = C_DATA + n * COLS_PER_DAY
ws.column_dimensions[get_column_letter(C_NAME_L)].width = NAME_W
for c in range(C_DATA, c_name_r):
ws.column_dimensions[get_column_letter(c)].width = DAY_W
ws.column_dimensions[get_column_letter(c_name_r)].width = NAME_W
def _block_col(i: int) -> int:
"""日付ブロック i (0始まり) の先頭列"""
return C_DATA + i * COLS_PER_DAY
def _hours_formula(row: int, n: int) -> str:
"""=(D9-B9)+(G9-E9)+... 形式の勤務時間合計式(期間 n 日分)を返す"""
terms = []
for i in range(n):
c = _block_col(i)
terms.append(f"({get_column_letter(c + 2)}{row}-{get_column_letter(c)}{row})")
return "=" + "+".join(terms)
# ── 出力前チェック ────────────────────────────────────────────────────────
def layout_warnings(employees: list[Employee]) -> list[str]:
"""シフト表出力時のレイアウト上の注意点を返す(出力画面での事前警告用)。
- キッチン/ホールいずれかの所属スタッフが0名
- グループの枠数(K社員2/K A・P18/H社員3/H A・P16)を超えて行追加が発生する
"""
def _out_pos(e):
return e.output_position or e.primary_position
def _is_ft(e):
return e.employment_type == EmploymentType.FULL_TIME
kitchen = [e for e in employees if _out_pos(e) == PrimaryPosition.KITCHEN]
hall = [e for e in employees if _out_pos(e) != PrimaryPosition.KITCHEN]
warnings: list[str] = []
if not kitchen:
warnings.append("キッチン所属のスタッフが0名です(全員ホール枠に出力されます)。"
"従業員の「所属ポジション」を確認してください")
if not hall:
warnings.append("ホール所属のスタッフが0名です。"
"従業員の「所属ポジション」を確認してください")
groups = [
("キッチン社員", [e for e in kitchen if _is_ft(e)], K_FT_SLOTS),
("キッチンA・P", [e for e in kitchen if not _is_ft(e)], K_PT_SLOTS),
("ホール社員", [e for e in hall if _is_ft(e)], H_FT_SLOTS),
("ホールA・P", [e for e in hall if not _is_ft(e)], H_PT_SLOTS),
]
for label, emps, slots in groups:
if len(emps) > slots:
warnings.append(f"{label}が{len(emps)}名で枠数({slots})を超えるため、"
f"行を追加して出力します")
return warnings
# ── メイン出力 ────────────────────────────────────────────────────────────
def export_excel(
path: str,
period: SchedulePeriod,
employees: list[Employee],
assignments: dict,
show_reservation_counts: bool = True,
):
"""SDU_shift テンプレートにシフトデータを流し込んで Excel を出力する。
show_reservation_counts=False の場合、「朝食見込」「夜予約」行(1・2ブロック目とも)
を非表示にする(全体周知用シフト表向け。値自体はセルに残るため再表示も可能)。"""
from db import repositories as repo
requests = repo.get_shift_requests(period.id)
req_map = {(r.employee_id, r.date): r for r in requests}
notes = repo.get_schedule_notes(period.id) # 全体向けお知らせ → メモ1欄
staff_notes = repo.get_staff_notes(period.id) # 社員向けお知らせ → メモ2欄
reserves = repo.get_reservation_counts(period.id) # 予約客数 → 朝食見込・夜予約
cell_notes = repo.get_cell_notes(period.id) # スタッフ個別コメント → 区切りセル
app_settings = repo.get_all_app_settings()
base_hourly_wage = int(app_settings.get("base_hourly_wage", "0") or 0)
wb = load_workbook(TEMPLATE_PATH)
ws = wb.active
dates = list(period.date_range())[:N_BLOCKS] # テンプレートは最大16日分
n = len(dates)
holidays = holiday_set(dates)
# 月の日数(28/30/31日)に合わせて余りブロックを列ごと削除し、空白を作らない
_delete_trailing_blocks(ws, n)
c_name_r = C_DATA + n * COLS_PER_DAY # 氏名列(右)
c_hours = c_name_r + 1 # 勤務時間合計式
c_wage = c_name_r + 2 # 人件費式
# 削除で左に寄ってきたテンプレート外の作業メモ等を消す
for row in ws.iter_rows(min_row=1, max_row=100, min_col=c_wage + 1, max_col=63):
for cell in row:
if cell.value is not None:
cell.value = None
# ── 従業員をテンプレートの4グループに分類 ─────────────────────────
# output_position 優先、未設定なら primary_position で判断
def _out_pos(e):
return e.output_position or e.primary_position
def _is_ft(e):
return e.employment_type == EmploymentType.FULL_TIME
kitchen = [e for e in employees if _out_pos(e) == PrimaryPosition.KITCHEN]
hall = [e for e in employees if _out_pos(e) != PrimaryPosition.KITCHEN]
k_ft = [e for e in kitchen if _is_ft(e)]
k_pt = [e for e in kitchen if not _is_ft(e)]
h_ft = [e for e in hall if _is_ft(e)]
h_pt = [e for e in hall if not _is_ft(e)]
# ── 枠が足りないグループには行を挿入(下のグループから処理)────────
k_ft_extra = max(0, len(k_ft) - K_FT_SLOTS)
k_pt_extra = max(0, len(k_pt) - K_PT_SLOTS)
h_ft_extra = max(0, len(h_ft) - H_FT_SLOTS)
h_pt_extra = max(0, len(h_pt) - H_PT_SLOTS)
_insert_rows_keep_layout(ws, H_PT_START + H_PT_SLOTS, h_pt_extra, c_wage)
_insert_rows_keep_layout(ws, H_FT_START + H_FT_SLOTS, h_ft_extra, c_wage)
_insert_rows_keep_layout(ws, K_PT_START + K_PT_SLOTS, k_pt_extra, c_wage)
_insert_rows_keep_layout(ws, K_FT_START + K_FT_SLOTS, k_ft_extra, c_wage)
# 挿入後の行位置
ko = k_ft_extra + k_pt_extra
k_pt_row = K_PT_START + k_ft_extra
r_date2 = R_DATE2 + ko
h_ft_row = H_FT_START + ko
h_pt_row = H_PT_START + ko + h_ft_extra
r_date3 = R_DATE3 + ko + h_ft_extra + h_pt_extra
# ── タイトル・シート名 ─────────────────────────────────────────────
start_d = date.fromisoformat(period.start_date)
end_d = date.fromisoformat(period.end_date)
half = "前半" if start_d.day <= 15 else "後半"
draft = "" if period.status == "confirmed" else "(案)"
title = f"{start_d.month}月      {half}{draft}"
for r in (R_DATE1, r_date2, r_date3):
for c in (C_NAME_L, c_name_r):
cell = ws.cell(r, c)
cell.value = title
cell.alignment = ALIGN_CC
ws.title = (f"{start_d.month:02d}{start_d.day:02d}_{end_d.month:02d}{end_d.day:02d}"
+ ("(確定)" if period.status == "confirmed" else ""))
# ── ヘッダー・フッター(日付/曜日/メモ/朝食見込/夜予約)────────────
for i, d in enumerate(dates):
col = _block_col(i)
cl = get_column_letter(col)
ds = d.isoformat()
day_val = d.day
dow = DAY_JP[d.weekday()]
note = notes.get(ds, "") or None
res = reserves.get(ds, {})
bf = res.get("breakfast")
din = res.get("dinner")
if d.weekday() == 5:
day_fill = FILL_SAT
elif d.weekday() == 6 or ds in holidays:
day_fill = FILL_SUN
else:
day_fill = FILL_NONE
for r in (R_DATE1, r_date2, r_date3):
ws.cell(r, col).value = day_val
ws.cell(r + 1, col).value = dow
# 土曜=水色 / 日曜・祝日=オレンジ(マージ範囲の3セルすべてに塗る)
for cc in range(col, col + COLS_PER_DAY):
ws.cell(r, cc).fill = day_fill
ws.cell(r + 1, cc).fill = day_fill
ws.cell(R_MEMO1, col).value = note
ws.cell(R_MEMO2, col).value = staff_notes.get(ds, "") or None
ws.cell(R_BF, col).value = bf
ws.cell(R_DIN, col).value = din
# 2ブロック目の朝食見込・夜予約は1ブロック目を参照する式(テンプレート流儀)
ws.cell(r_date2 + 2, col).value = f"={cl}{R_BF}" if bf is not None else None
ws.cell(r_date2 + 3, col).value = f"={cl}{R_DIN}" if din is not None else None
# 2ブロック目の朝食見込・夜予約ラベルとヘッダーラベルの中央揃え
for r, label in ((R_BF, "朝食見込"), (R_DIN, "夜予約"),
(r_date2 + 2, "朝食見込"), (r_date2 + 3, "夜予約")):
for c in (C_NAME_L, c_name_r):
cell = ws.cell(r, c)
cell.value = label
cell.alignment = ALIGN_CC
# 「朝食見込・夜予約」行を非表示(全体周知用シフト表向け。値は残し再表示も可能)
if not show_reservation_counts:
for r in (R_BF, R_DIN, r_date2 + 2, r_date2 + 3):
ws.row_dimensions[r].hidden = True
# ── 従業員グループの流し込み ───────────────────────────────────────
def _fill_group(start_row: int, slot_count: int, emps: list[Employee]):
for j in range(slot_count):
r = start_row + j
emp = emps[j] if j < len(emps) else None
for c in (C_NAME_L, c_name_r):
cell = ws.cell(r, c)
cell.value = emp.name if emp else None
cell.alignment = ALIGN_CC
# 全ブロックをクリア(テンプレートの凡例サンプル・前回値を消す)
for i in range(n):
col = _block_col(i)
for cc in range(col, col + COLS_PER_DAY):
cell = ws.cell(r, cc)
cell.value = None
cell.fill = FILL_NONE
if emp is None:
ws.cell(r, c_hours).value = None
ws.cell(r, c_wage).value = None
continue
for i, d in enumerate(dates):
col = _block_col(i)
s_txt, m_txt, e_txt, style = _get_shift_cells(
emp.id, d.isoformat(), assignments, req_map, cell_notes)
ws.cell(r, col).value = _num_or_text(s_txt)
ws.cell(r, col + 1).value = m_txt or None
ws.cell(r, col + 2).value = _num_or_text(e_txt)
if style == "assigned_note":
ws.cell(r, col + 1).fill = FILL_NOTE
elif style == "leave":
# 有給は3セル全体を緑塗り(完成イメージ準拠)
for cc in range(col, col + COLS_PER_DAY):
ws.cell(r, cc).fill = FILL_LEAVE
elif style == "ft_off" or (
style == "off" and emp.employment_type == EmploymentType.FULL_TIME
):
# 社員の休み・指定休は3セル全体を赤塗り
for cc in range(col, col + COLS_PER_DAY):
ws.cell(r, cc).fill = FILL_FTOFF
ws.cell(r, c_hours).value = _hours_formula(r, n)
if _is_ft(emp):
# 社員は統計画面と同様に時給計算の対象外
ws.cell(r, c_wage).value = None
else:
# 実効時給 = 基本時給 + 個人昇給額(統計画面の base_rate と同じ計算)
rate = base_hourly_wage + emp.hourly_wage
ws.cell(r, c_wage).value = (
f"={get_column_letter(c_hours)}{r}*{rate}"
if rate else None)
_fill_group(K_FT_START, K_FT_SLOTS + k_ft_extra, k_ft)
_fill_group(k_pt_row, K_PT_SLOTS + k_pt_extra, k_pt)
_fill_group(h_ft_row, H_FT_SLOTS + h_ft_extra, h_ft)
_fill_group(h_pt_row, H_PT_SLOTS + h_pt_extra, h_pt)
# ── 凡例(フッターの1行下: 黄=メモ / 赤=社員休 / 緑=有給)──────────
r_leg = r_date3 + 2
leg_font = Font(size=10, bold=True)
cell = ws.cell(r_leg, C_NAME_L)
cell.value, cell.font, cell.alignment = "凡例:", leg_font, ALIGN_CC
for i, (fill, label) in enumerate(
[(FILL_NOTE, "メモ"), (FILL_FTOFF, "社員休"), (FILL_LEAVE, "有給")]):
col = _block_col(i)
for cc in range(col, col + COLS_PER_DAY):
ws.cell(r_leg, cc).fill = fill
ws.merge_cells(start_row=r_leg, start_column=col,
end_row=r_leg, end_column=col + COLS_PER_DAY - 1)
cell = ws.cell(r_leg, col)
cell.value, cell.font, cell.alignment = label, leg_font, ALIGN_CC
ws.row_dimensions[r_leg].height = 18
wb.save(path)