Python 禅道自动出报告： 每天汇报还得写多少Bug，太烦了，

这次使用了Docker 快速生成环境的方法：每次更新比较麻烦，把步骤也写下来，大家参考一下

#使用Dockerfile 创建镜像

docker build -t ?chandao:1.0 ./
#如果有老容器就删除老的容器
docker rm ?750898a2c06f
#启动镜像文件
docker run -it --rm -p 5000:5000 ?chandao:1.0
#启动镜像文件
docker run -d -p 5000:5000 ?--name chandao ?chandao:1.0

Dockerfile文件 ：创建禅道下载汇报文件

FROM python:3.9-slim

WORKDIR /app

COPY requirements.txt .

RUN pip3 install -i https://pypi.tuna.tsinghua.edu.cn/simple --upgrade pip
?
RUN pip3 config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple?

RUN pip install --no-cache-dir -r requirements.txt

COPY . .

CMD ["python", "app.py"]

接下是flask部分：组装汇报文件 ，文件格式是excel的

import loguru
from flask import Flask, render_template, send_file
import pymysql
import time
import traceback
import openpyxl
from openpyxl.styles import Border,Side,Alignment,Font
import datetime
import os


app = Flask(__name__)

@app.route('/')
def index():

    os.remove("./test.xlsx")

    today = datetime.date.today()
    # 居中样式
    align = Alignment(horizontal='center', vertical='center', wrap_text=True)
    # 边框样式
    border = Border(left=Side(border_style='thin'),
                    right=Side(border_style='thin'),
                    top=Side(border_style='thin'),
                    bottom=Side(border_style='thin'))

    # wb = openpyxl.load_workbook('./test.xlsx')
    wb = openpyxl.Workbook()
    sheets = wb.sheetnames
    sheet = wb[sheets[0]]
    sheet1 = wb.create_sheet("sheet1")

    print(sheet)

    conn = pymysql.connect(
        user='root',
        password='123456',
        # MySQL的默认端口为3306
        port=3307,
        # 本机地址为127.0.0.1或localhost
        host='49.232.233.30',
        # 指定使用的数据库
        init_command='use zentao'
    )
    # 创建游标对象
    cursor = conn.cursor()

    # 布置计时器
    start = time.time()
    # 将数据插入到目标数据库的指定表中
    try:

        # 今天创建的bug
        cursor.execute('select count(1) from  zt_bug  z where  DATE(z.openedDate) = CURRENT_DATE();')
        count = cursor.fetchone()

        cursor.execute('select id,title from  zt_bug  z where  DATE(z.openedDate) = CURRENT_DATE();')
        datas = cursor.fetchall()
        create_count = 1
        sheet.cell(create_count, 1, f"一、{today}日新增 {count[0]} 个Bug").border = border
        print(count[0])

        create_count += 1

        # font_title = Font(u'微软雅黑', size=12)
        # # 引用font字体
        # sheet['A:C'].font = font_title
        # 设置行高 第一行 40
        sheet.row_dimensions[1].height = 25
        sheet.merge_cells('A1:C1')
        sheet['A1'].alignment = align
        sheet.column_dimensions[chr(1 + 65)].width = 10
        sheet.column_dimensions[chr(2 + 65)].width = 10
        sheet.column_dimensions[chr(2 + 65)].width = 70

        # 加入标题
        sheet.cell(create_count, 1, "序号").border = border
        sheet.cell(create_count, 2, "Bug编号").border = border
        sheet.cell(create_count, 3, "Bug标题").border = border
        index = 1
        for data in datas:
            # print(type(data))
            # print(data)
            sheet.cell(create_count + 1, 1, index).border = border
            sheet.cell(create_count + 1, 2, data[0]).border = border
            sheet.cell(create_count + 1, 3, data[1]).border = border
            create_count += 1
            index += 1

        # 今天关闭的bug
        cursor.execute('select count(1) from  zt_bug  z where  DATE(z.closedDate) = CURRENT_DATE() and   z.`status` ="closed";')
        count = cursor.fetchone()

        # 今天激活的Bug

        cursor.execute('select count(1) from zt_bug z where  DATE(z.activatedDate) = CURRENT_DATE();')
        a_count = cursor.fetchone()
        # print(a_count)
        cursor.execute('select id,title,(select realname from zt_user where zt_user.account = z.resolvedBy ) as realname from zt_bug z where  DATE(z.activatedDate) = CURRENT_DATE();')
        datas = cursor.fetchall()
        active_count = create_count + 5
        sheet.cell(active_count, 1, f"二、今日共复测试关闭{count[0]}个Bug  提测新功能 条，{a_count[0]}条未验收通过").border = border
        sheet.merge_cells(f"A{active_count}:C{active_count}")
        active_count += 1
        # 加入标题
        sheet.cell(active_count, 1, "序号").border = border
        sheet.cell(active_count, 2, "Bug编号").border = border
        sheet.cell(active_count, 3, "Bug标题").border = border
        sheet.cell(active_count, 4, "开发人员").border = border
        sheet.cell(active_count, 5, "测试结果").border = border
        sheet.cell(active_count, 6, "是否加班修改").border = border
        for data in datas:
            # print(type(data))
            # print(data[0])
            sheet.cell(active_count + 1, 1, index)
            sheet.cell(active_count + 1, 2, data[0])
            sheet.cell(active_count + 1, 3, data[1])
            sheet.cell(active_count + 1, 4, data[2])
            active_count += 1
            index+=1



        #第二个sheet 存储统计值
        sheet1.cell(1,1,"BUG情况").border = border
        sheet1.cell(1, 2, f"{today}").border = border

        #剩余未关闭BUG总量
        sheet1.cell(2, 1, "剩余未关闭BUG总量").border = border
        #查询SQL
        cursor.execute("SELECT count(1) FROM  zt_bug  WHERE  deleted  ='0' AND  status <>'closed'  and  (product =2 or product = 12); ")
        count = cursor.fetchone()
        sheet1.cell(2, 2, f"{count[0]}").border = border

        #关闭BUG数量
        sheet1.cell(3, 1, "关闭BUG数量").border = border
        # 查询SQL
        cursor.execute('select count(1) from  zt_bug  z where  DATE(z.closedDate) = CURRENT_DATE() and   z.`status` ="closed";')
        count = cursor.fetchone()
        sheet1.cell(3, 2, f"{count[0]}").border = border

        #解决BUG数量
        sheet1.cell(4, 1, "解决BUG数量").border = border
        # sheet1.cell(4, 1).alignment = align
        # sheet1.cell(4, 1).column_dimensions[chr(1+65)].width = 40

        # 查询SQL
        cursor.execute('select count(1) from  zt_bug  z where   z.`status` = "resolved"  and  DATE(z.resolvedDate) = CURRENT_DATE() ;')
        count = cursor.fetchone()
        sheet1.cell(4, 2, f"{count[0]}").border = border

        #新增BUG数量
        sheet1.cell(5, 1, "新增BUG数量").border = border
        # 查询SQL
        cursor.execute('select count(1) from  zt_bug  z where  DATE(z.openedDate) = CURRENT_DATE();')
        count = cursor.fetchone()
        sheet1.cell(5, 2, f"{count[0]}").border = border

        # 新增BUG原因
        sheet1.cell(6, 1, "新增BUG原因").border = border
        sheet1.cell(6, 2, f"已解决未复测:   \n 未还原原型：    \n  原型遗漏：   \n  开发遗漏：    \n  已关闭：   \n  改bug引发新bug:  \n   激活：   \n  已有功能优化：   \n   新功能：  \n").border = border

        # 领导安排
        sheet1.cell(7, 1, "领导安排").border = border
        sheet1.cell(7, 2, "0").border = border

        # 今日完成
        sheet1.cell(8, 1, "今日完成").border = border
        sheet1.cell(8, 2, "0").border = border

        # 客户需求
        sheet1.cell(9, 1, "客户需求").border = border
        sheet1.cell(9, 2, "0").border = border

        # 今日完成
        sheet1.cell(10, 1, "今日完成").border = border
        sheet1.cell(10, 2, "0").border = border

        # 主干类 剩余未关闭
        sheet1.cell(11, 1, "主干类 剩余未关闭").border = border
        # 查询SQL
        cursor.execute('select count(1) from  zt_bug  z where  z.branch = 0 and   (z.`status` = "active" or  z.`status` = "resolved") ;')
        count = cursor.fetchone()
        sheet1.cell(11, 2, f"{count[0]}").border = border

        # iOS类 剩余未关闭
        sheet1.cell(12, 1, "iOS类 剩余未关闭").border = border
        # 查询SQL
        cursor.execute('select count(1) from  zt_bug  z where  z.branch = 1 and   (z.`status` = "active" or  z.`status` = "resolved")  ;')
        count = cursor.fetchone()
        sheet1.cell(12, 2, f"{count[0]}").border = border

        # 安卓类 剩余未关闭
        sheet1.cell(13, 1, "安卓类 剩余未关闭").border = border
        # 查询SQL
        cursor.execute('select count(1) from  zt_bug  z where  z.branch = 2 and   (z.`status` = "active" or  z.`status` = "resolved")  ;')
        count = cursor.fetchone()
        sheet1.cell(13, 2, f"{count[0]}").border = border

        #后台类 剩余未关闭
        sheet1.cell(14, 1, "后台类 剩余未关闭").border = border
        # 查询SQL
        cursor.execute('select count(1) from  zt_bug  z where  z.branch = 3 and   (z.`status` = "active" or  z.`status` = "resolved")  ;')
        count = cursor.fetchone()
        sheet1.cell(14, 2, f"{count[0]}").border = border

        # 优化类 剩余未关闭
        sheet1.cell(15, 1, "优化类 剩余未关闭").border = border
        # 查询SQL
        cursor.execute('select count(1) from  zt_bug  z where  z.branch = 4 and   (z.`status` = "active" or  z.`status` = "resolved")  ;')
        count = cursor.fetchone()
        sheet1.cell(15, 2, f"{count[0]}").border = border

        # 新功能类 剩余未关闭
        sheet1.cell(16, 1, "新功能类 剩余未关闭").border = border
        # 查询SQL
        cursor.execute('select count(1) from  zt_bug  z where  z.branch = 5 and   (z.`status` = "active" or  z.`status` = "resolved")  ;')
        count = cursor.fetchone()
        sheet1.cell(16, 2, f"{count[0]}").border = border

        # 前端类 剩余未关闭
        sheet1.cell(17, 1, "前端类 剩余未关闭").border = border
        # 查询SQL
        cursor.execute('select count(1) from  zt_bug  z where  z.branch = 6 and   (z.`status` = "active" or  z.`status` = "resolved")  ;')
        count = cursor.fetchone()
        sheet1.cell(17, 2, f"{count[0]}").border = border

        # 万店供应链  剩余未关闭
        sheet1.cell(17, 1, "万店供应链 剩余未关闭").border = border
        # 查询SQL
        cursor.execute('select count(1) from  zt_bug z where  z.product =12  and (z.`status` = "active" or  z.`status` = "resolved") ;')
        count = cursor.fetchone()
        sheet1.cell(17, 2, f"{count[0]}").border = border



        for r in sheet1:
            for c in r:
                c.alignment = openpyxl.styles.Alignment(wrapText=True)
                c.border = border
        for r in sheet:
            for c in r:
                c.alignment = openpyxl.styles.Alignment(wrapText=True)
                c.border = border

        wb.save('./test.xlsx')

        cursor.execute('select closedBy,count(1) from  zt_bug  where DATE(closedDate) = DATE(NOW())  GROUP BY closedBy;')
        datas = cursor.fetchall()
        data_list=[]


        for data in datas:
            name = {'name': data[0], 'closesum': data[1]}
            data_list.append(name)
        loguru.logger.error(data_list)

        cursor.execute('select openedBy,count(1) from  zt_bug  where DATE(openedDate) = DATE(NOW())  GROUP BY openedBy;')
        datas = cursor.fetchall()
        create_list = []
        for data in datas:
            name = {'name': data[0], 'createsum': data[1]}
            create_list.append(name)
        loguru.logger.error(create_list)

    except Exception:
        print('失败了！')
        # 打印错误信息
        print(traceback.print_exc())
        pass
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()
    return render_template('index.html',data =data_list,opened= create_list)

