excel导出模板，导入数据 后端代码

依赖如下

<!-- poi3.9：导出excel -->

  <dependency>

   <groupId>org.apache.poi</groupId>

   <artifactId>poi</artifactId>

   <version>3.9</version>

  </dependency>

  <dependency>

   <groupId>org.apache.poi</groupId>

   <artifactId>poi-ooxml</artifactId>

   <version>3.9</version>

  </dependency>

运行后防止读取excel报错,加上以下配置

<resource>

    <directory>src/main/resources</directory>

    <includes>

        <include>**/*</include>

    </includes>

    <excludes>

        <exclude>template/*.xlsx</exclude>

    </excludes>

    <filtering>true</filtering>

</resource>

<resource>

    <directory>src/main/resources</directory>

    <filtering>false</filtering>

    <includes>

        <include>template/*.xlsx</include>

    </includes>

</resource>

模板文件放入template文件夹中

代码如下

@RequestMapping(value = "downloadExcel")//method = RequestMethod.GET将数据传递给前端

public void downloadExcel(HttpServletResponse response, HttpServletRequest request) throws IOException {

    //获取输入流，原始模板位置

    String filePath = "template/person.xlsx";

    InputStream inputStream = null;

    OutputStream outputStream = null;

    // 获取文件对象

    try {

        inputStream = this.getClass().getClassLoader().getResourceAsStream(filePath);

        XSSFWorkbook wb = new XSSFWorkbook(inputStream);

        // 当客户端请求的资源是一个可下载的资源（这里的“可下载”是指浏览器会弹出下载框或者下载界面）时，对这个可下载资源的描述（例如下载框中的文件名称）就是来源于该头域。

        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + "外包人员信息登记.xlsx");

        // 服务器告诉浏览器它发送的数据属于什么文件类型，也就是响应数据的MIME类型

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        response.setCharacterEncoding("utf-8");

        // 关闭缓存（HTTP/1.1）

        response.setHeader("Cache-Control", "no-store");

        // 关闭缓存（HTTP/1.0）

        response.setHeader("Pragma", "no-cache");

        // 缓存有效时间

        response.setDateHeader("Expires", 0);

        outputStream = response.getOutputStream();

        wb.write(outputStream);

        outputStream.flush();

    } catch (Exception e) {

        e.printStackTrace();

    } finally {

        IoUtil.close(inputStream);

        IoUtil.close(outputStream);

    }

}

@PostMapping("/uploadExcel")

public void uploadExcel(

        @RequestParam("file") MultipartFile file) throws Exception {

    this.service.uploadExcel(file);

    ResponseResult.output(ResponseResult.success());

}

public void uploadExcel(MultipartFile file) throws IOException { 

    Workbook workbook = WorkbookFactory.create(file.getInputStream());

    Sheet sheet = workbook.getSheetAt(0);

    int startRow = 3; 

    List<Trainee> list = new ArrayList<>();

    for (Row row : sheet) {

        if (row.getRowNum() >= startRow - 1) {

            // 需要去除空格

            int rowNum = row.getRowNum() + 1;

            String name = row.getCell(1).getStringCellValue().trim();

            CheckFlow.start("表中姓名有误,错误行" + rowNum).isNotNull(name);

            String card = row.getCell(3).getStringCellValue().trim();

            Trainee person = new Trainee();

            person.setName(name);

            person.setIdNo(card); 

            list.add(person);

        }

    }  

    this.saveBatch(list);

}