导出
html页面
定义的按钮
<a class="btn btn-warning" onclick="$.table.exportExcel()" shiro:hasPermission="system:student:export"> <i class="fa fa-download"></i> 导出 </a>
映射的路径
exportUrl: prefix + "/export",
实体类
加入注解,导出谁就在谁上面加入注解---- @Excel
Controller层
//导出 @Log(title = "学生管理", businessType = BusinessType.EXPORT) @RequiresPermissions("system:student:export") @PostMapping("/export") @ResponseBody public AjaxResult export(SysStudent student) { List<SysStudent> list = studentService.selectStudentList(student); ExcelUtil<SysStudent> util = new ExcelUtil<SysStudent>(SysStudent.class); return util.exportExcel(list, "学生数据"); }
导入
没有模板就不知道要导入什么,所以需要先有一个模板
导入的时候,因必要条件还要确定是否重复,出现两个一摸一样的就没有必要了吧,我这是学生表,所以还需要对其通过名字进行查询
HTML页面
<a class="btn btn-info" onclick="$.table.importExcel()" shiro:hasPermission="system:student:import"> <i class="fa fa-upload"></i> 导入 </a>
映射
importUrl: prefix + "/importStudent",//导入 importTemplateUrl: prefix + "/importTemplate",//导出模板
导入的前端页面
<!-- 导入区域 --> <script id="importTpl" type="text/template"> <form enctype="multipart/form-data" class="mt20 mb10"> <div class="col-xs-offset-1"> <input type="file" id="file" name="file"/> <div class="mt10 pt5"> <input type="checkbox" id="updateSupport" name="updateSupport" title="如果登录账户已经存在,更新这条数据。"> 是否更新已经存在的用户数据 <a onclick="$.table.importTemplate()" class="btn btn-default btn-xs"><i class="fa fa-file-excel-o"></i> 下载模板</a> </div> <font color="red" class="pull-left mt10"> 提示:仅允许导入“xls”或“xlsx”格式文件! </font> </div> </form> </script>
Controller层
/** * 下载模板 */ @RequiresPermissions("system:student:view") @GetMapping("/importTemplate") @ResponseBody public AjaxResult importTemplate() { ExcelUtil<SysStudent> util = new ExcelUtil<SysStudent>(SysStudent.class); return util.importTemplateExcel("学生数据"); }
/** * 导入 */ @RequiresPermissions("system:student:import") @PostMapping("/importStudent") @ResponseBody public AjaxResult importStudent(MultipartFile file, boolean updateSupport) throws Exception { ExcelUtil<SysStudent> util = new ExcelUtil<SysStudent>(SysStudent.class); List<SysStudent> studentList = util.importExcel(file.getInputStream()); String operName = ShiroUtils.getSysUser().getLoginName(); String message = studentService.importStudent(studentList, updateSupport, operName); return AjaxResult.success(message); }
service层
/** * 导入学生数据 * * @param studentList 学生数据列表 * @param updateSupport 是否更新支持,如果已存在,则进行更新数据 * @param operName 是否更新支持,如果已存在,则进行更新数据 * @return 结果 */ public String importStudent(List<SysStudent> studentList, Boolean updateSupport, String operName) ;
serviceImpl层
/** * 导入用户数据 * * @param studentList 用户数据列表 * @param updateSupport 是否更新支持,如果已存在,则进行更新数据 * @param operName 操作用户 * @return 结果 */ private static final Logger log = LoggerFactory.getLogger(SysStudentServiceImpl.class); @Override public String importStudent(List<SysStudent> studentList, Boolean updateSupport, String operName) { if (StringUtils.isNull(studentList) || studentList.size() == 0) { throw new BusinessException("导入用户数据不能为空!"); } int successNum = 0; int failureNum = 0; StringBuilder successMsg = new StringBuilder(); StringBuilder failureMsg = new StringBuilder(); for (SysStudent student : studentList) { try { // 验证是否存在这个用户 SysStudent u = studentMapper.selectStudentByName(student.getStudentName() ); System.out.print(student.getStudentSex()+""); if (StringUtils.isNull(u)) { student.setStudentName(student.getStudentName()); this.insertStudent(student); successNum++; successMsg.append("<br/>" + successNum + "学校信息" + student.getStudentName() + " 导入成功"); } else if (updateSupport) { student.setUpdateBy(operName); this.updateStudent(student); successNum++; successMsg.append("<br/>" + successNum + "学校信息 " + student.getStudentName() + " 更新成功"); } else { failureNum++; failureMsg.append("<br/>" + failureNum + "学校信息" + student.getStudentName() + " 已存在"); } } catch (Exception e) { failureNum++; String msg = "<br/>" + failureNum + "学校信息" + student.getStudentName() + " 导入失败:"; failureMsg.append(msg + e.getMessage()); log.error(msg, e); } } if (failureNum > 0) { failureMsg.insert(0, "很抱歉,导入失败!共 " + failureNum + " 条数据格式不正确,错误如下:"); throw new BusinessException(failureMsg.toString()); } else { successMsg.insert(0, "恭喜您,数据已全部导入成功!共 " + successNum + " 条,数据如下:"); } return successMsg.toString(); }
mapper层
mapper.xml
写一个根据姓名查询的方法,避免重复的
<select id="selectStudentByName" parameterType="String" resultMap="SysStudentResult"> <include refid="selectStudentVo"/> where student_name = #{studentName} </select>
mapper.java文件写方法
/** * 通过学生姓名查询学生 * * @param studentName 用户名 * @return 用户对象信息 */ public SysStudent selectStudentByName(String studentName);