@app.route('/download')
def download_file():




    file_path = './test.xlsx'

    try:
        return send_file(file_path, as_attachment=True)
    except Exception as e:
        return str(e)

if __name__ == '__main__':
    app.run(host="0.0.0.0",port=5000)

index.html文件 简单的一个页面：

<!DOCTYPE html>
<html>
<head>
    <title>文件下载页面</title>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>
<script>
var today = new Date();
var year = today.getFullYear();
var month = today.getMonth() + 1;
var day = today.getDate();
var dateText = year + "/" + month + "/" + day;
document.write("<h1 style='display: inline-block'>禅道统计下载日期：</h1>" + dateText);
</script>
<body>
    <h1></h1>
    <button id="downloadBtn">点击下载今日统计</button>

    <script>
        $(document).ready(function() {
            $('#downloadBtn').click(function() {
                window.location.href = '/download';
            });
        });
    </script>

    <br/>
    <hr/>

    <h3>禅道今日创建数量：</h3>
    <ul>
        {% for bug in opened %}
            <li>创建人：{{ bug.name }}</li><li>创建bug数：{{ bug.createsum }}</li><br/>
        {% else %}

            <li>无数据</li>
        {% endfor %}
    </ul>
    <br/>

    <h3>禅道今日关闭数量：</h3>
    <ul>
        {% for bug in data%}
            <li>关闭人：{{ bug.name }}</li><li>关闭bug数：{{ bug.closesum }}</li><br/>
        {% else %}
            <li>无数据</li>
        {% endfor %}


    </ul>


</body>
</html>

那位老铁实在想要所有代码，可以私信我哈，我给你发链接