EasyExcel是一款由阿里项目组提供的，基于Java的简单、省内存的读写Excel的来源项目。
不废话，直接上代码。
1.添加依赖
<!-- easyexcel 依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.0-beta3</version>
</dependency>
2.在实体类中通过注解，标识表头信息
@Data
@EqualsAndHashCode()
public class Student implements Serializable {
private static final long serialVersionUID = -8969368116719425827L;
@ExcelIgnore //忽略该字段
private Integer id;
@ExcelProperty("学号")
private Integer stuNum;
@ExcelProperty("姓名")
private String name;
@ExcelProperty("电话")
private String phone;
@ColumnWidth(20) //设置列宽
@ExcelProperty("所在班级")
private String className;
}
3.导出Excel数据
网页中导出-单个sheet
@GetMapping("download/simple")
public void downloadSimpleExcel(HttpServletResponse response) throws IOException {
//getStudents() 数据源
ExcelUtil.downloadSimpleExcel("学生信息",getStudents(),response);
}
/**
* web 导出单个sheet的excel，
*
* @param fileName excel 文件名
* @param data 数据源
* @param response HttpServletResponse
* @throws IOException 异常
*/
public static void downloadSimpleExcel(String fileName, List<?> data, HttpServletResponse response) throws IOException {
if(CollectionUtils.isEmpty(data)){
return;
}
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), data.get(0).getClass()).sheet("sheet")
.doWrite(()->{
return data;
});
} catch (IOException e) {
log.error("excel文件导出失败!",e);
}
}
效果如下：
[图片上传失败...(image-65e804-1634222883101)]
网页中导出-多个sheet
@GetMapping("download/multipleSheet")
public void downloadMultipleSheetExcel(HttpServletResponse response) throws IOException {
List<ExcelUtil.MultipleSheetMode> multipleSheetModes = new CopyOnWriteArrayList<>();
multipleSheetModes.add(new ExcelUtil.MultipleSheetMode("学生",getStudents()));
multipleSheetModes.add(new ExcelUtil.MultipleSheetMode("课程",getClazzs()));
ExcelUtil.downloadMultipleSheetExcel("学生信息",multipleSheetModes,response);
}
/**
* web 导出多个sheet的excel，
*
* @param fileName excel文件名称
* @param multipleSheetModes 数据源，里面包括sheet名称，以及sheet内容
* @param response HttpServletResponse
* @throws IOException 异常
*/
public static void downloadMultipleSheetExcel(String fileName, List<MultipleSheetMode> multipleSheetModes, HttpServletResponse response) throws IOException {
if(CollectionUtils.isEmpty(multipleSheetModes)){
return;
}
ExcelWriter excelWriter = null;
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
excelWriter = EasyExcel.write(response.getOutputStream()).build();
int i = 0;
for (MultipleSheetMode multipleSheetMode : multipleSheetModes) {
// 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
WriteSheet writeSheet = EasyExcel.writerSheet(i, multipleSheetMode.getSheetName()).head(multipleSheetMode.getData().get(0).getClass()).build();
excelWriter.write(multipleSheetMode.getData(), writeSheet);
i++;
}
} catch (IOException e){
log.error("excel文件导出失败!",e);
} finally {
// 调用finish，关闭流
if (excelWriter != null) {
excelWriter.finish();
}
}
}
/**
* 用于临时存储每个sheet的数据信息
*/
public class MultipleSheetMode{
public MultipleSheetMode() {
}
public MultipleSheetMode(String sheetName, List<?> data) {
this.sheetName = sheetName;
this.data = data;
}
/**
* sheet名称
*/
private String sheetName;
/**
* 填充的数据
*/
private List<?> data;
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<?> getData() {
return data;
}
public void setData(List<?> data) {
this.data = data;
}
}
效果如下：
[图片上传失败...(image-8e1582-1634222883101)]
网页中自定义表头导出excel
@GetMapping("download/dynamicHead")
public void downloadDynamicHeadExcel(HttpServletResponse response) throws IOException {
List<String> head = Arrays.asList("学号","姓名","电话","所属班级");
ExcelUtil.downloadDynamicHeadExcel("学生信息",getStudents(),head,response);
}
/**
* web 自定义表头导出单个sheet的excel,
* @param fileName excel文件名称
* @param data 数据源
* @param head 表头信息
* @param response HttpServletResponse
* @throws IOException
*/
public static void downloadDynamicHeadExcel(String fileName, List<?> data, List<String> head, HttpServletResponse response) throws IOException {
if(CollectionUtils.isEmpty(data)){
return;
}
ExcelWriter excelWriter = null;
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet writeSheet = null;
if(!CollectionUtils.isEmpty(head)){
List<List<String>> list = new ArrayList<>();
head.forEach(h->{list.add(Collections.singletonList(h));});
writeSheet = EasyExcel.writerSheet("sheet").head(list).build();
}else {
writeSheet = EasyExcel.writerSheet("sheet").head(data.get(0).getClass()).build();
}
excelWriter.write(data, writeSheet);
} catch (IOException e){
log.error("excel文件导出失败!",e);
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
效果如下：
[图片上传失败...(image-5fd34d-1634222883101)]
网页中，根据模板导出excel
@GetMapping("download/template")
public void downloadExcelByTemplate(HttpServletResponse response) throws IOException {
String filePath = "src/main/resources/templates/fill/123.xlsx";
String fileName = "学生信息表";
ExcelUtil.downloadExcelByTemplate(filePath,fileName,getStudents(),response);
}
/**
* web 根据excel模板导出excel
* @param filePath 模板文件的绝对路径
* @param data 数据源
* @param response HttpServletResponse
*/
public static void downloadExcelByTemplate(String filePath, String fileName, List<?> data, HttpServletResponse response){
if(CollectionUtils.isEmpty(data)){
return;
}
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream()).withTemplate(filePath).sheet()
.doFill(() -> {
return data;
});
} catch (FileNotFoundException e){
log.error("找不到模板文件或文件路径错误，文件：{}",filePath);
} catch (IOException e) {
log.error("excel文件导出失败!",e);
}
}
模板如下:
模板中 用{} 来表示你要用的变量，模板中{.} 多了个点 表示list
[图片上传失败...(image-c0baca-1634222883101)]
4.导入Excel数据
从excel中读取单个sheet
@GetMapping("read/one")
public void readExcel(){
String fileName = "src/main/resources/templates/fill/学生信息.xlsx";
EasyExcel.read(fileName, Student.class, new PageReadListener<Student>(dataList -> {
for (Student data : dataList) {
//todo 业务逻辑
System.out.println(data);
}
})).sheet().doRead();
}
从excel中读取多个sheet
@GetMapping("read/multipleSheet")
public void readMultipleSheetExcel() {
String fileName = "src/main/resources/templates/fill/学生信息.xlsx";
ExcelReader excelReader = null;
try {
excelReader = EasyExcel.read(fileName).build();
ReadSheet readSheet1 =
EasyExcel.readSheet(0).head(Student.class).registerReadListener(new PageReadListener<Student>(dataList -> {
for (Student data : dataList) {
//todo 业务逻辑
System.out.println(data);
}
})).build();
ReadSheet readSheet2 =
EasyExcel.readSheet(1).head(Clazz.class).registerReadListener(new PageReadListener<Clazz>(dataList -> {
for (Clazz data : dataList) {
//todo 业务逻辑
System.out.println(data);
}
})).build();
excelReader.read(readSheet1,readSheet2);
} finally {
if (excelReader != null) {
// 关闭流
excelReader.finish();
}
}
}
若是需要获取更详细的内容，请前往
开源地址：https://github.com/alibaba/easyexcel
