Excel ファイルとデータベース間でデータをエクスポートおよびインポートする機能は、データ管理において非常に重要です。Excel からデータベースへデータをエクスポートすることで、スケーラビリティやセキュリティが向上し、大規模データの効率的な処理や、同時アクセスを伴う共同作業が可能になります。一方、データベースから Excel にデータをインポートすることで、使い慣れたスプレッドシート環境でのデータ分析や可視化、プレゼンテーションが可能となり、複雑な情報もユーザーにとって扱いやすくなります。 本記事では、Python 言語の強力な機能を活用して、データベースと Excel ファイル間のデータ転送を自動化する方法を紹介します。具体的には、Spire.XLS for Python を使用し、SQLite データベースを例にして、データベースから Excel ファイルへのエクスポート、および Excel ファイルからデータベースへのインポートの方法を解説します。 Python で DB から Excel へデータ出力 Python で Excel から DB へデータ入力 Spire.XLS for Python のインストール この操作には、Spire.XLS for Python と plum-dispatch v1.7.4 が必要です。これらは、Spire.XLS for Python の公式ウェブサイトから手動でダウンロードするか、以下の pip コマンドでインストールできます。 pip install Spire.XLS Python で DB から Excel へデータ出力 Spire.XLS for Python は、Excel ブックの作成、読み込み、編集を行うための豊富なクラスやメソッド、プロパティを提供しています。Python 標準ライブラリの sqlite3 モジュールを使用してデータベースからデータを取得し、Spire.XLS で Excel ファイルを作成してデータを書き込むことで、データベースの内容を簡単にExcelにエクスポートできます。 具体的な手順は以下の通りです： データベースに接続します。 Workbook クラスのインスタンスを作成し、初期状態のワークシートを削除します。 データベース内の各テーブルをループ処理し、列名とデータを取得します。 各テーブルに対応するワークシートを Workbook.Worksheets.Add() メソッドで作成し、ヘッダー行に列名を、その他の行にデータを Worksheet.Range[].Value プロパティを使って書き込みます。 ワークシートの体裁を整えます。 Workbook.SaveToFile() メソッドでファイルを保存します。 Python from spire.xls import Workbook, FileFormat import sqlite3 # データベースに接続 conn = sqlite3.connect("Data.db") cursor = conn.cursor() # データベース内のすべてのテーブル名を取得 cursor.execute("SELECT name FROM sqlite_master WHERE type='table';") tableNames = [name[0] for name in cursor.fetchall()] # Excel ファイルを作成 workbook = Workbook() workbook.Worksheets.Clear() # データベース内の各テーブルを処理 for tableName in tableNames: # テーブルの列名を取得 cursor.execute(f"PRAGMA table_info('{tableName}')") columnsInfo = cursor.fetchall() columnNames = [columnInfo[1] for columnInfo in columnsInfo] # テーブルのデータを取得 cursor.execute(f"SELECT * FROM {tableName}") rows = cursor.fetchall() # ワークシートを作成 sheet = workbook.Worksheets.Add(tableName) # ヘッダーをワークシートに書き込む for i in range(len(columnNames)): sheet.Range[1, i + 1].Value = columnNames[i] # データをワークシートに書き込む for j in range(1, len(rows)): column = rows[j] for k in range(len(column)): sheet.Range[j + 1, k + 1].Value = str(column[k]) # ワークシートをフォーマット sheet.AllocatedRange.Style.Font.FontName = "Yu Gothic UI" sheet.AllocatedRange.Style.Font.Size = 12.0 sheet.AllocatedRange.AutoFitRows() sheet.AllocatedRange.AutoFitColumns() # Excel ファイルを保存 workbook.SaveToFile("output/データベースをExcelへ出力.xlsx", FileFormat.Version2016) workbook.Dispose() conn.close() Python で Excel から DB へデータ入力 Spire.XLS for Python は、Excel ファイルからさまざまな形式のデータを読み取る機能も備えており、読み取ったデータを sqlite3 を使ってデータベースに書き込むことが可能です。以下はその詳細な手順です： Workbook クラスのインスタンスを作成します。 Workbook.LoadFromFile() メソッドで Excel ファイルを読み込みます。 データベースに接続します。 Excel ブック内の各ワークシートをループ処理します。 Workbook.Worksheets.get_Item() メソッドでワークシートを取得し、Worksheet.Name プロパティでシート名を取得します。 ヘッダー行のデータを Worksheet.Range[].Value プロパティで取得します。 ワークシート名をテーブル名とし、ヘッダー行のデータを列名として、データベースにテーブルを作成します。 ワークシートのセルからデータを取得し、対応するテーブルに書き込みます。 変更をコミットして接続を閉じます。 Python from spire.xls import Workbook import sqlite3 # Workbook のインスタンスを作成 workbook = Workbook() # Excel ファイルを読み込む workbook.LoadFromFile("Sample.xlsx") # データベースに接続 conn = sqlite3.connect("output/Report.db") cursor = conn.cursor() for s in range(workbook.Worksheets.Count): # ワークシートを取得 sheet = workbook.Worksheets.get_Item(s) # シート名を取得 sheetName = sheet.Name sheetName = sheetName.replace(" ", "") # ヘッダー行のデータを取得 header = [] for i in range(sheet.AllocatedRange.ColumnCount): headerValue = sheet.Range[1, i + 1].Value headerValue = headerValue.replace(" ", "") header.append(headerValue) # データベースのテーブルを作成 createTableSql = f"CREATE TABLE IF NOT EXISTS {sheetName} ({', '.join([f'{header[i]} TEXT' for i in range(len(header))])})" cursor.execute(createTableSql) # データをデータベースのテーブルに挿入 for row in range(1, sheet.AllocatedRange.RowCount): data = [] for col in range(sheet.AllocatedRange.ColumnCount): # セルの値を取得 value = sheet.Range[row + 1, col + 1].Value data.append(value) # セルの値をテーブルに挿入 insertSql = f"INSERT INTO {sheetName} ({', '.join(header)}) VALUES ({', '.join(['?' for _ in data])})" cursor.execute(insertSql, data) # 変更をコミットして接続を閉じる conn.commit() conn.close() workbook.Dispose() 一時ライセンスを申請する 結果ドキュメントから評価メッセージを削除したい場合、または機能制限を取り除く場合は、についてこのメールアドレスはスパムボットから保護されています。閲覧するにはJavaScriptを有効にする必要があります。 にお問い合わせ、30 日間有効な一時ライセンスを取得してください。
