使用reportlab生成每日报表

最近公司一个产品发布了，需要每天汇报产品使用情况，具体做法是每天从数据库中查询数据，然后用excel表整理数据并生成相关图表，最后通过邮件发送给相关干系人。一开始我是全手工去做，后来觉得实在太麻烦了，想把整个过程做成自动化，在网上搜了下reportlab可以用来生成pdf文件，这样就好办了，每天早上10点自动运行一个定时任务：从数据库中查询数据，自动生成pdf文件，最后将pdf文件通过邮件发送出去。这样就省去我手工填数据的麻烦了。（我也是个new beginner，但是想把这个过程记录下来）

一、需要用到的知识

1.python基础语法(如：list tuple的应用)
2.python数据库操作（如：数据库连接，数据查询）
3.mysql基础知识（如：在数据库中添加删除或查询数据）
4.python邮件知识（如：填写主题、添加收件人、添加附件）
5.reportlab（如：生成表格、饼图、柱状图、线性图等）

二、安装

1.Python2.7
2.reportlab-3.3.0
3.mysql-5.6.29
4.mysqldb-1.2.5

三、注意

reportlab对中文需要进行特殊支持，这里需要注意。

四、在mysql数据库中添加数据

五、数据库操作

这里主要进行连接数据库、查询数据、关闭数据库连接。

#coding=utf-8
import MySQLdb

class dbexecute():
    def __init__(self):
        pass
    def select(self,sql):
        self.sql=sql
        self.conn=MySQLdb.connect(host='127.0.0.1',port=3306,user='root',passwd='',db='test',charset='utf8')
        self.cur=self.conn.cursor()
        aa=self.cur.execute(self.sql)
        info=self.cur.fetchmany(aa)
        return info
    def close(self):
        if(self.conn):
            try:
                if(type(self.cur)=='object'):
                    self.cur.close()
                if(type(self.conn)=='object'):
                    self.conn.close()
            except Exception,data:
                self.logger.warn("close database exception, %s,%s,%s" % (data, type(self._cursor), type(self._conn)))

六、组装数据

#encoding=utf-8
from dbexecute import *
from reportlab.platypus import Paragraph
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.pdfbase.ttfonts import TTFont
from reportlab.pdfbase import pdfmetrics
pdfmetrics.registerFont(TTFont('xinwei','STXINWEI.TTF'))
class getdatas():
    def __init__(self):
        pass
    #商店销售数量
    def get_shopcounts(self):
        styles=getSampleStyleSheet()
        styleT=styles['Title']
        styleT.fontName='xinwei'
        styleT.fontSize=20
        db=dbexecute()
        info=db.select("select shopname,sum(count) as counts  from testtable group by shopname order by counts desc;")
        datas=[]
        shop=[]
        counts=[]
        for i in info:
            shop.append(Paragraph(i[0],styleT))
            counts.append(int(i[1]))
        datas.append(shop)
        datas.append(counts)
        db.close()
        return datas
    def get_datecounts(self):
        db=dbexecute()
        info=db.select("select date,sum(count) as counts from testtable group by date order by counts desc;")
        datas=[]#组装成该格式：[['2016-07-03 00:00:00', '2016-06-23 10:08:31'], [1000, 500]]
        date=[]
        counts=[]
        for i in info :
            date.append(str(i[0]))
            counts.append(int(i[1]))
        datas.append(date)
        datas.append(counts)
        db.close()
        return datas

if __name__=="__main__":
    data=getdatas()
    data.get_shopcounts()
    data.get_datecounts()

七、PDF排版

#encoding=utf-8
from reportlab.platypus import PageTemplate,Frame
from reportlab.lib.units import inch
class MyPageTemp(PageTemplate):
    def __init__(self):
        self.width=9*inch
        self.hight=12*inch
        Frame1=Frame(inch,10*inch,4*inch,inch,id='frame1',showBoundary=1)#商店销售报告标题
        Frame2=Frame(inch,9*inch,4*inch,inch,id='frame2',showBoundary=1)#商店销售数据表
        Frame3=Frame(inch,5*inch,4*inch,4*inch,id='frame3',showBoundary=1)#日期销售数据柱状图
        PageTemplate.__init__(self,'MyTemplate',[Frame1,Frame2,Frame3])

八、图表类

