xlsx-processing

XLSX 処理

概要

openpyxl を使った高度な書式設定と、pandas を使ったデータ分析により、Excel ファイルをプログラムで操作します。このスキルでは、スプレッドシートの読み書き、数式、グラフ、条件付き書式、データの入力規則、ピボットテーブルの生成、CSV のインポート／エクスポート、大容量ファイルの処理方法を扱います。

Excel ファイルを手作業ではなくコードで作成、読み込み、変換、または拡充する必要があるときは、いつでもこのスキルを適用してください。

複数フェーズのプロセス

フェーズ 1：要件

1. 操作内容（読み込み、書き込み、変換、レポート）を特定します
2. データソースとデータ量を確認します
3. 書式と数式の要件を定義します
4. シートの構成と命名を計画します
5. パフォーマンス要件（行数、ファイルサイズ）を評価します

停止 — 行数と書式設定が必要かどうかが分かるまで、実装を始めないでください（これはライブラリの選択に関わります）。

フェーズ 2：実装

1. ライブラリを選択します（選択表を参照）
2. データの読み込みと変換を実装します
3. 書式、数式、入力規則を適用します
4. グラフと条件付き書式を追加します
5. ファイルサイズとメモリ使用量を最適化します

停止 — 10,000 行を超えるファイルでは、メモリ最適化を省略しないでください。

フェーズ 3：検証

1. Excel、LibreOffice、Google Sheets で開きます
2. 数式が正しく計算されることを確認します
3. 書式が一貫して表示されることを確認します
4. エッジケース（データが空の場合、最大行数）でテストします
5. データの正確性を検証します

ライブラリ選択表

シナリオ	ライブラリ	理由
高度な書式設定（色、罫線、フォント）	openpyxl	完全な書式設定 API
データ分析、集計、ピボット	pandas	DataFrame 操作
データ分析結果の書式付きレポート	pandas + openpyxl	両者の長所を組み合わせます
データの読み込みのみで、書式設定が不要	pandas	最もシンプルな API
大容量ファイル（> 10K 行）、書き込み中心	openpyxl write_only	ストリーミング書き込みでメモリ使用量が少ない
大容量ファイル（> 10K 行）、読み込み中心	openpyxl read_only	ストリーミング読み込みでメモリ使用量が少ない
CSV と Excel の相互変換	pandas	1 行で実行できます
スプレッドシート内のグラフ	openpyxl	高い制御性を備えたグラフ API

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 = "Report"

# Header row
headers = ['Name', 'Department', 'Revenue', 'Target', 'Achievement']
header_font = Font(name='Calibri', size=11, bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
header_alignment = Alignment(horizontal='center', vertical='center')
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin'),
)

for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_alignment
    cell.border = thin_border

# Data rows
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        cell = ws.cell(row=row_idx, column=col_idx, value=value)
        cell.border = thin_border

# Auto-fit column widths
for col in range(1, len(headers) + 1):
    max_length = max(
        len(str(ws.cell(row=row, column=col).value or ''))
        for row in range(1, ws.max_row + 1)
    )
    ws.column_dimensions[get_column_letter(col)].width = min(max_length + 2, 50)

# Freeze header row
ws.freeze_panes = 'A2'

wb.save('report.xlsx')

数式

# Basic formulas
ws['E2'] = '=C2/D2'                    # Division
ws['F2'] = '=SUM(C2:C100)'             # Sum
ws['G2'] = '=AVERAGE(C2:C100)'         # Average
ws['H2'] = '=COUNTIF(E2:E100,">1")'   # Count if
ws['I2'] = '=IF(E2>=1,"Met","Below")'  # Conditional
ws['J2'] = '=VLOOKUP(A2,Sheet2!A:B,2,FALSE)'  # Lookup

# Array formula (Excel 365 dynamic array)
ws['K2'] = '=UNIQUE(A2:A100)'

