Python数据统计导出excel

需求就是统计每天客户端开机数据，将开机率低于30%的数据导出，每天发送邮件给相关业务人员。

• 查询数据
• 整理excel
• 发送邮件

一、数据查询方法（此项可根据实际业务需求更改）

 def getUpRate(project_id):
       val = str(1)
       sql = "select rateNum from tablename where project_id=" + str(project_id)
       cursor.execute(sql)
       rateNum = cursor.fetchone()
       if type(rateNum) != tuple :
               insert_sql = "insert into tablename (project_id,rateNum) values('"+ str(project_id) + "','" + val + "')"
               cursor.execute(insert_sql)
               db.commit()
               return val
       else:
               i = rateNum[0]+1
               update_sql = "update tablename set rateNum="+str(i)+" where project_id=" + str(project_id)
               cursor.execute(update_sql)
               db.commit()
               return i

二、数据生成excel

#create excel
s = 0;
wb = xlwt.Workbook(encoding = 'utf-8')
sh = wb.add_sheet('hotel')
sh.write(0,0,'project_id')
sh.write(0,1,'项目名称')
sh.write(0,2,'设备总数')
sh.write(0,3,'开机数')
sh.write(0,4,'异常累计天数')
for project in res:
       s=s+1
       err_num = getUpRate(project[1])
       sh.write(s,0,project[1])
       sh.write(s,1,project[2])
       sh.write(s,2,project[3])
       sh.write(s,3,project[4])
       sh.write(s,4,err_num)
wb.save("example-"+ Yesterday + ".xls")
sendMail()  #发送邮件方法，下方详情

三、发送邮件方法介绍

def sendMail():
        host_server = 'smtp.qq.com' #根据实际情况写
        sender_qq = '123@qq.com' #根据实际情况写
        pwd = '123@123'  #根据实际情况写

        #发件人的邮箱
        sender_qq_mail = '123@qq.com' #根据实际情况写
        #收件人邮箱
        receiver = '456@qq.com'#根据实际情况写
        #邮件的正文内容
        mail_content = "你好，<p>每日统计项目数据，请查收附件</p>"
        #邮件标题
        mail_title = 'xxx数据统计'
        #邮件正文内容
        msg = MIMEMultipart()
        #msg = MIMEText(mail_content, "plain", 'utf-8')
        msg["Subject"] = Header(mail_title, 'utf-8')
        msg["From"] = sender_qq_mail
        msg["To"] = Header("运维通知邮件组", 'utf-8') ## 接收者的别名
        #邮件正文内容
        msg.attach(MIMEText(mail_content, 'html', 'utf-8'))
        # 构造附件1，传送当前目录下的 test.txt 文件
        att1 = MIMEText(open("example-"+Yesterday+".xls", 'rb').read(), 'base64', 'utf-8')
        att1["Content-Type"] = 'application/octet-stream'
        # 这里的filename可以任意写，写什么名字，邮件中显示什么名字
        att1["Content-Disposition"] = 'attachment; filename="example.xls"'
        msg.attach(att1)
        #ssl登录
        smtp = SMTP_SSL(host_server)
        #set_debuglevel()是用来调试的。参数值为1表示开启调试模式，参数值为0关闭调试模式
        smtp.set_debuglevel(1)
        smtp.ehlo(host_server)
        smtp.login(sender_qq, pwd)
        smtp.sendmail(sender_qq_mail, receiver, msg.as_string())
        smtp.quit()

导出excel如遇到下图报错：
1.排查编码 #coding:utf-8 sys.setdefaultencoding('utf8') 等
2.写入第一行数据的中文字符，或者字符串需要有引号，忽略会报错。

Traceback (most recent call last):
  File "excel.py", line 19, in <module>
    wb.save('example.xls')
  File "/usr/lib/python2.7/site-packages/xlwt/Workbook.py", line 643, in save
    doc.save(filename, self.get_biff_data())
  File "/usr/lib/python2.7/site-packages/xlwt/Workbook.py", line 618, in get_biff_data
    shared_str_table   = self.__sst_rec()
  File "/usr/lib/python2.7/site-packages/xlwt/Workbook.py", line 580, in __sst_rec
    return self.__sst.get_biff_record()
  File "/usr/lib/python2.7/site-packages/xlwt/BIFFRecords.py", line 77, in get_biff_record
    self._add_to_sst(s)
  File "/usr/lib/python2.7/site-packages/xlwt/BIFFRecords.py", line 92, in _add_to_sst
    u_str = upack2(s, self.encoding)
  File "/usr/lib/python2.7/site-packages/xlwt/UnicodeUtils.py", line 50, in upack2
    us = unicode(s, encoding)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe9 in position 0: ordinal not in range(128)