excel数据结构

在这里插入图片描述

java代码

Controller层

我这里传入的参数除了文件，有一个id，id为页面上的目录上的一个节点id,可理解为我导入的目录实在某一个节点下展现，id也可为总的父节点，我这里为判断传入的id为所有节点的父id,默认为0，也可为已存在的某一节点id.

@SneakyThrows@RequestMapping("/importBookExcel")public AjaxResult importBookExcel(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request) {//获取参数String id = request.getParameter("id");if (id == null || "".equals(id)) {id = "0";}//poi解析excelInputStream inputStream = file.getInputStream();// 读取工作薄Workbook workbook = new XSSFWorkbook(inputStream);Sheet sheet = workbook.getSheetAt(0);//定义一个cellId,此为每一次循环前一列的idint cellId = 0;if (sheet.getPhysicalNumberOfRows() > 1) {//按照行进行循环，读取当前行的列for (int k = 1; k < sheet.getPhysicalNumberOfRows(); k++) {// 读取单元格Row row = sheet.getRow(k);if (row != null) {//第一列单独拿出操作Cell cell1 = row.getCell(0);//判断单元格是否为空if (!(cell1 == null || "".equals(cell1.toString().trim()))) {cell1.setCellType(Cell.CELL_TYPE_STRING);//根据传入的id和得到的name查询此节点是否存在Book book= bookService.getByName(Integer.parseInt(id), cell1.getStringCellValue());//如果第一列的name相关为空，向数据库添加此记录if (book== null) {Booklog bk= new Book();bk.setName(cell1.getStringCellValue());bk.setParentId(Integer.parseInt(id));bk.setCreateTime(new Date());//创建人一般从当前用户session或者redis拿取，我这里随便填了bk.setCreateUser("test"));Boolean result = bookService.save(bk);//因为id自增，所以插入后，查询刚插入数据，得到id，作为下一列的父idif (result) {Book bookResult= catalogService.getByName(bk.getParentId(), bk.getName());cellId = bookResult.getId();}} else {cellId = book.getId();}}//从第二列开始循环遍历列,我这里excel一行就三列，已查询第一列for (int j = 1; j < 3; j++) {Cell cell = row.getCell(j);Book bo= null;//父idif (cellId != 0) {bo= bookService.getById(cellId);}//判断此列是否为空if (!(cell == null || "".equals(cell.toString().trim()))) {//懒得编对象别名了Book book1= bookSerice.getByName(book.getId(), cell.getStringCellValue());if (cat == null) {Book  book2= new Book();book2.setName(cell.getStringCellValue());book2.setParentId(cellId);book2.setCreateTime(new Date());book2.setCreateUser("test");Boolean bl = bookService.save(book2);if (bl) {Book book3= bookService.getByName(cellId, book2.getName());cellId = book3.getId();}} else {cellId = book2.getId();}}}}}return AjaxResult.success("文件导入成功");} else {return AjaxResult.fail("文件内容为空");}}