数据库python前端_python django前端界面实现数据库数据excel导出

最近在做前端界面时遇到一个需求，要在前端界面实现数据库数据的excel导出，经过多方查阅资料终于找到解决方法！

下面贴出导出excel函数的代码片段。

# -*- coding: utf-8 -*-

import MySQLdb

import xlwt

import StringIO

from django.shortcuts import HttpResponse

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 export_xls(request):

sql='select id,label from category'

conn.cursor().execute(sql)#执行sql语句

conn.cursor().fetchall()#获取查询结果

response = HttpResponse(content_type='application/vnd.ms-excel')#指定返回为excel文件

response['Content-Disposition'] = 'attachment;filename=export_agencycustomer.xls'#指定返回文件名

wb = xlwt.Workbook(encoding = 'utf-8')#设定编码类型为utf8

sheet = wb.add_sheet(u'类别')#excel里添加类别

sheet.write(0,0, 'id')

sheet.write(0,1, '名字')

row = 1

for list in result:

sheet.write(row,0, list['id'])

sheet.write(row,1, list['label'])

row=row + 1

output = StringIO.StringIO()

wb.save(output)

output.seek(0)

response.write(output.getvalue())

return response