python3查询数据库并生成excel报表

#!/usr/bin/env python3
#encoding=UTF-8

import os
import time
import xlwt

hostIp = 'xxx.xxx.xxx.xx'
user = 'user0001'
passwd = 'xxxx'
db = 'db01'

sqlStr1 = 'SELECT timeout_day as 逾期天数,COUNT(1) as 统计次数 FROM t_order_info where status in (7,9) GROUP BY timeout_day HAVING timeout_day <> 0;'

def createTable(selectSql,tableName):
    #连接数据库，执行sql
    results = os.popen('mysql -h'+hostIp+' -u'+user+' -p'+passwd+' -D'+db+' -e "'+selectSql+'"').read().strip().split('\n')
    #获取列名
    columnName = results[0].split('\t')

    #创建一个excel工作簿，编码utf-8，表格中支持中文
    wb=xlwt.Workbook(encoding='utf-8')
    #创建一个sheet
    sheet=wb.add_sheet('sheet 1')

    #获取行数
    rows = len(results)
    #获取列数
    columns = len(columnName)

    #创建格式style
    style = xlwt.XFStyle()
    #创建font，设置字体
    font = xlwt.Font()
    #字体格式
    font.name = 'Times New Roman'
    #将字体font，应用到格式style
    style.font = font
    #创建alignment，居中
    alignment = xlwt.Alignment() 
    #居中
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    #应用到格式style
    style.alignment = alignment

    
    style1 = xlwt.XFStyle()
    font1 = xlwt.Font()
    font1.name = 'Times New Roman'
    #字体颜色（绿色）
    #font1.colour_index = 3 
    #字体加粗
    font1.bold = True
    style1.font = font1
    style1.alignment = alignment

    for i in range(columns):
        #设置列的宽度
        sheet.col(i).width = 5000

    #插入列名
    for i in range(columns):
        
        sheet.write(0,i,columnName[i],style1) 

    #将数据插入表格
    for i in range(1,rows):
        for j in range(columns):
            sheet.write(i,j,results[i].split('\t')[j],style)
    
    #保存表格，并命名为‘xxxx’户.xls
    wb.save(tableName)


#获取当前时间
excelTime = time.strftime("%Y%m%d") 

createTable(sqlStr1,excelTime+'逾期统计.xls')

 

转载于:https://www.cnblogs.com/vijayfly/p/6925020.html