Python 实现自动化Excel报表的步骤

矫情吗；* 2023-09-27 22:38 81阅读 0赞

这篇文章主要介绍了Python 实现自动化Excel报表的步骤，帮助大家更好的理解和学习使用python，感兴趣的朋友可以了解下

**目录**

*  总体解决方案
 *  输出报表
 *  自动化Py脚本
 *  打包 EXE 桌面小程序

好几个月没有写笔记了, 并非没有积累, 而是有点懒了. 想想还是要续上, 作为工作成长的一部分哦.

最近有做一些报表, 但一直找不到一个合适的报表工具, 又实在不想写前端, 后端... 思来想去, 感觉 Excel 就一定程度上能做可视化的, 除了不能动态交互外, 其他都挺好. 今天分享的就是一个关于如何用 Py 来自动化Excel 报表, 解放双手, 提高工作效率哦.

### 总体解决方案 ###

![fd4da539917fc6edab9e21fce19e5bed.png][]

### 输出报表 ###

当然是测试用的假数据啦.

![1b7f69b4a95ed8efbb2a8e679671199c.png][]

![40a1f060fded1e4131be8c5077152114.png][]

### 自动化Py脚本 ###

基本思路:  
1. 准备模板数据需要的 SQL  
2. 用 Pandas 连接 数据库 并执行 SQL, 返回 DataFrame  
3. 用 Xlwings 直接打开 Excel, 并将这些 DataFrame 填充到 写死的 单元格  
4. 保存并退出

具体代码如下哦:

import pandas as pd 
    import xlwings as xw
    import pymssql
    
    
    # 各品类月同期 
    def get_last_year_sale(start_date, end_date):
      """各品类同期销量, 对比19年"""
      sql_01 = f"""
      SELECT 
       品类
       , SUM(数量) AS QTY
      FROM XXX
      WHERE 是否电商 = 1 
       AND 销售时间 BETWEEN DATEADD(YEAR, -2, '{start_date}') AND DATEADD(YEAR, -2, '{end_date}')   
      GROUP BY 品类
      """
      df = pd.read_sql(sql_01, con=con)
      df_xtc = df[df['品类'] == 'A品类'][['品类', 'QTY']]
      df_bbk = df[df['品类'] == 'B品类'][['品类', 'QTY']]
      return df_xtc, df_bbk 
      
    def get_anget_sale(start_date, end_date):
        """返回各品类, 各区域的时间段销量"""
        sql = f"""
        SELECT 
         品类
         , AGENT
         , SUM(数量) AS QTY
         , ROW_NUMBER()OVER(PARTITION BY 品类 ORDER BY SUM(数量) DESC) MY_RANK
        FROM XXX
        WHERE 是否电商 = 1 
         AND 销售时间 BETWEEN '{start_date}' AND '{end_date}'
        GROUP BY AGENT, 品类
        """
        df = pd.read_sql(sql, con=con)
        df_xtc = df[df['品类'] == 'A品类'][['AGENT', 'QTY']]
        df_bbk = df[df['品类'] == 'B品类'][['AGENT', 'QTY']]
        df_pad = df[df['品类'] == 'C品类'][['AGENT', 'QTY']]
    
        return df_xtc, df_bbk, df_pad 
      
    def get_machine_sale(start_date, end_date):
      """返回各品类, 各区域的时间段销量"""
      sql = f"""
      SELECT 
       品类
       , 机型
       , SUM(数量) AS QTY
       , ROW_NUMBER()OVER(PARTITION BY 品类 ORDER BY SUM(数量) DESC) MY_RANK
      FROM V_REALSALE
      WHERE 是否电商 = 1 
       AND 销售时间 BETWEEN '{start_date}' AND '{end_date}'
      GROUP BY 机型, 品类
      """
      df = pd.read_sql(sql, con=con)
      df_xtc = df[df['品类'] == 'A品类'][['机型', 'QTY']]
      df_bbk = df[df['品类'] == 'B品类'][['机型', 'QTY']]
    
      return df_xtc, df_bbk 
    
    
    # main 
    con = pymssql.connect('xxxxx', 'sxxx', 'xxxxxx', 'xxxxx')
    
    # 基础配置: 根据用户输入当前日期, 输出当月, 当季度第一天 
    print("欢迎哦, 此小程序专门为XX看板做数据自动更新呢~")
    print()
    
    today = input("请输入截止日期(昨天), 形如: 2021/5/20 按回车结束:  ")
    
    if len(today.split('/')) != 3:
      raise "日期格式输入错误!!, 请按照形如 '2021/5/20'的格式重新输入"
    else:
      m_cur = today.split('/')[1]
      m_first_day = '2021/' + m_cur + '/1'
    
    # 季度第一天 
    if m_cur in ('1', '01', '2', '02', '3', '03'):
      q_time_start = '2021/1/1'
      
    elif m_cur in ('4', '04', '5', '05', '6', '06'):
      q_time_start = '2021/4/1'
      
    elif m_cur in ('7', '07', '8', '08', '9', '09'):
      q_time_start = '2021/7/1'
    else:
      q_time_start = '2021/10/1'
    
    print()
    print("正在开始更新....")
    print("提示, 接下看到闪退, 是正常现象, 就程序模拟人去打开文件, 填充数据, 不要紧张哦~~~")
    
    # 去年月, 季度同期 
    df_mm_xtc, df_mm_bbk = get_last_year_sale(m_first_day, today)
    df_qq_xtc, df_qq_bbk = get_last_year_sale(q_time_start, today)
    
    # 当月各地区累积销量
    df_m_xtc, df_m_bbk, df_m_pad = get_anget_sale(m_first_day, today)
    
    # 各地区当季度销量 
    df_q_xtc, df_q_bbk, df_q_pad = get_anget_sale(q_time_start, today)
    
    # 各机型当季度销量 
    df_q_type_xtc, df_q_type_bbk = get_machine_sale(q_time_start, today) 
    # 过滤掉 销量为0的型号 
    df_q_type_xtc = df_q_type_xtc[df_q_type_xtc.QTY > 0]
    df_q_type_xtc.replace('Z6áÛ·å°æ', 'Z6巅峰版', inplace=True)
    
    df_q_type_bbk = df_q_type_bbk[df_q_type_bbk.QTY > 0]
    
    # 打开excel 模板 等待数据填充 
    app = xw.App(visible=True, add_book=False)
    
    app.display_alerts = False  # 关闭一些提示信息，可以加快运行速度。默认为 True。
    app.screen_updating = True
    
    wb = app.books.open("XXX_全品类_看板.xlsx")
    data_sht = wb.sheets['数据']
    
    # 19年当月同期销量
    data_sht.range('B9').value = df_mm_xtc.values
    data_sht.range('G9').value = df_mm_bbk.values
    
    # 当季度同比
    data_sht.range('B10').value = df_qq_xtc.values
    data_sht.range('G10').value = df_qq_bbk.values
    
    # 填充各品类当月销量, 注意单元格是写死的哦
    data_sht.range('I72').value = df_m_xtc.values
    data_sht.range('T72').value = df_m_bbk.values
    data_sht.range('AE72').value = df_m_pad.values
    
    # 填充当季度销量, 同理是写死的
    data_sht.range('A54').value = df_q_xtc.values
    data_sht.range('F54').value = df_q_bbk.values
    data_sht.range('K54').value = df_q_pad.values
    
    # 填充当季度各型号, 同理是写死的
    data_sht.range('A21').value = df_q_type_xtc.values
    data_sht.range('F21').value = df_q_type_bbk.values
    
    wb.save()
    app.quit()
    
    print()
    print("~~更新结束了哦~~")
    print()
    input("请按任意键退出~~")
    print()
    print('BYE~~ 人生若只如初见呢~~')

