# 上传Excel文件表
class Excel(models.Model):
booksid = models.AutoField(primary_key=True, unique=True, null=False)
booksimg_name = models.CharField(max_length=50, null=False)
booksimg = models.FileField(null=True, upload_to='%Y/%m/%d/excel_files')
class Meta:
db_table = 'Excel'
# 数据导入表
class Import_excel(models.Model):
# 图书ID:默认自动创建,这里手动创建,Django3.0以上不要加max_length=11
booksid = models.AutoField(primary_key=True, unique=True, null=False)
# 图书种类:如果用默认值就必须得用max_length
booksspecies = models.CharField(max_length=6, default='计算机')
# 图书名称,null=False:不允许为空值,unique=True:不允许重复
booksname = models.CharField(max_length=16, null=False, unique=True)
# 图书几本
booksnumber = models.IntegerField(null=False)
# 图书国籍:中国,美国,英国
bookscountry = models.CharField(max_length=4, null=False)
# 图书是否入库:默认值没有入库
booksstorage = models.BooleanField(default=False)
# 图书价格
booksmoney = models.CharField(max_length=10, null=False)
# 添加图书时间:auto_now_add:第一次创建的时候自动设置当前日期,默认值:false
bookstimes = models.DateTimeField(auto_now=True)
# 图书图片名称
booksimg_name = models.CharField(max_length=50, null=False)
class Meta:
# 更改表名称为Books,如果不更改就是,App名_models类名。
db_table = 'Import_excel'
urls.py路由
urlpatterns = [
path('importexcel/', views.import_excel, name='import_excel'),
]
views.py视图
def import_excel(request):
if request.method == "GET":
return render(request, 'import_excel.html')
elif request.method == "POST":
booksimg_name = request.POST.get("booksimg_name")
booksimg = request.FILES.get("booksimg")
if booksimg_name == '' or booksimg == '':
return render(request, 'import_excel.html')
max_upload_size = 5242880
extenedname = booksimg_name[booksimg_name.rindex('.') + 1:]
allowedname = ['xls', 'xlsx']
if extenedname not in allowedname or (booksimg.size > max_upload_size):
return render(request, 'import_excel.html')
try:
# 先保存上传的名字和文件
save = Excel.objects.create(booksimg_name=booksimg_name, booksimg=booksimg)
# 根据保存的文件获取地址
path1 = save.booksimg
# 把上传路径的绝对地址写成字符串的方式
path2 = 'static/uploads/'
# 把两个地址进行拼接起来然后进行读取
path = xlrd.open_workbook(str(path2) + str(path1))
print(path)
# 根据地址读取工作表名,(excel里是什么名字,这里就写什么名字)
sheet = path.sheet_by_name("sheet1")
# 在读取的过程中把第一行不做读取范围,然后进行循环读取数据并且进行保存,下面注释的都是数据表里自动生成的。
for r in range(1, sheet.nrows):
# booksspecies和bookstimes这两个进行注释的原因是在ORM里已经自动生成数据了
# booksspecies = sheet.cell(r, 1).value
booksname = sheet.cell(r, 2).value
booksnumber = sheet.cell(r, 3).value
bookscountry = sheet.cell(r, 4).value
booksstorage = sheet.cell(r, 5).value
booksmoney = sheet.cell(r, 6).value
# bookstimes = sheet.cell(r, 7).value
booksimg_name = sheet.cell(r, 8).value
save_excel = Import_excel.objects.create(booksname=booksname, booksnumber=booksnumber,
bookscountry=bookscountry,
booksstorage=booksstorage, booksmoney=booksmoney,
booksimg_name=booksimg_name)
# 根据上传文件表的Id进行获取id然后就行删除上传的记录和上传的文件,也就是说不管你上传多少次上传上传的记录和文件都会删除,只是Excel表里的记录导入成功了。根据项目具体需求你也可以把以下代码删除掉。
booksid = save.booksid
onedelete = Excel.objects.get(pk=booksid)
c = onedelete.booksid
book = onedelete.booksimg
delpath = 'static/uploads/' + str(book)
onedelete.delete()
os.remove(delpath)
return redirect(reverse("book:import_excel"))
except Exception as ex:
return render(request, 'import_excel.html')
前端代码:
{% load static %}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<script src="{% static 'js/jquery-2.14.min.js' %}"></script>
<title>Title</title>
</head>
<body>
<form action="{% url 'excel:import_excel' %}" method="post" enctype="multipart/form-data">{% csrf_token %}
<div class="form-group" style="margin-left: 30px">
<label for="fileupload">导入excel数据:</label>
<input type='text' class="form-control" name='booksimg_name' id='textfield' autocomplete="off"/>
<input id="fileupload" type="file" name="booksimg" style="display:none;"/>
<input id="excelImport" type="button" value="浏览" class="btn btn-primary"/>
</div>
<button>提交</button>
</form>
</body>
<script>
$(document).ready(function () {
$("#excelImport").click(function () {
$('#fileupload').click();
});
$("#uploadSubmit").click(function () {
$('#excelForm').submit();
});
$('#fileupload').change(function () {
document.getElementById("textfield").value =
document.getElementById("fileupload").files[0].name;
})
});
</script>
</html>