lidge-jun/officecli-data-pipeline

officecli-data-pipeline

Overlay skill for OfficeCLI that bridges Python pandas DataFrames with formatted Excel documents.

Architecture: pandas creates the data → CSV/TSV export preserves the table cleanly → officecli creates/imports the workbook → officecli formats and validates. This path keeps pandas focused on transforms and lets officecli own the OOXML package from creation through validation.

---

When to Use

• Converting pandas DataFrame to a formatted Excel report
• Creating data reports from CSV, JSON, or database sources
• Building multi-sheet dashboards with Python data + officecli formatting
• Batch data processing pipelines that need styled output
• Any workflow where raw data needs professional formatting before delivery

---

Pipeline Architecture

┌─────────────┐    ┌───────────────┐    ┌──────────────┐    ┌──────────────┐
│   pandas     │ →  │   CSV / TSV    │ →  │  officecli   │ →  │  officecli   │
│  DataFrame   │    │ export table   │    │ create/import│    │ format/valid │
└─────────────┘    └───────────────┘    └──────────────┘    └──────────────┘
  Data transforms     Portable rows      Native workbook      Schema + layout
  Joins, aggregates   Header-safe text   ownership            verification

Why export CSV/TSV first instead of writing .xlsx directly? officecli formatting is:

1. Declarative (one command per style) vs imperative (multiple Python API calls)
2. Scriptable in shell (composable with other CLI tools)
3. Batchable (single open/save cycle for all formatting)
4. Owned end-to-end by officecli, so officecli validate stays aligned with the generated workbook

---

Quick Pipeline: Single Sheet

Step 1: Export Data with Python

import pandas as pd

df = pd.DataFrame({
    "제품명": ["김치냉장고", "에어컨", "세탁기", "건조기"],
    "매출액": [15000000, 23000000, 18000000, 12000000],
    "전년비": [1.25, 1.15, 0.95, 1.30],
    "카테고리": ["가전", "가전", "가전", "가전"],
})

df.to_csv("sales_report.csv", index=False)
print(f"Written {len(df)} rows to sales_report.csv")

Step 2: Format with officecli

# Create workbook and import pandas output
officecli create sales_report.xlsx
officecli import sales_report.xlsx /Sheet1 sales_report.csv --header

# Header row styling
officecli set sales_report.xlsx '/Sheet1/A1:D1' \
  --prop font.bold=true --prop font.name="Malgun Gothic" --prop font.size=12

# Number formatting
officecli set sales_report.xlsx '/Sheet1/B2:B5' --prop numFmt="#,##0"
officecli set sales_report.xlsx '/Sheet1/C2:C5' --prop numFmt="0.0%"

# Column widths (accommodate Korean text)
officecli set sales_report.xlsx '/Sheet1/col[A]' --prop width=18
officecli set sales_report.xlsx '/Sheet1/col[B]' --prop width=15
officecli set sales_report.xlsx '/Sheet1/col[C]' --prop width=12
officecli set sales_report.xlsx '/Sheet1/col[D]' --prop width=12

# Freeze header row
officecli set sales_report.xlsx /Sheet1 --prop freeze=A2

# Validate
officecli validate sales_report.xlsx

Step 3: Batch Formatting (Recommended)

Batch mode performs all operations in a single open/save cycle — much faster:

officecli batch sales_report.xlsx --commands '[
  {"command":"set","path":"/Sheet1/A1:D1","props":{"font.bold":"true","font.size":"12","font.name":"Malgun Gothic"}},
  {"command":"set","path":"/Sheet1/B2:B5","props":{"numFmt":"#,##0"}},
  {"command":"set","path":"/Sheet1/C2:C5","props":{"numFmt":"0.0%"}},
  {"command":"set","path":"/Sheet1/col[A]","props":{"width":"18"}},
  {"command":"set","path":"/Sheet1/col[B]","props":{"width":"15"}},
  {"command":"set","path":"/Sheet1/col[C]","props":{"width":"12"}},
  {"command":"set","path":"/Sheet1/col[D]","props":{"width":"12"}},
  {"command":"set","path":"/Sheet1","props":{"freeze":"A2"}}
]'