### 打包 EXE 桌面小程序 ###

最好用一个纯净的 虚拟环境打包.

终端命令: `python -m venv` 虚拟环境名称

然后进入脚本目录下, 进行打包哦.

`pyinstaller main.py -F`

![cb2b10d86a8dd3437ef60d8b93cd6cb1.png][]

打包成功后的样子.

![44173b861831875b8c36128510994131.png][]

双击运行即可哦.

![7e66564668564fd09b008790da7f5f25.png][]

这时候再重新打开该目录下的 Excel 模板, 发现数据已经自动更新了.

我现在真的感受到, 用开发的思维做一些脚本工具, 真的会极大提高我现在当文员的很多重复性工作哦!

以上就是Python 实现自动化Excel报表的步骤的详细内容

**最后：**下方这份完整的软件测试视频学习教程已经整理上传完成，需要的朋友们可以自行领取**【保证100%免费】**

![在这里插入图片描述][9d205f76a3bb49d8bce29972548618fa.png_pic_center]

### 软件测试面试文档 ###

我们学习必然是为了找到高薪的工作，下面这些面试题是来自阿里、腾讯、字节等一线互联网大厂最新的面试资料，并且有字节大佬给出了权威的解答，刷完这一套面试资料相信大家都能找到满意的工作。

![图片][5edae9a0b47e4dfc8a71a369c5468e60.png]

#### 整套资料获取 ####

![369a983c462d44889b8ad60237393e76.gif][]

[fd4da539917fc6edab9e21fce19e5bed.png]: https://img-blog.csdnimg.cn/img_convert/fd4da539917fc6edab9e21fce19e5bed.png
[1b7f69b4a95ed8efbb2a8e679671199c.png]: https://img-blog.csdnimg.cn/img_convert/1b7f69b4a95ed8efbb2a8e679671199c.png
[40a1f060fded1e4131be8c5077152114.png]: https://img-blog.csdnimg.cn/img_convert/40a1f060fded1e4131be8c5077152114.png
[cb2b10d86a8dd3437ef60d8b93cd6cb1.png]: https://img-blog.csdnimg.cn/img_convert/cb2b10d86a8dd3437ef60d8b93cd6cb1.png
[44173b861831875b8c36128510994131.png]: https://img-blog.csdnimg.cn/img_convert/44173b861831875b8c36128510994131.png
[7e66564668564fd09b008790da7f5f25.png]: https://img-blog.csdnimg.cn/img_convert/7e66564668564fd09b008790da7f5f25.png
[9d205f76a3bb49d8bce29972548618fa.png_pic_center]: https://img-blog.csdnimg.cn/9d205f76a3bb49d8bce29972548618fa.png#pic_center
[5edae9a0b47e4dfc8a71a369c5468e60.png]: https://img-blog.csdnimg.cn/5edae9a0b47e4dfc8a71a369c5468e60.png
[369a983c462d44889b8ad60237393e76.gif]: https://img-blog.csdnimg.cn/369a983c462d44889b8ad60237393e76.gif