从数据库查询数据并导出到excel

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

import pymysql
import datetime
import xlwt
import os
import pandas as pd
 
def export_excel(output_dir):
    current_datetime = datetime.datetime.now()
    # 将日期时间格式化为字符串，例如：2023-10-23_14-30-15
    formatted_datetime = current_datetime.strftime('%Y-%m')
    formatted_datetime1 = current_datetime.strftime('%Y-%m-%d')
    # 构造文件名，包含表名和格式化后的日期时间
    file_name = f'{formatted_datetime}月.xls'
    # 使用f-string来格式化工作表名称
    sheet_name1 = f'{formatted_datetime1}'
    # 构造完整的文件路径
    file_path = os.path.join(output_dir, file_name)
    # 连接MySQL数据库
    conn = pymysql.connect(host='***', user='***', password='***', db='***')
    # 创建一个Cursor对象，用于执行SQL命令
    cur = conn.cursor()
    today = datetime.date.today()
    weekday = today.weekday()
    startday = today - datetime.timedelta(days=weekday)
    endday = startday + datetime.timedelta(days=(6 - weekday) if weekday != 6 else 0)
    startday_str = startday.strftime('%Y-%m-%d')
    endday_str = endday.strftime('%Y-%m-%d')
 
    print(f"本周起始日期: {startday_str}")
    print(f"本周结束日期: {endday_str}")
    person = 5
    sql_query =f"""  
    SELECT          
        CASE dept          
            WHEN 119 THEN '规划南方大区'          
            WHEN 148 THEN '调度主网'          
            WHEN 149 THEN '调度配网'          
            WHEN 150 THEN '调度发电'          
            WHEN 151 THEN '调度校核'          
            WHEN 152 THEN '调度数字化'          
            WHEN 32 THEN '规划产品研发'          
            WHEN 92 THEN '大数据产品研发'          
            WHEN 102 THEN '新电-平台开发部'          
            WHEN 76 THEN '新电-产品交付部'          
            WHEN 62 THEN '新电-交易'          
            WHEN 75 THEN '数字设备研发二部'          
            WHEN 26 THEN '数字设备研发一部'          
            WHEN 19 THEN '研发中心平台巴'         
        END AS 部门,        
        dh.计划消耗总工时,      
        dh.总人数,      
        '{person}' * dh.总人数 * 8 AS 标准工时,
        round((dh.计划消耗总工时 / ('{person}' * dh.总人数 * 8)) * 100, 2) AS 研发计划负载率, 
        LEAST(round((dh.计划消耗总工时 / ('{person}' * dh.总人数 * 8)) * 100, 2), 100) AS 研发计算负载率,  -- 确保负载率不超过100    
        GREATEST(0, (1 - (100 - LEAST(round((dh.计划消耗总工时 / ('{person}' * dh.总人数 * 8)) * 100, 2), 100)) / (100 - 95)) * 2.5) AS 得分 -- 确保得分不低于0  
    FROM (      
        SELECT          
            dept,      
        round(
        SUM(  
            CASE  
              WHEN zt.realStarted = '0000-00-00 00:00:00' THEN  
                CASE  
                  WHEN zt.deadline BETWEEN '{startday}'  AND '{endday}' AND zt.estStarted < '{startday}'  THEN  
                    (zt.left / (DATEDIFF(zt.deadline, NOW()) + 1)) * (DATEDIFF(zt.deadline, '{startday}' ) + 1)  -- 任务未开始，预计开始日期在计划外，截止日期在计划内
                  WHEN zt.estStarted BETWEEN '{startday}'  AND '{endday}' AND zt.deadline > '{endday}' THEN  
                    (zt.left / (DATEDIFF(zt.deadline, zt.estStarted) + 1)) * (DATEDIFF('{endday}', zt.estStarted) + 1)  -- 任务未开始，预计开始日期在计划内，截止日期在计划外
                  WHEN zt.estStarted < '{startday}'  AND zt.deadline > '{endday}' THEN  
                    (zt.left / (DATEDIFF(zt.deadline, NOW()) + 1)) * (DATEDIFF('{endday}', '{startday}' ) + 1)  -- 任务未开始，预计开始日期，截止日期均在计划外
                  WHEN zt.estStarted > '{startday}'  AND zt.deadline < '{endday}' THEN  
                    zt.left  -- 任务未开始，预计开始日期，截止日期均在计划内
                  ELSE 0  
                END  
              WHEN zt.realStarted != '0000-00-00 00:00:00' THEN  -- 任务已开始
                CASE  
                  WHEN zt.deadline BETWEEN '{startday}'  AND '{endday}' AND zt.estStarted < '{startday}'  THEN  
                    (zt.left / (DATEDIFF(zt.deadline, NOW()) + 1)) * (DATEDIFF(zt.deadline, '{startday}' ) + 1)  
                  WHEN zt.estStarted BETWEEN '{startday}'  AND '{endday}' AND zt.deadline > '{endday}' THEN  
                    (zt.left / (DATEDIFF(zt.deadline, zt.estStarted) + 1)) * (DATEDIFF('{endday}', zt.estStarted) + 1)  
                  WHEN zt.estStarted < '{startday}'  AND zt.deadline > '{endday}' THEN  
                    (zt.left / (DATEDIFF(zt.deadline, NOW()) + 1)) * (DATEDIFF('{endday}', '{startday}' ) + 1)  
                  WHEN zt.estStarted > '{startday}'  AND zt.deadline < '{endday}' THEN  
                    zt.left  
                  ELSE 0  
                END  
              ELSE 0  
            end  
          ),2) AS 计划消耗总工时,  
            count(DISTINCT zu.id) AS 总人数  
        FROM          
            zt_user zu          
           inner JOIN zt_task zt ON zu.account IN (zt.finishedBy, zt.assignedTo) and zt.parent != -1
                AND (zt.deadline BETWEEN '{startday}'  AND '{endday}' OR zt.estStarted BETWEEN '{startday}'  AND '{endday}')          
                AND zt.deleted != '1'           
        WHERE          
            zu.dept IN (148, 149, 150, 151, 152, 119, 32, 62, 92, 102, 75, 26, 76, 19)          
            AND zu.deleted = '0'          
            AND zu.realname NOT IN('仇佳丽','郭鸿志','谭晓龙','郑朋','韩世飞','陈伟','付龙明','于瑞丰','谭小龙','张文斌','贺成利','宁杰','张琪','杨海燕','朱博','赵少飞')
        GROUP BY          
            zu.dept      
    ) dh      
    ORDER BY       
        部门;
    """
 
    # 执行SQL命令
    cur.execute(sql_query)
    fileds=[filed[0] for filed in cur.description]#所有字段
    all_data=cur.fetchall()#所有数据值，数组形式存放
    book=xlwt.Workbook()
    sheet=book.add_sheet(sheet_name1)
    for col,filed in enumerate(fileds):
        sheet.write(0, col, filed)
    row=1
    for data in all_data:
        for index, datacol in enumerate(data):
            sheet.write(row, index, datacol)
        row = row + 1
    book.save(file_path)  # 使用完整的文件路径来保存工作簿
 
    # 设置输出目录
output_dir = r'C:\Users\Administrator\Desktop\2024年工作文件夹\研发绩效考核\负载得分'
 
    # 导出Excel，使用带日期时间的文件名
export_excel(output_dir)