Django 使用xlwt实现从后台mysql导出数据到excel表

1. pip install xlwt   #安装xlwt 模块

2. 新建excel.py

import xlwt
#from io import StringIO
from django.db import connection
from django.shortcuts import HttpResponse
try:
    import cStringIO as stringIOModule
except ImportError:
    try:
        import StringIO as stringIOModule
    except ImportError:
        import io as stringIOModule

#from MySQLdb.cursors import DictCursor

#conn = MySQLdb.connect(host='192.168.2.4', user='root', passwd='zj88friend', db='zz91crm', port=3306, charset='utf8',
 #                      cursorclass=MySQLdb.cursors.DictCursor)  # 其中cursorclass设定返回数据类型为字典
#cur = conn.cursor()  # 获取游标

def exc_sql(sql):

    cursor = connection.cursor()

    cursor.execute(sql)

    result = cursor.fetchall()

    return result


def export_xls(request):

    response = HttpResponse(content_type='application/vnd.ms-excel')  # 指定返回为excel文件
    response['Content-Disposition'] = 'attachment;filename=export_list.xls'  # 指定返回文件名
    wb = xlwt.Workbook(encoding='utf-8')  # 设定编码类型为utf8
    hosttype_id1 = 1  # workstation
    hosttype_id2 = 2    #laptop
    hosttype_id3 = 3    #desktop
    hosttype_id4 = 4    #server
    hosttype_id5 = 5    #switch

    host_status1 = "in use"   # was using by users
    host_status2 = "inventory"  # in space , no user
    host_status3 = "P-Dispose"  # apply for disposal,waiting for approval
    host_status4 = "disposed"   # disposed , but it is till in use

    if request.method=='GET':
        id=request.GET.get('id')
        if id == '1':
            sql = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) , \
             TIMESTAMPDIFF(MONTH,p.receive_date,CURDATE())%%12 ,u.full_name , d.dept_name,p.studio, s.sitename as location,p.host_status, \
             p.asset_code,p.remark from deviceman_pc_list as p, deviceman_user_list as u, deviceman_dept_list as d, deviceman_hosttype as h, deviceman_site as s \
              where p.user_list_id=u.id and u.dept_list_id=d.id and p.site_id=s.id and p.hosttype_id=h.id and ( hosttype_id ='%s' or hosttype_id='%s' or hosttype_id= '%s' ) \
               and (host_status = '%s' or host_status='%s' )order by h.id, p.host_name """ % (hosttype_id1, hosttype_id2, hosttype_id3, host_status1, host_status2)
            sheet = wb.add_sheet(u'01 Deployed')  # excel里添加类别
        if id == '2':

            sql = """select p.host_name ,p.service_tag,h.name , p.host_model,p.receive_date , CURDATE(),TIMESTAMPDIFF(YEAR,p.receive_date,CURDATE()) , \
                         TIMESTAMPDIFF(MONTH,p.receive_date,CURDATE())%%12 ,u.full_name , d.dept_name,p.studio, s.sitename as location,p.host_status, \
                         p.asset_code,p.remark from deviceman_pc_list as p, deviceman_user_li