flask 导出excel

Flask 导出Excel 的两种方法 ，第一种使用Flask-Excel

安装：

pip install Flask-Excel
pip install pyexcel-xls
pip install pyexcel-xlsx
pip install pyexcel-ods

除了安装主要的Falsk-Excel，还需要安装一些格式的扩展，需要导出什么格式就安装什么扩展

使用:

#extendsions.py
import flask_excel as excel


#__init__.py
from hifeiji.extendsions import excel
excel.init_excel(app)

#blueprint
#activity.py
import flask_excel as excel


@activity_bp.route("/export", methods=['GET'])
@login_required
def export_records():
    content = [['No','Title','Name','Tel','Start','End','Service Time']]

    activityList = ActivityAtten.query.order_by(ActivityAtten.activity_start.asc()).all()
    if activityList:
        for value in activityList:
            new_content = []
            new_content = [value.activity_no,value.activity_title,value.activity_atten_name,value.activity_atten_tel,value.activity_start,value.activity_end,minutes2hours(value.activity_servicetime)]

            content.append(new_content)

    current_app.logger.info("exportActivity")
    return excel.make_response_from_array(content, "xlsx",
                                          file_name="exportActivity") 

 首先，要初始化Flask-Excel，

excel.init_excel(app)

重点是最后一句

return excel.make_response_from_array(content, "xlsx",file_name="exportActivity") 

#注释是这样的
(function) make_response_from_array: (array, file_type, status=200, file_name=None, **keywords) -> None

第二种是使用 xlsxwriter

首先安装

pip install xlswriter

xlsxwriter 不需要初始化，可以直接调用，代码如下

import xlsxwriter
import io


@activity_bp.route("/exportActivity",methods=['GET'])
@login_required
def exportActivity():

    output = io.BytesIO()
    workbook = xlsxwriter.Workbook(output)
    sheet = workbook.add_worksheet('考勤人员')
    activityList = Activity.query.order_by(Activity.starttime.asc()).all()

    format1 = {
        # 'bold': True,  # 字体加粗
        'align': 'center',  # 水平位置设置：居中
        'valign': 'vcenter',  # 垂直位置设置，居中
        # 'font_size': 14,  # '字体大小设置'
        'border':1
    }
    title_format = workbook.add_format(format1)

    #构建格式并添加入实例
    format2={
        # 'bold':True,#字体加粗
        # 'num_format':'$#,##0',#货币数字显示样式
        'align':'center',#水平位置设置：居中
        'valign':'vcenter',#垂直位置设置，居中
        # 'font_size':16,#'字体大小设置'
        # 'font_name':'Courier New',#字体设置
        # 'italic':True,# 斜体设置
        # 'underline':1,#下划线设置 1.单下划线 2.双下划线 33.单一会计下划线 34双重会计下划线
        # 'font_color':"red",#字体颜色设置
        'border':1,#边框设置样式1
        # 'border_color':'green',#边框颜色
        # 'bg_color':'#c7ffec',#背景颜色设置
    }
    content_format = workbook.add_format(format2)




    row = 0
    number = 1
    if activityList:
        for index in range(len(activityList)):

            activityAttenList = ActivityAtten.query.filter_by(activity_no=activityList[index].no).all()
            if activityAttenList:

                title_date = time.strftime("%Y-%m-%d", time.strptime(str(activityList[index].starttime), "%Y-%m-%d %H:%M:%S")) 
                title = activityList[index].title
                # sheet.merge_range('B'+str(row)+':F'+str(row),str(index+1)+'.'+str(title_date)+' '+title)
                sheet.merge_range(row,1,row,5,str(number)+'.'+str(title_date)+' '+title,title_format)
                row = row+1
                sheet.set_column(row,1, 5) 
                sheet.set_column(row,2, 10)
                sheet.set_column(row,3, 20)
                sheet.set_column(row,4, 20)
                sheet.set_column(row,5, 10)

            
            
                sheet.write(row,1,'序号',title_format)
                sheet.write(row,2,'姓名',title_format)
                sheet.write(row,3,'手机号码',title_format)
                sheet.write(row,4,'服务/培训时长',title_format)
                sheet.write(row,5,'备注',title_format)
                row = row+1


                for key in range(len(activityAttenList)):
                    sheet.write(row,1,str(key+1),title_format)
                    sheet.write(row,2,activityAttenList[key].activity_atten_name,title_format)
                    sheet.write(row,3,activityAttenList[key].activity_atten_tel,title_format)
                    sheet.write(row,4,minutes2hours(activityAttenList[key].activity_servicetime),title_format)
                    sheet.write(row,5,'',title_format)
                    row = row+1

                row = row+2 #每个活动换2行
                number = number+1 #活动序号


    workbook.close()
    response = make_response(output.getvalue())
    response.headers['Content-Type'] = "application/x-xlsx"
    response.headers["Cache-Control"] = "no-cache"
    response.headers["Content-Disposition"] = "attachment; filename=download.xlsx"
    return response

