最近在做前端界面时遇到一个需求,要在前端界面实现数据库数据的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