# Named range
from openpyxl.workbook.defined_name import DefinedName
ref = f"Report!$C$2:$C${len(data)+1}"
defn = DefinedName('RevenueRange', attr_text=ref)
wb.defined_names.add(defn)

グラフ

from openpyxl.chart import BarChart, LineChart, PieChart, Reference

# Bar chart
chart = BarChart()
chart.type = 'col'
chart.title = 'Revenue by Department'
chart.y_axis.title = 'Revenue ($)'
chart.x_axis.title = 'Department'
chart.style = 10  # Built-in style

data_ref = Reference(ws, min_col=3, min_row=1, max_row=ws.max_row)
cats_ref = Reference(ws, min_col=2, min_row=2, max_row=ws.max_row)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats_ref)
chart.width = 20
chart.height = 12

ws.add_chart(chart, 'G2')

# Line chart with multiple series
line = LineChart()
line.title = 'Monthly Trends'
for col in range(3, 6):
    values = Reference(ws, min_col=col, min_row=1, max_row=13)
    line.add_data(values, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=2, max_row=13)
line.set_categories(cats)
ws.add_chart(line, 'G20')

条件付き書式

from openpyxl.formatting.rule import CellIsRule, ColorScaleRule, DataBarRule

# Highlight cells above threshold
ws.conditional_formatting.add(
    'C2:C100',
    CellIsRule(operator='greaterThan', formula=['100000'],
              fill=PatternFill(bgColor='C6EFCE'))
)

# Red for below target
ws.conditional_formatting.add(
    'E2:E100',
    CellIsRule(operator='lessThan', formula=['1'],
              fill=PatternFill(bgColor='FFC7CE'),
              font=Font(color='9C0006'))
)

# Color scale (green to red)
ws.conditional_formatting.add(
    'E2:E100',
    ColorScaleRule(
        start_type='min', start_color='F8696B',
        mid_type='percentile', mid_value=50, mid_color='FFEB84',
        end_type='max', end_color='63BE7B'
    )
)

# Data bars
ws.conditional_formatting.add(
    'C2:C100',
    DataBarRule(start_type='min', end_type='max', color='638EC6')
)

データの入力規則

from openpyxl.worksheet.datavalidation import DataValidation

# Dropdown list
dv = DataValidation(type='list', formula1='"Active,Inactive,Pending"', allow_blank=True)
dv.error = 'Please select a valid status'
dv.errorTitle = 'Invalid Entry'
ws.add_data_validation(dv)
dv.add('D2:D100')

# Number range
nv = DataValidation(type='whole', operator='between', formula1=0, formula2=100)
nv.error = 'Value must be between 0 and 100'
ws.add_data_validation(nv)
nv.add('F2:F100')

# Date validation
dv_date = DataValidation(type='date', operator='greaterThan', formula1='2025-01-01')
ws.add_data_validation(dv_date)
dv_date.add('G2:G100')

pandas のパターン

Excel の読み込み

import pandas as pd

# Read single sheet
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# Read with options
df = pd.read_excel('data.xlsx',
    sheet_name='Sales',
    header=0,
    usecols='A:E',
    dtype={'ID': str, 'Revenue': float},
    parse_dates=['Date'],
    na_values=['N/A', 'null', ''],
)

# Read all sheets
sheets = pd.read_excel('data.xlsx', sheet_name=None)  # Dict of DataFrames

pandas + openpyxl を使った Excel の書き込み

with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
    df_summary.to_excel(writer, sheet_name='Summary', index=False)
    df_detail.to_excel(writer, sheet_name='Detail', index=False)

    # Access openpyxl workbook for formatting
    wb = writer.book
    ws = wb['Summary']
    # Apply formatting...

ピボットテーブル

# Create pivot table
pivot = pd.pivot_table(
    df,
    values='Revenue',
    index='Department',
    columns='Quarter',
    aggfunc='sum',
    margins=True,
    margins_name='Total'
)

# Write to Excel with formatting
pivot.to_excel(writer, sheet_name='Pivot')

CSV のインポート／エクスポート

