首先得引用layui的js
jsp页面:<button type="button" class="layui-btn" id="importExcel">导入</button>
js代码 $(function () { layui.use('upload', function(){ debugger; var $ = layui.jquery ,upload = layui.upload; upload.render({ elem: '#import_excel_btn' ,url: '/excel/import' ,accept: 'file' //普通文件 ,multiple: true ,exts: 'xls|xlsx' //允许上传的文件后缀 ,done: function(res){//返回值接收 layer.msg('导入成功!'); } }); }); })
控制器中:
@RsMethod(name = "导入", url = "/import", security = SecurityType.AUTHORIZED) @RsFile(type = FileType.TEMPORARY,folders = {"sys","menu"}) public Map<String, Object> importexcel(@RequestParam MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception { /* public void exImport(@RequestParam(value = "filename")MultipartFile file, HttpSession session) {*/ boolean a = false; String fileName = file.getOriginalFilename(); Map<String, Object> map = new HashMap<String, Object>(); try { a = iPurchaseExcelService.batchImport(fileName, file); if(a){ String Msg = "导入EXCEL成功!"; map.put("msg", Msg); map.put("success", true); }else{ String Msg = "导入EXCEL失败!"; map.put("success", false); map.put("message", Msg); } } catch (Exception e) { e.printStackTrace(); } return map; }
service实现类
@Override @Transactional(readOnly = false,rollbackFor = Exception.class) public boolean batchImport(String fileName, MultipartFile file) throws Exception { boolean notNull = false; List<PurchaseExcel> texcelList = new ArrayList<>(); //判断文件格式 if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { throw new Exception("上传文件格式不正确"); } InputStream is = file.getInputStream(); Workbook wb = WorkbookFactory.create(is); Sheet sheet = wb.getSheetAt(0); if(sheet!=null){ notNull = true; } PurchaseExcel texcel; for (int r = 1; r <= sheet.getLastRowNum(); r++) {//r = 1表示从第2行开始循环 如果你的第2行开始是数据 Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象 if (row == null){ continue; } texcel = new PurchaseExcel(); /* 获取到excel中的数据 */ String cord=null; String schoolName=null; String projectName=null; String purchaseProjectName=null; String unit=null; String nums=null; String unitPrice=null; String amountMoney=null; String remarks=null; if(row.getCell(0)!=null) { row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第一个单元格的值 cord = row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值 } if(row.getCell(1)!=null) { row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值 schoolName = row.getCell(1).getStringCellValue(); } if(row.getCell(2)!=null) { row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第3个单元格的值 projectName = row.getCell(2).getStringCellValue(); } if(row.getCell(3)!=null) { row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第4个单元格的值 purchaseProjectName = row.getCell(3).getStringCellValue(); } if(row.getCell(4)!=null) { row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第5个单元格的值 unit = row.getCell(4).getStringCellValue(); } if(row.getCell(5)!=null) { row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第6个单元格的值 nums = row.getCell(5).getStringCellValue(); } if(row.getCell(6)!=null) { row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第7个单元格的值 unitPrice = row.getCell(6).getStringCellValue(); } if(row.getCell(7)!=null) { row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第8个单元格的值 amountMoney = row.getCell(7).getStringCellValue(); } if(row.getCell(8)!=null) { row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第9个单元格的值 remarks = row.getCell(8).getStringCellValue(); } texcel.setId(UUID.randomUUID().toString()); if(cord!=null) texcel.setCord(Integer.valueOf(cord)); texcel.setSchoolName(schoolName); texcel.setProjectName(projectName); texcel.setPurchaseProjectName(purchaseProjectName); texcel.setUnit(unit); if(nums!=null) { BigDecimal nunsDecimal = new BigDecimal(nums); texcel.setNums(nunsDecimal); } if(unitPrice!=null) { BigDecimal unitDecimal = new BigDecimal(unitPrice); texcel.setUnitPrice(unitDecimal); } if(amountMoney!=null) { BigDecimal amountMoneyDecimal = new BigDecimal(amountMoney); texcel.setAmountMoney(amountMoneyDecimal); } texcel.setRemarks(remarks); texcelList.add(texcel); } for (PurchaseExcel texcel1 : texcelList) {//通过循环将数据添加到数据库中 purchaseExcelMapper.insert(texcel1); } return notNull; } 注purchaseExcelMapper这个是使用自动生成的就不一一写出来了
pox.xml中得引用poi