Py 实现自动化Excel报表
好几个月没有写笔记了, 并非没有积累, 而是有点懒了. 想想还是要续上, 作为工作成长的一部分哦.
最近有做一些报表, 但一直找不到一个合适的报表工具, 又实在不想写前端, 后端… 思来想去, 感觉 Excel 就一定程度上能做可视化的, 除了不能动态交互外, 其他都挺好. 今天分享的就是一个关于如何用 Py 来自动化Excel 报表, 解放双手, 提高工作效率哦.
总体解决方案
输出报表
当然是测试用的假数据啦.
自动化Py脚本
基本思路:1. 准备模板数据需要的 SQL2. 用 Pandas 连接 数据库 并执行 SQL, 返回 DataFrame3. 用 Xlwings 直接打开 Excel, 并将这些 DataFrame 填充到 写死的 单元格4. 保存并退出
具体代码如下哦:
import pandas as pdimport xlwings as xwimport pymssql# 各品类月同期def get_last_year_sale(start_date, end_date):\"\"\"各品类同期销量, 对比19年\"\"\"sql_01 = f\"\"\"SELECT品类, SUM(数量) AS QTYFROM XXXWHERE 是否电商 = 1AND 销售时间 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_bbkdef get_anget_sale(start_date, end_date):\"\"\"返回各品类, 各区域的时间段销量\"\"\"sql = f\"\"\"SELECT品类, AGENT, SUM(数量) AS QTY, ROW_NUMBER()OVER(PARTITION BY 品类 ORDER BY SUM(数量) DESC) MY_RANKFROM XXXWHERE 是否电商 = 1AND 销售时间 BETWEEN \'{start_date}\' AND \'15a8{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_paddef get_machine_sale(start_date, end_date):\"\"\"返回各品类, 各区域的时间段销量\"\"\"sql = f\"\"\"SELECT品类, 机型, SUM(数量) AS QTY, ROW_NUMBER()OVER(PARTITION BY 品类 ORDER BY SUM(数量) DESC) MY_RANKFROM V_REALSALEWHERE 是否电商 = 1AND 销售时间 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# maincon = pymssql.connect(\'172.28.1.158\', \'sa\', \'dwbbkkzw168\', \'biee\')# 基础配置: 根据用户输入当前日期, 输出当月, 当季度第一天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 = Truewb = app.books.open(\"XXX_全品类_看板.xlsx\")data_sht = wb.sheets[\'数据\']# 19年当月同期销量data_sht.range(\'B9\').value = df_mm_xtc.valuesdata_sht.range(\'G9\').value = df_mm_bbk.values# 当季度同比data_sht.range(\'B10\').value = df_qq_xtc.valuesdata_sht.range(\'G10\').value = df_qq_bbk.values# 填充各品类当月销量, 注意单元格是写死的哦data_sht.range(\'I72\').value = df_m_xtc.valuesdata_sht.range(\'T72\').value = df_m_bbk.valuesdata_sht.range(\'AE72\').value = df_m_pad.values# 填充当季度销量, 同理是写死的data_sht.range(\'A54\').value = df_q_xtc.valuesdata_sht.range(\'F54\').value = df_q_bbk.valuesdata_sht.range(\'K54\').value = df_q_pad.values# 填充当季度各型号, 同理是写死的data_sht.range(\'A21\').value = df_q_type_xtc.valuesdata_sht.range(\'F21\').value = df_q_type_bbk.valueswb.save()app.quit()print()print(\"~~更新结束了哦~~\")print()input(\"请按任意键退出~~\")print()print(\'BYE~~ 人生若只如初见呢~~\')
打包 EXE 桌面小程序
最好用一个纯净的 虚拟环境打包.
终端命令: python -m venv 虚拟环境名称
然后进入脚本目录下, 进行打包哦.
pyinstaller main.py -F
打包成功后的样子.
双击运行即可哦.
这时候再重新打开该目录下的 Excel 模板, 发现数据已经自动更新了.
我现在真的感受到, 用开发的思维做一些脚本工具, 真的会极大提高我现在当文员的很多重复性工作哦!