Django前端传入excel表格并将表格数据记录到MySQL里

背景交代

Django最简单的用途，就是在前台页面展示mysql的数据。为了提高录入效率，我们使用Excel上传文件的形式，这样事前在Excel里先按照格式准备好数据，然后前端做一个“上传”的功能，就可以进行解析各数据并且保存到Mysql里了。

先交代软件版本:Django 2.1.8和Python 3.6,project的名字是naxx，App名称是naxxramas，整个目录结构如下：

配置URL和view

。然后需要安装一个插件：pip install openpyxl==2.5.1

然后我们在Excel里先加上两组数据，如下：

先说明一下我这里的urls.py，我在project层的urls.py里给路径做了特别设置，如下：

from django.contrib import admin
from django.urls import path, include
from . import views

urlpatterns = [
    path('admin/', admin.site.urls),    #后台
    path(r'naxx/', include('naxxramas.urls')),  #naxx的单独去naxxramas里的`urls.py`里配置
    path('', views.portal, name='portal'),  #主页
]

naxxramas里面的urls.py的配置是这样的：

from django.urls import path
from . import views

urlpatterns = [
    path('excel-practice', views.excelupdate, name="excel-practice"),  #录入资损攻防数据页面
]

然后来写naxxramas/views.py里的excelupdate函数:

from django.shortcuts import render_to_response, get_object_or_404
from django.views.decorators.csrf import csrf_exempt
from .models import BlueArmy, STAPractice, CAPPractice
import openpyxl

# excel上传文件录入
@csrf_exempt
def excelupdate(request):
    if "GET" == request.method:
        return render_to_response('excel-practice.html')
    else:
        excel_file = request.FILES["excel_file"]
        wb = openpyxl.load_workbook(excel_file)
        worksheet = wb["Sheet1"]

        excel_data = list()  # 效果等同于 excel_data = []
        for row in worksheet.iter_rows():
            row_data = list()
            for cell in row:
                row_data.append(str(cell.value))

            if str(excel_file).find("可用性攻防") > 0:  # 如果上传的excel名称是带有“可用性攻防”的
                practice_date, redarmy, practice_scene, app, reach1, reach5, reach10, scene_level, workdomain, bluearmy = row_data
                STAPractice.objects.create(		# 这里就把Excel的值按列一一录入到models
                    date=practice_date,
                    practice_scene=practice_scene,
                    oneok=reach1,
                    fiveok=reach5,
                    tenok=reach10,
                    workdomain=workdomain,
                    bluearmy=BlueArmy.objects.get(army_name=bluearmy),
                    redarmy=redarmy,
                    app=app,
                    scene_level=scene_level
                )

            elif str(excel_file).find("资损攻防") > 0:	#如果上传的excel名称是带有“资损攻防”的
                practice_date, redarmy, practice_scene, app, scene_level, blueok, redok, practice_time, workdomain, bluearmy = row_data
                CAPPractice.objects.create(
                    date=practice_date,
                    practice_scene=practice_scene,
                    redok=redok,
                    blueok=blueok,
                    scene_level=scene_level,
                    workdomain=workdomain,
                    bluearmy=BlueArmy.objects.get(army_name=bluearmy),  # 关联外键的写法，注意这里要跟外键默认的值匹配，不然会报错
                    redarmy=redarmy,
                    app=app,
                    practice_time=practice_time
                )
            else:
                pass
            excel_data.append(row_data)
    return render_to_response('excel-practice.html', {"excel_data": excel_data})

写前端

上面的views.py里我们规定前端页面是excel-practice.html，我这个HTML页面是基于adminLTE上改造的：

{% extends 'naxx-base.html' %}

{% block title %}
  阿里供应链平台 | Excel批量数据录入
{% endblock %}

{% block content %}
  <!-- Content Wrapper. Contains page content -->
  <div class="content-wrapper">
    <!-- Content Header (Page header) -->
    <section class="content-header">
      <div class="container-fluid">
        <div class="row mb-2">
          <div class="col-sm-6">
            <h1>Excel录入攻防情况</h1>
          </div>
          <div class="col-sm-6">
            <ol class="breadcrumb float-sm-right">
              <li class="breadcrumb-item"><a href="#">Home</a></li>
              <li class="breadcrumb-item active">Excel录入</li>
            </ol>
          </div>
        </div>
      </div><!-- /.container-fluid -->
    </section>

    <!-- Main content -->
    <section class="content">
      <div class="container-fluid">
        <div class="row">
          <!-- left column -->
          <div class="col-md-12">
            <!-- general form elements -->
            <div class="card card-primary">
              <div class="card-header">
                <h3 class="card-title">上传对应表格文件</h3>
              </div>
              <!-- /.card-header -->
              <!-- form start -->
              <form role="form" enctype="multipart/form-data" action="{% url "excel-practice" %}" method="post">
                <div class="card-body">
                  <div class="form-group">
                    <label for="exampleInputFile">File input</label>
                    <div class="input-group">
                      <div class="custom-file">
                        {% csrf_token %}
                        <input type="file" class="custom-file-input" title="Upload excel file" name="excel_file" required="required">
                        <label class="custom-file-label" for="exampleInputFile">Choose file</label>
                      </div>
                    </div>
                  </div>
                 <h> 注意！上传的Excel是有格式的，具体格式请去咨询桃熙</h>
                </div>
                <!-- /.card-body -->

                <div class="card-footer">
                  <button type="submit" class="btn btn-primary">确认提交</button>
                </div>
              </form>
            </div>
            <!-- /.card -->
          </div>
          <!-- /.card-body -->
          <table border="1">
           <tr>
            {% for row in excel_data %}
              {% for cell in row %}
                <table border="1">
                <tr>               
               <td> {{ cell }}&nbsp;&nbsp; </td>
              {% endfor %}
            {% endfor %}
           </tr>
          </table>
         <br>
        </div>
        <!-- /.card -->
      </div>
      <!--/.col (right) -->
    </section>
    <!-- /.content -->
  </div>
  <!-- /.content-wrapper -->
{% endblock %}

整个效果如下：

到时候直接传入Excel然后上传就好了。

参考资料

https://www.pythoncircle.com/post/591/how-to-upload-and-process-the-excel-file-in-django/