---
name: xlsx
description: "Use this skill whenever the user wants to create, edit, read, or analyze Excel spreadsheets (.xlsx files). Triggers include: any mention of 'Excel', 'spreadsheet', '.xlsx', 'xls', or requests to produce financial models, data tables, pivot reports, or calculation sheets. Also use when building insurance premium calculators, loss ratio analyses, financial projections, or any tabular data with formulas. If the user asks for a '보험료 계산표', '실적표', '손해율', '재무모델', or similar deliverable as an Excel file, use this skill. Do NOT use for Word documents, PowerPoint presentations, PDFs, or Google Sheets tasks unrelated to .xlsx generation."
license: Proprietary. LICENSE.txt has complete terms
---

# XLSX 생성, 편집, 분석

## Overview

openpyxl로 Excel 파일의 셀 값, 수식, 서식을 제어하고, pandas로 대용량 데이터를 분석합니다.
핵심 원칙: **수식은 Python에서 계산 후 하드코딩 금지** — 반드시 Excel 수식 문자열로 작성하여 스프레드시트 자체에서 재계산 가능하게 유지해야 합니다.

## Quick Reference

| 작업 | 방법 |
|------|------|
| 새 파일 생성 | `openpyxl.Workbook()` |
| 기존 파일 읽기 | `openpyxl.load_workbook()` |
| 대용량 데이터 읽기 | `pandas.read_excel()` |
| 수식 입력 | `cell.value = "=SUM(B2:B10)"` 형태로 문자열 할당 |
| 수식 강제 재계산 | LibreOffice 기반 `recalc` (아래 참조) |
| 서식 적용 | `openpyxl.styles` 모듈 사용 |

---

## 설치 요구사항

```bash
pip install openpyxl pandas
pip install openpyxl[charts]   # 차트 기능 (선택)
```

---

## Quick Start

```python
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "보험료 계산표"

# 헤더 작성
ws["A1"] = "상품명"
ws["B1"] = "월 보험료(원)"
ws["C1"] = "연 보험료(원)"

# 데이터 입력 (하드코딩)
ws["A2"] = "실손의료보험"
ws["B2"] = 91000

# 수식 입력 (절대 하드코딩 금지 — 수식 문자열 사용)
ws["C2"] = "=B2*12"

wb.save("output.xlsx")
```

---

## 재무 모델 색상 코딩 표준

Excel 재무 모델에서 셀 유형을 색상으로 구분하는 업계 표준입니다.
이 규칙을 항상 준수하여 모델 유지보수성을 높이세요.

| 셀 유형 | 색상 | HEX | 의미 |
|---------|------|-----|------|
| 입력값 (Input) | 파란 글씨 | `#0000FF` | 사용자가 직접 입력하는 가정값 |
| 수식 (Formula) | 검정 글씨 | `#000000` | 시트 내 계산 수식 |
| 시트간 링크 | 초록 글씨 | `#008000` | 동일 워크북 내 다른 시트 참조 |
| 외부 링크 | 빨간 글씨 | `#FF0000` | 외부 파일/워크북 참조 |

```python
from openpyxl.styles import Font

# 입력값 셀 — 파란 글씨
def style_input(cell, value):
    cell.value = value
    cell.font = Font(color="0000FF", bold=False)

# 수식 셀 — 검정 글씨 (기본값이지만 명시)
def style_formula(cell, formula):
    cell.value = formula  # 반드시 "=..." 형태
    cell.font = Font(color="000000")

# 시트간 링크 셀 — 초록 글씨
def style_sheet_link(cell, formula):
    cell.value = formula  # 예: "=Sheet1!B5"
    cell.font = Font(color="008000")
```

---

## 수식 규칙 (Zero Formula Errors 원칙)

**반드시 지켜야 할 5가지 금지 에러:**

| 에러 | 원인 | 방지 방법 |
|------|------|-----------|
| `#REF!` | 삭제된 셀 참조 | 행/열 삭제 전 참조 확인 |
| `#DIV/0!` | 0으로 나누기 | `=IFERROR(A/B, 0)` 또는 `=IF(B=0, 0, A/B)` |
| `#VALUE!` | 잘못된 데이터 타입 | 숫자/텍스트 혼용 방지 |
| `#N/A` | VLOOKUP 미매칭 | `=IFERROR(VLOOKUP(...), "미해당")` |
| `#NAME?` | 잘못된 함수명 | 함수명 철자 확인, 영문 사용 |

