1、django中上传excel并保存数据库

一、分析

1.前端表格提交

<form action="/import-excel/" method="POST" enctype="multipart/form-data">
    {% csrf_token %}
    请上传员工excel:  <input type="file" name="excel_file">
    <input type="submit" name="" value="提交">
</form>

2.django中app的views.py中方法：

获取上传文件二进制流对象

    excel_file = request.FILES.get('excel_file')
    file_name = '%d.xlsx' % round(time.time() * 1000)
    # UPLOAD_DIR = os.path.join(BASE_DIR, 'upload')
    file_path = os.path.join(UPLOAD_DIR, file_name)

大文件分块读写（存入本地文件夹，用时间戳命名）

    with open(file_path, 'ab') as f:
        for chunk in excel_file.chunks():
            f.write(chunk)

读取excel(需安装导入xlrd包)，并保存到数据库。

    workbook = xlrd.open_workbook(file_path)
    sheet = workbook.sheets()[0]

    # 获取excel行数
    rows = sheet.nrows
    # 异步保存数据库
    thread = Thread(target=save_db, args=(sheet,))
    thread.start()

    context = {'message': '文件正在保存', 'row_count': rows}

    return render(request, 'hr/import_data.html', context)

保存数据库的方法。

def save_db(sheet):
    # 获取excel行数
    rows = sheet.nrows
    # 获取excel列数
    cols = sheet.ncols
    for row in range(rows):
        comic = Comic()
        comic.comic_id = sheet.cell(row, 1)
        comic.name = sheet.cell(row, 2)
        comic.cover = sheet.cell(row, 3)
        comic.category = sheet.cell(row, 4)
        comic.save()

二、完整views.py文件

import os

import xlrd
from django.http import JsonResponse
from django.shortcuts import render
import time
from u18.settings import UPLOAD_DIR
from hr.models import *
from threading import Thread


def import_data(request):
    context = {'message': '', 'row_count': 0}
    return render(request, 'hr/import_data.html', context)


def get_count(request):
    current_count = Comic.objects.count()
    return JsonResponse({'current_count': current_count})


def save_db(sheet):
    # 获取excel行数
    rows = sheet.nrows
    # 获取excel列数
    cols = sheet.ncols
    for row in range(rows):
        comic = Comic()
        comic.comic_id = sheet.cell(row, 1)
        comic.name = sheet.cell(row, 2)
        comic.cover = sheet.cell(row, 3)
        comic.category = sheet.cell(row, 4)
        comic.save()


def import_excel(request):
    # 获取上传文件二进制流对象
    excel_file = request.FILES.get('excel_file')
    file_name = '%d.xlsx' % round(time.time() * 1000)
    # UPLOAD_DIR = os.path.join(BASE_DIR, 'upload')
    file_path = os.path.join(UPLOAD_DIR, file_name)

    # 大文件分块读写
    with open(file_path, 'ab') as f:
        for chunk in excel_file.chunks():
            f.write(chunk)

    # 读取excel
    workbook = xlrd.open_workbook(file_path)
    sheet = workbook.sheets()[0]
    # 获取excel行数
    rows = sheet.nrows
    # 异步保存数据库
    thread = Thread(target=save_db, args=(sheet,))
    thread.start()

    context = {'message': '文件正在保存', 'row_count': rows}

    return render(request, 'hr/import_data.html', context)