---

Conditional Formatting

Data Bars

# Add data bars to revenue column
officecli add sales_report.xlsx '/매출데이터' --type databar \
  --prop range=B2:B5 --prop color=4472C4

Color Scale (Heatmap)

# Green-to-red heatmap on growth rates
officecli add sales_report.xlsx '/매출데이터' --type colorscale \
  --prop range=C2:C5

Icon Sets

# Traffic light icons for performance indicators
officecli add sales_report.xlsx '/매출데이터' --type iconset \
  --prop range=C2:C5

Formula-Based Conditional Formatting

# Highlight cells where growth rate < 100% (declining)
officecli add sales_report.xlsx '/매출데이터' --type formulacf \
  --prop range=C2:C5 --prop formula='$C2<1' --prop fill=FF6B6B

# Highlight top performers (> 120%)
officecli add sales_report.xlsx '/매출데이터' --type formulacf \
  --prop range=C2:C5 --prop formula='$C2>=1.2' --prop fill=51CF66

---

Charts

Add Chart from Data Range

# Column chart from existing data
officecli add sales_report.xlsx '/매출데이터' --type chart \
  --prop chartType=bar \
  --prop dataRange="매출데이터!A1:B5" \
  --prop title="제품별 매출액" \
  --prop width=10 --prop height=15 \
  --prop x=6 --prop y=1

# Line chart for trends
officecli add sales_report.xlsx '/매출데이터' --type chart \
  --prop chartType=line \
  --prop dataRange="매출데이터!A1:C5" \
  --prop title="매출 및 성장률" \
  --prop legend=bottom

Chart with Inline Data

officecli add sales_report.xlsx '/매출데이터' --type chart \
  --prop chartType=pie \
  --prop categories="김치냉장고,에어컨,세탁기,건조기" \
  --prop data="Sales:15000000,23000000,18000000,12000000" \
  --prop title="매출 비중" \
  --prop dataLabels=true \
  --prop labelPos=outside

---

Multi-Sheet Reports

Step 1: Export Multiple DataFrames

import pandas as pd

df_sales = pd.DataFrame({
    "월": ["1월", "2월", "3월"],
    "매출액": [50000000, 55000000, 48000000],
    "영업이익": [8000000, 9500000, 7200000],
})

df_costs = pd.DataFrame({
    "비목": ["인건비", "재료비", "마케팅", "기타"],
    "금액": [25000000, 15000000, 8000000, 5000000],
    "비율": [0.47, 0.28, 0.15, 0.10],
})

df_summary = pd.DataFrame({
    "지표": ["총매출", "총비용", "순이익", "이익률"],
    "금액": [153000000, 53000000, 100000000, 0.654],
})

df_sales.to_csv("sales.csv", index=False)
df_costs.to_csv("costs.csv", index=False)
df_summary.to_csv("summary.csv", index=False)

Step 2: Format All Sheets with Batch

officecli create quarterly_report.xlsx
officecli add quarterly_report.xlsx / --type sheet --prop name="매출"
officecli add quarterly_report.xlsx / --type sheet --prop name="비용"
officecli add quarterly_report.xlsx / --type sheet --prop name="요약"
officecli import quarterly_report.xlsx /매출 sales.csv --header
officecli import quarterly_report.xlsx /비용 costs.csv --header
officecli import quarterly_report.xlsx /요약 summary.csv --header

