Django框架(上传Excel文件并读取)
博主今天整理下Django框架中上传Excel文件并读取
博主是要在管理平台中新增用例的维护功能,想着通过上传Excel文件来展示用例,下面是项目的路径图:
首先先建数据库模型
model.py
可以根据上传的日期区分目录
1 # 用例文件 2 class CaseFile(models.Model): 3 case_class = models.ForeignKey(CaseClass) 4 file_name = models.FileField(upload_to='case/%Y/%m/%d/', verbose_name=u"文件名称") 5 6 # 不注释会报错 7 # def __str__(self): 8 # return self.file_name 9 10 # 定义表名称 11 class Meta: 12 verbose_name = "用例文件管理" 13 verbose_name_plural = "用例文件管理"
第二步
settings.py
配置文件上传路径
1 # 文件上传配置 2 MEDIA_ROOT = os.path.join(BASE_DIR, 'upload/') 3 MEDIA_URL = '/upload/'
第三步
新建excel.py
根据excel的数据来提取自己需要的转化成json格式,再组成数组
1 # -*- coding: utf-8 -*- 2 3 import xlrd 4 from MyPlatform.settings import MEDIA_ROOT 5 6 7 class ExcelImport: 8 def __init__(self, file_name, version): 9 # self.file_name = unicode(file_name, "utf-8") 10 # 文件路径修改 11 self.file_name = (MEDIA_ROOT + str(file_name)).replace("/", "\\").decode("utf-8") 12 # print self.file_name 13 self.workbook = xlrd.open_workbook(self.file_name) 14 self.table = self.workbook.sheets()[0] 15 # 获取总行数 16 self.nrows = self.table.nrows 17 18 # 版本号 19 self.version = version 20 21 self.cases = [] 22 23 def get_cases(self): 24 # 从第二行开始 25 for x in range(1, self.nrows): 26 row = self.table.row_values(x) 27 self.cases.append( 28 { 29 "case_class": row[3], 30 "name": row[4], 31 "code": row[0], 32 "level": row[5], 33 "condition": row[6], 34 "step": row[7], 35 "expected_result": row[9], 36 "version": self.version 37 } 38 )
第四步
forms.py
表单中新建文件表单
1 # 文件上传表单 2 class FileForm(forms.Form): 3 file_name = forms.FileField(label=u"用例文件")
第五步
views.py
完成上传的逻辑
1 # 用例导入 2 def case_excel_import(request, class_id): 3 # 判断是否有session 4 username = request.session.get("username") 5 if username: 6 # 如果有session 获取指定信息 7 if request.method == "POST": 8 ff = FileForm(request.POST, request.FILES) 9 # file_name = request.FILES.get('exampleInputFile') 10 if ff.is_valid(): 11 file_name = ff.cleaned_data["file_name"] 12 13 # 向数据库中新增用例文件数据 14 case_file = CaseFile() 15 case_file.case_class_id = int(class_id) 16 case_file.file_name = file_name 17 case_file.save() 18 # print case_file.file_name 19 20 # 返回上传成功 21 messages.add_message(request, messages.INFO, u"上传成功!") 22 try: 23 build_case_import(file_name=case_file.file_name) 24 except Exception, e: 25 messages.add_message(request, messages.INFO, u"用例导入出错:" + str(e)) 26 return HttpResponseRedirect("/caseList/" + class_id, {"ff": ff}) 27 else: 28 ff = FileForm() 29 messages.add_message(request, messages.INFO, u"请选择文件!") 30 return HttpResponseRedirect("/caseList/" + class_id, {"ff": ff}) 31 else: 32 ff = FileForm() 33 return HttpResponseRedirect("/caseList/" + class_id, {"ff": ff}) 34 else: 35 # 如果没有session,重定向到路由 /login/, 返回表单 36 uf = UserForm(request.POST) 37 # 重定向 38 return HttpResponseRedirect("/login/", {"uf": uf})
导入成功后前端回显展示
前端代码:
博主使用的是模态框
1 <div class="modal modal-default fade" id="modal-class"> 2 <div class="modal-dialog"> 3 <div class="modal-content"> 4 <div class="modal-header"> 5 <button type="button" class="close" data-dismiss="modal" aria-label="Close"> 6 <span aria-hidden="true">×</span></button> 7 <h4 class="modal-title">上传用例文件</h4> 8 </div> 9 <div class="modal-body"> 10 <form class="form-group" enctype="multipart/form-data" method="post" action="/caseExcelImport/{{ case_class_one.id }}"> 11 {% csrf_token %} 12 {{ ff.as_p }} 13 <p class="help-block">请求确保文件格式正确 .</p> 14 <p class="help-block">用例模块文件可下载 .</p> 15 <a href="/download/template" class="btn btn-default"> 16 <i class="fa fa-download"> 下载</i> 17 </a> 18 <p class="help-block">excel格式的文件 .</p> 19 <p class="help-block">文件中小模块一栏填入最终所属测试模块 .</p> 20 <input type="submit" class="btn btn-default" value="上传"/> 21 <button type="button" class="btn btn-default pull-right" data-dismiss="modal">取消</button> 22 </form> 23 </div> 24 </div> 25 </div> 26 </div>
列表展示代码:
1 <div class="row"> 2 <div class="col-xs-12"> 3 <div class="box"> 4 <div class="box-header"> 5 <h3 class="box-title">用例模块:{{ case_class_one.name }}</h3> 6 </div> 7 <!-- /.box-header --> 8 <div class="box-body"> 9 <p> 10 <button type="button" class="btn btn-default" data-toggle="modal" data-target="#modal-class" data-id="{{ case_class_one.id }}"> 11 <i class="fa fa-caret-square-o-right"> 导入</i> 12 </button> 13 </p> 14 <table id="testcase" class="table table-bordered table-striped"> 15 <thead> 16 <tr> 17 <th>用例编号</th> 18 <th>用例名称</th> 19 <th>用例等级</th> 20 <th>期望结果</th> 21 <th>操作按钮</th> 22 </tr> 23 </thead> 24 <tbody> 25 {% for case in cases %} 26 <tr> 27 <td>{{ case.code }}</td> 28 <td>{{ case.name }}</td> 29 <td>Level {{ case.level }}</td> 30 <td>{{ case.expected_result }}</td> 31 <td> 32 <a href="/caseInfo/{{ case_class_one.id }}/{{ case.case_id }}" > 33 <i class="fa fa-folder-open"> 查看</i> 34 </a> 35 </td> 36 </tr> 37 {% endfor %} 38 </tbody> 39 </table> 40 </div> 41 <!-- /.box-body --> 42 </div> 43 </div> 44 </div>
前端效果: