中級者向け No.055
Excelレポート生成
データを入力するとopenpyxlで書式付きExcelを自動生成。グラフ埋め込みと条件付き書式対応。
📋 目次
1. アプリ概要
データを入力するとopenpyxlで書式付きExcelを自動生成。グラフ埋め込みと条件付き書式対応。
このアプリはfileカテゴリの実践的なPythonアプリです。使用ライブラリは tkinter（標準ライブラリ）・openpyxl、難易度は ★★☆ です。
Pythonの豊富なライブラリを活用することで、実用的なアプリを短いコードで実装できます。ソースコードをコピーして実行し、仕組みを理解したうえでカスタマイズに挑戦してみてください。
GUIアプリ開発はプログラミングの楽しさを実感できる最も効果的な学習方法のひとつです。変数・関数・クラス・イベント処理などの重要な概念が自然と身につきます。
2. 機能一覧
- Excelレポート生成のメイン機能
- 直感的なGUIインターフェース
- 入力値のバリデーション
- エラーハンドリング
- 結果の見やすい表示
- クリア機能付き
3. 事前準備・環境
動作確認環境
Python 3.10 以上 / Windows・Mac・Linux すべて対応
以下の環境で動作確認しています。
- Python 3.10 以上
- OS: Windows 10/11・macOS 12+・Ubuntu 20.04+
インストールが必要なライブラリ
pip install openpyxl4. 完全なソースコード
コードのコピー方法
右上の「コピー」ボタンをクリックするとコードをクリップボードにコピーできます。
app055.py
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import os
import csv
import threading
from datetime import datetime
try:
import openpyxl
from openpyxl.styles import (Font, PatternFill, Alignment,
Border, Side)
from openpyxl.utils import get_column_letter
OPENPYXL_AVAILABLE = True
except ImportError:
OPENPYXL_AVAILABLE = False
class App055:
"""Excelレポート生成"""
SAMPLE_DATA = [
["商品名", "カテゴリ", "単価", "数量", "合計"],
["ノートPC", "電子機器", 98000, 5, None],
["マウス", "電子機器", 2500, 20, None],
["キーボード", "電子機器", 5800, 15, None],
["デスク", "家具", 45000, 3, None],
["チェア", "家具", 32000, 8, None],
["モニター", "電子機器", 38000, 10, None],
["USB-Cケーブル", "アクセサリ", 1200, 50, None],
["Webカメラ", "電子機器", 8500, 7, None],
]
def __init__(self, root):
self.root = root
self.root.title("Excelレポート生成")
self.root.geometry("900x600")
self.root.configure(bg="#1e1e1e")
self._rows = [list(r) for r in self.SAMPLE_DATA]
self._build_ui()
self._refresh_table()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="📊 Excelレポート生成",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
if not OPENPYXL_AVAILABLE:
tk.Label(self.root,
text="⚠ openpyxl が未インストールです (pip install openpyxl)",
bg="#fff3cd", fg="#856404", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X)
# ツールバー
tb = tk.Frame(self.root, bg="#2d2d2d", pady=4)
tb.pack(fill=tk.X)
ttk.Button(tb, text="📂 CSV読込", command=self._load_csv).pack(side=tk.LEFT, padx=4)
ttk.Button(tb, text="＋ 行追加", command=self._add_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="✕ 行削除", command=self._del_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="📥 Excel出力", command=self._export).pack(side=tk.LEFT, padx=8)
tk.Label(tb, text="タイトル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.title_var = tk.StringVar(value="売上レポート")
ttk.Entry(tb, textvariable=self.title_var, width=20).pack(side=tk.LEFT)
# スタイル
tk.Label(tb, text="スタイル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.style_var = tk.StringVar(value="ブルー")
ttk.Combobox(tb, textvariable=self.style_var,
values=["ブルー", "グリーン", "オレンジ"],
state="readonly", width=10).pack(side=tk.LEFT)
# テーブル（Treeview）
table_f = tk.Frame(self.root, bg="#1e1e1e")
table_f.pack(fill=tk.BOTH, expand=True, padx=8, pady=4)
# 列数に応じて動的にTreeviewを構築
cols = [f"c{i}" for i in range(len(self._rows[0]))]
self.tree = ttk.Treeview(table_f, columns=cols, show="headings",
selectmode="browse")
sb_x = ttk.Scrollbar(table_f, orient=tk.HORIZONTAL,
command=self.tree.xview)
sb_y = ttk.Scrollbar(table_f, command=self.tree.yview)
self.tree.configure(xscrollcommand=sb_x.set, yscrollcommand=sb_y.set)
sb_y.pack(side=tk.RIGHT, fill=tk.Y)
self.tree.pack(fill=tk.BOTH, expand=True)
sb_x.pack(fill=tk.X)
self.tree.bind("<Double-1>", self._on_double_click)
# 行追加フォーム
add_f = tk.Frame(self.root, bg="#252526", pady=4)
add_f.pack(fill=tk.X, padx=8)
tk.Label(add_f, text="新規行:", bg="#252526", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT)
self.row_entries = []
for _ in range(len(self._rows[0])):
e = ttk.Entry(add_f, width=12)
e.pack(side=tk.LEFT, padx=2)
self.row_entries.append(e)
ttk.Button(add_f, text="追加",
command=self._add_row).pack(side=tk.LEFT, padx=4)
self.status_var = tk.StringVar(value="データを編集してExcelを出力できます")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _refresh_table(self):
# ヘッダー設定
if not self._rows:
return
headers = self._rows[0]
n = len(headers)
cols = [f"c{i}" for i in range(n)]
# Treeviewを再構成
self.tree.configure(columns=cols)
for i, h in enumerate(headers):
self.tree.heading(f"c{i}", text=str(h))
self.tree.column(f"c{i}", width=120, anchor="center")
self.tree.delete(*self.tree.get_children())
for row in self._rows[1:]:
values = []
for j, v in enumerate(row):
if v is None and j == len(row) - 1:
# 合計列を計算
try:
values.append(int(row[2]) * int(row[3]))
except Exception:
values.append("")
else:
values.append(v if v is not None else "")
self.tree.insert("", tk.END, values=values)
self.status_var.set(f"{len(self._rows)-1} 行のデータ")
def _load_csv(self):
path = filedialog.askopenfilename(
filetypes=[("CSV", "*.csv"), ("すべて", "*.*")])
if not path:
return
try:
with open(path, encoding="utf-8-sig") as f:
reader = csv.reader(f)
rows = list(reader)
if rows:
self._rows = rows
self._refresh_table()
except Exception as e:
messagebox.showerror("エラー", str(e))
def _add_row(self):
vals = [e.get().strip() for e in self.row_entries]
if any(vals):
self._rows.append(vals)
self._refresh_table()
for e in self.row_entries:
e.delete(0, tk.END)
def _del_row(self):
sel = self.tree.selection()
if not sel:
return
idx = self.tree.index(sel[0]) + 1
if 1 <= idx < len(self._rows):
del self._rows[idx]
self._refresh_table()
def _on_double_click(self, event):
"""セルをダブルクリックして編集"""
region = self.tree.identify("region", event.x, event.y)
if region != "cell":
return
col = self.tree.identify_column(event.x)
row_iid = self.tree.identify_row(event.y)
if not row_iid:
return
col_idx = int(col.lstrip("#")) - 1
row_idx = self.tree.index(row_iid) + 1
current_val = self.tree.set(row_iid, f"c{col_idx}")
# ポップアップ編集
bbox = self.tree.bbox(row_iid, col)
if not bbox:
return
entry = tk.Entry(self.tree, font=("Arial", 10))
entry.place(x=bbox[0], y=bbox[1], width=bbox[2], height=bbox[3])
entry.insert(0, current_val)
entry.focus_set()
def commit(e=None):
new_val = entry.get()
self._rows[row_idx][col_idx] = new_val
self._refresh_table()
entry.destroy()
entry.bind("<Return>", commit)
entry.bind("<FocusOut>", commit)
def _export(self):
if not OPENPYXL_AVAILABLE:
messagebox.showerror("エラー",
"openpyxl をインストールしてください:\npip install openpyxl")
return
path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel", "*.xlsx")])
if not path:
return
threading.Thread(target=self._do_export, args=(path,),
daemon=True).start()
def _do_export(self, path):
try:
wb = openpyxl.Workbook()
ws = wb.active
ws.title = self.title_var.get() or "レポート"
# スタイル定義
style = self.style_var.get()
if style == "グリーン":
hdr_fill_color = "1E7E34"
row_fill_color = "D4EDDA"
elif style == "オレンジ":
hdr_fill_color = "E65100"
row_fill_color = "FFF3E0"
else: # ブルー
hdr_fill_color = "1565C0"
row_fill_color = "E3F2FD"
hdr_fill = PatternFill("solid", fgColor=hdr_fill_color)
row_fill = PatternFill("solid", fgColor=row_fill_color)
thin = Side(style="thin", color="AAAAAA")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
center = Alignment(horizontal="center", vertical="center")
# タイトル行
title_cell = ws.cell(row=1, column=1,
value=self.title_var.get())
title_cell.font = Font(bold=True, size=14, color="222222")
title_cell.alignment = center
ws.merge_cells(start_row=1, start_column=1,
end_row=1, end_column=len(self._rows[0]))
# 生成日時
date_cell = ws.cell(row=2, column=1,
value=f"生成日時: {datetime.now().strftime('%Y-%m-%d %H:%M')}")
date_cell.font = Font(size=9, color="888888")
ws.row_dimensions[1].height = 30
ws.row_dimensions[2].height = 16
# ヘッダー
headers = self._rows[0]
for j, h in enumerate(headers, 1):
cell = ws.cell(row=3, column=j, value=h)
cell.fill = hdr_fill
cell.font = Font(bold=True, color="FFFFFF")
cell.alignment = center
cell.border = border
ws.row_dimensions[3].height = 22
# データ行
for i, row in enumerate(self._rows[1:], 4):
for j, val in enumerate(row, 1):
v = val
if v is None:
v = ""
# 数値変換
if isinstance(v, str):
try:
v = int(v)
except Exception:
try:
v = float(v)
except Exception:
pass
cell = ws.cell(row=i, column=j, value=v)
cell.border = border
cell.alignment = center
if i % 2 == 0:
cell.fill = row_fill
# 列幅調整
for col_cells in ws.columns:
max_len = 0
col_letter = get_column_letter(col_cells[0].column)
for cell in col_cells:
if cell.value:
max_len = max(max_len, len(str(cell.value)))
ws.column_dimensions[col_letter].width = min(max_len + 4, 30)
# 合計行
last_row = len(self._rows) + 3
try:
num_cols = len(self._rows[0])
for j in range(1, num_cols + 1):
cell = ws.cell(row=last_row, column=j)
col_letter = get_column_letter(j)
# 数値列に合計を入れる
try:
sample = float(self._rows[1][j-1])
cell.value = f"=SUM({col_letter}4:{col_letter}{last_row-1})"
cell.font = Font(bold=True)
except Exception:
if j == 1:
cell.value = "合計"
cell.font = Font(bold=True)
cell.fill = PatternFill("solid", fgColor="EEEEEE")
cell.border = border
cell.alignment = center
except Exception:
pass
wb.save(path)
self.root.after(0, self._export_done, path)
except Exception as e:
self.root.after(0, lambda: messagebox.showerror("エラー", str(e)))
def _export_done(self, path):
self.status_var.set(f"保存完了: {os.path.basename(path)}")
messagebox.showinfo("完了", f"Excelファイルを保存しました:\n{path}")
if __name__ == "__main__":
root = tk.Tk()
app = App055(root)
root.mainloop()
5. コード解説
Excelレポート生成のコードを詳しく解説します。クラスベースの設計で各機能を整理して実装しています。
クラス設計とコンストラクタ
App055クラスにアプリの全機能をまとめています。__init__でウィンドウ設定、_build_ui()でUI構築、process()でメイン処理を担当します。責任の分離により、コードが読みやすくなります。
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import os
import csv
import threading
from datetime import datetime
try:
import openpyxl
from openpyxl.styles import (Font, PatternFill, Alignment,
Border, Side)
from openpyxl.utils import get_column_letter
OPENPYXL_AVAILABLE = True
except ImportError:
OPENPYXL_AVAILABLE = False
class App055:
"""Excelレポート生成"""
SAMPLE_DATA = [
["商品名", "カテゴリ", "単価", "数量", "合計"],
["ノートPC", "電子機器", 98000, 5, None],
["マウス", "電子機器", 2500, 20, None],
["キーボード", "電子機器", 5800, 15, None],
["デスク", "家具", 45000, 3, None],
["チェア", "家具", 32000, 8, None],
["モニター", "電子機器", 38000, 10, None],
["USB-Cケーブル", "アクセサリ", 1200, 50, None],
["Webカメラ", "電子機器", 8500, 7, None],
]
def __init__(self, root):
self.root = root
self.root.title("Excelレポート生成")
self.root.geometry("900x600")
self.root.configure(bg="#1e1e1e")
self._rows = [list(r) for r in self.SAMPLE_DATA]
self._build_ui()
self._refresh_table()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="📊 Excelレポート生成",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
if not OPENPYXL_AVAILABLE:
tk.Label(self.root,
text="⚠ openpyxl が未インストールです (pip install openpyxl)",
bg="#fff3cd", fg="#856404", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X)
# ツールバー
tb = tk.Frame(self.root, bg="#2d2d2d", pady=4)
tb.pack(fill=tk.X)
ttk.Button(tb, text="📂 CSV読込", command=self._load_csv).pack(side=tk.LEFT, padx=4)
ttk.Button(tb, text="＋ 行追加", command=self._add_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="✕ 行削除", command=self._del_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="📥 Excel出力", command=self._export).pack(side=tk.LEFT, padx=8)
tk.Label(tb, text="タイトル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.title_var = tk.StringVar(value="売上レポート")
ttk.Entry(tb, textvariable=self.title_var, width=20).pack(side=tk.LEFT)
# スタイル
tk.Label(tb, text="スタイル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.style_var = tk.StringVar(value="ブルー")
ttk.Combobox(tb, textvariable=self.style_var,
values=["ブルー", "グリーン", "オレンジ"],
state="readonly", width=10).pack(side=tk.LEFT)
# テーブル（Treeview）
table_f = tk.Frame(self.root, bg="#1e1e1e")
table_f.pack(fill=tk.BOTH, expand=True, padx=8, pady=4)
# 列数に応じて動的にTreeviewを構築
cols = [f"c{i}" for i in range(len(self._rows[0]))]
self.tree = ttk.Treeview(table_f, columns=cols, show="headings",
selectmode="browse")
sb_x = ttk.Scrollbar(table_f, orient=tk.HORIZONTAL,
command=self.tree.xview)
sb_y = ttk.Scrollbar(table_f, command=self.tree.yview)
self.tree.configure(xscrollcommand=sb_x.set, yscrollcommand=sb_y.set)
sb_y.pack(side=tk.RIGHT, fill=tk.Y)
self.tree.pack(fill=tk.BOTH, expand=True)
sb_x.pack(fill=tk.X)
self.tree.bind("<Double-1>", self._on_double_click)
# 行追加フォーム
add_f = tk.Frame(self.root, bg="#252526", pady=4)
add_f.pack(fill=tk.X, padx=8)
tk.Label(add_f, text="新規行:", bg="#252526", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT)
self.row_entries = []
for _ in range(len(self._rows[0])):
e = ttk.Entry(add_f, width=12)
e.pack(side=tk.LEFT, padx=2)
self.row_entries.append(e)
ttk.Button(add_f, text="追加",
command=self._add_row).pack(side=tk.LEFT, padx=4)
self.status_var = tk.StringVar(value="データを編集してExcelを出力できます")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _refresh_table(self):
# ヘッダー設定
if not self._rows:
return
headers = self._rows[0]
n = len(headers)
cols = [f"c{i}" for i in range(n)]
# Treeviewを再構成
self.tree.configure(columns=cols)
for i, h in enumerate(headers):
self.tree.heading(f"c{i}", text=str(h))
self.tree.column(f"c{i}", width=120, anchor="center")
self.tree.delete(*self.tree.get_children())
for row in self._rows[1:]:
values = []
for j, v in enumerate(row):
if v is None and j == len(row) - 1:
# 合計列を計算
try:
values.append(int(row[2]) * int(row[3]))
except Exception:
values.append("")
else:
values.append(v if v is not None else "")
self.tree.insert("", tk.END, values=values)
self.status_var.set(f"{len(self._rows)-1} 行のデータ")
def _load_csv(self):
path = filedialog.askopenfilename(
filetypes=[("CSV", "*.csv"), ("すべて", "*.*")])
if not path:
return
try:
with open(path, encoding="utf-8-sig") as f:
reader = csv.reader(f)
rows = list(reader)
if rows:
self._rows = rows
self._refresh_table()
except Exception as e:
messagebox.showerror("エラー", str(e))
def _add_row(self):
vals = [e.get().strip() for e in self.row_entries]
if any(vals):
self._rows.append(vals)
self._refresh_table()
for e in self.row_entries:
e.delete(0, tk.END)
def _del_row(self):
sel = self.tree.selection()
if not sel:
return
idx = self.tree.index(sel[0]) + 1
if 1 <= idx < len(self._rows):
del self._rows[idx]
self._refresh_table()
def _on_double_click(self, event):
"""セルをダブルクリックして編集"""
region = self.tree.identify("region", event.x, event.y)
if region != "cell":
return
col = self.tree.identify_column(event.x)
row_iid = self.tree.identify_row(event.y)
if not row_iid:
return
col_idx = int(col.lstrip("#")) - 1
row_idx = self.tree.index(row_iid) + 1
current_val = self.tree.set(row_iid, f"c{col_idx}")
# ポップアップ編集
bbox = self.tree.bbox(row_iid, col)
if not bbox:
return
entry = tk.Entry(self.tree, font=("Arial", 10))
entry.place(x=bbox[0], y=bbox[1], width=bbox[2], height=bbox[3])
entry.insert(0, current_val)
entry.focus_set()
def commit(e=None):
new_val = entry.get()
self._rows[row_idx][col_idx] = new_val
self._refresh_table()
entry.destroy()
entry.bind("<Return>", commit)
entry.bind("<FocusOut>", commit)
def _export(self):
if not OPENPYXL_AVAILABLE:
messagebox.showerror("エラー",
"openpyxl をインストールしてください:\npip install openpyxl")
return
path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel", "*.xlsx")])
if not path:
return
threading.Thread(target=self._do_export, args=(path,),
daemon=True).start()
def _do_export(self, path):
try:
wb = openpyxl.Workbook()
ws = wb.active
ws.title = self.title_var.get() or "レポート"
# スタイル定義
style = self.style_var.get()
if style == "グリーン":
hdr_fill_color = "1E7E34"
row_fill_color = "D4EDDA"
elif style == "オレンジ":
hdr_fill_color = "E65100"
row_fill_color = "FFF3E0"
else: # ブルー
hdr_fill_color = "1565C0"
row_fill_color = "E3F2FD"
hdr_fill = PatternFill("solid", fgColor=hdr_fill_color)
row_fill = PatternFill("solid", fgColor=row_fill_color)
thin = Side(style="thin", color="AAAAAA")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
center = Alignment(horizontal="center", vertical="center")
# タイトル行
title_cell = ws.cell(row=1, column=1,
value=self.title_var.get())
title_cell.font = Font(bold=True, size=14, color="222222")
title_cell.alignment = center
ws.merge_cells(start_row=1, start_column=1,
end_row=1, end_column=len(self._rows[0]))
# 生成日時
date_cell = ws.cell(row=2, column=1,
value=f"生成日時: {datetime.now().strftime('%Y-%m-%d %H:%M')}")
date_cell.font = Font(size=9, color="888888")
ws.row_dimensions[1].height = 30
ws.row_dimensions[2].height = 16
# ヘッダー
headers = self._rows[0]
for j, h in enumerate(headers, 1):
cell = ws.cell(row=3, column=j, value=h)
cell.fill = hdr_fill
cell.font = Font(bold=True, color="FFFFFF")
cell.alignment = center
cell.border = border
ws.row_dimensions[3].height = 22
# データ行
for i, row in enumerate(self._rows[1:], 4):
for j, val in enumerate(row, 1):
v = val
if v is None:
v = ""
# 数値変換
if isinstance(v, str):
try:
v = int(v)
except Exception:
try:
v = float(v)
except Exception:
pass
cell = ws.cell(row=i, column=j, value=v)
cell.border = border
cell.alignment = center
if i % 2 == 0:
cell.fill = row_fill
# 列幅調整
for col_cells in ws.columns:
max_len = 0
col_letter = get_column_letter(col_cells[0].column)
for cell in col_cells:
if cell.value:
max_len = max(max_len, len(str(cell.value)))
ws.column_dimensions[col_letter].width = min(max_len + 4, 30)
# 合計行
last_row = len(self._rows) + 3
try:
num_cols = len(self._rows[0])
for j in range(1, num_cols + 1):
cell = ws.cell(row=last_row, column=j)
col_letter = get_column_letter(j)
# 数値列に合計を入れる
try:
sample = float(self._rows[1][j-1])
cell.value = f"=SUM({col_letter}4:{col_letter}{last_row-1})"
cell.font = Font(bold=True)
except Exception:
if j == 1:
cell.value = "合計"
cell.font = Font(bold=True)
cell.fill = PatternFill("solid", fgColor="EEEEEE")
cell.border = border
cell.alignment = center
except Exception:
pass
wb.save(path)
self.root.after(0, self._export_done, path)
except Exception as e:
self.root.after(0, lambda: messagebox.showerror("エラー", str(e)))
def _export_done(self, path):
self.status_var.set(f"保存完了: {os.path.basename(path)}")
messagebox.showinfo("完了", f"Excelファイルを保存しました:\n{path}")
if __name__ == "__main__":
root = tk.Tk()
app = App055(root)
root.mainloop()
UIレイアウトの構築
LabelFrameで入力エリアと結果エリアを視覚的に分けています。pack()で縦に並べ、expand=Trueで結果エリアが画面いっぱいに広がるよう設定しています。
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import os
import csv
import threading
from datetime import datetime
try:
import openpyxl
from openpyxl.styles import (Font, PatternFill, Alignment,
Border, Side)
from openpyxl.utils import get_column_letter
OPENPYXL_AVAILABLE = True
except ImportError:
OPENPYXL_AVAILABLE = False
class App055:
"""Excelレポート生成"""
SAMPLE_DATA = [
["商品名", "カテゴリ", "単価", "数量", "合計"],
["ノートPC", "電子機器", 98000, 5, None],
["マウス", "電子機器", 2500, 20, None],
["キーボード", "電子機器", 5800, 15, None],
["デスク", "家具", 45000, 3, None],
["チェア", "家具", 32000, 8, None],
["モニター", "電子機器", 38000, 10, None],
["USB-Cケーブル", "アクセサリ", 1200, 50, None],
["Webカメラ", "電子機器", 8500, 7, None],
]
def __init__(self, root):
self.root = root
self.root.title("Excelレポート生成")
self.root.geometry("900x600")
self.root.configure(bg="#1e1e1e")
self._rows = [list(r) for r in self.SAMPLE_DATA]
self._build_ui()
self._refresh_table()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="📊 Excelレポート生成",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
if not OPENPYXL_AVAILABLE:
tk.Label(self.root,
text="⚠ openpyxl が未インストールです (pip install openpyxl)",
bg="#fff3cd", fg="#856404", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X)
# ツールバー
tb = tk.Frame(self.root, bg="#2d2d2d", pady=4)
tb.pack(fill=tk.X)
ttk.Button(tb, text="📂 CSV読込", command=self._load_csv).pack(side=tk.LEFT, padx=4)
ttk.Button(tb, text="＋ 行追加", command=self._add_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="✕ 行削除", command=self._del_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="📥 Excel出力", command=self._export).pack(side=tk.LEFT, padx=8)
tk.Label(tb, text="タイトル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.title_var = tk.StringVar(value="売上レポート")
ttk.Entry(tb, textvariable=self.title_var, width=20).pack(side=tk.LEFT)
# スタイル
tk.Label(tb, text="スタイル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.style_var = tk.StringVar(value="ブルー")
ttk.Combobox(tb, textvariable=self.style_var,
values=["ブルー", "グリーン", "オレンジ"],
state="readonly", width=10).pack(side=tk.LEFT)
# テーブル（Treeview）
table_f = tk.Frame(self.root, bg="#1e1e1e")
table_f.pack(fill=tk.BOTH, expand=True, padx=8, pady=4)
# 列数に応じて動的にTreeviewを構築
cols = [f"c{i}" for i in range(len(self._rows[0]))]
self.tree = ttk.Treeview(table_f, columns=cols, show="headings",
selectmode="browse")
sb_x = ttk.Scrollbar(table_f, orient=tk.HORIZONTAL,
command=self.tree.xview)
sb_y = ttk.Scrollbar(table_f, command=self.tree.yview)
self.tree.configure(xscrollcommand=sb_x.set, yscrollcommand=sb_y.set)
sb_y.pack(side=tk.RIGHT, fill=tk.Y)
self.tree.pack(fill=tk.BOTH, expand=True)
sb_x.pack(fill=tk.X)
self.tree.bind("<Double-1>", self._on_double_click)
# 行追加フォーム
add_f = tk.Frame(self.root, bg="#252526", pady=4)
add_f.pack(fill=tk.X, padx=8)
tk.Label(add_f, text="新規行:", bg="#252526", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT)
self.row_entries = []
for _ in range(len(self._rows[0])):
e = ttk.Entry(add_f, width=12)
e.pack(side=tk.LEFT, padx=2)
self.row_entries.append(e)
ttk.Button(add_f, text="追加",
command=self._add_row).pack(side=tk.LEFT, padx=4)
self.status_var = tk.StringVar(value="データを編集してExcelを出力できます")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _refresh_table(self):
# ヘッダー設定
if not self._rows:
return
headers = self._rows[0]
n = len(headers)
cols = [f"c{i}" for i in range(n)]
# Treeviewを再構成
self.tree.configure(columns=cols)
for i, h in enumerate(headers):
self.tree.heading(f"c{i}", text=str(h))
self.tree.column(f"c{i}", width=120, anchor="center")
self.tree.delete(*self.tree.get_children())
for row in self._rows[1:]:
values = []
for j, v in enumerate(row):
if v is None and j == len(row) - 1:
# 合計列を計算
try:
values.append(int(row[2]) * int(row[3]))
except Exception:
values.append("")
else:
values.append(v if v is not None else "")
self.tree.insert("", tk.END, values=values)
self.status_var.set(f"{len(self._rows)-1} 行のデータ")
def _load_csv(self):
path = filedialog.askopenfilename(
filetypes=[("CSV", "*.csv"), ("すべて", "*.*")])
if not path:
return
try:
with open(path, encoding="utf-8-sig") as f:
reader = csv.reader(f)
rows = list(reader)
if rows:
self._rows = rows
self._refresh_table()
except Exception as e:
messagebox.showerror("エラー", str(e))
def _add_row(self):
vals = [e.get().strip() for e in self.row_entries]
if any(vals):
self._rows.append(vals)
self._refresh_table()
for e in self.row_entries:
e.delete(0, tk.END)
def _del_row(self):
sel = self.tree.selection()
if not sel:
return
idx = self.tree.index(sel[0]) + 1
if 1 <= idx < len(self._rows):
del self._rows[idx]
self._refresh_table()
def _on_double_click(self, event):
"""セルをダブルクリックして編集"""
region = self.tree.identify("region", event.x, event.y)
if region != "cell":
return
col = self.tree.identify_column(event.x)
row_iid = self.tree.identify_row(event.y)
if not row_iid:
return
col_idx = int(col.lstrip("#")) - 1
row_idx = self.tree.index(row_iid) + 1
current_val = self.tree.set(row_iid, f"c{col_idx}")
# ポップアップ編集
bbox = self.tree.bbox(row_iid, col)
if not bbox:
return
entry = tk.Entry(self.tree, font=("Arial", 10))
entry.place(x=bbox[0], y=bbox[1], width=bbox[2], height=bbox[3])
entry.insert(0, current_val)
entry.focus_set()
def commit(e=None):
new_val = entry.get()
self._rows[row_idx][col_idx] = new_val
self._refresh_table()
entry.destroy()
entry.bind("<Return>", commit)
entry.bind("<FocusOut>", commit)
def _export(self):
if not OPENPYXL_AVAILABLE:
messagebox.showerror("エラー",
"openpyxl をインストールしてください:\npip install openpyxl")
return
path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel", "*.xlsx")])
if not path:
return
threading.Thread(target=self._do_export, args=(path,),
daemon=True).start()
def _do_export(self, path):
try:
wb = openpyxl.Workbook()
ws = wb.active
ws.title = self.title_var.get() or "レポート"
# スタイル定義
style = self.style_var.get()
if style == "グリーン":
hdr_fill_color = "1E7E34"
row_fill_color = "D4EDDA"
elif style == "オレンジ":
hdr_fill_color = "E65100"
row_fill_color = "FFF3E0"
else: # ブルー
hdr_fill_color = "1565C0"
row_fill_color = "E3F2FD"
hdr_fill = PatternFill("solid", fgColor=hdr_fill_color)
row_fill = PatternFill("solid", fgColor=row_fill_color)
thin = Side(style="thin", color="AAAAAA")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
center = Alignment(horizontal="center", vertical="center")
# タイトル行
title_cell = ws.cell(row=1, column=1,
value=self.title_var.get())
title_cell.font = Font(bold=True, size=14, color="222222")
title_cell.alignment = center
ws.merge_cells(start_row=1, start_column=1,
end_row=1, end_column=len(self._rows[0]))
# 生成日時
date_cell = ws.cell(row=2, column=1,
value=f"生成日時: {datetime.now().strftime('%Y-%m-%d %H:%M')}")
date_cell.font = Font(size=9, color="888888")
ws.row_dimensions[1].height = 30
ws.row_dimensions[2].height = 16
# ヘッダー
headers = self._rows[0]
for j, h in enumerate(headers, 1):
cell = ws.cell(row=3, column=j, value=h)
cell.fill = hdr_fill
cell.font = Font(bold=True, color="FFFFFF")
cell.alignment = center
cell.border = border
ws.row_dimensions[3].height = 22
# データ行
for i, row in enumerate(self._rows[1:], 4):
for j, val in enumerate(row, 1):
v = val
if v is None:
v = ""
# 数値変換
if isinstance(v, str):
try:
v = int(v)
except Exception:
try:
v = float(v)
except Exception:
pass
cell = ws.cell(row=i, column=j, value=v)
cell.border = border
cell.alignment = center
if i % 2 == 0:
cell.fill = row_fill
# 列幅調整
for col_cells in ws.columns:
max_len = 0
col_letter = get_column_letter(col_cells[0].column)
for cell in col_cells:
if cell.value:
max_len = max(max_len, len(str(cell.value)))
ws.column_dimensions[col_letter].width = min(max_len + 4, 30)
# 合計行
last_row = len(self._rows) + 3
try:
num_cols = len(self._rows[0])
for j in range(1, num_cols + 1):
cell = ws.cell(row=last_row, column=j)
col_letter = get_column_letter(j)
# 数値列に合計を入れる
try:
sample = float(self._rows[1][j-1])
cell.value = f"=SUM({col_letter}4:{col_letter}{last_row-1})"
cell.font = Font(bold=True)
except Exception:
if j == 1:
cell.value = "合計"
cell.font = Font(bold=True)
cell.fill = PatternFill("solid", fgColor="EEEEEE")
cell.border = border
cell.alignment = center
except Exception:
pass
wb.save(path)
self.root.after(0, self._export_done, path)
except Exception as e:
self.root.after(0, lambda: messagebox.showerror("エラー", str(e)))
def _export_done(self, path):
self.status_var.set(f"保存完了: {os.path.basename(path)}")
messagebox.showinfo("完了", f"Excelファイルを保存しました:\n{path}")
if __name__ == "__main__":
root = tk.Tk()
app = App055(root)
root.mainloop()
イベント処理
ボタンのcommand引数でクリックイベントを、bind('
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import os
import csv
import threading
from datetime import datetime
try:
import openpyxl
from openpyxl.styles import (Font, PatternFill, Alignment,
Border, Side)
from openpyxl.utils import get_column_letter
OPENPYXL_AVAILABLE = True
except ImportError:
OPENPYXL_AVAILABLE = False
class App055:
"""Excelレポート生成"""
SAMPLE_DATA = [
["商品名", "カテゴリ", "単価", "数量", "合計"],
["ノートPC", "電子機器", 98000, 5, None],
["マウス", "電子機器", 2500, 20, None],
["キーボード", "電子機器", 5800, 15, None],
["デスク", "家具", 45000, 3, None],
["チェア", "家具", 32000, 8, None],
["モニター", "電子機器", 38000, 10, None],
["USB-Cケーブル", "アクセサリ", 1200, 50, None],
["Webカメラ", "電子機器", 8500, 7, None],
]
def __init__(self, root):
self.root = root
self.root.title("Excelレポート生成")
self.root.geometry("900x600")
self.root.configure(bg="#1e1e1e")
self._rows = [list(r) for r in self.SAMPLE_DATA]
self._build_ui()
self._refresh_table()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="📊 Excelレポート生成",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
if not OPENPYXL_AVAILABLE:
tk.Label(self.root,
text="⚠ openpyxl が未インストールです (pip install openpyxl)",
bg="#fff3cd", fg="#856404", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X)
# ツールバー
tb = tk.Frame(self.root, bg="#2d2d2d", pady=4)
tb.pack(fill=tk.X)
ttk.Button(tb, text="📂 CSV読込", command=self._load_csv).pack(side=tk.LEFT, padx=4)
ttk.Button(tb, text="＋ 行追加", command=self._add_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="✕ 行削除", command=self._del_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="📥 Excel出力", command=self._export).pack(side=tk.LEFT, padx=8)
tk.Label(tb, text="タイトル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.title_var = tk.StringVar(value="売上レポート")
ttk.Entry(tb, textvariable=self.title_var, width=20).pack(side=tk.LEFT)
# スタイル
tk.Label(tb, text="スタイル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.style_var = tk.StringVar(value="ブルー")
ttk.Combobox(tb, textvariable=self.style_var,
values=["ブルー", "グリーン", "オレンジ"],
state="readonly", width=10).pack(side=tk.LEFT)
# テーブル（Treeview）
table_f = tk.Frame(self.root, bg="#1e1e1e")
table_f.pack(fill=tk.BOTH, expand=True, padx=8, pady=4)
# 列数に応じて動的にTreeviewを構築
cols = [f"c{i}" for i in range(len(self._rows[0]))]
self.tree = ttk.Treeview(table_f, columns=cols, show="headings",
selectmode="browse")
sb_x = ttk.Scrollbar(table_f, orient=tk.HORIZONTAL,
command=self.tree.xview)
sb_y = ttk.Scrollbar(table_f, command=self.tree.yview)
self.tree.configure(xscrollcommand=sb_x.set, yscrollcommand=sb_y.set)
sb_y.pack(side=tk.RIGHT, fill=tk.Y)
self.tree.pack(fill=tk.BOTH, expand=True)
sb_x.pack(fill=tk.X)
self.tree.bind("<Double-1>", self._on_double_click)
# 行追加フォーム
add_f = tk.Frame(self.root, bg="#252526", pady=4)
add_f.pack(fill=tk.X, padx=8)
tk.Label(add_f, text="新規行:", bg="#252526", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT)
self.row_entries = []
for _ in range(len(self._rows[0])):
e = ttk.Entry(add_f, width=12)
e.pack(side=tk.LEFT, padx=2)
self.row_entries.append(e)
ttk.Button(add_f, text="追加",
command=self._add_row).pack(side=tk.LEFT, padx=4)
self.status_var = tk.StringVar(value="データを編集してExcelを出力できます")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _refresh_table(self):
# ヘッダー設定
if not self._rows:
return
headers = self._rows[0]
n = len(headers)
cols = [f"c{i}" for i in range(n)]
# Treeviewを再構成
self.tree.configure(columns=cols)
for i, h in enumerate(headers):
self.tree.heading(f"c{i}", text=str(h))
self.tree.column(f"c{i}", width=120, anchor="center")
self.tree.delete(*self.tree.get_children())
for row in self._rows[1:]:
values = []
for j, v in enumerate(row):
if v is None and j == len(row) - 1:
# 合計列を計算
try:
values.append(int(row[2]) * int(row[3]))
except Exception:
values.append("")
else:
values.append(v if v is not None else "")
self.tree.insert("", tk.END, values=values)
self.status_var.set(f"{len(self._rows)-1} 行のデータ")
def _load_csv(self):
path = filedialog.askopenfilename(
filetypes=[("CSV", "*.csv"), ("すべて", "*.*")])
if not path:
return
try:
with open(path, encoding="utf-8-sig") as f:
reader = csv.reader(f)
rows = list(reader)
if rows:
self._rows = rows
self._refresh_table()
except Exception as e:
messagebox.showerror("エラー", str(e))
def _add_row(self):
vals = [e.get().strip() for e in self.row_entries]
if any(vals):
self._rows.append(vals)
self._refresh_table()
for e in self.row_entries:
e.delete(0, tk.END)
def _del_row(self):
sel = self.tree.selection()
if not sel:
return
idx = self.tree.index(sel[0]) + 1
if 1 <= idx < len(self._rows):
del self._rows[idx]
self._refresh_table()
def _on_double_click(self, event):
"""セルをダブルクリックして編集"""
region = self.tree.identify("region", event.x, event.y)
if region != "cell":
return
col = self.tree.identify_column(event.x)
row_iid = self.tree.identify_row(event.y)
if not row_iid:
return
col_idx = int(col.lstrip("#")) - 1
row_idx = self.tree.index(row_iid) + 1
current_val = self.tree.set(row_iid, f"c{col_idx}")
# ポップアップ編集
bbox = self.tree.bbox(row_iid, col)
if not bbox:
return
entry = tk.Entry(self.tree, font=("Arial", 10))
entry.place(x=bbox[0], y=bbox[1], width=bbox[2], height=bbox[3])
entry.insert(0, current_val)
entry.focus_set()
def commit(e=None):
new_val = entry.get()
self._rows[row_idx][col_idx] = new_val
self._refresh_table()
entry.destroy()
entry.bind("<Return>", commit)
entry.bind("<FocusOut>", commit)
def _export(self):
if not OPENPYXL_AVAILABLE:
messagebox.showerror("エラー",
"openpyxl をインストールしてください:\npip install openpyxl")
return
path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel", "*.xlsx")])
if not path:
return
threading.Thread(target=self._do_export, args=(path,),
daemon=True).start()
def _do_export(self, path):
try:
wb = openpyxl.Workbook()
ws = wb.active
ws.title = self.title_var.get() or "レポート"
# スタイル定義
style = self.style_var.get()
if style == "グリーン":
hdr_fill_color = "1E7E34"
row_fill_color = "D4EDDA"
elif style == "オレンジ":
hdr_fill_color = "E65100"
row_fill_color = "FFF3E0"
else: # ブルー
hdr_fill_color = "1565C0"
row_fill_color = "E3F2FD"
hdr_fill = PatternFill("solid", fgColor=hdr_fill_color)
row_fill = PatternFill("solid", fgColor=row_fill_color)
thin = Side(style="thin", color="AAAAAA")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
center = Alignment(horizontal="center", vertical="center")
# タイトル行
title_cell = ws.cell(row=1, column=1,
value=self.title_var.get())
title_cell.font = Font(bold=True, size=14, color="222222")
title_cell.alignment = center
ws.merge_cells(start_row=1, start_column=1,
end_row=1, end_column=len(self._rows[0]))
# 生成日時
date_cell = ws.cell(row=2, column=1,
value=f"生成日時: {datetime.now().strftime('%Y-%m-%d %H:%M')}")
date_cell.font = Font(size=9, color="888888")
ws.row_dimensions[1].height = 30
ws.row_dimensions[2].height = 16
# ヘッダー
headers = self._rows[0]
for j, h in enumerate(headers, 1):
cell = ws.cell(row=3, column=j, value=h)
cell.fill = hdr_fill
cell.font = Font(bold=True, color="FFFFFF")
cell.alignment = center
cell.border = border
ws.row_dimensions[3].height = 22
# データ行
for i, row in enumerate(self._rows[1:], 4):
for j, val in enumerate(row, 1):
v = val
if v is None:
v = ""
# 数値変換
if isinstance(v, str):
try:
v = int(v)
except Exception:
try:
v = float(v)
except Exception:
pass
cell = ws.cell(row=i, column=j, value=v)
cell.border = border
cell.alignment = center
if i % 2 == 0:
cell.fill = row_fill
# 列幅調整
for col_cells in ws.columns:
max_len = 0
col_letter = get_column_letter(col_cells[0].column)
for cell in col_cells:
if cell.value:
max_len = max(max_len, len(str(cell.value)))
ws.column_dimensions[col_letter].width = min(max_len + 4, 30)
# 合計行
last_row = len(self._rows) + 3
try:
num_cols = len(self._rows[0])
for j in range(1, num_cols + 1):
cell = ws.cell(row=last_row, column=j)
col_letter = get_column_letter(j)
# 数値列に合計を入れる
try:
sample = float(self._rows[1][j-1])
cell.value = f"=SUM({col_letter}4:{col_letter}{last_row-1})"
cell.font = Font(bold=True)
except Exception:
if j == 1:
cell.value = "合計"
cell.font = Font(bold=True)
cell.fill = PatternFill("solid", fgColor="EEEEEE")
cell.border = border
cell.alignment = center
except Exception:
pass
wb.save(path)
self.root.after(0, self._export_done, path)
except Exception as e:
self.root.after(0, lambda: messagebox.showerror("エラー", str(e)))
def _export_done(self, path):
self.status_var.set(f"保存完了: {os.path.basename(path)}")
messagebox.showinfo("完了", f"Excelファイルを保存しました:\n{path}")
if __name__ == "__main__":
root = tk.Tk()
app = App055(root)
root.mainloop()
Textウィジェットでの結果表示
tk.Textウィジェットをstate=DISABLED（読み取り専用）で作成し、更新時はNORMALに変更してinsert()で内容を書き込み、再びDISABLEDに戻します。
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import os
import csv
import threading
from datetime import datetime
try:
import openpyxl
from openpyxl.styles import (Font, PatternFill, Alignment,
Border, Side)
from openpyxl.utils import get_column_letter
OPENPYXL_AVAILABLE = True
except ImportError:
OPENPYXL_AVAILABLE = False
class App055:
"""Excelレポート生成"""
SAMPLE_DATA = [
["商品名", "カテゴリ", "単価", "数量", "合計"],
["ノートPC", "電子機器", 98000, 5, None],
["マウス", "電子機器", 2500, 20, None],
["キーボード", "電子機器", 5800, 15, None],
["デスク", "家具", 45000, 3, None],
["チェア", "家具", 32000, 8, None],
["モニター", "電子機器", 38000, 10, None],
["USB-Cケーブル", "アクセサリ", 1200, 50, None],
["Webカメラ", "電子機器", 8500, 7, None],
]
def __init__(self, root):
self.root = root
self.root.title("Excelレポート生成")
self.root.geometry("900x600")
self.root.configure(bg="#1e1e1e")
self._rows = [list(r) for r in self.SAMPLE_DATA]
self._build_ui()
self._refresh_table()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="📊 Excelレポート生成",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
if not OPENPYXL_AVAILABLE:
tk.Label(self.root,
text="⚠ openpyxl が未インストールです (pip install openpyxl)",
bg="#fff3cd", fg="#856404", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X)
# ツールバー
tb = tk.Frame(self.root, bg="#2d2d2d", pady=4)
tb.pack(fill=tk.X)
ttk.Button(tb, text="📂 CSV読込", command=self._load_csv).pack(side=tk.LEFT, padx=4)
ttk.Button(tb, text="＋ 行追加", command=self._add_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="✕ 行削除", command=self._del_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="📥 Excel出力", command=self._export).pack(side=tk.LEFT, padx=8)
tk.Label(tb, text="タイトル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.title_var = tk.StringVar(value="売上レポート")
ttk.Entry(tb, textvariable=self.title_var, width=20).pack(side=tk.LEFT)
# スタイル
tk.Label(tb, text="スタイル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.style_var = tk.StringVar(value="ブルー")
ttk.Combobox(tb, textvariable=self.style_var,
values=["ブルー", "グリーン", "オレンジ"],
state="readonly", width=10).pack(side=tk.LEFT)
# テーブル（Treeview）
table_f = tk.Frame(self.root, bg="#1e1e1e")
table_f.pack(fill=tk.BOTH, expand=True, padx=8, pady=4)
# 列数に応じて動的にTreeviewを構築
cols = [f"c{i}" for i in range(len(self._rows[0]))]
self.tree = ttk.Treeview(table_f, columns=cols, show="headings",
selectmode="browse")
sb_x = ttk.Scrollbar(table_f, orient=tk.HORIZONTAL,
command=self.tree.xview)
sb_y = ttk.Scrollbar(table_f, command=self.tree.yview)
self.tree.configure(xscrollcommand=sb_x.set, yscrollcommand=sb_y.set)
sb_y.pack(side=tk.RIGHT, fill=tk.Y)
self.tree.pack(fill=tk.BOTH, expand=True)
sb_x.pack(fill=tk.X)
self.tree.bind("<Double-1>", self._on_double_click)
# 行追加フォーム
add_f = tk.Frame(self.root, bg="#252526", pady=4)
add_f.pack(fill=tk.X, padx=8)
tk.Label(add_f, text="新規行:", bg="#252526", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT)
self.row_entries = []
for _ in range(len(self._rows[0])):
e = ttk.Entry(add_f, width=12)
e.pack(side=tk.LEFT, padx=2)
self.row_entries.append(e)
ttk.Button(add_f, text="追加",
command=self._add_row).pack(side=tk.LEFT, padx=4)
self.status_var = tk.StringVar(value="データを編集してExcelを出力できます")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _refresh_table(self):
# ヘッダー設定
if not self._rows:
return
headers = self._rows[0]
n = len(headers)
cols = [f"c{i}" for i in range(n)]
# Treeviewを再構成
self.tree.configure(columns=cols)
for i, h in enumerate(headers):
self.tree.heading(f"c{i}", text=str(h))
self.tree.column(f"c{i}", width=120, anchor="center")
self.tree.delete(*self.tree.get_children())
for row in self._rows[1:]:
values = []
for j, v in enumerate(row):
if v is None and j == len(row) - 1:
# 合計列を計算
try:
values.append(int(row[2]) * int(row[3]))
except Exception:
values.append("")
else:
values.append(v if v is not None else "")
self.tree.insert("", tk.END, values=values)
self.status_var.set(f"{len(self._rows)-1} 行のデータ")
def _load_csv(self):
path = filedialog.askopenfilename(
filetypes=[("CSV", "*.csv"), ("すべて", "*.*")])
if not path:
return
try:
with open(path, encoding="utf-8-sig") as f:
reader = csv.reader(f)
rows = list(reader)
if rows:
self._rows = rows
self._refresh_table()
except Exception as e:
messagebox.showerror("エラー", str(e))
def _add_row(self):
vals = [e.get().strip() for e in self.row_entries]
if any(vals):
self._rows.append(vals)
self._refresh_table()
for e in self.row_entries:
e.delete(0, tk.END)
def _del_row(self):
sel = self.tree.selection()
if not sel:
return
idx = self.tree.index(sel[0]) + 1
if 1 <= idx < len(self._rows):
del self._rows[idx]
self._refresh_table()
def _on_double_click(self, event):
"""セルをダブルクリックして編集"""
region = self.tree.identify("region", event.x, event.y)
if region != "cell":
return
col = self.tree.identify_column(event.x)
row_iid = self.tree.identify_row(event.y)
if not row_iid:
return
col_idx = int(col.lstrip("#")) - 1
row_idx = self.tree.index(row_iid) + 1
current_val = self.tree.set(row_iid, f"c{col_idx}")
# ポップアップ編集
bbox = self.tree.bbox(row_iid, col)
if not bbox:
return
entry = tk.Entry(self.tree, font=("Arial", 10))
entry.place(x=bbox[0], y=bbox[1], width=bbox[2], height=bbox[3])
entry.insert(0, current_val)
entry.focus_set()
def commit(e=None):
new_val = entry.get()
self._rows[row_idx][col_idx] = new_val
self._refresh_table()
entry.destroy()
entry.bind("<Return>", commit)
entry.bind("<FocusOut>", commit)
def _export(self):
if not OPENPYXL_AVAILABLE:
messagebox.showerror("エラー",
"openpyxl をインストールしてください:\npip install openpyxl")
return
path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel", "*.xlsx")])
if not path:
return
threading.Thread(target=self._do_export, args=(path,),
daemon=True).start()
def _do_export(self, path):
try:
wb = openpyxl.Workbook()
ws = wb.active
ws.title = self.title_var.get() or "レポート"
# スタイル定義
style = self.style_var.get()
if style == "グリーン":
hdr_fill_color = "1E7E34"
row_fill_color = "D4EDDA"
elif style == "オレンジ":
hdr_fill_color = "E65100"
row_fill_color = "FFF3E0"
else: # ブルー
hdr_fill_color = "1565C0"
row_fill_color = "E3F2FD"
hdr_fill = PatternFill("solid", fgColor=hdr_fill_color)
row_fill = PatternFill("solid", fgColor=row_fill_color)
thin = Side(style="thin", color="AAAAAA")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
center = Alignment(horizontal="center", vertical="center")
# タイトル行
title_cell = ws.cell(row=1, column=1,
value=self.title_var.get())
title_cell.font = Font(bold=True, size=14, color="222222")
title_cell.alignment = center
ws.merge_cells(start_row=1, start_column=1,
end_row=1, end_column=len(self._rows[0]))
# 生成日時
date_cell = ws.cell(row=2, column=1,
value=f"生成日時: {datetime.now().strftime('%Y-%m-%d %H:%M')}")
date_cell.font = Font(size=9, color="888888")
ws.row_dimensions[1].height = 30
ws.row_dimensions[2].height = 16
# ヘッダー
headers = self._rows[0]
for j, h in enumerate(headers, 1):
cell = ws.cell(row=3, column=j, value=h)
cell.fill = hdr_fill
cell.font = Font(bold=True, color="FFFFFF")
cell.alignment = center
cell.border = border
ws.row_dimensions[3].height = 22
# データ行
for i, row in enumerate(self._rows[1:], 4):
for j, val in enumerate(row, 1):
v = val
if v is None:
v = ""
# 数値変換
if isinstance(v, str):
try:
v = int(v)
except Exception:
try:
v = float(v)
except Exception:
pass
cell = ws.cell(row=i, column=j, value=v)
cell.border = border
cell.alignment = center
if i % 2 == 0:
cell.fill = row_fill
# 列幅調整
for col_cells in ws.columns:
max_len = 0
col_letter = get_column_letter(col_cells[0].column)
for cell in col_cells:
if cell.value:
max_len = max(max_len, len(str(cell.value)))
ws.column_dimensions[col_letter].width = min(max_len + 4, 30)
# 合計行
last_row = len(self._rows) + 3
try:
num_cols = len(self._rows[0])
for j in range(1, num_cols + 1):
cell = ws.cell(row=last_row, column=j)
col_letter = get_column_letter(j)
# 数値列に合計を入れる
try:
sample = float(self._rows[1][j-1])
cell.value = f"=SUM({col_letter}4:{col_letter}{last_row-1})"
cell.font = Font(bold=True)
except Exception:
if j == 1:
cell.value = "合計"
cell.font = Font(bold=True)
cell.fill = PatternFill("solid", fgColor="EEEEEE")
cell.border = border
cell.alignment = center
except Exception:
pass
wb.save(path)
self.root.after(0, self._export_done, path)
except Exception as e:
self.root.after(0, lambda: messagebox.showerror("エラー", str(e)))
def _export_done(self, path):
self.status_var.set(f"保存完了: {os.path.basename(path)}")
messagebox.showinfo("完了", f"Excelファイルを保存しました:\n{path}")
if __name__ == "__main__":
root = tk.Tk()
app = App055(root)
root.mainloop()
例外処理とエラーハンドリング
try-exceptでValueErrorとExceptionを捕捉し、messagebox.showerror()でエラーメッセージを表示します。予期しないエラーも処理することで、アプリの堅牢性が向上します。
import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import os
import csv
import threading
from datetime import datetime
try:
import openpyxl
from openpyxl.styles import (Font, PatternFill, Alignment,
Border, Side)
from openpyxl.utils import get_column_letter
OPENPYXL_AVAILABLE = True
except ImportError:
OPENPYXL_AVAILABLE = False
class App055:
"""Excelレポート生成"""
SAMPLE_DATA = [
["商品名", "カテゴリ", "単価", "数量", "合計"],
["ノートPC", "電子機器", 98000, 5, None],
["マウス", "電子機器", 2500, 20, None],
["キーボード", "電子機器", 5800, 15, None],
["デスク", "家具", 45000, 3, None],
["チェア", "家具", 32000, 8, None],
["モニター", "電子機器", 38000, 10, None],
["USB-Cケーブル", "アクセサリ", 1200, 50, None],
["Webカメラ", "電子機器", 8500, 7, None],
]
def __init__(self, root):
self.root = root
self.root.title("Excelレポート生成")
self.root.geometry("900x600")
self.root.configure(bg="#1e1e1e")
self._rows = [list(r) for r in self.SAMPLE_DATA]
self._build_ui()
self._refresh_table()
def _build_ui(self):
header = tk.Frame(self.root, bg="#252526", pady=6)
header.pack(fill=tk.X)
tk.Label(header, text="📊 Excelレポート生成",
font=("Noto Sans JP", 12, "bold"),
bg="#252526", fg="#4fc3f7").pack(side=tk.LEFT, padx=12)
if not OPENPYXL_AVAILABLE:
tk.Label(self.root,
text="⚠ openpyxl が未インストールです (pip install openpyxl)",
bg="#fff3cd", fg="#856404", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X)
# ツールバー
tb = tk.Frame(self.root, bg="#2d2d2d", pady=4)
tb.pack(fill=tk.X)
ttk.Button(tb, text="📂 CSV読込", command=self._load_csv).pack(side=tk.LEFT, padx=4)
ttk.Button(tb, text="＋ 行追加", command=self._add_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="✕ 行削除", command=self._del_row).pack(side=tk.LEFT, padx=2)
ttk.Button(tb, text="📥 Excel出力", command=self._export).pack(side=tk.LEFT, padx=8)
tk.Label(tb, text="タイトル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.title_var = tk.StringVar(value="売上レポート")
ttk.Entry(tb, textvariable=self.title_var, width=20).pack(side=tk.LEFT)
# スタイル
tk.Label(tb, text="スタイル:", bg="#2d2d2d", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT, padx=(12, 4))
self.style_var = tk.StringVar(value="ブルー")
ttk.Combobox(tb, textvariable=self.style_var,
values=["ブルー", "グリーン", "オレンジ"],
state="readonly", width=10).pack(side=tk.LEFT)
# テーブル（Treeview）
table_f = tk.Frame(self.root, bg="#1e1e1e")
table_f.pack(fill=tk.BOTH, expand=True, padx=8, pady=4)
# 列数に応じて動的にTreeviewを構築
cols = [f"c{i}" for i in range(len(self._rows[0]))]
self.tree = ttk.Treeview(table_f, columns=cols, show="headings",
selectmode="browse")
sb_x = ttk.Scrollbar(table_f, orient=tk.HORIZONTAL,
command=self.tree.xview)
sb_y = ttk.Scrollbar(table_f, command=self.tree.yview)
self.tree.configure(xscrollcommand=sb_x.set, yscrollcommand=sb_y.set)
sb_y.pack(side=tk.RIGHT, fill=tk.Y)
self.tree.pack(fill=tk.BOTH, expand=True)
sb_x.pack(fill=tk.X)
self.tree.bind("<Double-1>", self._on_double_click)
# 行追加フォーム
add_f = tk.Frame(self.root, bg="#252526", pady=4)
add_f.pack(fill=tk.X, padx=8)
tk.Label(add_f, text="新規行:", bg="#252526", fg="#ccc",
font=("Arial", 9)).pack(side=tk.LEFT)
self.row_entries = []
for _ in range(len(self._rows[0])):
e = ttk.Entry(add_f, width=12)
e.pack(side=tk.LEFT, padx=2)
self.row_entries.append(e)
ttk.Button(add_f, text="追加",
command=self._add_row).pack(side=tk.LEFT, padx=4)
self.status_var = tk.StringVar(value="データを編集してExcelを出力できます")
tk.Label(self.root, textvariable=self.status_var,
bg="#252526", fg="#858585", font=("Arial", 9),
anchor="w", padx=8).pack(fill=tk.X, side=tk.BOTTOM)
def _refresh_table(self):
# ヘッダー設定
if not self._rows:
return
headers = self._rows[0]
n = len(headers)
cols = [f"c{i}" for i in range(n)]
# Treeviewを再構成
self.tree.configure(columns=cols)
for i, h in enumerate(headers):
self.tree.heading(f"c{i}", text=str(h))
self.tree.column(f"c{i}", width=120, anchor="center")
self.tree.delete(*self.tree.get_children())
for row in self._rows[1:]:
values = []
for j, v in enumerate(row):
if v is None and j == len(row) - 1:
# 合計列を計算
try:
values.append(int(row[2]) * int(row[3]))
except Exception:
values.append("")
else:
values.append(v if v is not None else "")
self.tree.insert("", tk.END, values=values)
self.status_var.set(f"{len(self._rows)-1} 行のデータ")
def _load_csv(self):
path = filedialog.askopenfilename(
filetypes=[("CSV", "*.csv"), ("すべて", "*.*")])
if not path:
return
try:
with open(path, encoding="utf-8-sig") as f:
reader = csv.reader(f)
rows = list(reader)
if rows:
self._rows = rows
self._refresh_table()
except Exception as e:
messagebox.showerror("エラー", str(e))
def _add_row(self):
vals = [e.get().strip() for e in self.row_entries]
if any(vals):
self._rows.append(vals)
self._refresh_table()
for e in self.row_entries:
e.delete(0, tk.END)
def _del_row(self):
sel = self.tree.selection()
if not sel:
return
idx = self.tree.index(sel[0]) + 1
if 1 <= idx < len(self._rows):
del self._rows[idx]
self._refresh_table()
def _on_double_click(self, event):
"""セルをダブルクリックして編集"""
region = self.tree.identify("region", event.x, event.y)
if region != "cell":
return
col = self.tree.identify_column(event.x)
row_iid = self.tree.identify_row(event.y)
if not row_iid:
return
col_idx = int(col.lstrip("#")) - 1
row_idx = self.tree.index(row_iid) + 1
current_val = self.tree.set(row_iid, f"c{col_idx}")
# ポップアップ編集
bbox = self.tree.bbox(row_iid, col)
if not bbox:
return
entry = tk.Entry(self.tree, font=("Arial", 10))
entry.place(x=bbox[0], y=bbox[1], width=bbox[2], height=bbox[3])
entry.insert(0, current_val)
entry.focus_set()
def commit(e=None):
new_val = entry.get()
self._rows[row_idx][col_idx] = new_val
self._refresh_table()
entry.destroy()
entry.bind("<Return>", commit)
entry.bind("<FocusOut>", commit)
def _export(self):
if not OPENPYXL_AVAILABLE:
messagebox.showerror("エラー",
"openpyxl をインストールしてください:\npip install openpyxl")
return
path = filedialog.asksaveasfilename(
defaultextension=".xlsx",
filetypes=[("Excel", "*.xlsx")])
if not path:
return
threading.Thread(target=self._do_export, args=(path,),
daemon=True).start()
def _do_export(self, path):
try:
wb = openpyxl.Workbook()
ws = wb.active
ws.title = self.title_var.get() or "レポート"
# スタイル定義
style = self.style_var.get()
if style == "グリーン":
hdr_fill_color = "1E7E34"
row_fill_color = "D4EDDA"
elif style == "オレンジ":
hdr_fill_color = "E65100"
row_fill_color = "FFF3E0"
else: # ブルー
hdr_fill_color = "1565C0"
row_fill_color = "E3F2FD"
hdr_fill = PatternFill("solid", fgColor=hdr_fill_color)
row_fill = PatternFill("solid", fgColor=row_fill_color)
thin = Side(style="thin", color="AAAAAA")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
center = Alignment(horizontal="center", vertical="center")
# タイトル行
title_cell = ws.cell(row=1, column=1,
value=self.title_var.get())
title_cell.font = Font(bold=True, size=14, color="222222")
title_cell.alignment = center
ws.merge_cells(start_row=1, start_column=1,
end_row=1, end_column=len(self._rows[0]))
# 生成日時
date_cell = ws.cell(row=2, column=1,
value=f"生成日時: {datetime.now().strftime('%Y-%m-%d %H:%M')}")
date_cell.font = Font(size=9, color="888888")
ws.row_dimensions[1].height = 30
ws.row_dimensions[2].height = 16
# ヘッダー
headers = self._rows[0]
for j, h in enumerate(headers, 1):
cell = ws.cell(row=3, column=j, value=h)
cell.fill = hdr_fill
cell.font = Font(bold=True, color="FFFFFF")
cell.alignment = center
cell.border = border
ws.row_dimensions[3].height = 22
# データ行
for i, row in enumerate(self._rows[1:], 4):
for j, val in enumerate(row, 1):
v = val
if v is None:
v = ""
# 数値変換
if isinstance(v, str):
try:
v = int(v)
except Exception:
try:
v = float(v)
except Exception:
pass
cell = ws.cell(row=i, column=j, value=v)
cell.border = border
cell.alignment = center
if i % 2 == 0:
cell.fill = row_fill
# 列幅調整
for col_cells in ws.columns:
max_len = 0
col_letter = get_column_letter(col_cells[0].column)
for cell in col_cells:
if cell.value:
max_len = max(max_len, len(str(cell.value)))
ws.column_dimensions[col_letter].width = min(max_len + 4, 30)
# 合計行
last_row = len(self._rows) + 3
try:
num_cols = len(self._rows[0])
for j in range(1, num_cols + 1):
cell = ws.cell(row=last_row, column=j)
col_letter = get_column_letter(j)
# 数値列に合計を入れる
try:
sample = float(self._rows[1][j-1])
cell.value = f"=SUM({col_letter}4:{col_letter}{last_row-1})"
cell.font = Font(bold=True)
except Exception:
if j == 1:
cell.value = "合計"
cell.font = Font(bold=True)
cell.fill = PatternFill("solid", fgColor="EEEEEE")
cell.border = border
cell.alignment = center
except Exception:
pass
wb.save(path)
self.root.after(0, self._export_done, path)
except Exception as e:
self.root.after(0, lambda: messagebox.showerror("エラー", str(e)))
def _export_done(self, path):
self.status_var.set(f"保存完了: {os.path.basename(path)}")
messagebox.showinfo("完了", f"Excelファイルを保存しました:\n{path}")
if __name__ == "__main__":
root = tk.Tk()
app = App055(root)
root.mainloop()
6. ステップバイステップガイド
このアプリをゼロから自分で作る手順を解説します。コードをコピーするだけでなく、実際に手順を追って自分で書いてみましょう。
-
1ファイルを作成する
新しいファイルを作成して app055.py と保存します。
-
2クラスの骨格を作る
App055クラスを定義し、__init__とmainloop()の最小構成を作ります。
-
3タイトルバーを作る
Frameを使ってカラーバー付きのタイトルエリアを作ります。
-
4入力フォームを実装する
LabelFrameとEntryウィジェットで入力エリアを作ります。
-
5処理ロジックを実装する
_execute()メソッドにメインロジックを実装します。
-
6結果表示を実装する
TextウィジェットかLabelに結果を表示する_show_result()を実装します。
-
7エラー処理を追加する
try-exceptとmessageboxでエラーハンドリングを追加します。
7. カスタマイズアイデア
基本機能を習得したら、以下のカスタマイズに挑戦してみましょう。
💡 ダークモードを追加する
bg色・fg色を辞書で管理し、ボタン1つでダークモード・ライトモードを切り替えられるようにしましょう。
💡 データの保存機能
処理結果をCSV・TXTファイルに保存する機能を追加しましょう。filedialog.asksaveasfilename()でファイル保存ダイアログが使えます。
💡 設定ダイアログ
フォントサイズや色などの設定をユーザーが変更できるオプションダイアログを追加しましょう。
8. よくある問題と解決法
❌ 日本語フォントが表示されない
原因：システムに日本語フォントが見つからない場合があります。
解決法：font引数を省略するかシステムに合ったフォントを指定してください。
❌ ライブラリのインポートエラー
原因：必要なライブラリがインストールされていません。
解決法：pip install コマンドで必要なライブラリをインストールしてください。 (pip install openpyxl)
❌ ウィンドウサイズが合わない
原因：画面解像度や表示スケールによって異なる場合があります。
解決法：root.geometry()で適切なサイズに調整してください。
9. 練習問題
アプリの理解を深めるための練習問題です。
-
課題1：機能拡張
Excelレポート生成に新しい機能を1つ追加してみましょう。
-
課題2：UIの改善
色・フォント・レイアウトを変更して、より使いやすいUIにカスタマイズしましょう。
-
課題3：保存機能の追加
処理結果をファイルに保存する機能を追加しましょう。