# CSV to XLSX
df = pd.read_csv('data.csv', encoding='utf-8-sig')
df.to_excel('output.xlsx', index=False)

# XLSX to CSV
df = pd.read_excel('data.xlsx')
df.to_csv('output.csv', index=False, encoding='utf-8-sig')

# Handle encoding issues
df = pd.read_csv('data.csv', encoding='latin-1')  # or 'cp1252'

大容量ファイルの処理

メモリ効率の高い読み込み

# openpyxl read-only mode
from openpyxl import load_workbook

wb = load_workbook('large_file.xlsx', read_only=True)
ws = wb.active

for row in ws.iter_rows(min_row=2, values_only=True):
    process_row(row)

wb.close()

分割書き込み

# Write large datasets in chunks
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

wb = Workbook(write_only=True)
ws = wb.create_sheet()

# Write header
ws.append(headers)

# Write in chunks
chunk_size = 10000
for chunk in pd.read_csv('large.csv', chunksize=chunk_size):
    for row in dataframe_to_rows(chunk, index=False, header=False):
        ws.append(row)

wb.save('output.xlsx')

パフォーマンス選択表

行数	戦略	備考
< 10,000	標準の openpyxl または pandas	すべての書式設定が利用できます
10K - 100K	write_only / read_only モード、分割処理	write_only では書式設定が制限されます
100K - 1M	write_only モードを使用し、CSV の利用も検討します	Excel の行数上限に近づきます
> 1M	XLSX ではなく CSV または Parquet を使用します	Excel の上限：1,048,576 行

アンチパターン／よくある間違い

アンチパターン	失敗する理由	代わりに行うこと
データ分析だけに openpyxl を使う	分析には冗長で、処理も遅い	データ操作には pandas を使用します
大容量ファイルをメモリに読み込む	メモリ不足やクラッシュにつながります	read_only / write_only モードを使用します
行番号や列番号をハードコードする	データの形状が変わると動作しなくなります	データの長さから計算します
日付形式に一貫性がない	日付が数値や文字列として表示されます	number_format を明示的に設定します
read_only ワークブックを閉じない	リソースリークが発生します	必ず wb.close() を呼び出すか、コンテキストマネージャーを使用します
.xls 形式を使用する	古く、機能が限られ、セキュリティリスクがあります	必ず .xlsx を使用します
セルを 1 つずつ書式設定する	大きな範囲では非常に遅くなります	範囲にスタイルを適用するか、名前付きスタイルを使用します
実際の Excel でテストしない	機能によって表示が異なります	Excel、LibreOffice、Google Sheets でテストします
ヘッダー行の固定を忘れる	大量のデータをスクロールする際の使い勝手が悪くなります	データシートでは必ずウィンドウ枠を固定します

不合理な判断を防ぐガード

• pandas で1行で処理できるデータ分析に、openpyxl を使わないでください。
• 行数の評価を省略しないでください。評価によって、全体のアプローチが決まります。
• 1万行を超えるファイルで標準モードが使えると想定しないでください。ストリーミングモードを使用してください。
• 1つのスプレッドシートアプリケーションだけでテストしないでください。書式はアプリケーションによって異なります。
• read_only モードで開いたワークブックを閉じ忘れないでください。

統合ポイント

スキル	連携方法
pdf-processing	Excel データを PDF レポートの生成に使用します
docx-processing	Excel データを Word 文書の表に入力します
email-composer	生成したスプレッドシートをビジネスメールに添付します
file-organizer	出力ファイルの命名規則とディレクトリ構造の規約
database-schema-design	レポート作成用にデータベースから Excel にデータをエクスポートします
deployment	CI/CD パイプラインでレポート生成を自動化します

スキルの種類

柔軟 — 書式設定が豊富な場合は openpyxl、データ分析には pandas を選択してください。データ分析の結果から書式付きレポートを作成する場合は、両方を組み合わせてください。データ量に応じてファイル処理方法を調整してください。