[django视图下载工具]django如何将视图的数据形成excel表并返回给前端？（包括数据过滤）

思路

关键：django的视图本身返回的是json数据，而pandas本身就可以对json数据形成excel输出。所以，关键是拿到需要的json数据即可

突破点：无论是filterset还是serializers，都是支持手动使用的。

• 后端

def queryset_to_dataframe(queryset, serializer_cls, filterset_cls=None, filter_params=None, extra_rename_dict=None):
	if filter_params is None:
		filter_params = {}
	if extra_rename_dict is None:
		extra_rename_dict = {}
	if filterset_cls is not None:
		queryset = filterset_cls(
			filter_params,
			queryset
		).qs

	ser = serializer_cls(queryset, many=True)
	df = pd.DataFrame(ser.data)
	df.index += 1
	rename_dict = {
		field: queryset.model._meta.get_field(field).verbose_name
		for field in serializer_cls.Meta.fields
		if field in dir(queryset.model)
	}
	rename_dict.update(extra_rename_dict)
	df = df.rename(columns=rename_dict)

	return df


def queryset_to_excel_response(queryset,
							   serializer_cls,
							   filterset_cls=None,
							   filter_params=None,
							   extra_rename_dict=None,
							   prev_name='导出结果'):
	df = queryset_to_dataframe(queryset, serializer_cls, filterset_cls, filter_params, extra_rename_dict)
	io = BytesIO()
	df.to_excel(io)
	res = HttpResponse(io.getvalue(), content_type='application/octet-stream; charset=UTF-8')
	# filename如果有中文必需编码下，要不会造成下载失败
	filename = f'{prev_name}_{get_now_time("%Y-%m-%d_%H_%M_%S")}.xlsx'.encode('utf8').decode('ISO-8859-1')  # 为了保证中文名正常显示
	res['Content-Disposition'] = f'attachment; filename={filename}'

	return res



class DownloadUserView(APIView):  # 视图中使用
	def get(self, request):
		res = queryset_to_excel_response(
			UserInfoView.queryset,
			serializer_cls=UserInfoSerializer,
			filterset_cls=UserInfoFilterSet,
			filter_params=request.GET,
			extra_rename_dict={
				'version': '版本',
				'tab_page_title': '标签页',
				'developer_name': '开发人',
				'template_type_display': '模板类型',
				'requirement_document_files': '需求文档',
				'progress_display': '进度',
			},
			prev_name='用户信息',
		)

		return res

• 前端

exportExcel(){
	let searchParams = new URLSearchParams(this.searchForm)
	let url = `${this.axios.defaults.baseURL}/admin/export-excel?${searchParams}&token=${this.token}`  // 下载地址灵活变通
	window.open(url, '_blank')
}