fastapi 导出excel文件

  1 @fetch.get('/export')
  3 def fl_query(db: Session = Depends(get_db), bank_name=None, acc_code=None, file_type=None, prod_codes=None,
  4              prod_names=None, status=None, page=1, rows=10):
  5     header = ['账户号', '项目编码', '项目名称', "银行", "月份", "文件类型", "拉取状态", "获取备注", "最后获取时间"]
  6     """
  7     以流的形式导出到excel 14     """
 15 
 16     def set_style():
 17         """
 18         设置样式
 19         :return:
 20         """
 21         # 居中设置
 22         alignment = xlwt.Alignment()
 23         alignment.horz = xlwt.Alignment.HORZ_CENTER
 24         alignment.vert = xlwt.Alignment.VERT_CENTER
 25 
 26         # 设置表头字体样式
 27         head_style = xlwt.XFStyle()
 28         font = xlwt.Font()
 29         font.name = 'Times New Roman'  # 字体
 30         font.bold = True  # 字体加粗
 31         head_style.font = font  # 设置字体
 32         head_style.alignment = alignment  # Add Alignment to Style
 33 
 34         # 设置表中内容样式
 35         cont_style = xlwt.XFStyle()
 36         font = xlwt.Font()
 37         font.name = 'Times New Roman'  # 字体
 38         font.bold = False  # 字体加粗
 39         cont_style.font = font  # 设置字体
 40         cont_style.alignment = alignment  # Add Alignment to Style
 41 
 42         # 设置单元格边界
 43         borders = xlwt.Borders()
 44         borders.left = xlwt.Borders.THIN
 45         borders.right = xlwt.Borders.THIN
 46         borders.top = xlwt.Borders.THIN
 47         borders.bottom = xlwt.Borders.THIN
 48         head_style.borders = borders
 49         cont_style.borders = borders
 50         return head_style, cont_style
 51 
 52     def get_sheet(_book, _index):
 53         """
 54         创建sheet页
 55         :param _book:
 56         :param _index:
 57         :return:
 58         """
 59         _name = "sheet_{}".format(str(_index))
 60         _sheet = _book.add_sheet(_name)
 61         return _sheet
 62 
 63     def write_head(_head, _sheet, _head_style):
 64         """
 65         写入表头
 66         :param _head:
 67         :param _sheet:
 68         :param _head_style:
 69         :return:
 70         """
 71         for head in range(len(header)):
 72             context = str(header[head])
 73             need_width = (1 + len(context)) * 256
 74             table_sheet.col(head).width = need_width
 75             # table_sheet.write(0, head, context, style=_head_style)
 76             table_sheet.write(0, head, context, style=_head_style)
 77 
 78     sheet_index = 1
 79     book = xlwt.Workbook(encoding='utf-8')  # 创建 Excel 文件
 80     table_sheet = get_sheet(book, sheet_index)  # 添加sheet表
 81     h_style, c_style = set_style()
 82     write_head(header, table_sheet, h_style)
 83 
 84     # 获取数据
 85     fls = crud.get_fls_export(db, bank_name, acc_code, file_type, prod_codes, prod_names, status)
 86     # 插入数据
 87     row = 1
 88     for f_log in fls:
 89         table_sheet.write(row, 0, str(f_log.getAcc_code()), style=c_style)
 90         table_sheet.write(row, 1, str(f_log.getProd_code()), style=c_style)
 91         table_sheet.write(row, 2, str(f_log.getProd_name()), style=c_style)
 92         table_sheet.write(row, 3, str(f_log.getBank_name()), style=c_style) 98         row += 1
 99         if row > 50000:  # 单表数量超过 65535 条 添加新的表
100             row = 1
101             sheet_index += 1
102             table_sheet = get_sheet(book, sheet_index)  # 添加sheet表
103             write_head(header, table_sheet, h_style)
104     sio = BytesIO()  # 返回文件流到浏览端下载，浏览端必须以form提交方式方能下载成功！
105     book.save(sio)  # 这点很重要，传给save函数的不是保存文件名，而是一个StringIO流
106     sio.seek(0)  # 保存流
107     # 组装header
108     headers = {"content-type": "application/vnd.ms-excel", "content-disposition": 'attachment;filename=download.xls'}
109     # 以流的形式返回浏览器
110     return StreamingResponse(sio, media_type="text/xlsx", headers=headers)
这里有个坑点：filename 不支持中文。
参考文章：https://blog.csdn.net/weixin_37923411/article/details/115895139