Django Series(Django2.1.2 + Anaconda3)
(一)安装并配置 Django 环境 ||| 基于 Django 进行 Web 开发
(四)数据的增删改:用户提交数据,验证数据的有效性并传输至后台(jQuery.post、jQuery.getJSON)
(五)基于 "xlsxwriter + BytesIO"(Python3)生成 Excel 报表 ||| Python2 StringIO.StringIO()
说明:本系列教程根据最近实践过程进行整理、总结和分享。由于时间和精力有限,发表时内容分析部分可能不是很完整,后续有时间会慢慢补充。同时!!也希望感兴趣的同学可以提出一些细节问题和建议,我会根据这些问题进一步整理和完善哈。
更新日志:
20181019:发表第一版,主要以代码分享为主;知识点分析较为粗略。
知识点分析:
实现代码:
Python3 + Django2.1.2
views.py
## 导出excel报表
@csrf_exempt
def Cur_ProjectsReport(request):
try:
import xlsxwriter
output = BytesIO()
workbook = xlsxwriter.Workbook(output)
target_sheet1 = workbook.add_worksheet(u"XXXX现有项目情况")
target_sheet1.merge_range(0, 0, 0, 0+10, u"XXXX现有项目情况报表")
## title
target_sheet1.write(1, 0, u"工作令号")
target_sheet1.write(1, 1, u"项目名称")
target_sheet1.write(1, 2, u"是否结项")
target_sheet1.write(1, 3, u"合同日期")
target_sheet1.write(1, 4, u"合同金额")
## table
projects_list = Cur_Projects.objects.all()
i = 2
for project in projects_list:
target_sheet1.write(i, 0, u"{}".format(project.proj_id))
target_sheet1.write(i, 1, u"{}".format(project.proj_name))
target_sheet1.write(i, 2, u"{}".format(project.progress))
target_sheet1.write(i, 3, u"{}".format(project.contract_date.strftime("%Y%m%d")))
target_sheet1.write(i, 4, u"{}".format(project.contract_cost))
i += 1
date_day = datetime.datetime.now().strftime('%Y%m%d')
file_name = "ProjectReport{}_{}.xlsx".format(date_day, request.session.get("UserName"))
workbook.close()
xlsx_data = output.getvalue()
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=%s' % file_name
response.write(xlsx_data)
hcq_write(request.session.get('log_file_path'), True, True, "[{}({})]【成功】导出现有项目报表:{}".format(
request.session.get('UserName'), request.session.get('RoleTypeID'), file_name))
return response
except Exception as e:
hcq_write(request.session.get('log_file_path'), True, True, "[{}({})]【异常】导出现有项目报表:{}".format(
request.session.get('UserName'), request.session.get('RoleTypeID'), e))
response = HttpResponse()
response.write("failure")
return response
xxx.html
<a class="btn btn-default" type="button" href="{% url 'app_ManageSystem:Cur_ProjectsReport' %}">生成报表</a>
Python2 + Django1.6.11
Python2 直接 imort StringIO 模块,调用 StringIO.StringIO()。但 Python3 已经对 StringIO 模型进行修改,如下方法在Python2能够成功导出excel报表,但在Python3环境下会报错。
## 项目出货:导出excel报表
@csrf_exempt
def ProjectSaleReport(request, proj_id):
import xlwt
import StringIO
print("proj_id = {}".format(proj_id))
try:
## 获取内容
if Proj_info.objects.filter(proj_id = proj_id).exists():
proj_name = Proj_info.objects.get(proj_id = proj_id).proj_name
else:
proj_name = "没有获取到项目名称"
ProjectSale_list = ProjectSale_info.objects.filter(proj_id = proj_id).order_by("-sale_goods_type")
date_day = datetime.datetime.now().strftime('%Y%m%d')
## 写入 excel
ws = xlwt.Workbook(encoding='utf-8')
target_sheet = ws.add_sheet(u"{}项目出货报表".format(proj_name))
'''
- 合并单元格
write_merge(x, x + h, y, w + y, string, sytle)
x表示行,y表示列,w表示跨列个数,h表示跨行个数,string表示要写入的单元格内容,style表示单元格样式
注意,x,y,w,h,都是以0开始计算的
'''
target_sheet.write_merge(0, 0, 0, 0+4, u"工程名称:{}".format(proj_name))
target_sheet.write_merge(0, 0, 7, 7+2, u"出货编号:{}".format(proj_id))
target_sheet.write_merge(1, 1+0, 7, 7+2, u"导出日期:{}".format(date_day))
## title
target_sheet.write(2, 0, u"序号")
target_sheet.write(2, 1, u"收费项目")
target_sheet.write(2, 2, u"品名")
target_sheet.write(2, 3, u"单位")
target_sheet.write(2, 4, u"数量")
target_sheet.write(2, 5, u"人工费单价")
target_sheet.write(2, 6, u"人工费总价")
target_sheet.write(2, 7, u"报价")
target_sheet.write(2, 8, u"收入金额(元)")
target_sheet.write(2, 9, u"备注")
## write content
index = 1
i = 3
for ProjectSale in ProjectSale_list:
target_sheet.write(i, 0, u"{}".format(index))
target_sheet.write(i, 1, u"{}".format(ProjectSale.sale_goods_type))
target_sheet.write(i, 2, u"{}".format(ProjectSale.sale_goods_name))
target_sheet.write(i, 3, u"{}".format(ProjectSale.sale_unit))
target_sheet.write(i, 4, u"{}".format(ProjectSale.sale_number))
target_sheet.write(i, 5, u"{}".format(ProjectSale.staff_unit_price))
target_sheet.write(i, 6, u"{}".format(ProjectSale.staff_cost))
target_sheet.write(i, 7, u"{}".format(ProjectSale.sale_unit_price))
target_sheet.write(i, 8, u"{}".format(ProjectSale.sale_income))
target_sheet.write(i, 9, u"{}".format(ProjectSale.note))
i += 1
index += 1
file_name = proj_name + "_" + date_day + ".xls"
file_path = "./report/" + file_name
print(file_path)
if os.path.exists(file_path):
os.remove(file_path)
ws.save(file_path)
sio = StringIO.StringIO()
ws.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(), content_type='text/html')
response['Content-Disposition'] = 'attachment; filename=' + file_name.encode('GBK')
response.write(sio.getvalue())
print("[successful]: ProjectSaleReport")
return response
except Exception as e:
print("[failure]: ProjectSaleReport - {}".format(e))
response = HttpResponse()
response.write("failure")
return response