1、引言
小屌丝:鱼哥帮个忙
小鱼:稍等会哦,
小屌丝:好嘞。
小屌丝: 鱼哥, 还没忙完嘛?
小鱼:快了快了, 再耐心等一等
小屌丝:哦…
小屌丝:鱼哥,能不能行了, 给个痛快话
小鱼:你看我是不是男的
小屌丝:这还用问,必须得
小鱼:那你还问能不能行?
小屌丝:你这时间,还挺久的啊。
小鱼:哎哎哎, 你这是要飙车啊
小屌丝:…
小鱼:有啥事情?
小屌丝:嗯… 就是… 嗯…
小鱼:你这还磨磨唧唧的?赶紧说
小屌丝:就是, 想着统计数据库的各维度数据,并把结果写入到Excel中
小鱼:就这个??
小屌丝:嗯,是的
小鱼:我还以为啥事的? 你等会吧
小屌丝:唉… 鱼哥,别等了, 我这等的花儿都谢了。
小鱼:你斗地主呢?
小屌丝:没有了,就是… 我寻思 这快到夜宵时间了, 咱是不是得赶紧的。
小鱼:哎呀,说的也是, 那这就开整。
2、代码实例
由于代码没有什么特别的难度, 这里,我就直接上代码了。
也会在代码中做详细的注释。
# -*- coding:utf-8 -*-
# @Time : 2024-06-01
# @Author : Carl_DJ
'''
实现功能:
1、读取sql.txt 文件,把查询统计结果写在Excel中文件,
2、把Excel中每个sheet页中的每列数据进行统计求和,写在第四列中
3、最后把第四列中有数据的值背景色填充为黄色
'''
import os
import pymysql
import pandas as pd
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
#配置数据库
db_config = {
'host': '数据库服务器地址',
'port': '输入端口号',
'user': '输入用户名',
'password': '输入密码',
'db': '输入库信息',
'charset': 'utf8'
}
#数据库链接口
conn = pymysql.connect(**db_config)
#版本信息
Ver_name = 'Ver 1.1'
#获取当前时间并格式化
now = datetime.now().strftime('%Y%m%d%H%M')
Outfile_name = Ver_name+'_统计结果_'+now+'.xlsx'
result_path = './002_统计结果'
Outfile_path = os.path.join(result_path,Outfile_name)
#读取sql文件=
sql_name = r'test_sql.txt'
#把查询结果写入不同的sheet页,也需要对sheet页进行命名
sheet_names = ['完整度','颜色','大类','小类']
#读取sql文件并分割sql语句
def sqls(sql_name):
global sqlstrs
with open(sql_name,'r',encoding='utf-8') as f:
sqlstrs = f.read().split(';')
# 执行sql并写入Excel
def quert_and_write_to_excel():
with pd.ExcelWriter(Outfile_path) as writer:
for i in range (len(sqlstrs)):
df = pd.read_sql(sqlstrs[i],con=conn)
df.to_excel(writer,sheet_name=sheet_names[i],index=False,header = True)
#加载Excel文件并计算B列及以后每列的和
def calculate_sum_and_save():
workbook = load_workbook(Outfile_path)
#添加黄色背景色
yellow_fill = PatternFill( start_color='FFFF00', end_color='FFFF00', fill_type='solid')
# 遍历工作簿中的每个工作表
for sheet_name in workbook.sheetnames:
sheet = workbook[sheet_name]
# 初始化一个列表用于存储每列的求和结果,列表长度是工作表最大列数减1(因为不计算第一列)
column_sums = [0]*(sheet.max_column - 1)
# 在A4列添加"统计"文字并设置黄色背景色
cell_a4 = sheet['A4']
cell_a4.value = 'Total'
cell_a4.fill = yellow_fill
# 遍历所有行,从B列开始累加数字(只获取单元格的值)
for row in sheet.iter_rows(min_col=2, values_only=True):
# 遍历每一行的单元格(从第二列开始),并计算它们的和
for col_idx, cell_value in enumerate(row, start=2): # 从第二列开始,col_idx从2开始
# 如果单元格的值不为空,且是数值类型(整数或浮点数)
if cell_value is not None and isinstance(cell_value, (int, float)):
# 将该值加到对应列的求和列表中
column_sums[col_idx - 2] += cell_value # 减去2,从B列开始
# 将从B列开始的每列的总和写入到对应列的第四行中
for col_idx, sum_value in enumerate(column_sums):
# 设置第四列的单元格背景色为黄色
if col_idx + 2 <= sheet.max_column:
# 获取第四行对应列的单元格对象
cell = sheet.cell(row=4, column=col_idx + 2)
cell.fill = yellow_fill
cell.value = sum_value
#保存每个sheet
workbook.save(Outfile_path)
print(f'数据求和完成,并已保持到:{Outfile_path}')
#执行
if __name__ == '__main__':
sqls(sql_name)
quert_and_write_to_excel()
calculate_sum_and_save()
print ('数据保存完成')
#关闭数据库链接
conn.close()
print ('数据库链接已关闭')
注意:
- 1、由于sql比较多,且便于整个代码的维护,所以,这里我把sql放在文件中,
- 2、生成的Excel文件,展示如下:
3、总结
这个方法,在平时工作中很常用。
不管是做数据统计,还是做业务统计,测试开发等岗位,都会用到。
所以,平时多积累,总会用得到。
我是小鱼:
- CSDN 博客专家;
- 阿里云 专家博主;
- 51CTO博客专家;
- 企业认证金牌面试官;
- 多个名企认证&特邀讲师等;
- 名企签约职场面试培训、职场规划师;
- 多个国内主流技术社区的认证专家博主;
- 多款主流产品(阿里云等)评测一等奖获得者;
关注小鱼,带你学习更多更专业更前言的Python领域技术知识。