依赖如下
<!-- 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);
}