正文
Python/Django 下载Excel2003
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
一、安装
目前支持Excel2003的第三方库多少还有几个,本文使用的是xlwt,安装方式命令行:pip install xlwt
二、使用
首先、引入该库,例如:from xlwt import *
其次、
创建Excel文件,如:workbook = Workbook(encoding = 'utf-8')
创建Excel sheet,如:worksheet = workbook.add_sheet("Summary"),即sheet名称为Summary
常用方式:
设置列宽:worksheet.col(0).width = 10000,即设置第一列宽度为10000,Excel文件的行列索引均从0开始
写数据:worksheet.write(row, column, domain_title[column], style.head), 写入第row+1行,column+1列的单元格数据 domain_title[column],设置的格式style.head
此处格式的定义如:
head = easyxf("""
font:
name Arial,
colour_index white,
bold on,
height 0xA0;
align:
wrap off,
vert center,
horiz center;
pattern:
pattern solid,
fore-colour 0x19;
borders:
left THIN,
right THIN,
top THIN,
bottom THIN;
""")
body = easyxf("""
font:
name Arial,
bold off,
height 0XA0;
align:
wrap on,
vert center,
horiz left;
borders:
left THIN,
right THIN,
top THIN,
bottom THIN;
""")
以上为一些设置的格式,使用xlwt的类EasyXF创建。
获取某个sheet, 如:worksheet = workbook.get_sheet("Summary"),则可获取sheet 名称为Summary的sheet
第三、保存输出
输出到Response流:
out = BytesIO()
workbook.save(out)
out.seek(0)
response = HttpResponse(out.getvalue(), content_type = 'application/vnd.ms-excel')
dt = datetime.datetime.now()
response['Content-Disposition'] = 'attachment;filename={} {}.xls'.format(urlquote(domain_name), dt.strftime('%Y-%m-%d %H:%M:%S'))
print("end downloading...")
return response
以上,值得一提的是,如果你的Excel文件的名称含有中文,请使用urlquote包装一下,这样下载下来的Excel文件中文名才能正常显示,上面也是因为文件名含有中文才使用的这个函数的;引入的方式:from django.utils.http import urlquote
至此、下载完成。
贴下代码:
# coding: UTF-8from io import BytesIOimport pandas
from django.http import HttpResponse, StreamingHttpResponse
from django.utils import timezone as datetime
from django.utils.http import urlquote
from xlwt import *from automation import style
from automation.ecarxzip import ZipFile
from automation.tasks import *
from web.settings import DOWNLOAD_URLdef download_excel(request, task_id, domain_name = '全部'):
print("start downloading xls...", task_id) domains = [{'domain_name': domain_name}]
ai_task = AITask.objects.get(id = task_id)
if '全部' == domain_name:
if 1 == ai_task.type:
domains = Classification.objects.values('domain_name').distinct().filter(type = 1)
elif 2 == ai_task.type:
domains = Classification.objects.values('domain_name').distinct().filter(type = 2)
else:
pass summaries = []
summary_title = ["Domain", "Pass", "Fail"]
domain_title = ['Domain', 'One level', 'Two level', 'Semantic', 'Priority', 'Intent group', 'Intent', 'Result',
'Handle time', 'Response time', 'Server Domain', 'Detail'] workbook = Workbook(encoding = 'utf-8')
worksheet = workbook.add_sheet("Summary")
for column in range(len(summary_title)):
worksheet.write(0, column, summary_title[column], style.head) for domain in domains:
dmain_name = domain["domain_name"]
reports = ai_task.report.filter(semantic__classification__domain_name__exact = dmain_name)
if len(reports):
row = pass_no = fail_no = 0
worksheet = workbook.add_sheet(dmain_name)
worksheet.col(3).width = worksheet.col(5).width = worksheet.col(6).width = 7000
worksheet.col(11).width = 10000
for column in range(len(domain_title)):
worksheet.write(row, column, domain_title[column], style.head) for report in reports:
row = row + 1
semantic = report.semantic
classification = semantic.classification
worksheet.write(row, 0, classification.domain_name, style.body)
worksheet.write(row, 1, classification.first_classification, style.body)
worksheet.write(row, 2, classification.second_classification, style.body)
worksheet.write(row, 3, semantic.name, style.body)
worksheet.write(row, 4, classification.semantic_property, style.body)
worksheet.write(row, 5, classification.intent_group, style.body)
worksheet.write(row, 6, classification.intent, style.body)
worksheet.write(row, 7, report.result, style.body)
worksheet.write(row, 8, report.in_handle_time, style.body)
worksheet.write(row, 9, report.ex_handle_time, style.body)
worksheet.write(row, 10, report.server_domain, style.body)
worksheet.write(row, 11, report.description[:32767], style.large_text) if "pass" == report.result:
pass_no = pass_no + 1
elif "fail" == report.result:
fail_no = fail_no + 1
summaries.append((dmain_name, pass_no, fail_no)) row = 0
worksheet = workbook.get_sheet("Summary")
for dmain_name, pass_no, fail_no in summaries:
row = row + 1
worksheet.write(row, 0, dmain_name, style.body)
worksheet.write(row, 1, pass_no, style.body)
worksheet.write(row, 2, fail_no, style.body) out = BytesIO()
workbook.save(out)
out.seek(0)
response = HttpResponse(out.getvalue(), content_type = 'application/vnd.ms-excel')
dt = datetime.datetime.now()
response['Content-Disposition'] = 'attachment;filename={} {}.xls'.format(urlquote(domain_name), dt.strftime('%Y-%m-%d %H:%M:%S'))
print("end downloading...")
return response