Moment For Technology

Flask export data and write it to Excel to realize file report export

Posted on Dec. 2, 2022, 7:48 p.m. by Dr. Russell Brown
Category: The back-end Tag: The back-end

Flask import data and write it to Excel. Welcome to learn!!

@course_blu.route('/download') def download(): data = request.args search_date = data.get('searchDate') if data.get('searchDate') else '' teacher = data.get('teacher')  if data.get('teacher') else '' cur_date = get_cur_date() tmp_date_m = '-'.join(cur_date.split('-')[:-1]) lessons = Lesson.query.filter( Lesson.ClassStatus == '1', Lesson.classDay.like(f'{search_date if search_date else tmp_date_m}%'), Lesson.teacher.like(f'%{teacher if teacher else ""}%'), ).order_by(Lesson.classDay).all() data = [i.to_json() for I in lessons] # Create IO object output = BytesIO() # Write Excel workbook = Xlsxwriter.workbook (output) Sheet = workbook. add_sheet1 ('sheet1') fileds = [' type ', 'Class Name ',' Direction ', 'Period ',' Class ', 'Class Date ',' Period ', 'Instructor ', Sheet. write_row('A1', fileds) # for I in range(len(data)) For x in range(len(fileds)): Key = [key for key in data[I].keys()] sheet.write(I + 1, x, data[I][key[x]]) # current_app.logger.info(' current row: {} current column: {} data: {}'.format(str(i), str(x), Data [I][key[x]])) workbook.close() # To close output.seek(0) # to find the start of stream resp = make_response(output.getValue ()) filename = Headers [" content-disposition "] = f"attachment; get_cur_datetime() basename = f'{filename}.xlsx'; filename={basename}" resp.headers['Content-Type'] = 'application/x-xlsx' return respCopy the code

Flask provides send_file for exporting files. Here is an example

  • The routing file
@con.route("/exportExcel", methods=["GET", "POST"]) def export_excel_inter(): """ return export_excel()Copy the code
  • implementation
import time from io import BytesIO from flask import send_file from openpyxl import Workbook from loguru import logger def export_excel(): "" wb = Workbook() sheet = wb.create_sheet(" report ") # Excel data processing and setting style excel_data_deal(sheet) # Use byte stream to store output = BytesIO() # save wb.save() # seek () Logger.info ("{} - {} b".format(filename, Len (output.getValue ()))) # as_attachment) : add content-attachment # attachment_filename: File name used for download # conditional: Whether resumable transmission is supported fv = send_FILE (output, as_attachment=True, attachment_filename=filename, conditional=True) fv.headers['Content-Disposition'] += "; filename*=utf-8''{}".format(filename) fv.headers["Cache-Control"] = "no_store" fv.headers["max-age"] = 1 Logger. The info (" export report -- -- -- -- -- -- -- -- -- % s "% filename) return fvCopy the code
  • Matters needing attention
"" Here openPyXL is used to create files instead of XLWT, mainly because XLWT supports upload files with a size limit, More than will be submitted to the following error "' File"/usr/local/lib/python3.8 / site - packages/XLWT/UnicodeUtils. Py ", line 55, in upack2 raise Exception('String longer than 32767 characters') Exception: Flask: the default cache time of static files is 12 hours. In case the exported files are not previously cached, it is recommended to clear the cache. From datetime import timedelta app.config['SEND_FILE_MAX_AGE_DEFAULT'] = timedelta(seconds=1) # Flask default configuration source default_config = ImmutableDict({"ENV": None, "DEBUG": None, "TESTING": False, "PROPAGATE_EXCEPTIONS": None, "PRESERVE_CONTEXT_ON_EXCEPTION": None, "SECRET_KEY": None, "PERMANENT_SESSION_LIFETIME": timedelta(days=31), "USE_X_SENDFILE": False, "SERVER_NAME": None, "APPLICATION_ROOT": "/", "SESSION_COOKIE_NAME": "session", "SESSION_COOKIE_DOMAIN": None, "SESSION_COOKIE_PATH": None, "SESSION_COOKIE_HTTPONLY": True, "SESSION_COOKIE_SECURE": False, "SESSION_COOKIE_SAMESITE": None, "SESSION_REFRESH_EACH_REQUEST": True, "MAX_CONTENT_LENGTH": None, "SEND_FILE_MAX_AGE_DEFAULT": "TRAP_BAD_REQUEST_ERRORS": None, "TRAP_HTTP_EXCEPTIONS": False, "EXPLAIN_TEMPLATE_LOADING": False, "PREFERRED_URL_SCHEME": "http", "JSON_AS_ASCII": True, "JSON_SORT_KEYS": True, "JSONIFY_PRETTYPRINT_REGULAR": False, "JSONIFY_MIMETYPE": "application/json", "TEMPLATES_AUTO_RELOAD": None, "MAX_COOKIE_SIZE": 4093, } )Copy the code
Search
About
mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.