Overview
Python openpyxl Based on the library, it generates spreadsheets in XLSX format. It automatically generates Korean business reports (financial analysis, KPI dashboards, budget planning, summary tables) that include data tables, charts, formulas, and conditional formatting.
Trigger Keywords
Excel, xlsx, Excel, spreadsheet, table, chart, data sheet, summary table, budget planning, financial analysis, KPI dashboard, profit and loss statement, sales analysis
Workflow
Step 1: Determine Report Type
After analyzing the user request, select the report type:
- KPI Dashboard: Track monthly metrics
- Financial/Sales Analysis: Monthly profit and loss, performance vs. budget
- Project Schedule: Gantt chart
- Data Summary Table: Pivot format
- Payroll/HR Management: Wage calculation
Step 2: Design Data Structure
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border
from openpyxl.chart import BarChart, LineChart, Reference
wb = Workbook()
ws = wb.active
# 헤더, 데이터, 수식, 차트 영역 설계
Step 3: Configure Sheets and Enter Data
KPI Dashboard Example:
시트 1: 대시보드 요약
- 핵심 KPI 카드 (목표 vs 실적)
- 월별 추이 꺾은선 차트
- 달성률 게이지 (조건부 서식)
시트 2: 월별 데이터
- 각 KPI 월별 실적 데이터
시트 3: 부서별 분류
Financial/Sales Analysis Example:
헤더: 구분 | 1월 | 2월 | ... | 12월 | 합계 | 전년 대비
데이터: 매출 | 비용 | 영업이익 | 영업이익률
수식: =SUM(), =AVERAGE(), =(실적-목표)/목표
조건부 서식: 목표 미달 시 빨간색, 초과 달성 시 파란색
Project Gantt Chart Example:
행: 업무 항목
열: 날짜 (주 단위 또는 일 단위)
표시: 시작일/종료일에 따른 셀 색상 자동 적용
Step 4: Apply Formatting
Apply Korean business standard formatting:
| Item | Standard Value |
|---|---|
| Number Format | #,##0 (thousands separator) |
| Currency Format | #,##0 "원" or ₩#,##0 |
| Percent | 0.0% |
| Date | YYYY-MM-DD or YYYY년 M월 D일 |
| Header Background | Blue series (#4472C4) or gray series |
| Total Row | Bold + light background color |
Step 5: Insert Charts
Create charts by referencing the data range:
- Bar chart: Monthly sales comparison
- Line chart: KPI trends
- Pie chart: Proportion analysis
- Scatter chart: Correlation
Step 6: Insert Formulas
Automatically generate standard Excel formulas:
- SUM, AVERAGE, COUNT
- VLOOKUP, HLOOKUP
- IF, AND, OR
- CONCATENATE, TEXT
- Date/Time Function
Usage Example
- "Make me a KPI dashboard in Excel (sales, new customers, retention)"
- "Generate a monthly sales analysis table (January-December)"
- "Create a project schedule Gantt chart in Excel"
- "Make me a payroll calculation sheet (base salary, allowances, taxes)"
- "Generate a performance vs. budget tracking table"
Output Format
- File Format:
.xlsx(Excel 2007+ compatible) - Sheet Composition: Multiple sheets (summary + data + charts)
- Print settings: A4 page, page numbers, print area
- Formula description: Document which formula is used in which cell
Precautions
Microsoft Excel compatibility
Files generated by openpyxl can be opened normally in Excel 2016+ and Google Sheets. Older versions of Excel (2003 or earlier) require a compatibility pack.
Large-volume data
For data with 100,000 rows or more, write_only=True mode is used to improve memory efficiency. For typical reports (a few thousand rows or fewer), the default mode is sufficient.
Korean font
When displaying Korean in cells, it is recommended to set the font to 'Malgun Gothic' or 'NanumGothic'. In environments where Korean fonts are not installed, it will be rendered with a substitute font.
Chart limitations
openpyxl charts support only basic formats. If advanced charts (histograms, waterfall charts, combo charts, etc.) are needed, modify them manually in Excel or use the xlsxwriter library as an alternative.
Security
For files containing personal information (salary, resident registration number), apply sheet protection and file encryption. openpyxlsupports sheet protection, but file encryption requires a separate tool.
Formula language
The formula function names in Korean Excel and English Excel may differ (e.g., SUM vs 합계). openpyxluses English function names, and they are automatically converted in Korean Excel.
Troubleshooting
| Situation | Solution |
|---|---|
| File creation failure | Check whether openpyxl is installed: pip install openpyxl. Retry after installation |
| Template not set | If you tell us the report type and the list of required metrics, we will generate it with a basic structure |
| Formula error | If you explain the data range and the desired calculation method, we will modify the formula |
| Chart not generated | If you specify the chart type (bar/line/pie) and data range, we will generate it |
| Conditional formatting not applied | If you clearly state the condition (e.g., "red if 1000 or more"), we will apply it |
| File size too large | Reduce images or charts, or use write_only=True mode |
Related skills / self-review
When Excel generation is finished, reopen the produced .xlsx file and self-reviewfor remaining placeholders, insufficient number of sheets, broken Korean encoding, and broken tables/charts, and if there are problems, automatically fix and regenerate, then report the final PASS/FAIL result.
moai-office:docx-generator- DOCX (Word) document generationmoai-office:pptx-designer- PPT slide generation for presentationsmoai-office:hwpx-writer- Hangul (HWPX) document generationmoai-business:sbiz365-analyst- Small business commercial district and sales data analysis (convertible to Excel)
Technical reference
- openpyxl official documentation: https://openpyxl.readthedocs.io/
- Excel formula reference: Microsoft Excel function reference
- Data visualization guide: Excel chart best practices