使用IRONXL FOR PYTHON

如何使用Python將Excel檔案匯入資料庫表格

發佈 2024年6月6日

在當今以數據為導向的世界中，高效處理和管理數據對於任何組織或個人來說都是至關重要的任務。 Python，擁有豐富的庫生態系統，提供強大的數據操作和管理工具，例如導入 pandas 庫。一個常見的情境是需要從中提取或匯入數據Excel試算表並在數據庫中存儲或插入數據，以便進一步分析或與其他系統集成。在本教程中，我們將探討如何創建一個自動化該過程的 Python 腳本，允許您無縫地從 Excel 表單檔案中讀取數據並將其插入數據庫。在本教程結束時，您將準備好有效地處理資料遷移任務。讓我們開始!

如何使用Python將Excel檔案匯入資料庫表格

首先安裝IronXL函式庫。
使用 IronXL 將您的 Excel 檔案載入記憶體。
載入您希望處理的特定試算表。
選擇您打算匯入的精確數據範圍。
使用 Python 與任何資料庫建立連接，例如 SQLite 或 MySQL 連接資料庫。
在您的 SQLite 資料庫中建立一個新表來容納匯入的數據。
將選定的行從 Excel 文件插入到新創建的 SQLite 資料表中。
從已創建的SQLite表中檢索並選擇數據，以進行進一步的分析或處理。
在本教程中，我們將使用IronXL, 一個以高效處理 Excel 文件而聞名的 Python 函式庫。通過將IronXL整合到我們的腳本中，我們確保從Excel試算表中無縫提取數據，並順利插入數據庫以進行進一步的分析和處理。

什麼是IronXL？

IronXL 是 Iron Software 開發的一個 Python 庫，提供強大的功能來讀取, 生成以及直接在 Python 應用程式中編輯 Excel 文件。值得注意的是，IronXL 的突出之处在於其不依賴於安裝 Microsoft Excel，從而簡化了在不同環境中的部署。使用IronXL，開發人員可以受益於：

跨平台支援： 在 Windows、macOS、Linux、Docker、Azure 和 AWS 平台上享受無縫運行，確保適應各種開發環境。

資料匯入和匯出： 輕鬆處理從 XLS、XLSX、CSV 和 TSV 文件匯入資料，並靈活地將工作表匯出到這些格式，甚至匯出為 JSON，以增強互操作性。

加密功能： 利用IronXL的加密功能，確保數據安全，允許使用密碼保護XLSX、XLSM和XLTX檔案。

公式與重新計算： 輕鬆運用 Excel公式此外，每次编辑工作表时都会自动重新计算，确保数据操作的准确性和可靠性。

儲存格樣式：通過調整字體來自訂個別儲存格的外觀。樣式大小、背景圖案、邊框和對齊方式，增強 Excel 文檔的視覺呈現效果。

廣泛的文件格式： IronXL 支援包括 XLS、XLSX、XLST、XLSM、CSV 和 TSV 在內的各種格式，讓開發者能夠輕鬆高效地處理多種情境中的資料。

現在，讓我們開始安裝 IronXL。

步驟 1：安裝 IronXL 庫

第一步是安裝 IronXL 庫。在命令提示字元中執行以下指令以安裝IronXL。

pip install IronXL

pip install IronXL

PYTHON

步驟 2：加載 Excel 活頁簿

下一步是載入 Excel 檔案。這份教學將使用下面的 Excel 檔案。

如何使用 Python 將 Excel 文件匯入資料庫表：圖 1 - 範例 Excel 文件輸入

以下程式碼將在記憶體中載入現有的 Excel 文件。

from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")

from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")

PYTHON

以上的 Python 代碼片段演示了使用 IronXL 庫載入名為「sample_excel.xlsx」的 Excel 工作簿。首先，從 IronXL 匯入必要的 Python 模組。然後，指定授權密鑰以驗證庫的使用。您可以從获取您的免費授權金鑰IronXL 網站. 最後，使用 Load 方法將指定的 Excel 工作簿打開並加載到記憶體中。這使得可以通過程式設計語言對其內容進行後續的操作，例如讀取數據、修改儲存格值或應用格式化。

步驟 3：選擇工作表