```python
# 올바른 수식 작성 예시
ws["D2"] = "=IFERROR(C2/B2, 0)"          # 0 나누기 방지
ws["E2"] = "=IF(B2=0, \"-\", C2/B2)"     # 조건부 처리
ws["F2"] = "=IFERROR(VLOOKUP(A2, $H:$I, 2, FALSE), \"미매칭\")"

# Python에서 계산 후 하드코딩 — 절대 금지
# ws["C2"] = 91000 * 12  # BAD: Excel에서 재계산 불가
ws["C2"] = "=B2*12"      # GOOD: Excel 수식으로 입력
```

---

## 서식 적용

### 헤더 서식

```python
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

NAVY_FILL = PatternFill("solid", fgColor="1F3564")
WHITE_FONT = Font(color="FFFFFF", bold=True, name="맑은 고딕", size=11)
CENTER_ALIGN = Alignment(horizontal="center", vertical="center", wrap_text=True)

def style_header(cell, text):
    cell.value = text
    cell.fill = NAVY_FILL
    cell.font = WHITE_FONT
    cell.alignment = CENTER_ALIGN

# 헤더 행 서식 적용
headers = ["상품코드", "상품명", "월보험료", "연보험료", "손해율(%)"]
for col, header in enumerate(headers, start=1):
    style_header(ws.cell(row=1, column=col), header)

# 행 높이 설정
ws.row_dimensions[1].height = 28
```

### 숫자/날짜 서식

```python
from openpyxl.styles import numbers

# 한국 통화 형식: ₩#,##0
ws["B2"].number_format = '₩#,##0'

# 천 단위 구분 숫자
ws["C2"].number_format = '#,##0'

# 백분율 (소수점 1자리)
ws["D2"].number_format = '0.0%'

# 한국 날짜 형식
ws["E2"].number_format = 'YYYY-MM-DD'

# 소수점 2자리
ws["F2"].number_format = '0.00'
```

### 셀 테두리

```python
from openpyxl.styles import Border, Side

THIN = Side(style="thin", color="AAAAAA")
THICK = Side(style="medium", color="1F3564")

def add_border(cell, style="thin"):
    s = Side(style=style, color="AAAAAA")
    cell.border = Border(top=s, bottom=s, left=s, right=s)

# 범위에 테두리 일괄 적용
for row in ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=5):
    for cell in row:
        add_border(cell)
```

### 열 너비 자동 조정

```python
from openpyxl.utils import get_column_letter

def auto_fit_columns(ws, min_width=8, max_width=40):
    for col in ws.columns:
        max_len = 0
        col_letter = get_column_letter(col[0].column)
        for cell in col:
            if cell.value:
                # 한글은 영문의 약 2배 너비
                text = str(cell.value)
                length = sum(2 if ord(c) > 127 else 1 for c in text)
                max_len = max(max_len, length)
        ws.column_dimensions[col_letter].width = max(min_width, min(max_len + 2, max_width))
```

---

## 다중 시트 관리

```python
wb = Workbook()

# 기본 시트 이름 변경
ws_input = wb.active
ws_input.title = "입력값"

# 시트 추가
ws_calc  = wb.create_sheet("계산시트")
ws_output = wb.create_sheet("결과표")
ws_raw   = wb.create_sheet("원시데이터")

# 시트간 참조 (초록 글씨 적용)
ws_output["B2"] = "=계산시트!C5"        # 동일 워크북 다른 시트 참조
ws_output["B2"].font = Font(color="008000")
```

---

## pandas 연동

pandas는 데이터 읽기/분석에, openpyxl은 수식/서식 작성에 사용합니다.

