- 实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
@ContentRowHeight(20) //内容的行高
@HeadRowHeight(30) //表头的行高
public class Student {
@ExcelProperty(value = "ID")
@ColumnWidth(30)
private String id;
@ExcelProperty(value = "姓名")
private String name;
@ExcelProperty(value = "生日")
private Date birth;
@ExcelProperty(value = "分数")
private Double score;
}
- 上传文件读取的时候需要一个监听器类
/**
* excel文件读取的监听器,如果上传了文件,我们可以将service类中的业务逻辑在这个监听器中写。
*/
@Component
public class StudentReadListener extends AnalysisEventListener<Student>{
private List<Student> students = new ArrayList<>();
/**
* 每读取一行的回调
* @param student
* @param analysisContext
*/
@Override
public void invoke(Student student, AnalysisContext analysisContext) {
System.out.println("Student:" + student);
students.add(student);
}
/**
* 全部读取完的回调
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("读取数据完成,总共有" + students.size() + "条数据!");
students = new ArrayList<>();
}
}
- 上传和下载的controller
@RestController
public class DownloadController {
@Autowired
private StudentReadListener studentReadListener;
/**
* 导出excel文件
*/
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
response.setContentType("application/octet-stream");
response.setCharacterEncoding("UTF-8");
String filename = URLEncoder.encode("学生信息", "UTF-8");
response.setHeader("Content-Disposition", "attachment; filename="+filename+".xlsx");
ExcelWriterBuilder write = EasyExcel.write(response.getOutputStream(), Student.class);
write.sheet().doWrite(getData());
}
/**
* 上传文件
*/
@PostMapping("/upload")
public void upload(MultipartFile file,HttpServletResponse response) throws IOException {
ExcelReaderBuilder read = EasyExcel.read(file.getInputStream(), Student.class, studentReadListener);
read.sheet().doRead();
response.setContentType("text/html; charset=UTF-8");
response.getWriter().write("<h1>上传成功!<h1>");
}
public List<Student> getData(){
List<Student> list = new ArrayList<>();
for (int i = 0;i < 10;i++){
Student student = new Student();
student.setBirth(new Date());
student.setId(UUID.randomUUID().toString().replace("-",""));
student.setName("name" + i);
student.setScore(Double.parseDouble(String.valueOf(i)));
list.add(student);
}
return list;
}
}
注意:在上传文件时,要修改表单的enctype属性值
<form action="/upload" method="post" enctype="multipart/form-data">
<input type="file" name="file">
<button>提交</button>
</form>