Excelファイルからデータをデータベースにエクスポートすることで、拡張性と高度なセキュリティを提供し、大規模なデータセットの効率的な処理を可能にし、同時実行制御による共同作業を促進します。逆に、データベースからExcelへのデータのインポートは、データ分析、視覚化、プレゼンテーションにおいてなじみ深いスプレッドシートインターフェースを提供し、複雑な情報をユーザーにアクセスしやすくします。強力なPython言語を用いることで、開発者はデータベースとスプレッドシート間のデータ転送を簡単に自動化することができます。
この記事では、SQLiteデータベースを例にして、データベースからExcelファイルへのデータエクスポートおよびExcelファイルからデータベースへのデータインポートをPythonコードで実現する方法を示します。
この記事で使用する方法には、Python標準ライブラリのsqlite3およびSpire.XLS for Python(PyPI: pip install Spire.XLS
)が必要です。
Spire.XLSの無料ライセンスを申し込む
Pythonを使用したデータベースからExcelファイルへのデータエクスポート
sqlite3モジュールを使用してデータベースからデータを読み取り、Spire.XLSモジュールを利用してExcelファイルを作成し、データを書き込むことで、データベースデータをExcelワークシートにエクスポートすることができます。
詳細な手順は以下の通りです:
- 必要なモジュールをインポートします。
- データベースに接続します:
sqlite3.connect()
を使用してSQLiteデータベースに接続し、SQLコマンドを実行するカーソルcursor
を作成します。 - テーブル名を取得します:データベース内のすべてのテーブルの名前を取得するSQLクエリを実行し、
tableNames
リストに保存します。 - Excelワークブックを作成します:
Workbook
オブジェクトを初期化し、Workbook.Worksheets.Clear()
メソッドを使用してデフォルトのワークシートをクリアします。 - データベースのテーブルごとに繰り返します:
tableNames
内の各テーブル名に対して以下を実行します:- テーブルの列情報をクエリし、列名を抽出して
columnNames
リストに追加します。 - テーブルのすべてのデータ行を
rows
に取得します。 -
Workbook.Worksheets.Add(sheetname)
メソッドを使用してExcelに新しいワークシートを追加し、テーブル名と同じ名前にします。 -
Worksheet.Range[row, col].Value
プロパティを使用して、columnNames
をヘッダーとしてワークシートに書き込みます。 - データ行をイテレートし、同じプロパティを使用してそれらをワークシートの対応するセルに書き込みます。
- ワークシートをフォーマットします。
- テーブルの列情報をクエリし、列名を抽出して
-
Workbook.SaveToFile()
メソッドを使用してワークブックをファイルに保存します。 - リソースを解放し、データベース接続を閉じます。
コードの例
from spire.xls import *
from spire.xls.common import *
import sqlite3
# データベースへの接続
conn = sqlite3.connect("CompanySales.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(len(rows)):
row = rows[j]
for k in range(len(row)):
sheet.Range[j + 2, k + 1].Value = row[k]
# ワークシートの書式設定
sheet.AllocatedRange.Style.Font.FontName = "Times New Roman"
sheet.AllocatedRange.Style.Font.Size = 12.0
sheet.AllocatedRange.AutoFitRows()
sheet.AllocatedRange.AutoFitColumns()
# Excelファイルを保存
workbook.SaveToFile("output/DataBaseToExcel.xlsx", FileFormat.Version2016)
workbook.Dispose()
conn.close()
Pythonを使用したExcelワークシートからデータベースへのデータエクスポート
Spire.XLSを使用してExcelファイルからさまざまなタイプのデータを読み取り、それをsqlite3を使用してデータベースに書き込むこともできます。
以下は詳細な手順です:
- 必要なモジュールをインポートします。
-
Workbook
インスタンスを作成します:Excelワークブックを操作するためにWorkbook
オブジェクトを初期化します。 - Excelファイルを読み込みます:
LoadFromFile
メソッドを使用して指定されたパスからExcelファイルを読み込みます。 - データベースに接続します:
sqlite3.connect()
を使用してSQLiteデータベースに接続し、SQLコマンドを実行するカーソルcursor
を作成します。 - ワークブック内の各ワークシートについて繰り返します:ワークブック内の各ワークシートに対して以下を実行します:
- ワークシートオブジェクトと名前を取得します:Worksheet.Nameプロパティを使用してワークシートオブジェクトと名前を取得し、名前からスペースを除去します。
- ヘッダーを抽出します:
Worksheet.Range[row, col].Value
プロパティを使用して最初のデータ行を収集し、データベーステーブルの列名として使用するヘッダーを取得します(スペースを除去)。 - データベーステーブルを作成します:抽出したヘッダーに基づいて動的にSQLステートメントを生成し、テーブルを作成します(存在しない場合)。
- データを挿入します:ワークシートの各行をイテレートし、
Worksheet.Range[row, col].Value
プロパティを使用してデータを収集し、対応するデータベーステーブルにデータを挿入するSQL挿入ステートメントを構築します。
- データベースへのすべての変更をコミットし、データベース接続を閉じます。
-
Workbook
オブジェクトが使用するリソースを解放します。
コードの例
from spire.xls import *
from spire.xls.common import *
import sqlite3
# Workbookのインスタンスを作成
workbook = Workbook()
# Excelファイルをロード
workbook.LoadFromFile("Sample.xlsx")
# データベースへの接続
conn = sqlite3.connect("output/ExcelToDatabase.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()
この記事ではPythonコードを使用してExcelワークブックとデータベース間でデータを転送する方法を示しています。
さらにExcelファイル処理のスキルを学ぶには、Spire.XLS for Pythonのチュートリアルを参照してください。