好几个月没有写笔记了, 并非没有积累, 而是有点懒了. 想想还是要续上, 作为工作成长的一部分哦.
最近有做一些报表, 但一直找不到一个合适的报表工具, 又实在不想写前端, 后端... 思来想去, 感觉 excel 就一定程度上能做可视化的, 除了不能动态交互外, 其他都挺好. 今天分享的就是一个关于如何用 py 来自动化excel 报表, 解放双手, 提高工作效率哦.
总体解决方案
输出报表
当然是测试用的假数据啦.
自动化py脚本
基本思路:
1. 准备模板数据需要的 sql
2. 用 pandas 连接 数据库 并执行 sql, 返回 dataframe
3. 用 xlwings 直接打开 excel, 并将这些 dataframe 填充到 写死的 单元格
4. 保存并退出
具体代码如下哦:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
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 模板, 发现数据已经自动更新了.
我现在真的感受到, 用开发的思维做一些脚本工具, 真的会极大提高我现在当文员的很多重复性工作哦!
以上就是python 实现自动化excel报表的步骤的详细内容，更多关于python 自动化excel报表的资料请关注服务器之家其它相关文章！
原文链接：https://www.cnblogs.com/chenjieyouge/p/14604277.html