EasyExcel 自定义配置列名 导入导出

实体类

@Data
// 如果lombok全局开启了lombok.accessors.chain=true, 这里需要关闭
@Accessors(chain = false)
public class DownloadData {

    // 占位标识, 后续有处理
    @ExcelProperty({"${first}", "${catalog}"})
    private String name;
    @ExcelProperty({"${first}", "${name}"})
    private String title;
    @ExcelProperty({"${other}", "${xxxx}"})
    private String xxx;
}

导出

导出Controller

    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        // 实体类中占位符想要改成的列名, 当然这边可以读取自定义在其他地方的配置, 再转换为这种结构
        HashMap<String, String> map = new HashMap<>();
        map.put("first","项目");
        map.put("other","其他");
        map.put("catalog", "目录");
        map.put("name", "名称");
        map.put("xxxx", "test");
        EasyExcel.write(response.getOutputStream(), DownloadData.class)
                .sheet("模板")
                .registerWriteHandler(new DownloadHandler(map))
                .doWrite(data());
    }

    private List<DownloadData> data() {
        return IntStream.range(1, 10).mapToObj(i -> {
                    DownloadData downloadData = new DownloadData();
                    downloadData.setName("name" + i);
                    downloadData.setTitle("title" + i);
                    downloadData.setXxx("xxx" + i);
                    return downloadData;
                })
                .collect(Collectors.toList());
    }

导出Listener

public class DownloadHandler implements CellWriteHandler {

    private final Map<String, String> map;

    // 实体类注解内的前后缀
    PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");

    public DownloadHandler(Map<String, String> map) {
        this.map = map;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
        if (head != null) {
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)) {
                Properties properties = new Properties();
                map.forEach(properties::setProperty);
                // 核心 替换表头导出
                headNameList.replaceAll(value -> placeholderHelper.replacePlaceholders(value, properties));
            }
        }
    }

}

导入Controller

    @PostMapping("upload")
    public String upload(MultipartFile file) throws IOException {
        // 与导出相反的数据结构
        HashMap<String, String> map = new HashMap<>();
        map.put("项目", "first");
        map.put("其他", "other");
        map.put("目录", "catalog");
        map.put("名称", "name");
        map.put("test", "xxxx");
        EasyExcel.read(file.getInputStream(), new DownloadListener(map)).head(DownloadData.class).sheet().doRead();
        return "success";
    }

导入

导入Listener

public class DownloadListener extends AnalysisEventListener<DownloadData> {

    private final Map<String, String> map;

    public DownloadListener(Map<String, String> map) {
        this.map = map;
    }

    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        headMap.forEach((k, v) -> {
            String name = map.get(v.getStringValue());
            String format = String.format("${%s}", name);
            // 核心 替换读取的表头数据
            v.setStringValue(format);
        });
    }

    @Override
    public void invoke(DownloadData data, AnalysisContext context) {
        System.out.println("data = " + JSON.toJSONString(data));
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
    }

    private void saveData() {

    }
}

导入AnalysisEventProcessor(核心)

在EasyExcel 的 github-pr-2795 未合并时, 暂时使用这种方式
如果不改这个的话, 上面替换表头数据是不生效的, 因为在回调invokeHead之前就已经把表头数据build存储在了headMap里, 需要把buildMap放在回调之后

利用同全类名（包名和类文件名全部一致）的加载顺序优先级来来覆盖原 class

有兴趣的话可以看一下

// 利用同全类名（包名和类文件名全部一致）的加载顺序优先级来来覆盖原 class
package com.alibaba.excel.read.processor;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.enums.HeadKindEnum;
import com.alibaba.excel.enums.RowTypeEnum;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.exception.ExcelAnalysisStopException;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.holder.ReadRowHolder;
import com.alibaba.excel.read.metadata.property.ExcelReadHeadProperty;
import com.alibaba.excel.util.ConverterUtils;
import com.alibaba.excel.util.StringUtils;
import org.apache.commons.collections4.MapUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
 * Analysis event 在pr <a href="https://github.com/alibaba/easyexcel/pull/2795">...</a> 未合并时, 暂时使用这种方式
 */
@SuppressWarnings("all")
public class DefaultAnalysisEventProcessor implements AnalysisEventProcessor {
	private static final Logger LOGGER = LoggerFactory.getLogger(DefaultAnalysisEventProcessor.class);

	@Override
	public void extra(AnalysisContext analysisContext) {
		dealExtra(analysisContext);
	}

	@Override
	public void endRow(AnalysisContext analysisContext) {
		if (RowTypeEnum.EMPTY.equals(analysisContext.readRowHolder().getRowType())) {
			if (LOGGER.isDebugEnabled()) {
				LOGGER.debug("Empty row!");
			}
			if (analysisContext.readWorkbookHolder().getIgnoreEmptyRow()) {
				return;
			}
		}
		dealData(analysisContext);
	}