```python
import pandas as pd
import openpyxl

# 데이터 읽기
df = pd.read_excel("raw_data.xlsx", sheet_name="원시데이터")

# pandas로 분석
summary = df.groupby("보험상품").agg(
    계약건수=("계약번호", "count"),
    총보험료=("월보험료", "sum"),
    평균손해율=("손해율", "mean"),
).reset_index()

# openpyxl로 서식 포함 출력
wb = openpyxl.load_workbook("template.xlsx")
ws = wb["결과표"]

for row_idx, row in summary.iterrows():
    ws_row = row_idx + 2  # 헤더 다음 행
    ws.cell(ws_row, 1).value = row["보험상품"]
    ws.cell(ws_row, 2).value = row["계약건수"]
    ws.cell(ws_row, 3).value = row["총보험료"]
    ws.cell(ws_row, 3).number_format = '₩#,##0'
    ws.cell(ws_row, 4).value = row["평균손해율"] / 100
    ws.cell(ws_row, 4).number_format = '0.0%'

wb.save("결과표.xlsx")
```

---

## 수식 재계산 (LibreOffice 기반)

openpyxl로 수식을 작성한 파일은 Excel에서 열 때 자동 재계산됩니다.
서버 환경(CI/자동화)에서 재계산된 값이 필요한 경우:

```python
import subprocess, shutil

def recalculate_xlsx(input_path: str, output_path: str) -> str:
    """LibreOffice로 수식을 재계산한 xlsx 파일을 반환합니다."""
    result = subprocess.run(
        [
            "libreoffice", "--headless",
            "--convert-to", "xlsx",
            "--outdir", "/tmp",
            input_path,
        ],
        capture_output=True, text=True, timeout=60
    )
    if result.returncode != 0:
        raise RuntimeError(f"LibreOffice 변환 실패: {result.stderr}")

    import os
    basename = os.path.splitext(os.path.basename(input_path))[0]
    converted = f"/tmp/{basename}.xlsx"
    shutil.move(converted, output_path)
    return output_path
```

---

## 수식 검증 체크리스트

xlsx 파일 저장 전 반드시 확인:

- [ ] `#REF!` — 참조 오류 없음
- [ ] `#DIV/0!` — 0 나누기 없음 (`IFERROR` 또는 `IF` 처리)
- [ ] `#VALUE!` — 타입 오류 없음 (숫자 열에 텍스트 없음)
- [ ] `#N/A` — VLOOKUP/MATCH 미매칭 처리됨
- [ ] `#NAME?` — 함수명 올바름
- [ ] 입력값 셀: 파란 글씨 (`#0000FF`)
- [ ] 수식 셀: 검정 글씨 (`#000000`)
- [ ] 시트간 링크: 초록 글씨 (`#008000`)
- [ ] 한국 통화 형식: `₩#,##0`
- [ ] 날짜 형식: `YYYY-MM-DD`

---

## 보험/금융 도메인 예시: 손해율 분석 스프레드시트

