Python×Excel入門

pip install openpyxl

import openpyxl
print(openpyxl.__version__)

from openpyxl import Workbook

# 新規ワークブック作成
wb = Workbook()
ws = wb.active  # アクティブシート

# セルに値を設定
ws['A1'] = "Hello"
ws['B1'] = "World"

# 保存
wb.save("sample.xlsx")

from openpyxl import load_workbook

# ファイルを開く
wb = load_workbook("sample.xlsx")
ws = wb.active

# 値を取得
print(ws['A1'].value)

# シートの一覧
print(wb.sheetnames)

# シートを取得
ws = wb["Sheet1"]

# シートを追加
ws_new = wb.create_sheet("新しいシート")

# シートを削除
del wb["Sheet1"]

# シート名を変更
ws.title = "データ"

# 方法1: アドレス指定
cell = ws['A1']
value = cell.value

# 方法2: 行・列指定
cell = ws.cell(row=1, column=1)
value = cell.value

# 値の設定
ws['A1'] = "値"
ws.cell(row=1, column=2, value="値2")

# 範囲を取得
for row in ws['A1:C3']:
    for cell in row:
        print(cell.value)

# 行ごとに取得
for row in ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=3):
    print([cell.value for cell in row])

# 列ごとに取得
for col in ws.iter_cols(min_row=1, max_row=10, min_col=1, max_col=3):
    print([cell.value for cell in col])

# 行を追加
ws.append([1, 2, 3])
ws.append(["A", "B", "C"])

# 複数行を追加
data = [
    [1, "田中", 100],
    [2, "鈴木", 200],
    [3, "佐藤", 150]
]
for row in data:
    ws.append(row)

# 最終行
last_row = ws.max_row

# 最終列
last_col = ws.max_column

# データがある範囲をループ
for row in range(1, ws.max_row + 1):
    for col in range(1, ws.max_column + 1):
        print(ws.cell(row, col).value)

from openpyxl.styles import Font

# フォントを設定
ws['A1'].font = Font(
    name='メイリオ',
    size=14,
    bold=True,
    italic=False,
    color='FF0000'  # 赤
)

from openpyxl.styles import PatternFill

# 背景色を設定
ws['A1'].fill = PatternFill(
    patternType='solid',
    fgColor='FFFF00'  # 黄色
)

from openpyxl.styles import Border, Side

thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

ws['A1'].border = thin_border

from openpyxl.styles import Alignment

ws['A1'].alignment = Alignment(
    horizontal='center',
    vertical='center'
)

# 列幅を設定
ws.column_dimensions['A'].width = 20

# 行高を設定
ws.row_dimensions[1].height = 30

import os
from openpyxl import load_workbook, Workbook

def aggregate_files(folder_path, output_path):
    """フォルダ内のExcelファイルを集計"""
    result_wb = Workbook()
    result_ws = result_wb.active
    result_ws.append(['ファイル名', '合計'])

    for filename in os.listdir(folder_path):
        if filename.endswith('.xlsx'):
            filepath = os.path.join(folder_path, filename)
            wb = load_workbook(filepath)
            ws = wb.active

            # B列の合計を計算
            total = sum(
                cell.value for cell in ws['B']
                if isinstance(cell.value, (int, float))
            )

            result_ws.append([filename, total])

    result_wb.save(output_path)
    print(f"集計完了: {output_path}")

# 実行
aggregate_files("data/", "集計結果.xlsx")

from openpyxl import load_workbook
from datetime import date

def generate_invoice(template_path, output_path, data):
    """テンプレートから請求書を生成"""
    wb = load_workbook(template_path)
    ws = wb.active

    # データを埋め込み
    ws['B3'] = data['company_name']
    ws['B5'] = date.today()
    ws['B7'] = data['invoice_no']

    # 明細を入力
    start_row = 10
    for i, item in enumerate(data['items']):
        row = start_row + i
        ws.cell(row, 1, item['name'])
        ws.cell(row, 2, item['quantity'])
        ws.cell(row, 3, item['price'])
        ws.cell(row, 4, f'=B{row}*C{row}')

    wb.save(output_path)
    print(f"請求書を生成: {output_path}")

# データ
invoice_data = {
    'company_name': '株式会社サンプル',
    'invoice_no': 'INV-2025-001',
    'items': [
        {'name': '商品A', 'quantity': 10, 'price': 1000},
        {'name': '商品B', 'quantity': 5, 'price': 2000},
    ]
}

generate_invoice("template.xlsx", "請求書.xlsx", invoice_data)

from openpyxl import load_workbook, Workbook

def extract_data(input_path, output_path, condition_func):
    """条件に合うデータを抽出"""
    wb_in = load_workbook(input_path)
    ws_in = wb_in.active

    wb_out = Workbook()
    ws_out = wb_out.active

    # ヘッダーをコピー
    for col, cell in enumerate(ws_in[1], 1):
        ws_out.cell(1, col, cell.value)

    # 条件に合う行を抽出
    out_row = 2
    for row in ws_in.iter_rows(min_row=2):
        row_values = [cell.value for cell in row]
        if condition_func(row_values):
            for col, cell in enumerate(row, 1):
                ws_out.cell(out_row, col, cell.value)
            out_row += 1

    wb_out.save(output_path)
    print(f"抽出完了: {out_row - 2}件")

# 使用例：C列が100以上のデータを抽出
extract_data(
    "data.xlsx",
    "抽出結果.xlsx",
    lambda row: row[2] is not None and row[2] >= 100
)

import pandas as pd

# Excelを読み込み
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")

# 特定の列のみ
df = pd.read_excel("data.xlsx", usecols=["名前", "売上"])

# ヘッダーなし
df = pd.read_excel("data.xlsx", header=None)

# DataFrameをExcelに出力
df.to_excel("output.xlsx", index=False)

# 複数シートに出力
with pd.ExcelWriter("output.xlsx") as writer:
    df1.to_excel(writer, sheet_name="データ1")
    df2.to_excel(writer, sheet_name="データ2")

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font

# pandasで処理
df = pd.read_excel("data.xlsx")
df_summary = df.groupby("カテゴリ").sum()

# Excelに出力
df_summary.to_excel("summary.xlsx")

# openpyxlで書式設定
wb = load_workbook("summary.xlsx")
ws = wb.active
ws['A1'].font = Font(bold=True)
wb.save("summary.xlsx")

from openpyxl import Workbook, load_workbook

# 新規作成
wb = Workbook()
ws = wb.active

# 読み込み
wb = load_workbook("file.xlsx")

# セル操作
ws['A1'] = "値"
value = ws['A1'].value

# 保存
wb.save("output.xlsx")