excel导入到数据库&数据库导出到excel

• 通过pandas读取excel文件，并将读取的内容写入sqlite3数据库

import pandas,sqlite3,os
from sqlalchemy import create_engine

def exlToSql(excel_file,db_file):
    result = pandas.read_excel(excel_file)  #读取excel内容
    conn = sqlite3.connect(db_file)  #创建数据库
    result.to_sql(db_file,con=conn,if_exists='replace',index=False)  #将excel内容写入到数据库

excel_file = r'D:\abc.xlsx'
db_file = os.path.join(os.path.dirname(__file__),'test.db')

exlToSql(excel_file,db_file)

可以通过SQLiteStudio来连接数据库验证数据是否导入成功

• 读取数据库表的内容，将内容写入excel

import sqlite3,os
import pandas as pd

def get_data(db_file):
    conn = sqlite3.connect(db_file)   
    cursor = conn.cursor()  
    cursor.execute('SELECT name FROM sqlite_master where type = "table" ')  
    table_name = cursor.fetchall()  
    cursor.execute(f'SELECT * from {table_name[0][0]}')  
    global result
    result = cursor.fetchall()
    conn.close()

def export_excel(excel_file):
    rs = pd.DataFrame(result,columns=("c1","c1","cn"))  #用获取的数据库数据创建DataFrame并设置字段名
    rs.to_excel(excel_file,sheet_name='abc',index=False,header=True)  #导出数据到excel文件

db_file = r'D:\test1.db'
excel_file = r'D:\one.xlsx'

if __name__ == '__main__':
    get_data(db_file)
    export_excel(excel_file)

除了可以通过pandas导出数据到excel外，我们也可以用openpyxl来完成这个步骤

from openpyxl import Workbook
def write_excel(excel_file):
    wb = Workbook()
    ws = wb.active
    ws.title = 'abc'
    for i in result:
        ws.append(i)
    ws.save(excel_file)
excel_file = os.path.join(os.path.dirname(__file__),'two.xlsx')
write_excel(excel_file)