Spring Boot整合EasyExcel(完整版包含上传解析excel和下载模板)

 
      com.alibaba
      easyexcel
      2.2.7
    

@PostMapping("plUpdate")
  public R plUpdate(@RequestParam("filename") MultipartFile file) throws IOException {
    //String Originalfilename = file.getOriginalFilename();
    // String fileName = file.getName();
   // System.out.println("orname="+Originalfilename+";"+"filename"+file.getName());

    // 获取文件全名
    String fileName = file.getOriginalFilename();
     //设置文件路径
    String templatePath = "G:/excel/";
    File dest0 = new File(templatePath);
    File dest = new File(dest0, fileName);
    //文件上传-覆盖
    try {
      // 检测是否存在目录
      if (!dest0.getParentFile().exists()) {
 dest0.getParentFile().mkdirs();
 //检测文件是否存在
      }
      if (!dest.exists()) {
 dest.mkdirs();
      }
      file.transferTo(dest);
    } catch (Exception e) {
      return R.error();
    }

    String finameUrl = templatePath+fileName;
    ExcelReader excelReader = null;
    try {
    //TeacherExcel.class对应的是和模板一样的实体类，
    //eduTeacherService对应持久层的接口
      excelReader = EasyExcel.read(finameUrl, TeacherExcel.class, new DemoDataListener(eduTeacherService)).build();
      ReadSheet readSheet = EasyExcel.readSheet(0).build();
      excelReader.read(readSheet);
    } finally {

      if (excelReader != null) {
 // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
 excelReader.finish();
      }
    }
    return R.ok();
  }

创建一个监听类：

package com.atguigu.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.atguigu.eduservice.entity.EduTeacher;
import com.atguigu.eduservice.entity.vo.TeacherExcel;
import com.atguigu.eduservice.service.EduTeacherService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener {
  private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
  //这里写持久层的类
  private EduTeacherService eduTeacherService;
  
  

  public DemoDataListener( EduTeacherService eduTeacherService) {
 //进行持久层的类
    this.eduTeacherService = eduTeacherService;

  }
  

  
  private static final int BATCH_COUNT = 5;
  List list = new ArrayList();
  
  //private DemoDAO demoDAO;
  public DemoDataListener() {
    // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
    //demoDAO = new DemoDAO();
  }
  
  
  
  @Override
  public void invoke(TeacherExcel data, AnalysisContext context) {
    LOGGER.info("解析到一条数据:{}",data.toString());
    list.add(data);
    // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM

  }
  
  @Override
  public void doAfterAllAnalysed(AnalysisContext context) {
    // 这里也要保存数据，确保最后遗留的数据也存储到数据库
    saveData();
    LOGGER.info("所有数据解析完成！");
  }
  
  private void saveData() {
    LOGGER.info("{}条数据，开始存储数据库！", list.size());
    //因为我excel模板的实体和插入数据库实体的类不一样，所以需要进行转化

    for (int i =0;i

例如我的excel模板是：



实体类：
字段上ExcelProperty的注解可以使用index声明字段在模板中的顺序，使用value声明模板各个字段的名称。

例如：



@ExcelProperty(value = “讲师简介”,index = 1)
private String intro;






模板的实体类要和excel字段一样对应，不然会出错
效果：



3. 模板下载：
创建一个工具类：

package com.atguigu.eduservice.config;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;


public class ExcelUtil {

  
  public static void writeExcel(HttpServletResponse response, List data, String fileName, String sheetName, Class clazz) throws Exception {
    //表头样式
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    //设置表头居中对齐
    headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    //内容样式
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    //设置内容靠左对齐
    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
    HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
  }
  private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
    fileName = URLEncoder.encode(fileName, "UTF-8");
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf8");
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
    response.addHeader("Access-Control-Expose-Headers", "Content-disposition");

    return response.getOutputStream();
  }
}

调用工具类下载模板

 
  @GetMapping("/download/template")
  public void downloadTemplate(HttpServletResponse response){
    String fileName = "导入讲师模板";
    String sheetName="导入讲师模板";
    List teacherExcelList = new ArrayList<>();
    TeacherExcel teacherExcel = new TeacherExcel();
    teacherExcel.setName("ljz");
    teacherExcel.setIntro("清华毕业，高材生");
    teacherExcel.setCareer("资深讲师");
    teacherExcel.setSort(1);
    teacherExcel.setLevel(1);
    teacherExcelList.add(teacherExcel);
    try {
      //TeacherExcel.class对应你的模板类
      //teacherExcelList模板的例子
      //也可以使用这种方式导出你查询出数据excel文件
      ExcelUtil.writeExcel(response,teacherExcelList,fileName,sheetName,TeacherExcel.class);
    } catch (Exception e) {
      System.out.println(e.getCause());

    }
  }

效果：



有问题可以互相交流，也可以去EasyExcel官网学习
到此这篇关于Spring Boot整合EasyExcel(完整版包含上传解析excel和下载模板)的文章就介绍到这了,更多相关SpringBoot整合EasyExcel内容请搜索考高分网以前的文章或继续浏览下面的相关文章希望大家以后多多支持考高分网！