有几个点需要注意，如果想设置单元格的样式，可以这样：

    format1 = {
        # 'bold': True,  # 字体加粗
        'align': 'center',  # 水平位置设置：居中
        'valign': 'vcenter',  # 垂直位置设置，居中
        # 'font_size': 14,  # '字体大小设置'
        'border':1
    }
    title_format = workbook.add_format(format1)


    #.......


    sheet.write(row,1,'序号',title_format)
    

write() 的方法是这样的

 #跟踪 write()方法，是这样的

    def write(self, row, col, *args):
        """
        Write data to a worksheet cell by calling the appropriate write_*()
        method based on the type of data being passed.

        Args:
            row:   The cell row (zero indexed).
            col:   The cell column (zero indexed).
            *args: Args to pass to sub functions.

        Returns:
             0:    Success.
            -1:    Row or column is out of worksheet bounds.
            other: Return value of called method.

        """
        return self._write(row, col, *args)  

行和列都从0 开始，比如A1，其实就是0,0

make_response() 方法使用时，要注意，是使用flask 中的make_response（）方法，而不是pyexcel 中的 make_response()，当初我就是在这里卡住了！两个方法是不一样的。

跟住make_response()方法

def make_response(*args: t.Any) -> "Response":
    """Sometimes it is necessary to set additional headers in a view.  Because
    views do not have to return response objects but can return a value that
    is converted into a response object by Flask itself, it becomes tricky to
    add headers to it.  This function can be called instead of using a return
    and you will get a response object which you can use to attach headers.

    If view looked like this and you want to add a new header::

        def index():
            return render_template('index.html', foo=42)

    You can now do something like this::

        def index():
            response = make_response(render_template('index.html', foo=42))
            response.headers['X-Parachutes'] = 'parachutes are cool'
            return response

    This function accepts the very same arguments you can return from a
    view function.  This for example creates a response with a 404 error
    code::

        response = make_response(render_template('not_found.html'), 404)

    The other use case of this function is to force the return value of a
    view function into a response which is helpful with view
    decorators::

        response = make_response(view_function())
        response.headers['X-Parachutes'] = 'parachutes are cool'

    Internally this function does the following things:

    -   if no arguments are passed, it creates a new response argument
    -   if one argument is passed, :meth:`flask.Flask.make_response`
        is invoked with it.
    -   if more than one argument is passed, the arguments are passed
        to the :meth:`flask.Flask.make_response` function as tuple.

    .. versionadded:: 0.6
    """
    if not args:
        return current_app.response_class()
    if len(args) == 1:
        args = args[0]
    return current_app.make_response(args)

从注释可以知道，response = make_response(render_template('index.html', foo=42))可以使用html 转存生成其他文件

以上是两种导出excel 的方法，

如果只是单纯导出报表，无需其他样式的，可以使用flask-excel，因为比较简单方便。

如果是需要有一定样式的，比如合拼单元格之类的，xlsxwriter 好用