python导出数据到excel

1，SMTP发送带excel附件的邮件：

def sendMail(filename, addressee):
    """
    :param content: 发送内容
    :param sendStr: 收件人
    :return: sendmail
    """
    msg = MIMEMultipart('related')
    msg["Subject"] = "表格导出"
    # msg["From"] = "***@qq.com"
    msg["To"] = addressee
    thebody = MIMEText(u'正文', 'plain', 'utf-8')
    msg.attach(thebody)
    att = MIMEText(open(u'%s.xls' % filename, 'rb').read(), 'base64', 'GB2312')#附件
    att['Content-Type'] = 'application/vnd.ms-excel'
    att['Content-Disposition'] = 'attachment; filename ="%s.xls"' % filename

    #xlsx格式
    # att = MIMEText(open(u'%s.xlsx' % filename, 'rb').read(), 'base64', 'utf-8')
    # att['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    # att['Content-Disposition'] = 'attachment; filename ="%s.xlsx"' % filename
    msg.attach(att)

    server = smtplib.SMTP_SSL("smtp.qq.com", 465)
    server.login("***@qq.com", "***")#第二个参数为邮件配置密钥
    server.sendmail("***@qq.com", addressee, msg.as_string())
    server.quit()

2，生成excel发送邮件并下载到本地客户端：

#views.py
def export_to_excel(request):
    data = [{'a': '1','b':'1'},{'a':'2','b':'2'}]
    filename = 'export'
    addressee = '***@***.com'
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet('sheet1', cell_overwrite_ok=True)
    for i in range(0, len(data[0])):
        sheet.write(0, i, data[0].keys()[i])
    for row in range(1, len(data) + 1):
        for col in range(0, len(data[0].items())):
            sheet.write(row, col, u'%s' % data[row - 1].values()[col])
    print data[0].keys()
    workbook.save(r"%s.xls" % filename)
    sendMail(filename, addressee)
    sio = StringIO.StringIO()  # StringIO用来作为字符串的缓存，有些接口和文件操作一致，代码可以同时当成文件操作或者StringIO操作。
    workbook.save(sio)
    sio.seek(0)
    response = HttpResponse(sio.getvalue(),content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename=%s.xls' % filename

    #xlsx文件，出现拓展名的地方都要改为xlsx
    #response = HttpResponse(sio.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    #response['Content-Disposition'] = 'attachment; filename=%s.xlsx' % filename

    response.write(sio.getvalue())
    exist_file = os.path.exists(r"%s.xls" % filename)    
    if exist_file:    #生成的文件会在项目文件里存留，所以在下载或者发送之后要删除
        os.remove(r"%s.xls" % filename)
        return response

python导出数据到excel

推荐阅读