Pythonを使用したデータベースとExcelファイル間のデータのエクスポートとインポート

Excelファイルからデータをデータベースにエクスポートすることで、拡張性と高度なセキュリティを提供し、大規模なデータセットの効率的な処理を可能にし、同時実行制御による共同作業を促進します。逆に、データベースからExcelへのデータのインポートは、データ分析、視覚化、プレゼンテーションにおいてなじみ深いスプレッドシートインターフェースを提供し、複雑な情報をユーザーにアクセスしやすくします。強力なPython言語を用いることで、開発者はデータベースとスプレッドシート間のデータ転送を簡単に自動化することができます。

この記事では、SQLiteデータベースを例にして、**データベースからExcelファイルへのデータエクスポート**および**Excelファイルからデータベースへのデータインポート**をPythonコードで実現する方法を示します。

- **[Pythonを使用したデータベースからExcelファイルへのデータエクスポート](#pythonを使用したデータベースからexcelファイルへのデータエクスポート)**
- **[Pythonを使用したExcelワークシートからデータベースへのデータエクスポート](#pythonを使用したexcelワークシートからデータベースへのデータエクスポート)**

この記事で使用する方法には、Python標準ライブラリのsqlite3および[Spire.XLS for Python](https://www.e-iceblue.com/Introduce/xls-for-python.html)（PyPI: pip `install Spire.XLS`）が必要です。

Spire.XLSの[無料ライセンスを申し込む](https://www.e-iceblue.com/TemLicense.html)

## Pythonを使用したデータベースからExcelファイルへのデータエクスポート
sqlite3モジュールを使用してデータベースからデータを読み取り、Spire.XLSモジュールを利用してExcelファイルを作成し、データを書き込むことで、データベースデータをExcelワークシートにエクスポートすることができます。
詳細な手順は以下の通りです：

1. 必要なモジュールをインポートします。
2. データベースに接続します：`sqlite3.connect()`を使用してSQLiteデータベースに接続し、SQLコマンドを実行するカーソル`cursor`を作成します。
3. テーブル名を取得します：データベース内のすべてのテーブルの名前を取得するSQLクエリを実行し、`tableNames`リストに保存します。
4. Excelワークブックを作成します：`Workbook`オブジェクトを初期化し、`Workbook.Worksheets.Clear()`メソッドを使用してデフォルトのワークシートをクリアします。
5. データベースのテーブルごとに繰り返します：`tableNames`内の各テーブル名に対して以下を実行します：
   * テーブルの列情報をクエリし、列名を抽出して`columnNames`リストに追加します。
   * テーブルのすべてのデータ行を`rows`に取得します。
   * `Workbook.Worksheets.Add(sheetname)`メソッドを使用してExcelに新しいワークシートを追加し、テーブル名と同じ名前にします。
   * `Worksheet.Range[row, col].Value`プロパティを使用して、`columnNames`をヘッダーとしてワークシートに書き込みます。
   * データ行をイテレートし、同じプロパティを使用してそれらをワークシートの対応するセルに書き込みます。
   * ワークシートをフォーマットします。
12. `Workbook.SaveToFile()`メソッドを使用してワークブックをファイルに保存します。
13. リソースを解放し、データベース接続を閉じます。

**コードの例**
```python
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ファイルへのデータエクスポート](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/278784/25507b9d-8969-dcc4-c96e-05bb6bed8f7d.png)

## Pythonを使用したExcelワークシートからデータベースへのデータエクスポート
Spire.XLSを使用してExcelファイルからさまざまなタイプのデータを読み取り、それをsqlite3を使用してデータベースに書き込むこともできます。
以下は詳細な手順です：

1. 必要なモジュールをインポートします。
2. `Workbook`インスタンスを作成します：Excelワークブックを操作するために`Workbook`オブジェクトを初期化します。
3. Excelファイルを読み込みます：`LoadFromFile`メソッドを使用して指定されたパスからExcelファイルを読み込みます。
4. データベースに接続します：`sqlite3.connect()`を使用してSQLiteデータベースに接続し、SQLコマンドを実行するカーソル`cursor`を作成します。
5. ワークブック内の各ワークシートについて繰り返します：ワークブック内の各ワークシートに対して以下を実行します：
   * ワークシートオブジェクトと名前を取得します：Worksheet.Nameプロパティを使用してワークシートオブジェクトと名前を取得し、名前からスペースを除去します。
   * ヘッダーを抽出します：`Worksheet.Range[row, col].Value`プロパティを使用して最初のデータ行を収集し、データベーステーブルの列名として使用するヘッダーを取得します（スペースを除去）。
   * データベーステーブルを作成します：抽出したヘッダーに基づいて動的にSQLステートメントを生成し、テーブルを作成します（存在しない場合）。
   * データを挿入します：ワークシートの各行をイテレートし、`Worksheet.Range[row, col].Value`プロパティを使用してデータを収集し、対応するデータベーステーブルにデータを挿入するSQL挿入ステートメントを構築します。
6. データベースへのすべての変更をコミットし、データベース接続を閉じます。
7. `Workbook`オブジェクトが使用するリソースを解放します。

**コードの例**
```python
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ワークシートからデータベースへのデータエクスポート](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/278784/edd23217-787c-e092-45cb-b8a6a390eba0.png)

この記事ではPythonコードを使用してExcelワークブックとデータベース間でデータを転送する方法を示しています。

さらにExcelファイル処理のスキルを学ぶには、[Spire.XLS for Pythonのチュートリアル](https://www.e-iceblue.com/Tutorials/Python/Spire.XLS-for-Python/Program-Guide/Spire.XLS-for-Python-Program-Guide-Content.html)を参照してください。