officecli batch quarterly_report.xlsx --commands '[
  {"command":"set","path":"/매출/A1:C1","props":{"font.bold":"true","font.name":"Malgun Gothic"}},
  {"command":"set","path":"/매출/B2:C4","props":{"numFmt":"#,##0"}},
  {"command":"set","path":"/매출/col[A]","props":{"width":"10"}},
  {"command":"set","path":"/매출/col[B]","props":{"width":"15"}},
  {"command":"set","path":"/매출/col[C]","props":{"width":"15"}},
  {"command":"set","path":"/매출","props":{"freeze":"A2"}},

  {"command":"set","path":"/비용/A1:C1","props":{"font.bold":"true","font.name":"Malgun Gothic"}},
  {"command":"set","path":"/비용/B2:B5","props":{"numFmt":"#,##0"}},
  {"command":"set","path":"/비용/C2:C5","props":{"numFmt":"0.0%"}},
  {"command":"set","path":"/비용/col[A]","props":{"width":"12"}},
  {"command":"set","path":"/비용/col[B]","props":{"width":"15"}},

  {"command":"set","path":"/요약/A1:B1","props":{"font.bold":"true","font.name":"Malgun Gothic"}},
  {"command":"set","path":"/요약/B2:B3","props":{"numFmt":"#,##0"}},
  {"command":"set","path":"/요약/B4","props":{"numFmt":"#,##0"}},
  {"command":"set","path":"/요약/B5","props":{"numFmt":"0.0%"}}
]'

officecli validate quarterly_report.xlsx

---

CSV Import Pipeline

For simple CSV-to-Excel conversion without Python:

# Create workbook and import CSV directly
officecli create report.xlsx
officecli import report.xlsx /Sheet1 data.csv --header

# Format the imported data
officecli set report.xlsx '/Sheet1/A1:D1' \
  --prop font.bold=true --prop font.name="Malgun Gothic"

# Add autofilter for data exploration
officecli add report.xlsx /Sheet1 --type autofilter --prop range=A1:D1

officecli validate report.xlsx

TSV Import

officecli import report.xlsx /Sheet1 data.tsv --format tsv --header

Stdin Import (Pipe from Other Tools)

# Pipe query results directly into Excel
cat query_results.csv | officecli import report.xlsx /Sheet1 --stdin --header

---

Number Format Reference

Common format codes for --prop numFmt=:

| Format Code | Example Output | Use Case | |------------|----------------|----------| | #,##0 | 15,000,000 | Integer with comma separators | | #,##0.00 | 15,000,000.00 | Currency (2 decimals) | | 0.0% | 125.0% | Percentage (1 decimal) | | 0% | 125% | Percentage (no decimal) | | 0.00 | 1.25 | Decimal (2 places) | | yyyy-mm-dd | 2026-03-27 | ISO date | | yyyy"년" mm"월" dd"일" | 2026년 03월 27일 | Korean date | | $#,##0 | $15,000,000 | USD currency | | ₩#,##0 | ₩15,000,000 | KRW currency | | ¥#,##0 | ¥15,000,000 | JPY/CNY currency |

# Apply Korean currency format
officecli set data.xlsx '/Sheet1/B2:B100' --prop numFmt="₩#,##0"

# Apply Korean date format
officecli set data.xlsx '/Sheet1/A2:A100' --prop numFmt='yyyy"년" mm"월" dd"일"'

---

End-to-End Example Script

Complete pipeline from data generation to validated output:

#!/bin/bash
set -euo pipefail

OUTPUT="monthly_sales_$(date +%Y%m).xlsx"

# Step 1: Generate data with Python
python3 -c "
import pandas as pd
import random

months = ['1월','2월','3월','4월','5월','6월']
products = ['김치냉장고','에어컨','세탁기','건조기','식기세척기']

rows = []
for product in products:
    for month in months:
        revenue = random.randint(8000000, 30000000)
        growth = round(random.uniform(0.85, 1.35), 2)
        rows.append({'제품명': product, '월': month, '매출액': revenue, '성장률': growth})

df = pd.DataFrame(rows)
df.to_csv('monthly_sales.csv', index=False)
print(f'Generated {len(df)} rows')
"

# Step 2: Create workbook and import CSV
officecli create "$OUTPUT"
officecli add "$OUTPUT" / --type sheet --prop name="월별매출"
officecli import "$OUTPUT" /월별매출 monthly_sales.csv --header

