在数据管理领域，在 Excel 文件与数据库之间相互导出数据是一项极为实用的技能。将 Excel 文件数据导入至数据库，不仅增强了数据的扩展性和安全性，还使得大量数据集的处理变得更加高效，同时方便多用户在并发控制环境下的协同工作。反之，把数据库数据导出到 Excel 表格，能够方便用户利用熟悉的电子表格界面来进行数据分析、可视化处理及报告展示，使复杂的数据能够被更好地理解。而借助 Python，开发者能够简单地实现数据库与 Excel 电子表格之间的数据迁移，并进行自动化处理。
本文将以 SQLite 数据库为例，介绍如何运用 Spire.XLS for Python 库以及 Python 标准库，通过 Python 代码实现数据从数据库导出至 Excel 文件，以及从 Excel 文件导入到数据库的操作。
安装 Spire.XLS for Python
此教程需要 Spire.XLS for Python 和 plum-dispatch v1.7.4。您可以通过以下 pip 命令将它们轻松安装到 Windows 中。
pip install Spire.XLS如果您不确定如何安装，请参考： 如何在 Windows 中安装 Spire.XLS for Python
用 Python 将数据从数据库导出到 Excel 文件
Spire.XLS for Python 提供了一系列类、方法和属性，用于创建、读取和编辑 Excel 工作簿。开发人员可以使用 Python 标准库中的 sqlite3 模块从数据库中读取数据，并利用 Spire.XLS for Python 创建 Excel 文件并将数据写入其中，从而将数据库数据导出到 Excel 工作表中。
具体步骤如下：
- 连接数据库。
- 创建 Workbook 类实例并清除默认工作表。
- 遍历数据库中的表，获取所有列名和列中的数据。
- 使用 Workbook.Worksheets.Add() 方法为每个表创建一个工作表，通过 Worksheet.Range[].Value 属性将列名称写入工作表的标题行，然后将其他数据写入相应单元格。
- 设置工作表格式。
- 使用 Workbook.SaveToFile() 方法保存工作簿。
- Python
from spire.xls import *
from spire.xls.common import *
import sqlite3
# 连接到数据库
conn = sqlite3.connect("output/公司信息.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 = column[k]
# 设置工作表格式
sheet.AllocatedRange.Style.Font.FontName = "HarmonyOS Sans SC"
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 工作表导入到数据库
Spire.XLS for Python 还可以帮助开发人员从 Excel 文件中读取各种类型的数据，然后开发人员可以使用 sqlite3 将数据写入到数据库。以下是详细步骤：
- 创建一个 Workbook 类实例。
- 使用 Workbook.LoadFromFile() 方法加载 Excel 工作簿。
- 连接数据库。
- 遍历工作簿中的工作表。
- 使用 Workbook.Worksheets.get_Item() 方法获取工作表，并使用 Worksheet.Name 属性获取工作表名称。
- 通过 Workheet.Range[].Value 属性获取标题行中的数据。
- 在数据库中创建一个以工作表名称作为表名的表，并在表中创建以标题行数据作为列名的列。
- 通过 Workheet.Range[].Value 属性获取工作表单元格中的值，并将其写入数据库表中的相应位置。
- 提交更改并关闭连接。
- Python
from spire.xls import *
from spire.xls.common import *
import sqlite3
# 创建 Workbook 实例
workbook = Workbook()
# 加载 Excel 文件
workbook.LoadFromFile("示例.xlsx")
# 连接到数据库
conn = sqlite3.connect("output/Excel到数据库.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()
申请临时 License
如果您希望删除结果文档中的评估消息，或者摆脱功能限制，请该Email地址已收到反垃圾邮件插件保护。要显示它您需要在浏览器中启用JavaScript。获取有效期 30 天的临时许可证。
