Python实现数据库与Excel文件之间的数据导入与导出

数据库和Excel文件是两种常见且重要的数据存储方式。数据库通常用于大规模数据的高效存储、管理和查询，而Excel则以其直观的界面和简单的操作方式广泛应用于数据分析、报告生成和可视化等领域。在实际工作中，可能需要在这两者之间进行数据的导入与导出。例如，从数据库中提取数据到Excel进行深入分析和图表绘制，或者将Excel文件中的数据清洗整理后导入数据库进行集中管理和进一步处理。而Python因其便捷性和丰富的生态，能够帮助用户通过简单的代码处理数据库与Excel文件之间数据转换。
本文将介绍如何使用Python有效地实现数据库与Excel文件之间的数据自动化导入与导出，以SQLite数据库为例。

本文所使用的方法需要用到sqlite3（Python标准库中的组件）和Spire.XLS for Python（PyPI: pip install Spire.XLS）。

用Python将数据库数据导出到Excel表格

我们可以使用 sqlite3 模块从数据库读取数据，并利用 Spire.XLS 模块创建 Excel 文件并将数据写入其中，从而实现数据库数据的导出。详细步骤如下：

1. 导入必要的模块。
2. 连接数据库：使用 sqlite3.connect() 连接 SQLite 数据库，并创建游标 cursor 执行 SQL 命令。
3. 获取表名：执行 SQL 查询获取数据库中所有表的名称，并存储在 tableNames 列表中。
4. 创建 Excel 工作簿：初始化一个 Workbook 对象，并使用 Workbook.Worksheets.Clear() 方法清除默认工作表。
5. 遍历数据库表：对于 tableNames 中的每一个表名：
   • 查询表的列信息，提取列名并添加到 columnNames 列表中。
   • 获取表中的所有数据行到 rows 中。
   • 使用 Workbook.Worksheets.Add(sheetname) 方法在 Excel 中添加一个以表名命名的新工作表。
   • 使用 Worksheet.Range[row, col].Value 属性将 columnNames 作为标题写入工作表。
   • 遍历数据行，并使用相同的属性将数据写入对应的单元格。
   • 格式化工作表。
6. 使用 Workbook.SaveToFile() 方法将工作簿保存到文件。
7. 释放 workbook 的资源并关闭数据库连接。

代码示例

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

# 连接到数据库
conn = sqlite3.connect("output/CompanyInfo.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