python 在 excel 文件和数据库之间导入与导出数据-ag凯发旗舰厅

pip install spire.xls

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()

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()