flask-excel使用

安装

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

文档

http://flask-excel.readthedocs.org/en/latest/ 

flask初始化：

from flask import Flask, request, jsonify
import flask_excel as excel

app = Flask(__name__)

@app.route("/download", methods=['GET'])
def download_file():
    return excel.make_response_from_array([[1, 2], [3, 4]], "csv")


@app.route("/export", methods=['GET'])
def export_records():
    return excel.make_response_from_array([[1, 2], [3, 4]], "csv",
                                          file_name="export_data")


@app.route("/download_file_named_in_unicode", methods=['GET'])
def download_file_named_in_unicode():
    return excel.make_response_from_array([[1, 2], [3, 4]], "csv",
                                          file_name=u"中文文件名")


# insert database related code here
if __name__ == "__main__":
    excel.init_excel(app)
    app.run()

 

使用

前台：

<table class="table table-bordered m-t">
            <thead>
            <tr>
                <th>头像</th>
                <th>姓名</th>
                <th>性别</th>
                <th>状态</th>
                <th>操作</th>
            </tr>
            </thead>
            <tbody>
            {% if list %}
            <a class="m-l" href="{{ buildUrl('/member/export') }}" title="数据导出">
                <i class="fa fa-sign-out" ></i>数据导出
            </a>
            {% for item in list %}
            <tr>
                <td><img alt="image" class="img-circle" src="{{ item.avatar }}" style="width: 40px;height: 40px;"></td>
                <td>{{ item.nickname }}</td>
                <td>{{ item.sex_desc }}</td>
                <td>{{ item.status_desc }}</td>
                <td>
                    <a href="{{ buildUrl('/member/info') }}?id={{  item.id }}">
                        <i class="fa fa-eye fa-lg"></i>
                    </a>

                    {% if item.status == 1 %}
                    <a class="m-l" href="{{ buildUrl('/member/set') }}?id={{  item.id }}">
                        <i class="fa fa-edit fa-lg"></i>
                    </a>

                    <a class="m-l remove" href="javascript:void(0);" data="{{  item.id }}">
                        <i class="fa fa-trash fa-lg"></i>
                    </a>
                    {% else %}
                    <a class="m-l recover" href="javascript:void(0);" data="{{  item.id }}">
                        <i class="fa fa-rotate-left fa-lg"></i>
                    </a>
                    {% endif %}
                </td>
            </tr>
            {% endfor %}
            {% else %}
            <tr>
                <td colspan="5">暂无数据</td>
            </tr>
            {% endif %}
            </tbody>
        </table>

后台实现：

from application import app,db
import flask_excel as excel
import re
route_member = Blueprint( 'member_page',__name__ )
# 初始化excel
'''
在路由的开头导入flask_excel,注意一定要用init_excel，否则会出现错误：
ValueError: View function did not return a response
'''
excel.init_excel(app)

@route_member.route("/export", methods=['GET'])
def export_records():
    query_sets = Member.query.order_by(Member.id.desc()).all()
    column_names = ['姓名', '性别','状态']
    excel_data = make_excel_list(query_sets,['nickname','sex_desc','status_desc'],column_names)
    app.logger.info(excel_data)


    return excel.make_response_from_array(excel_data,file_type='xls',
        file_name='export_member%s' %(getStrDate()),
        sheet_name='会员表')


#汇总Excel数据
def make_excel_list(data, fields, column_names):
    list_data = []

    list_data.append(column_names)
    for item in data:
        tmp = []
        for fi in fields:
            tmp.append(getattr(item,fi))
        list_data.append(tmp)
    return list_data

后台model：

class Member(db.Model):
    __tablename__ = 'member'

    id = db.Column(db.Integer, primary_key=True)
    nickname = db.Column(db.String(100), nullable=False, server_default=db.FetchedValue())
    mobile = db.Column(db.String(11), nullable=False, server_default=db.FetchedValue())
    sex = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())
    status = db.Column(db.Integer, nullable=False, server_default=db.FetchedValue())


    # 虚拟属性(数据库中没有的字段)
    @property
    def status_desc(self):
        return app.config['STATUS_MAPPING'][ str( self.status ) ]

    @property
    def sex_desc(self):
        sex_mapping = {
            "0":"未知",
            "1":"男",
            "2":"女"
        }
        return sex_mapping[str(self.sex)]

效果：