pip install Spire.Xls.Free

from spire.xls import *
from spire.xls.common import *
import sqlite3

# ---------------------- 1. 連接SQLite數(shù)據(jù)庫 ----------------------
# 替換為你的數(shù)據(jù)庫文件路徑（相對(duì)路徑/絕對(duì)路徑均可）
conn = sqlite3.connect("Sales Data.db")
cursor = conn.cursor()

# ---------------------- 2. 獲取數(shù)據(jù)庫中所有表名 ----------------------
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
# 提取所有表名，生成列表
tableNames = [name[0] for name in cursor.fetchall()]

# ---------------------- 3. 創(chuàng)建空白Excel工作簿 ----------------------
workbook = Workbook()
# 清空默認(rèn)工作表，避免多余表格
workbook.Worksheets.Clear()

# ---------------------- 4. 遍歷所有表，寫入Excel ----------------------
for tableName in tableNames:
    # 4.1 獲取當(dāng)前表的列名（Excel表頭）
    cursor.execute(f"PRAGMA table_info('{tableName}')")
    columnsInfo = cursor.fetchall()
    columnNames = [columnInfo[1] for columnInfo in columnsInfo]

    # 4.2 獲取當(dāng)前表的所有數(shù)據(jù)
    cursor.execute(f"SELECT * FROM {tableName}")
    rows = cursor.fetchall()
    
    # 4.3 新建工作表，名稱=數(shù)據(jù)庫表名
    sheet = workbook.Worksheets.Add(tableName)
    
    # 4.4 寫入表頭（第一行）
    for i in range(len(columnNames)):
        sheet.Range[1, i + 1].Value = columnNames[i]
    
    # 4.5 寫入表數(shù)據(jù)（修復(fù)原代碼bug：從第0行開始遍歷，避免丟失第一條數(shù)據(jù)）
    for j in range(len(rows)):
        row_data = rows[j]
        for k in range(len(row_data)):
            sheet.Range[j + 2, k + 1].Value = row_data[k]
    
    # 4.6 表格格式化：自適應(yīng)行列寬度 （可選）
    sheet.AllocatedRange.AutoFitRows()    # 自適應(yīng)行高
    sheet.AllocatedRange.AutoFitColumns() # 自適應(yīng)列寬

# ---------------------- 5. 保存文件并釋放資源 ----------------------
# 保存Excel到指定路徑
workbook.SaveToFile("DataBaseToExcel.xlsx", FileFormat.Version2016)
# 釋放Excel對(duì)象資源
workbook.Dispose()
# 關(guān)閉數(shù)據(jù)庫連接
conn.close()

print("數(shù)據(jù)導(dǎo)出完成！")