XLS文件导入导出

首先下载包

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.1.2</version>
</dependency>

导出

前端elment-ui

选中按钮

<el-table
        ref="multipleTable"
        :data="pageTicket"
        style="width: 100%"
        border
        :header-cell-class-name="'custom-header-cell'"
        @selection-change="handleSelectionChange"
      >
</el-table>

  handleSelectionChange(val) {
      this.multipleSelection = val;
      const combined = val.map((row) => row.id).join("/");
      this.idss = combined;
      console.log(this.idss);
    },

<el-button type="primary" @click="dao">导出</el-button>

dao(){
      console.log()
      api.get("/logs/export?idss="+this.idss).then((res) =>{
        console.log(res)
      })
},

后端

 @GetMapping(value = "/export",produces = "application/octet-stream")
    public void exportEmployee(HttpServletResponse response, @RequestParam("idss") String ids) throws Exception {
        String[] split = ids.split("/");
        List<Logs> list = new ArrayList<>();
        for (String s : split) {
            Query query = new Query(Criteria.where("_id").is(Integer.valueOf(s)));
            Logs list1 = mongoTemplate.findOne(query, Logs.class);
            list.add(list1);
        }
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("日志列表", "测试"), Logs.class, list);
//      指定位置
        FileOutputStream outputStream = new FileOutputStream("C:\\Users\\李贺森\\Desktop\\日志列表.xls");
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }


//        //导出的参数  生成文件名称和下载的文件后缀
//        ExportParams params = new ExportParams("用户表", "用户表", ExcelType.HSSF);
//        //导出员工表 出去导出的参数 实体类和需要导出的信息
//        Workbook workbook = ExcelExportUtil.exportExcel(params, Logs.class, list);
//        ServletOutputStream out = null;
//        try {
//            //流形式传输
//            response.setHeader("content-type", "application/octet-stream");
//            //防止中文乱码
//            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("用户表.xls", String.valueOf(StandardCharsets.UTF_8)));
//            out = response.getOutputStream();
//            //流形式导出
//            workbook.write(out);
//        } catch (IOException e) {
//            e.printStackTrace();
//        } finally {
//            if (null != out) {
//                try {
//                    //关闭流
//                    out.close();
//                } catch (IOException e) {
//                    e.printStackTrace();
//                }
//            }
//        }
//    }
}

导入

前端

<el-upload  :before-upload="beforeUpload" class="qw">
            <el-button type="primary" slot="trigger"  style="margin-left: 15px">导入 
             </elbutton>
</el-upload>


beforeUpload(file) {
      // 构造 FormData 对象
      let formData = new FormData();
      formData.append('file', file);

      // 使用 axios 发送 POST 请求
      api.post("/user/excel/upload", formData)
        .then(response => {
          if (response.status === 200) {
            this.$message.success('导入成功！');
          } else {
            this.$message.error('导入失败！');
          }
        })
        .catch(error => {
          this.$message.error(error);
        })
      return false;
    },

后端

 @PostMapping("/excel/upload")
    public String upload(@RequestParam("file") MultipartFile file) throws IOException {
        try (InputStream inputStream = file.getInputStream()) {
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);  // 使用HSSFWorkbook处理OLE2格式的Excel文件
            Sheet sheet = workbook.getSheetAt(0);
            StringBuilder data = new StringBuilder();
            // 遍历每一行
            for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
                // 遍历每一列
//                System.out.println(row);
                Row row = sheet.getRow(i);
                List<Object> list = new ArrayList<>();
                if (row == null) {
                    continue;
                }
                for (Cell cell : row) {
                    // 获取单元格的值
                    if (CellType.NUMERIC.equals(cell.getCellType())) {
                        int value = (int)cell.getNumericCellValue();
                        list.add(String.valueOf(value));
                    } else {
                        String value = cell.getStringCellValue();
                        list.add(value);
                    }
                }
                SubjectUserVo shop = new SubjectUserVo(Integer.parseInt((String) list.get(0)),(String)list.get(1),(String)list.get(2),(String)list.get(3));
                int id = generateRandomId();
                SubjectUser subjectUser = new SubjectUser();
                subjectUser.setId(id);
                subjectUser.setName(shop.getName());
                subjectUser.setDescs(shop.getDescs());
                subjectUserService.save(subjectUser);

                SubjectCharacteristics subjectCharacteristics = new SubjectCharacteristics();
                subjectCharacteristics.setSubjectid(id);
                subjectCharacteristics.setValue(shop.getValue());
                subjectCharacteristics.setType(1);
                subjectCharacteristicsService.save(subjectCharacteristics);

            }
            workbook.close();
            return data.toString();
        } catch (IOException e) {
            e.printStackTrace();
            return "Upload failed: " + e.getMessage();
        }
    }