要使用IronXL選擇Excel工作簿中的工作表，您可以指定工作表索引或名稱。

worksheet = workbook.WorkSheets[0]

worksheet = workbook.WorkSheets[0]

PYTHON

此行選擇已載入 Excel 工作簿中的第一個工作表，並將其賦值給變數「worksheet」，以便能夠在該工作簿中的特定工作表上執行後續操作。這將從 Excel 工作表讀取 Excel 數據到工作表變數。

步驟 4：開啟資料庫連線

在本教程中，我們使用 SQLite 資料庫而不是 MySQL 資料庫伺服器。要開始資料庫操作，我們首先需要建立與資料庫的連接。

import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')

PYTHON

上述行建立了與名為 'data.db' 的 SQLite 資料庫的連接。如果指定的資料庫不存在，將會自動建立。此連線允許隨後與 SQLite 資料庫的互動，例如執行查詢和進行資料操作。

步驟5：建立表格

下一步是在資料庫中建立一個資料表，此資料表將用於從 Excel 檔案匯入數據。要在 SQLite 資料庫中建立資料表，您可以使用連線對象執行 SQL 語句。

cursor = conn.cursor() // database objects
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
            FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
            Country TEXT,
            Age Number
                )''')

cursor = conn.cursor() // database objects
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
            FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
            Country TEXT,
            Age Number
                )''')

PYTHON

上面的程式碼片段初始化了一個游標物件，以在 SQLite 資料庫連線中執行 SQL 命令。它創建了一個名為「customer」的表，包含「id」、「FirstName」、「LastName」、「Gender」、「Country」和「Age」欄位。如果表尚不存在，則會創建該表，並遵循指定的列數據類型。

步驟6：使用Python將資料匯入資料庫

現在，我們將把資料插入新創建的表格中。我們將匯入一個 Excel 文件，並將其數據插入到 SQLite 資料庫中。

for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()

for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()

PYTHON

上述代碼迭代 Excel 工作表中的第 2 到第 10 行，提取每行中的 A 到 F 列的值。這些值被存儲在一個元組中，代表要插入到「customer」表中的數據。接著，游標執行 SQL INSERT 命令，將值組合併到表中。此過程針對每一行重複執行，有效地將數據從 Excel 文件導入到 SQLite 資料庫中。最後，conn.commit()提交交易，確保更改已保存並持久化在資料庫中。

步驟 7：從資料庫讀取數據

要驗證數據是否正確插入，您可以使用 SELECT 查詢從 SQLite 資料庫中的 'customer' 表讀取數據。例如：

cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

PYTHON

上述程式碼在 SQLite 資料庫中的 'customer' 表格執行一個 SELECT 查詢，檢索所有行。使用 fetchall 抓取的行儲存在 rows 變數中。()方法。接著，每一行將被迭代列印，顯示插入到 'customer' 表中的數據。最後，使用 close 關閉資料庫連接。()釋放資源的方法。

如何使用 Python 將 Excel 文件匯入資料庫表格：圖 2 - 從資料庫輸出讀取

完整的代碼如下：

import sqlite3
from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")
# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
                    FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
                    Country TEXT,
                    Age Number
                )''')
cursor.execute("DELETE FROM customer")
for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()  
cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

import sqlite3
from ironxl import *     # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-DEPLOYMENT.TRIAL-5X63V4.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("sample_excel.xlsx")
# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS customer ( id Number,
                    FirstName TEXT,
                    LastName TEXT,
                    Gender TEXT,
                    Country TEXT,
                    Age Number
                )''')
cursor.execute("DELETE FROM customer")
for i in range(2, 11):
    values_tuple = (
        worksheet[f"A{i}"].StringValue,
        worksheet[f"B{i}"].StringValue,
        worksheet[f"C{i}"].StringValue,
        worksheet[f"D{i}"].StringValue,
        worksheet[f"E{i}"].StringValue,
        worksheet[f"F{i}"].StringValue
    )
    cursor.execute("INSERT INTO customer VALUES (?, ?, ?, ?, ?, ?)", values_tuple)
conn.commit()  
cursor.execute("SELECT * FROM customer")
# Fetch all rows from the result set
rows = cursor.fetchall()
# Print the rows
for row in rows:
    print(row)
conn.close()

PYTHON