EasyExcel 自定义配置列名 导入导出_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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13

导出

导出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());
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

导出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));
            }
        }
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

导入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";
    }
1
2
3
4
5
6
7
8
9
10
11
12

导入

导入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() {

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

导入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);
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158