java|Spring Boot读取Excel表格快速上手 boot|spring|spring

最简单容易上手的EasyExcel 1.导入依赖 【java|Spring Boot读取Excel表格快速上手】
文章图片

2.编写监听器

```java package com.yumchina.cultivate.config; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.yumchina.cultivate.pojo.DscStudent; import com.yumchina.cultivate.service.ExcelService; import lombok.extern.slf4j.Slf4j; import org.springframework.stereotype.Component; import java.util.ArrayList; import java.util.List; /** * @Author: lucy * @Date: 2021/11/22 11:43 */ @Slf4j @Component public class ExcelListener extends AnalysisEventListener {/** * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收Student 根据情况换成自己需要的实体类解析出来的数据是保存在cachedDataList 中的 */ private static final int BATCH_COUNT = 20; private List cachedDataList = new ArrayList<>(BATCH_COUNT+2); /** * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。 */ private final ExcelService excelService; public ExcelListener(ExcelService excelService) { this.excelService = excelService; }/** * 每条数据都调 * @param * @param analysisContext */ @Override public void invoke(Student Student, AnalysisContext analysisContext) { //log.info("解析到一条数据:{}", JSON.toJSONString(dscStudent)); cachedDataList.add(dscStudent); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (cachedDataList.size() >= BATCH_COUNT) { //saveData(); // 存储完成清理 list cachedDataList = new ArrayList<>(BATCH_COUNT+2); } }/** * 所有数据解析完成了都会来调用 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 //saveData(); log.info("所有数据解析完成！"); }/** * 加上存储数据库 */ //private void saveData() { //log.info("{}条数据，开始存储数据库！", cachedDataList.size()); //excelService.readExcelAndCopyDataBase(cachedDataList); //log.info("存储数据库成功！"); //} public List getData(){ return cachedDataList; } }

3编写控制器

@PostMapping("/readStudentExcel") public Result readStudentExcel(MultipartFile file){ try { //固定写 DscStudent换成自己的实体类 EasyExcel.read(file.getInputStream(), DscStudent.class,excelListener).sheet().doRead(); } catch (IOException e) { e.printStackTrace(); } List data = https://www.it610.com/article/excelListener.getData(); for (DscStudent dscStudent:data){ System.out.println(dscStudent.getStudentName().toString()); } return Result.ok(); }

4编写pojo

public class Student implements Serializable { @ExcelProperty(value = "https://www.it610.com/article/学生姓名") private String studentName; private static final long serialVersionUID = 1L; }

下载Excel

@GetMapping("/reportExcel") public void reportExcel(String startTime, String endTime, HttpServletResponse response) throws IOException { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); //防止中文乱码 String fileName = URLEncoder.encode("Employeetraintime", "UTF-8"); //插入自己的查询语言 //List employeetraintimes = excelService.reportExce(startTime, endTime); response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx"); //EmployeeTrainTimeVo 为对应的实体类对象 EasyExcel.write(response.getOutputStream(),EmployeeTrainTimeVo.class).sheet("新工工时").doWrite(employeetraintimes); }