EasyExcel多sheet页导入导出
一、导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
二、随便整个application.yml
server:
# 服务器的HTTP端口
port: 8080
三、拿两个DO举例
@Data
public class Student {
@ExcelProperty(value = "学生姓名")
private String studentName;
@ExcelProperty(value = "科室号码")
private Integer classNum;
}
@Data
public class User {
@ExcelProperty(value = "用户名")
private String name;
@ExcelProperty(value = "年龄")
private Integer age;
}
四、controller
@RestController
public class EasyController {
/**
* http://localhost:8080/export
* desciption 多sheet导出
*/
@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 多sheet导入
*/
@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();
}
}
}
}
五、最后还有个监听
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) {
}
}
完事。