	@Override
	public void endSheet(AnalysisContext analysisContext) {
		for (ReadListener readListener : analysisContext.currentReadHolder().readListenerList()) {
			readListener.doAfterAllAnalysed(analysisContext);
		}
	}

	private void dealExtra(AnalysisContext analysisContext) {
		for (ReadListener readListener : analysisContext.currentReadHolder().readListenerList()) {
			try {
				readListener.extra(analysisContext.readSheetHolder().getCellExtra(), analysisContext);
			} catch (Exception e) {
				onException(analysisContext, e);
				break;
			}
			if (!readListener.hasNext(analysisContext)) {
				throw new ExcelAnalysisStopException();
			}
		}
	}

	private void onException(AnalysisContext analysisContext, Exception e) {
		for (ReadListener readListenerException : analysisContext.currentReadHolder().readListenerList()) {
			try {
				readListenerException.onException(e, analysisContext);
			} catch (RuntimeException re) {
				throw re;
			} catch (Exception e1) {
				throw new ExcelAnalysisException(e1.getMessage(), e1);
			}
		}
	}

	private void dealData(AnalysisContext analysisContext) {
		ReadRowHolder readRowHolder = analysisContext.readRowHolder();
		Map<Integer, ReadCellData<?>> cellDataMap = (Map)readRowHolder.getCellMap();
		readRowHolder.setCurrentRowAnalysisResult(cellDataMap);
		int rowIndex = readRowHolder.getRowIndex();
		int currentHeadRowNumber = analysisContext.readSheetHolder().getHeadRowNumber();

		boolean isData = rowIndex >= currentHeadRowNumber;

		// Now is data
		for (ReadListener readListener : analysisContext.currentReadHolder().readListenerList()) {
			try {
				if (isData) {
					readListener.invoke(readRowHolder.getCurrentRowAnalysisResult(), analysisContext);
				} else {
					readListener.invokeHead(cellDataMap, analysisContext);
				}
			} catch (Exception e) {
				onException(analysisContext, e);
				break;
			}
			if (!readListener.hasNext(analysisContext)) {
				throw new ExcelAnalysisStopException();
			}
		}
		// Last head column
		if (!isData && currentHeadRowNumber == rowIndex + 1) {
			buildHead(analysisContext, cellDataMap);
		}
	}

	private void buildHead(AnalysisContext analysisContext, Map<Integer, ReadCellData<?>> cellDataMap) {
		// Rule out empty head, and then take the largest column
		if (MapUtils.isNotEmpty(cellDataMap)) {
			cellDataMap.entrySet()
					.stream()
					.filter(entry -> CellDataTypeEnum.EMPTY != entry.getValue().getType())
					.forEach(entry -> analysisContext.readSheetHolder().setMaxNotEmptyDataHeadSize(entry.getKey()));
		}

		if (!HeadKindEnum.CLASS.equals(analysisContext.currentReadHolder().excelReadHeadProperty().getHeadKind())) {
			return;
		}
		Map<Integer, String> dataMap = ConverterUtils.convertToStringMap(cellDataMap, analysisContext);
		ExcelReadHeadProperty excelHeadPropertyData = analysisContext.readSheetHolder().excelReadHeadProperty();
		Map<Integer, Head> headMapData = excelHeadPropertyData.getHeadMap();
		Map<Integer, Head> tmpHeadMap = new HashMap<Integer, Head>(headMapData.size() * 4 / 3 + 1);
		for (Map.Entry<Integer, Head> entry : headMapData.entrySet()) {
			Head headData = entry.getValue();
			if (headData.getForceIndex() || !headData.getForceName()) {
				tmpHeadMap.put(entry.getKey(), headData);
				continue;
			}
			List<String> headNameList = headData.getHeadNameList();
			String headName = headNameList.get(headNameList.size() - 1);
			for (Map.Entry<Integer, String> stringEntry : dataMap.entrySet()) {
				if (stringEntry == null) {
					continue;
				}
				String headString = stringEntry.getValue();
				Integer stringKey = stringEntry.getKey();
				if (StringUtils.isEmpty(headString)) {
					continue;
				}
				if (analysisContext.currentReadHolder().globalConfiguration().getAutoTrim()) {
					headString = headString.trim();
				}
				if (headName.equals(headString)) {
					headData.setColumnIndex(stringKey);
					tmpHeadMap.put(stringKey, headData);
					break;
				}
			}
		}
		excelHeadPropertyData.setHeadMap(tmpHeadMap);
	}
}