EasyExcel是阿里系的excel导入导出工具包,是在poi的基础进行更为强大的封装,拥有更快的读写速度的同时占用内存较小。
首先,先导入依赖:
<!-- excel表格导入导出功能 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.1</version>
</dependency>
然后,编写实体类
@ExcelProperty(value = "姓名", index = 0)注解用于定位一列数据,value根据表头定位,index根据列的位置定位
@ExcelIgnoreUnannotated注解用来忽略没加@ExcelProperty注解的字段。
@Data
@ExcelIgnoreUnannotated
public class StudentExcelDto {
@ApiModelProperty(value = "姓名")
@ExcelProperty(value = "姓名", index = 0)
private String name;
@ApiModelProperty(value = "性别")
@ExcelProperty(value = "性别", index = 1)
private String sex;
@ApiModelProperty(value = "年龄")
@ExcelProperty(value = "年龄", index = 2)
private Integer age;
@ApiModelProperty(value = "籍贯")
private String place;
}
接着,编写控制器接口
导入需要用到springmvc提供的MultipartFile,用于获取前端传入的文件
@ApiOperation(value = "Excel导入")
@PostMapping("/exUpload")
public void exUpload(MultipartFile file){
log.info("开始导入");
//获取文件名称
String fileName = file.getOriginalFilename();
//判断文件名称是否以特定后缀结尾
if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
throw new GlobalException(fileName + "不是excel文件");
}
try {
List<StudentExcelDto> list = EasyExcel.read(file.getInputStream())
.head(StudentExcelDto.class)
.sheet()
//指定第一行为表头,从第二行开始读取
.headRowNumber(1)
//返回导入数据
.doReadSync();
for (StudentExcelDto sed : list){
log.info(sed.toString());
}
} catch (IOException e) {
throw new GlobalException("读取" + fileName + "文件失败,失败原因:{}" + e.getMessage());
}
}
导出:
excel表格有两种格式:xls和xlsx,建议用xlsx,因为它的占用内存小;如果使用xlsx格式,response.setContentType()就用xlsx变量的内容,
同时response.setHeader()中文件名称的后缀用xlsx,
EasyExcel .excelType()用ExcelTypeEnum.XLSX
如果使用xls格式,
response.setContentType()就用xls变量的内容,
同时response.setHeader()中文件名称的后缀用xls,
EasyExcel .excelType()用ExcelTypeEnum.XLS
@ApiOperation(value = "Excel导出")
@PostMapping("/exDownload")
public void exDownload(HttpServletResponse response){
log.info("开始导出");
//导出xls文件格式
String xls = "application/vnd.ms-excel";
String xlsx = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
response.setContentType(xlsx);
response.setCharacterEncoding("utf-8");
String filename = "";
try {
filename = URLEncoder.encode("学生信息", "UTF-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + filename + ".xlsx");
List<StudentExcelDto> list = new ArrayList<>();
for (int i = 0; i < 10; i++){
StudentExcelDto sed = new StudentExcelDto();
sed.setName("张三" + i);
sed.setSex("男" + (i + 1));
sed.setAge(10 + i);
list.add(sed);
}
try {
EasyExcel.write(response.getOutputStream(), StudentExcelDto.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("学生信息")
.doWrite(list);
} catch (IOException e) {
throw new GlobalException("导出学生信息失败,失败原因:{}" + e.getMessage());
}
}
最后,前端代码:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form method="post" action="/excel/exDownload" enctype="multipart/form-data">
<button>导出</button>
</form>
<form method="post" action="/excel/exUpload" enctype="multipart/form-data">
<input type="file" style="width: 100px; height: 30px;" name="file">
<button>导入</button>
</form>
</body>
</html>