Python 实现自动化Excel报表的步骤
作者:致于数据科学家的小陈 时间:2022-12-01 10:49:29
目录
总体解决方案
输出报表
自动化Py脚本
打包 EXE 桌面小程序
好几个月没有写笔记了, 并非没有积累, 而是有点懒了. 想想还是要续上, 作为工作成长的一部分哦.
最近有做一些报表, 但一直找不到一个合适的报表工具, 又实在不想写前端, 后端... 思来想去, 感觉 Excel 就一定程度上能做可视化的, 除了不能动态交互外, 其他都挺好. 今天分享的就是一个关于如何用 Py 来自动化Excel 报表, 解放双手, 提高工作效率哦.
总体解决方案
输出报表
当然是测试用的假数据啦.
自动化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
打包成功后的样子.
双击运行即可哦.
这时候再重新打开该目录下的 Excel 模板, 发现数据已经自动更新了.
我现在真的感受到, 用开发的思维做一些脚本工具, 真的会极大提高我现在当文员的很多重复性工作哦!
来源:https://www.cnblogs.com/chenjieyouge/p/14604277.html
标签:python,excel,报表
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
BluePrint CSS框架0.9版发布
2009-06-03 21:02:00
PHP结构型模式之代理模式
2023-05-25 06:55:34
sqlserver中如何查询出连续日期记录的代码
2011-09-30 11:16:56
用 onerror 获取错误信息 js Debug
2008-11-03 19:08:00
Python查找最长不包含重复字符的子字符串算法示例
2021-05-25 23:05:02
![](https://img.aspxhome.com/file/2023/8/66398_0s.png)
该死的IE,走好
2009-01-15 12:26:00
![](https://img.aspxhome.com/file/UploadPic/20091/15/byebye_ie6-71s.png)
asp中记录集对象的getrows和getstring用法分析
2012-11-30 20:09:49
django 快速启动数据库客户端程序的方法示例
2023-07-31 09:31:59
![](https://img.aspxhome.com/file/2023/1/61441_0s.png)
关于Ajax responseText 中文乱码问题
2008-02-12 16:30:00
CSS3中的box-sizing属性
2010-04-05 21:52:00
![](https://img.aspxhome.com/file/UploadPic/20104/5/20090424070002186-44s.png)
Go语言string,int,int64 ,float之间类型转换方法
2023-06-28 15:20:30
python3.7+selenium模拟淘宝登录功能的实现
2022-03-05 01:26:27
![](https://img.aspxhome.com/file/2023/9/70169_0s.png)
使用Python编写类UNIX系统的命令行工具的教程
2023-08-24 05:03:02
ASP操作XML文件的完整实例
2007-09-26 12:05:00
怎样在不同版本SQL Server中存储数据
2009-01-20 13:11:00
成为一个顶级设计师的第三准则
2009-09-15 21:00:00
![](https://img.aspxhome.com/file/UploadPic/20099/15/01-52s.jpg)
JSP 获取spring容器中bean的两种方法总结
2023-06-15 07:27:46
asp获取远程网页的指定内容的实现代码
2011-02-16 10:41:00
解决python3.6用cx_Oracle库连接Oracle的问题
2023-06-12 02:38:46
![](https://img.aspxhome.com/file/2023/7/59827_0s.png)
Python 栈实现的几种方式及优劣详解
2023-11-02 14:25:18
![](https://img.aspxhome.com/file/2023/3/62623_0s.jpg)