Python xlwings和sqlite3 模块的使用

在日常开发中经常使用数据库存储数据，然后生成数据报表，用于统计、打印和分析。本文针对这个应用场景举例说明，因此生成报表的处理流程，首先通过数据库查询数据，组织适合xlwings传入的数据，然后调用xlwings的相关接口，生成报表。

xlwings 安装

Python的安装包中不包含xlwings模块，需要安装，安装方法如下：

pip install xlwings

使用sqlite3模块获取数据

查询数据库的流程：

1. 通过数据库的connect获取游标cursor。
2. 组织数据库的查询语句，比如查询的列数据：学号、数学、英语、语文和时间，查询的表scores。
3. 执行查询的sql语句，通过fetchall获取查询的数据。
4. 返回查询结果。

#查询数据库数据
def query_reporter_data(self):
    self.db_error = DB_SUCCESS
    result = None
    try:
        #获取cursor
        cursor = self.db_connect.cursor()
        #组织查询语句
        exe_sql = "SELECT student_no, math, english, yuwen, time from scores"
        #执行数据库查询
        cursor.execute(exe_sql)
        #获取数据
        result = cursor.fetchall()
        #关闭cursor
        cursor.close()
    except Exception as e:
        #打印异常信息
        info = traceback.format_exc()
        print(info)
        self.db_error = DB_OPERATION_FAILED

    #返回数据
    return self.db_error, result

组织写入数据

1. 首先生成表头，写入list，即生成excel报表的第一行，说明每一列代表的数据属性。
2. 创建存放组织结果的list，然后每解析查询的一条数据库记录，生成一行数据，依次append到list里面。
3. 生成pandas 的DataFrame数据，并返回。

import pandas as pd

def get_data(self, db):
    items = ["编号", "数学", "英语", "语文", "录入时间"]
    error, datas = self.query_reporter_data()
    result = []
    if error == 0:
        #每次循环解析一条查询到的数据记录
        for data in datas:
            try:
                scores_result = []
                #取前四列数据
                scores_result.extend(data[:4])
                #格式化时间数据
                check_time = data[4]
                dtime = time.localtime(check_time)
                scores_result.append(time.strftime("%Y-%m-%d %H:%M:%S", dtime))
                #写入一行数据
                result.append(scores_result)
            except Exception as e:
                info = traceback.format_exc()
                print(info)
                continue

    df = pd.DataFrame(result, columns=items)
    return df, error

生成excel报表

1. 打开一个xlwings的App，并创建一个Book。一般生成报表基本集中在一个Book里面，创建几个Sheet页，本例中写入Book的第一个Sheet页。
2. 将生成DataFrame数据，赋值给Sheet页左上角位置，比如本例Sheet的A1位置。
3. 保存文件，关闭Book并且退出App。

import xlwings as xw

def generation_reporter(self, db, file_name):
    is_save_success = False
    #获取DataFrame数据
    df, error = self.get_data(db)
    if error == 0:
        try:
            #初始化App对象
            excel_app = xw.App(visible=False, add_book=False)
            #创建一个Book
            wb = excel_app.books.add()
            #打开第一个Sheet页
            sheet = wb.sheets[0]
            #写入数据
            sheet.range('A1').value = df
            #调整各列自动调整宽度
            sheet.range('B1:B3').columns.autofit()
            sheet.range('C1:C3').columns.autofit()
            sheet.range('D1:D3').columns.autofit()
            sheet.range('E1:E3').columns.autofit()
            sheet.range('F1:F3').columns.autofit()
            #保存文件
            wb.save(file_name)
            is_save_success = True
            #关闭book
            wb.close()
            #App退出
            excel_app.quit()
        except Exception as e:
            info = traceback.format_exc()
            print(info)
            is_save_success = False

    return is_save_success