```python
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "손해율분석"

# --- 스타일 정의 ---
NAVY_FILL  = PatternFill("solid", fgColor="1F3564")
LIGHT_FILL = PatternFill("solid", fgColor="E8EFF7")
WHITE_BOLD = Font(color="FFFFFF", bold=True, name="맑은 고딕", size=11)
BLUE_FONT  = Font(color="0000FF", name="맑은 고딕", size=10)   # 입력값
BLACK_FONT = Font(color="000000", name="맑은 고딕", size=10)   # 수식
CENTER     = Alignment(horizontal="center", vertical="center")
THIN_BORDER = Border(
    top=Side("thin", color="AAAAAA"), bottom=Side("thin", color="AAAAAA"),
    left=Side("thin", color="AAAAAA"), right=Side("thin", color="AAAAAA"),
)

# --- 헤더 ---
headers = ["보험 상품", "발생손해액(원)", "경과보험료(원)", "손해율(%)", "전년 손해율(%)", "증감(pp)"]
for col, h in enumerate(headers, 1):
    cell = ws.cell(1, col, h)
    cell.fill = NAVY_FILL
    cell.font = WHITE_BOLD
    cell.alignment = CENTER
    cell.border = THIN_BORDER
ws.row_dimensions[1].height = 28

# --- 데이터 (입력값: 파란 글씨) ---
data = [
    ("실손의료보험", 78_300_000_000, 100_000_000_000, 75.2),
    ("자동차보험",   65_680_000_000,  80_000_000_000, 80.5),
    ("종신보험",     18_750_000_000,  30_000_000_000, 71.3),
    ("화재보험",     11_000_000_000,  20_000_000_000, 55.8),
]

for row_idx, (product, loss, premium, prev_ratio) in enumerate(data, 2):
    # A열: 상품명 (입력)
    ws.cell(row_idx, 1, product).font = BLACK_FONT

    # B열: 발생손해액 (입력값 — 파란 글씨)
    c_loss = ws.cell(row_idx, 2, loss)
    c_loss.font = BLUE_FONT
    c_loss.number_format = '₩#,##0'

    # C열: 경과보험료 (입력값 — 파란 글씨)
    c_prem = ws.cell(row_idx, 3, premium)
    c_prem.font = BLUE_FONT
    c_prem.number_format = '₩#,##0'

    # D열: 손해율 = 수식 (검정 글씨)
    col_b = get_column_letter(2)
    col_c = get_column_letter(3)
    c_ratio = ws.cell(row_idx, 4, f"=IFERROR({col_b}{row_idx}/{col_c}{row_idx}, 0)")
    c_ratio.font = BLACK_FONT
    c_ratio.number_format = '0.0%'

    # E열: 전년 손해율 (입력값 — 파란 글씨)
    c_prev = ws.cell(row_idx, 5, prev_ratio / 100)
    c_prev.font = BLUE_FONT
    c_prev.number_format = '0.0%'

    # F열: 증감 = 수식 (검정 글씨)
    c_delta = ws.cell(row_idx, 6, f"=D{row_idx}-E{row_idx}")
    c_delta.font = BLACK_FONT
    c_delta.number_format = '+0.0%;-0.0%;0.0%'

    # 짝수 행 배경
    if row_idx % 2 == 0:
        for col in range(1, 7):
            ws.cell(row_idx, col).fill = LIGHT_FILL

    # 테두리
    for col in range(1, 7):
        ws.cell(row_idx, col).border = THIN_BORDER

# --- 합계 행 ---
last_row = len(data) + 2
ws.cell(last_row, 1, "합계").font = Font(bold=True, name="맑은 고딕")
ws.cell(last_row, 2, f"=SUM(B2:B{last_row-1})").number_format = '₩#,##0'
ws.cell(last_row, 3, f"=SUM(C2:C{last_row-1})").number_format = '₩#,##0'
ws.cell(last_row, 4, f"=IFERROR(B{last_row}/C{last_row}, 0)").number_format = '0.0%'
for col in range(1, 7):
    ws.cell(last_row, col).border = THIN_BORDER
    ws.cell(last_row, col).fill = PatternFill("solid", fgColor="BDD7EE")

# --- 열 너비 ---
widths = [20, 18, 18, 12, 14, 10]
for col, width in enumerate(widths, 1):
    ws.column_dimensions[get_column_letter(col)].width = width

# --- 틀 고정 (헤더 행) ---
ws.freeze_panes = "A2"

wb.save("손해율분석_2024.xlsx")
print("저장 완료: 손해율분석_2024.xlsx")
```

---

## 워크플로우 요약

```
요청 수신
   ↓
파일 존재 여부 확인
   ├─ 신규 생성 → Workbook() → 헤더/데이터/수식 작성 → 서식 적용 → save()
   └─ 기존 편집 → load_workbook() → 셀 수정 → save()

데이터 분석 필요 시:
   pandas.read_excel() → 분석 → openpyxl로 결과 서식 포함 출력

서버에서 재계산 필요 시:
   수식 작성 → LibreOffice recalculate → 결과 파일 반환
```

---

## 주의사항

- **수식 하드코딩 절대 금지**: `cell.value = 91000 * 12` 대신 `cell.value = "=B2*12"`
- `load_workbook(data_only=True)` 사용 시 수식이 아닌 마지막 저장된 값만 읽힘 — 수식 편집 시 반드시 `data_only=False`(기본값) 사용
- openpyxl은 `.xls` (Excel 97-2003) 미지원 — `.xlsx`만 처리 가능
- pandas `to_excel()` 사용 시 서식이 적용되지 않음 — 서식이 필요하면 openpyxl로 직접 작성
- 한국어 폰트("맑은 고딕", "나눔고딕")는 서버에 설치된 경우에만 LibreOffice 변환 시 정확히 렌더링됨