一、pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.1</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>easyExcel</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>easyExcel</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- easypoi -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
</dependencies>
</project>
二、实体类
package com.example.easyexcel.domain;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class Student {
private static final long serialVersionUID = 1L;
@ExcelProperty(value = "学生姓名")
private String studentName;
@ExcelProperty(value = "科室号码")
private Integer classNum;
}
package com.example.easyexcel.domain;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
@Data
public class User {
private static final long serialVersionUID = 1L;
@ExcelProperty(value = "用户名")
private String name;
@ExcelProperty(value = "年龄")
private Integer age;
}
三、控制器
package com.example.easyexcel.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.easyexcel.domain.Student;
import com.example.easyexcel.domain.User;
import com.example.easyexcel.listen.AnalysisEventListenerImpl;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.LinkedList;
import java.util.List;
/**
* desciption 导入导出测试控制器
*
* @author cjw
* @Date 2021/12/16 21:01
*/
@RestController
public class EasyController {
/**
* http://localhost:1001/export
* desciption 多sheet导出
*
* @param response
* @return
* @Date 2021/12/16 20:53
*/
@GetMapping("/export")
public void export(HttpServletResponse response) {
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("test", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet userSheet = EasyExcel.writerSheet(0, "用户信息").head(User.class).build();
List<User> userList = new LinkedList<>();
User user1=new User();
user1.setName("张三");
user1.setAge(13);
User user2=new User();
user2.setName("李四");
user2.setAge(14);
userList.add(user1);
userList.add(user2);
excelWriter.write(userList, userSheet);
WriteSheet studentSheet = EasyExcel.writerSheet(1, "学生信息").head(Student.class).build();
List<Student> studentList = new LinkedList<>();
Student student=new Student();
student.setStudentName("王同学");
student.setClassNum(1);
Student student2=new Student();
student2.setStudentName("王同学");
student2.setClassNum(2);
studentList.add(student);
studentList.add(student2);
excelWriter.write(studentList, studentSheet);
//关闭流
excelWriter.finish();
} catch (Exception ex) {
ex.printStackTrace();
}
}
/**
* desciption
*
* @param file
* @return
* @Date 2021/12/16 20:56
*/
@PostMapping("/importData")
public String importData(MultipartFile file) throws Exception {
InputStream inputStream = file.getInputStream();
ExcelReader excelReader = null;
try {
AnalysisEventListenerImpl listener = new AnalysisEventListenerImpl();
excelReader = EasyExcel.read(inputStream, listener).build();
ReadSheet readSheet1 = EasyExcel.readSheet(0).head(User.class).build();
excelReader.read(readSheet1);
List<Object> list1 = listener.getDatas();
List<User> userList = (List<User>) (List) list1;
for (User user : userList) {
System.out.println(user);
}
listener.getDatas().clear();
ReadSheet readSheet2 = EasyExcel.readSheet(1).head(Student.class).build();
excelReader.read(readSheet2);
List<Object> list2 = listener.getDatas();
List<Student> studentList = (List<Student>) (List) list2;
for (Student student : studentList) {
System.out.println(student);
}
listener.getDatas().clear();
return "导入成功";
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
try {
inputStream.close();
} catch (IOException e) {
throw new RuntimeException();
}
if (excelReader != null) {
excelReader.finish();
}
}
}
}
四、导入的listen实现类
package com.example.easyexcel.listen;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* desciption listen实现类
* @author cjw
* @return
* @Date 2021/12/16 20:58
*/
public class AnalysisEventListenerImpl extends AnalysisEventListener {
//可以通过实例获取该值
private List<Object> datas = new ArrayList<Object>();
@Override
public void invoke(Object o, AnalysisContext analysisContext) {
datas.add(o);
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
}
五、测试
导出 http://localhost:1001/export
导入