#encoding=utf-8
from reportlab.platypus import Table,TableStyle,SimpleDocTemplate,Paragraph,Spacer
from reportlab.lib import colors
from reportlab.lib.units import inch
from reportlab.lib.pagesizes import  letter
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.graphics.shapes import Drawing
from reportlab.graphics.charts.barcharts import VerticalBarChart
from reportlab.pdfbase.ttfonts import TTFont
from reportlab.pdfbase import pdfmetrics
pdfmetrics.registerFont(TTFont('xinwei','STXINWEI.TTF'))
class graphs():
    def __init__(self):
        self.styles=getSampleStyleSheet()
        self.styleT=self.styles['Title']
        self.styleT.fontName='xinwei'
        self.styleT.fontSize=20
    def drawtable(self,*args):
        table=Table(args)
        table.setStyle(TableStyle([('INNERGRID',(0,0),(-1,-1),0.25,colors.black),('BOX',(0,0),(-1,-1),0.25,colors.black)]))
        return table

    def drawbar(self,width,height,*args):
        drawing=Drawing(width,height)
        bc=VerticalBarChart()
        bc.x=0
        bc.y=0
        bc.height=height
        bc.width=width
        bc.data=[args[1]]
        bc.strokeColor=colors.black
        bc.valueAxis.valueMin=0
        bc.valueAxis.valueMax=1000
        bc.valueAxis.valueStep=200
        bc.categoryAxis.labels.boxAnchor='ne'
        bc.categoryAxis.labels.dx=0
        bc.categoryAxis.labels.dy=0
        bc.categoryAxis.labels.angle = 30
        bc.categoryAxis.categoryNames = args[0]
        drawing.add(bc)
        return drawing
if __name__=="__main__":
    graphs=graphs()
    story=[]
    p1=Paragraph("产品一",graphs.styleT)
    p2=Paragraph("产品二",graphs.styleT)
    data= [[p1,2700],[p2,1200]]
    #table1
    table=graphs.drawtable(*data)
    story.append(table)
    story.append(Spacer(inch,inch))
    s1=Paragraph("商店一",graphs.styleT)
    s2=Paragraph("商店二",graphs.styleT)
    data= [[s1,2700],[s2,200]]
    #table2
    story.append(table)
    story.append(Spacer(inch,inch))
    table=graphs.drawtable(*data)
    #bar1
    data=[['2016-07-03','2016-06-14','2016-07-13','2016-07-01','2016-06-23','2016-06-13'],[1000,500,400,200,500,300]]
    bar1=graphs.drawbar(4*inch,4*inch,*data)
    story.append(bar1)
    doc=SimpleDocTemplate('graphs.pdf',pagesize=letter)
    doc.build(story)

八、生成PDF文件

通过查询到的数据库数据生成表格、饼图柱状图线性图等，最后做为报表生成PDF文件。

#encoding=utf-8
from reportlab.pdfbase.ttfonts import TTFont
from reportlab.pdfbase import pdfmetrics
pdfmetrics.registerFont(TTFont('xinwei','STXINWEI.TTF'))
from pagetemp import MyPageTemp
from graphs import *
from reportlab.platypus import BaseDocTemplate,Paragraph,PageTemplate,FrameBreak
from reportlab.lib.units import inch
from reportlab.lib.styles import getSampleStyleSheet
from getdatas import *
from sendmails import *
class MyPDFdoc():
    def __init__(self,filename):
        self.mypage=MyPageTemp()
        self.filename=filename
        self.height=12*inch
        self.width=9*inch
        self.doc=BaseDocTemplate(self.filename,pagesize=(9*inch,12*inch))
        self.doc.addPageTemplates(self.mypage)
        self.styles=getSampleStyleSheet()
        self.styleT=self.styles['Title']
        self.styleT.fontName='xinwei'
        self.styleT.fontSize=20
    def drawPDF(self):
        stories=[]
        stories.append(Paragraph("商店销售报告",self.styleT))
        stories.append(FrameBreak())
        #商店销售数据
        data=getdatas().get_shopcounts()
        t1=graphs().drawtable(*data)
        stories.append(t1)
        stories.append(FrameBreak())
        #不同日期销售数据
        data=getdatas().get_datecounts()
        b1=graphs().drawbar(4*inch,3*inch,*data)
        stories.append(b1)
        self.doc.build(stories)
if __name__=="__main__":
    pdf=MyPDFdoc('mypdf.pdf')
    pdf.drawPDF()
    sendemail('mypdf,pdf')

九、发送邮件

添加收件人、主题、附件

#encoding=utf-8
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
def sendemail(filename):
    file_new=filename
    sender = 'xxx@xxx.com'#发送邮件
    receiver = 'xxxx@qxxx.com'#接收邮件
    subject = 'python email test'
    smtpserver ='smtp.qiye.163.com'
    username = 'xxx@xxx.com'
    password = 'xxxxxx'
    msgRoot = MIMEMultipart('related')
    msgRoot['Subject'] = u'商店销售活动日报'
#构造附件
    att = MIMEText(open('mypdf.pdf', 'rb').read(), 'base64', 'utf-8')
    att["Content-Type"] = 'application/octet-stream'
    att["Content-Disposition"] = 'attachment; filename="mypdf.pdf"'
    msgRoot.attach(att)

    smtp = smtplib.SMTP()
    smtp.connect('smtp.qiye.163.com')
    smtp.login('xxx@xxx.com', 'xxxx')
    smtp.sendmail(sender, receiver, msgRoot.as_string())
    smtp.quit()
    print 'email has send out !'

if __name__=="__main__":
    sendemail('mypdf.pdf')