alibaba-easyExcel动态列导入导出

一、导入Maven依赖

<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>4.0.2</version> <type>pom</type> <scope>import</scope> </dependency>

二、service层

//当前年指标项--动态列头 Map<String, EntInput> entInputMap = list.stream().collect(Collectors.toMap(EntInput::getItemCode, Function.identity())); //过滤必传列头 List<String> required = Optional.ofNullable(list).get().stream().filter(entInputCompare -> { return entInputCompare.getStatus() == -1; }).map(EntInput::getItemName).collect(Collectors.toList()); //监听拦截校验 EasyExcelListener readListener = new EasyExcelListener(required, list); EasyExcel.read(multipartFile.getInputStream(), readListener).sheet().headRowNumber(indexStart - 1).doRead(); Map<String, Integer> nameIndexHeaderMap = readListener.getNameIndexHeaderMap(); for (String string : required) { if (!nameIndexHeaderMap.containsKey(string)) { throw new ServerException("导入失败,请按照模版导入"); } }

三、监听层

package org.captain.modules.excel; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.metadata.CellExtra; import lombok.Getter; import org.captain.core.log.exception.ServiceException; import org.captain.core.tool.utils.DateTimeUtil; import org.captain.core.tool.utils.StringUtil; import org.captain.modules.ent.entity.EntInput; import org.captain.modules.enums.StatusEntInput; import java.math.BigDecimal; import java.util.*; public class EasyExcelListener extends AnalysisEventListener<Map<Integer, String>> { //表头数据（存储所有的表头数据） private List<Map<Integer, String>> headList = new ArrayList<>(); private List<String> headNameList = new ArrayList<>(); private Map<Integer, String> indexNameHeaderMap = new HashMap<>(); @Getter private Map<String, Integer> nameIndexHeaderMap = new HashMap<>(); //数据体 private List<Map<Integer, String>> dataList = new ArrayList<>(); private List<Integer> requiredIndex = new ArrayList<>(); private List<String> required; private List<EntInput> headerCheck; @Getter private Map<Integer, EntInput> headerCheckMap = new HashMap<>(); @Getter private Map<String, EntInput> entInputLogMap = new HashMap<>(); @Getter private Map<String, Integer> entTypeMap = new LinkedHashMap<String, Integer>(); private StringBuffer checkMessage = new StringBuffer(); private Integer firstDataRow = 0; private Integer startHeaderRow = 1; //默认开始行从0开始 public EasyExcelListener(List<String> required, List<EntInput> headerCheck) { this.required = required; this.headerCheck = headerCheck; } @Override//这里会一行行的返回头 public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { //存储全部表头数据 Integer currentRowNum = context.getCurrentRowNum(); if (currentRowNum < startHeaderRow) { return; } for (Integer keyNext : headMap.keySet()) { String valuesNext = headMap.get(keyNext); if (valuesNext != null) { String replace = valuesNext.replace((char) 12288, ' '); valuesNext = replace.trim(); } if (currentRowNum == startHeaderRow) { //代表是树形的第一行 当树形列名为空时，赋值前一列名的值 String firstTree = Optional.ofNullable(valuesNext).orElse(headMap.get(keyNext - 1)).trim(); headMap.put(keyNext, firstTree); headNameList.add(firstTree); nameIndexHeaderMap.put(firstTree, keyNext); if (required.contains(firstTree)) { requiredIndex.add(keyNext); } indexNameHeaderMap.put(keyNext, firstTree); } else { //树行的第二层 String last = headList.get(headList.size() - 1).get(keyNext); String current = Optional.ofNullable(valuesNext).orElse("").trim(); if (last!=null&&!last.equals(current)) { String values = last + current; headMap.put(keyNext, values); headNameList.add(values); indexNameHeaderMap.put(keyNext, values); nameIndexHeaderMap.put(values, keyNext); } } } headList.add(headMap); } @Override// 处理每一行数据 ,列号从0 开始计数 public void invoke(Map<Integer, String> data, AnalysisContext context) { if (firstDataRow == 0) { for (EntInput input : headerCheck) { if (Objects.equals(input.getStatus(), StatusEntInput.EntInfo.getStatus())) { if (!headNameList.contains(input.getItemName())) { throw new ServiceException("列头缺失"+input.getItemName()+"，请按下载模版导入"); } } if (input.getStatus() == 1) { //为一对多的企业类型项 entTypeMap.put(input.getItemName(), nameIndexHeaderMap.get(input.getItemName().trim())); //因为导入是多列，但是实际为一个属性值概念只记录一次企业类型项 if (input.getFlag()) { entInputLogMap.put("企业类型数据项", input); } } if (input.getStatus() == 0 && input.getFlag()) { entInputLogMap.put(input.getItemName(), input); } headerCheckMap.put(nameIndexHeaderMap.get(input.getItemName()), input); } firstDataRow++; } dataList.add(data); boolean flag; for (Map.Entry<Integer, String> entry : data.entrySet()) { Integer integer = entry.getKey(); String s = entry.getValue(); flag = false; StringBuilder message = new StringBuilder(); message.append("第").append(dataList.size()).append("行数据找不到必传数据，列头名为："); if (requiredIndex.contains(integer) && StringUtil.isBlank(s)) { flag = true; message.append(indexNameHeaderMap.get(integer)+","); } if (flag) { checkMessage.append(message).append("\n"); } } } @Override// 全部处理结束执行 public void doAfterAllAnalysed(AnalysisContext context) { if (StringUtil.isNotBlank(checkMessage.toString())) { throw new ServiceException(checkMessage.toString()); } } @Override public void extra(CellExtra extra, AnalysisContext context) { } public List<Map<Integer, String>> getDataList() { return dataList; } }

主要通过利用map结构，对表头进行确定，和数据整理对齐，这个方式适合各种业务场景。前提是一个sheet只有一个表头的情况，如果多个，则需要自写excel导入相关