Flask学习-设备管理系统3：从excel表导入设备信息

Flask学习-设备管理系统3：从excel表导入设备信息

---

本文博客链接:http://blog.csdn.net/jdh99,作者:jdh,转载请注明.

环境

• 主机:win10
• python版本:python3.4
• 开发环境：PyCharm5.0.2

说明

生产部有需求，从excel表格批量导入设备。

效果图

源代码

视图文件修改 view.py

    def open_excel(file= 'file.xls'):
        try:
            data = xlrd.open_workbook(file)
            return data
        except Exception as e:
            print (str(e))


    # 根据索引获取Excel表格中的数据   参数:file：Excel文件路径     colnameindex：表头列名所在行的所以  ，by_index：表的索引
    def excel_table_byindex(file= 'file.xls',colnameindex=0,by_index=0):
        data = open_excel(file)
        table = data.sheets()[by_index]
        nrows = table.nrows #行数
        ncols = table.ncols #列数
        colnames =  table.row_values(colnameindex) #某一行数据
        list =[]
        for rownum in range(1,nrows):

             row = table.row_values(rownum)
             if row:
                 app = {}
                 for i in range(len(colnames)):
                    app[colnames[i]] = row[i]
                 list.append(app)
        return list


    ALLOWED_EXTENSIONS = set(['xls', 'xlsx'])
    def allowed_file(filename):
        return '.' in filename and \
               filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS

    @login_required
    @main.route('/import_device', methods=['GET', 'POST'])
    def import_device():
        if request.method == 'POST':
            file = request.files['file']
            filename = file.filename

            # 判断文件名是否合规
            if file and allowed_file(filename):
                file.save(os.path.join('.\\upload', filename))
            else:
                flash('失败:上传文件格式不对')
                return render_template('import_device.html')

            # file.save(os.path.join('c:\\mnt', filename))
            # file.save(os.path.join('.\\upload', filename))

            # 添加到数据库
            tables = excel_table_byindex(file='.\\upload\\' + filename)
            for row in tables:
                # 判断表格式是否对
                if '手持机DEVICEID' not in row or \
                    '手持机SIMID' not in row or \
                    '手持机硬件版本' not in row or \
                    '手持机软件版本' not in row or \
                    '脚扣DEVICEID' not in row or \
                    '脚扣SIMID' not in row or \
                    '脚扣硬件版本' not in row or \
                    '脚扣软件版本' not in row:
                    flash('失败:excel表格式不对')
                    return render_template('import_device.html')

                # print('0x%04x' % int(str(row['手持机DEVICEID']).split('.')[0], base=16))
                # 判断手持机字段是否存在
                if row['手持机DEVICEID'] != '' and row['手持机SIMID'] != '' and \
                    row['手持机硬件版本'] != '' and row['手持机软件版本'] != '':
                    id_format = '0x%04x' % int(str(row['手持机DEVICEID']).split('.')[0], base=16)
                    device = Device(device_type='手持机',
                                    device_id=id_format,
                                    device_simid=str(row['手持机SIMID']).split('.')[0],
                                    hard_version=int(row['手持机硬件版本']),
                                    soft_version=int(row['手持机软件版本']),
                                    warehouse=False,
                                    shipment_time='无',
                                    agent='无',
                                    prison='无',
                                    shutdown=False)
                    # 判断是否id重复
                    flag = True
                    if Device.query.filter_by(device_id=device.device_id).count() > 0:
                        flash('失败:设备ID:%s已存在' %device.device_id)
                        flag = False
                    # 判断simid是否重复
                    elif Device.query.filter_by(device_simid=device.device_simid).count() > 0:
                        flash('失败:设备SIMID:%s已存在' %device.device_simid)
                        flag = False
                    if flag:
                        db.session.add(device)
                    else:
                        return render_template('import_device.html')

                if row['脚扣DEVICEID'] != '' and row['脚扣SIMID'] != '' and \
                    row['脚扣硬件版本'] != '' and row['脚扣软件版本'] != '':
                    id_format = '0x%04x' % int(str(row['脚扣DEVICEID']).split('.')[0], base=16)
                    device = Device(device_type='脚扣',
                                    device_id=id_format,
                                    device_simid=str(row['脚扣SIMID']).split('.')[0],
                                    hard_version=int(row['脚扣硬件版本']),
                                    soft_version=int(row['脚扣软件版本']),
                                    warehouse=False,
                                    shipment_time='无',
                                    agent='无',
                                    prison='无',
                                    shutdown=False)
                    # 判断是否id重复
                    flag = True
                    if Device.query.filter_by(device_id=device.device_id).count() > 0:
                        flash('失败:设备ID:%s已存在' %device.device_id)
                        flag = False
                    # 判断simid是否重复
                    elif Device.query.filter_by(device_simid=device.device_simid).count() > 0:
                        flash('失败:设备SIMID:%s已存在' %device.device_simid)
                        flag = False
                    if flag:
                        db.session.add(device)
                    else:
                        return render_template('import_device.html')
            return redirect(url_for('.index'))

        return render_template('import_device.html')    

新建网页文件 import_device.html

    {% extends "base.html" %}
    {% import "bootstrap/wtf.html" as wtf %}

    {% block title %}外出押解设备管理系统 by jdh{% endblock %}

    {% block page_content %}
    <div class="page-header">
        <h1>导入设备信息</h1>
    </div>
    {#<div class="col-md-4">#}
    {#    {{ wtf.quick_form(form) }}#}
    {#</div>#}
    <div class="col-md-4">
        <form action="" method=post enctype=multipart/form-data>
            <input type=file name=file><br/>
            <input type=submit value=Upload>
        </form>
    </div>
    {#<form class="well form-inline" method="POST">#}
    {#    {{ form.hidden_tag() }}#}
    {#    <input type=file name=file>#}
    {#         <input type=submit value=Upload>#}
    {##}
    {#    {{ form.name() }}#}
    {#    <button type="submit" class="btn">{{ form.submit() }}</button>#}
    {#</form>#}

    {% endblock %}