# Step 3: Format with officecli batch
officecli batch "$OUTPUT" --commands '[
  {"command":"set","path":"/월별매출/A1:D1","props":{"font.bold":"true","font.size":"11","font.name":"Malgun Gothic"}},
  {"command":"set","path":"/월별매출/C2:C31","props":{"numFmt":"#,##0"}},
  {"command":"set","path":"/월별매출/D2:D31","props":{"numFmt":"0.0%"}},
  {"command":"set","path":"/월별매출/col[A]","props":{"width":"16"}},
  {"command":"set","path":"/월별매출/col[B]","props":{"width":"8"}},
  {"command":"set","path":"/월별매출/col[C]","props":{"width":"15"}},
  {"command":"set","path":"/월별매출/col[D]","props":{"width":"10"}},
  {"command":"set","path":"/월별매출","props":{"freeze":"A2"}},
  {"command":"add","path":"/월별매출","type":"autofilter","props":{"range":"A1:D1"}},
  {"command":"add","path":"/월별매출","type":"databar","props":{"range":"C2:C31","color":"4472C4"}},
  {"command":"add","path":"/월별매출","type":"formulacf","props":{"range":"D2:D31","formula":"$D2<1","fill":"FF6B6B"}},
  {"command":"add","path":"/월별매출","type":"formulacf","props":{"range":"D2:D31","formula":"$D2>=1.2","fill":"51CF66"}}
]'

# Step 4: Add chart
officecli add "$OUTPUT" '/월별매출' --type chart \
  --prop chartType=bar \
  --prop dataRange="월별매출!A1:C31" \
  --prop title="월별 제품 매출" \
  --prop width=12 --prop height=18 \
  --prop x=6 --prop y=1

# Step 5: Validate
officecli validate "$OUTPUT"
echo "Pipeline complete: $OUTPUT"

---

Validation Checklist

After every pipeline run, verify:

# 1. Schema validation
officecli validate output.xlsx

# 2. Row/column count matches expectation
officecli view output.xlsx text --end 3
# Verify header + first few data rows look correct

# 3. CJK text renders correctly
officecli view output.xlsx text | head -5
# Korean/Japanese/Chinese characters should be readable

# 4. Number formats applied correctly
officecli get output.xlsx '/Sheet1/B2' --json
# Check numFmt in response

# 5. Conditional formatting present
officecli get output.xlsx '/Sheet1' --json
# Check for cf (conditional formatting) entries

Pass criteria:

• [ ] officecli validate passes with no errors
• [ ] Row count matches DataFrame length + 1 (header)
• [ ] CJK text displays correctly in view text
• [ ] Number formats show commas/percentages as expected
• [ ] Conditional formatting highlights correct cells
• [ ] Charts reference correct data ranges

---

Quick Reference Card

| Task | Command | |------|---------| | Import CSV | officecli import data.xlsx /Sheet1 data.csv --header | | Header styling | officecli set f.xlsx '/Sheet1/A1:D1' --prop font.bold=true --prop font.name="Malgun Gothic" | | Number format | officecli set f.xlsx '/Sheet1/B2:B99' --prop numFmt="#,##0" | | Percentage | officecli set f.xlsx '/Sheet1/C2:C99' --prop numFmt="0.0%" | | Column width | officecli set f.xlsx '/Sheet1/col[A]' --prop width=18 | | Freeze pane | officecli set f.xlsx /Sheet1 --prop freeze=A2 | | Data bar | officecli add f.xlsx /Sheet1 --type databar --prop range=B2:B99 | | Highlight rule | officecli add f.xlsx /Sheet1 --type formulacf --prop range=... --prop formula=... | | Chart | officecli add f.xlsx /Sheet1 --type chart --prop chartType=bar --prop dataRange=... | | Autofilter | officecli add f.xlsx /Sheet1 --type autofilter --prop range=A1:D1 | | Batch format | officecli batch f.xlsx --commands '[...]' | | Validate | officecli validate f.xlsx |