EasyExcel使用demo

Controller层代码

@Value("${jeecg.path.upload}")
private String multipartPre;

private static List<List<String>> head = new ArrayList<>();

static {
    head = new ArrayList<>(Arrays.asList(
            Collections.singletonList("编号"),
            Collections.singletonList("所属层级"),
            Collections.singletonList("原料编号"),
            Collections.singletonList("原料名称"),
            Collections.singletonList("原料状态"),
            Collections.singletonList("创建时间"),
            Collections.singletonList("更新时间"),
            Collections.singletonList("录入人")));
}

@ApiOperation(value = "模板导出")
@GetMapping("downloadTemp")
public void downloadTemp(HttpServletResponse response) throws Exception {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("数据模板", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    List<List<Object>> list = ListUtils.newArrayList();
    EasyExcel.write(response.getOutputStream()).head(head).sheet("data").doWrite(list);
}

@ApiOperation(value = "导出Excel数据")
@PostMapping("export")
public void exportAllStructData(@RequestBody MaterialInfoRequestDTO materialInfoRequestDTO, HttpServletResponse response){
    try (OutputStream out = response.getOutputStream()) {

        List<MaterialInfoPO> materialInfoPOS = iMaterialInfoService.queryList(materialInfoRequestDTO);

        //组装Excel内容
        List<List<String>> content = new ArrayList<>();
        materialInfoPOS.stream().forEach(m->{
            List<String> data = new ArrayList<String>();
            data.add(String.valueOf(m.getOrderNo()));
            data.add(m.getLevelName());
            data.add(m.getMaterialNo());
            data.add(m.getMaterialName());
            data.add(String.valueOf(m.getMaterialStatus()));
            DateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            data.add(dateformat.format(m.getCreateTime()));
            data.add(dateformat.format(m.getUpdateTime()));
            data.add(m.getRecorder());

            content.add(data);
        });

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("catalog", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        ExcelWriter excelWriter = EasyExcelFactory.write(out).build();
        WriteSheet data = EasyExcel.writerSheet(0, "data").head(head).build();
        excelWriter.write(content, data);
        excelWriter.finish();
        out.flush();
    } catch (Exception e) {
        throw new JeecgBootException("下载Excel失败！"+e.getMessage());
    }
}

@ApiOperation(value = "上传Excel数据")
@PostMapping("upload")
public void uploadData(@RequestParam("file") MultipartFile file, @RequestParam("ids") List<Integer> ids) {
    if (file.isEmpty()) {
        throw new JeecgBootException("请上传Excel！");
    }

    File inputFile = null;
    try {
        String fileName = file.getOriginalFilename();
        String filePath = multipartPre + "/" + fileName.replace("'", "");
        File localFile = new File(filePath);
        if (!localFile.getParentFile().exists()) {
            localFile.getParentFile().mkdirs();
        }
        file.transferTo(localFile);
        inputFile = new File(filePath);
        CatalogueReadListener catalogueReadListener = new CatalogueReadListener();
        List<CatalogueExcelVO> data = EasyExcel.read(inputFile, catalogueReadListener).head(CatalogueExcelVO.class).sheet().doReadSync();
        iMaterialInfoService.importData(data);
        if(localFile.exists()){
            localFile.delete();
        }
    } catch (Exception e) {
        throw new JeecgBootException("导入Excel失败！"+e.getMessage());
    } finally {
        if (!ObjectUtils.isEmpty(inputFile)) {
            inputFile.delete();
        }
    }
}

读取Excel 重写listener

package com.zhanwan.modules.catalogue.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.zhanwan.modules.catalogue.entity.CatalogueExcelVO;
import lombok.extern.slf4j.Slf4j;

import java.util.Map;

/**
 * @Copyright 上海展湾信息科技有限公司
 * @Author LZ
 * @Date 2022/8/8 16:14
 */
@Slf4j
public class CatalogueReadListener extends AnalysisEventListener<CatalogueExcelVO> {
    /**
     * 每解析一条数据，都会来调用该方法
     * 对所有数据进行校验，在此增加校验逻辑
     *
     * @param excelDTO
     * @param analysisContext
     */
    @Override
    public void invoke(CatalogueExcelVO excelDTO, AnalysisContext analysisContext) {
    }

    /**
     * 每解析一行表头，会调用该方法
     *
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        if (!headMap.containsKey(0) || !headMap.containsKey(1) || !headMap.containsKey(2)
                || !headMap.containsKey(3)
                || !headMap.get(0).equals("所属维度（必须）") || !headMap.get(1).equals("对象名称（必须）")
                || !headMap.get(2).equals("所属层级（必须）") || !headMap.get(3).equals("上一层级对象")
        ) {
            throw new RuntimeException("表头校验失败");
        }
    }

    /**
     * 所有数据都解析完成后，会调用该方法
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("数据处理完成后会调用该方法");
    }
}

@Data
@ApiModel(value = "CatalogueExcelVO", description = "Excel导入VO")
public class CatalogueExcelVO implements Serializable {

   private static final long serialVersionUID = 1L;

   @ExcelProperty(value = "所属维度（必须）")
   private String dimensionName;

   @ExcelProperty(value = "对象名称（必须）")
   private String catalogueName;

   @ExcelProperty(value = "所属层级（必须）")
   private String levelName;

   @ExcelProperty(value = "上一层级对象")
   private String parentName;

}