后端代码
controller层
@RequestMapping(value = "/importExcel", method = {RequestMethod.POST, RequestMethod.GET})@ResponseBodypublic ResponseBean importExcel(HttpServletRequest request) {SUser sUser = JwtService.getConnUser(request);MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;List<MultipartFile> file = multipartRequest.getFiles("file");String fileName = file.get(0).getOriginalFilename();try {return importService.importExcel(fileName, file.get(0), sUser);} catch (Exception e) {e.printStackTrace();}return new ResponseBean(false, 0, "解析xls文件失败", null);}
service层
@Transactional(readOnly = false,rollbackFor = Exception.class)@Overridepublic ResponseBean importExcel(String fileName, MultipartFile file) throws Exception {List<XXX> buses = new ArrayList<>();List<XXX> isCoveredList = new ArrayList<>();List<String> isCoveredIds = new ArrayList<>();if (!fileName.matches("^.+\\.(?i)(xls)$")) {return new ResponseBean(false, 0, "上传文件格式不正确", null);}InputStream is = file.getInputStream();Workbook wb = null;wb = new HSSFWorkbook(is);Sheet sheet = wb.getSheetAt(0);Bus bus;for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象if (row == null){continue;}bus = new Bus();//if( row.getCell(0).getCellType() != 1){//// 循环时，得到每一行的单元格进行判断// throw new MyException("导入失败(第"+(r+1)+"行,用户名请设为文本格式)");//}row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//设置读取转String类型row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);//设置读取转String类型//得到每一行的第一个单元格的值 String vehicleNo = row.getCell(0).getStringCellValue();if(vehicleNo == null || vehicleNo.isEmpty()){//判断是否为空return new ResponseBean(false, 0, "导入失败(第"+(r+1)+"行,未填写)", null);}//得到每一行的第二个单元格的值 日期String exp = null;if (!StringUtils.isEmpty(row.getCell(1))) {if (row.getCell(1).getCellType() == 1){//string类型exp = row.getCell(1).getStringCellValue();} else {//日期类型SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");try {exp = sdf.format(row.getCell(1).getDateCellValue());// 日期} catch (Exception e) {try {throw new Exception("exception on get date data !".concat(e.toString()));} catch (Exception e1) {e1.printStackTrace();}}finally{sdf = null;}}}else {return new ResponseBean(false, 0, "导入失败(第"+(r+1)+"行,时间未填写)", null);}String plateColorCodeName = row.getCell(2).getStringCellValue();Integer plateColorCode;if (!StringUtils.isEmpty(plateColorCodeName)) {if ("蓝色".equals(plateColorCodeName)){plateColorCode = 1;} else if ("黄色".equals(plateColorCodeName)){plateColorCode = 2;} else {plateColorCode = 0;}} else {return new ResponseBean(false, 0, "导入失败(第"+(r+1)+"行,未填写)", null);}//对车牌号进行查重操作，若有重复的则覆盖，并将被覆盖的存到一个list里Bus isNeedCover = checkIsNeedCover(vehicleNo,plateColorCode);if (!StringUtils.isEmpty(isNeedCover.getId())) {isCoveredList.add(isNeedCover);isCoveredIds.add(isNeedCover.getId());}String company = null;if (!StringUtils.isEmpty(row.getCell(3))) {row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);//设置读取转String类型company = row.getCell(3).getStringCellValue();}//完整的循环一次 就组成了一个对象bus.setVehicleNo(vehicleNo);bus.setExp(exp);bus.setPlateColorCode(Integer.valueOf(plateColorCode));if (!StringUtils.isEmpty(company)){bus.setCompany(company);}buses.add(bus);}this.insertBatch(buses);//删除被覆盖的listthis.deleteBatchIds(isCoveredIds);return new ResponseBean(true, 0, "解析xls文件成功", isCoveredList);}
html页面
<button class="layui-btn btn-export margintop-16" id="btn_export" style="float: left;"onclick="exportFormwork()">模板导出</button><div class="file-upload" id="handleXls" style="float: left;line-height: 51.4px;"><input type="file" name="multipartFile" id="xlsFile" style="opacity: 0;width: 0px; height: 1px;"/><label for="multipartFile" class='layui-btn layui-btn-warm layui-btn layui-btn-sm layui-btn-fluid' onclick="inputTrigger()" style="width: 120px;">选择xls文件</label></div>
js
/*** 导入模板导出*/function exportFormwork() {location.href = url.exportFormwork;}/*** 导入*/function inputTrigger() {$('#xlsFile').trigger('click')}
var xlsFile = [];/*** 上传文件*/$(document).on('change','#xlsFile', function () {for (var i = 0; i < $(this)[0].files.length; i++) {xlsFile.push($(this)[0].files[i]);}var formData = new FormData();for (var i = 0; i < xlsFile.length; i++) {formData.append('file', xlsFile[i]);}$.mask_element('#test_mask', 'xls文件解析中'); //打开加载层$.ajax({url: url.importShuttleBus,method: "post",data: formData,dataType: 'JSON',cache: false, // 不缓存processData: false, // jQuery不要去处理发送的数据contentType: false, // jQuery不要去设置Content-Type请求头success: function (data) {$.mask_close('#test_mask');//关闭加载层if (data.isSuccess) {//刷新tablesearchSubmitInfo();layer.msg(data.responseMsg);} else {layer.msg(data.responseMsg, {icon: 5})}},error: function (e) {layer.msg('上传文件过大[总上传大小不得超过50M]', {icon: 5});$.mask_close('#test_mask');//关闭加载层}})xlsFile = [];event.target.value="";})
