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
