benym的知识笔记
  • 实践
  • 实用代码
benym
2023-05-17
目录

EasyExcel工具类

# 起步依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.1</version>
</dependency>
1
2
3
4
5
1
2
3
4
5

# EasyExcelUtil

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

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

/**
 * Excel工具类
 */
public class EasyExcelUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(EasyExcelUtil.class);

    private EasyExcelUtil() {
        throw new IllegalStateException("工具类禁止实例化");
    }

    /**
     * 导出
     *
     * @param clazz    clazz
     * @param dataList dataList
     * @param fileName fileName
     * @param <T>      <T>
     */
    public static <T> void export(Class<T> clazz, List<T> dataList, String fileName) {
        try {
            HttpServletResponse response = ((ServletRequestAttributes) Objects.
                    requireNonNull(RequestContextHolder.getRequestAttributes())).getResponse();
            if (response != null && !CollectionUtils.isEmpty(dataList)) {
                LOGGER.info("当前导出文件为:{}, size:{}", fileName, dataList.size());
                // 设置头的样式
                WriteCellStyle headWriteCellStyle = new WriteCellStyle();
                WriteFont headWriteFont = new WriteFont();
                headWriteFont.setFontHeightInPoints((short) 11);
                headWriteCellStyle.setWriteFont(headWriteFont);
                // 设置内容的样式
                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                response.setContentType("application/x-download");
                response.addHeader("Content-Disposition",
                        "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
                EasyExcel.write(response.getOutputStream(), clazz)
                        .registerWriteHandler(new EasyExcelAutoWidthStrategy())
                        .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                        .sheet("Sheet")
                        .doWrite(dataList);
            } else {
                LOGGER.warn("获取不到response，dataList:{}", dataList);
            }
        } catch (Exception e) {
            throw ExceptionFactory.bizException("导出异常", e);
        }
    }
    
    /**
     * 导出文件并上传至文件存储
     *
     * @param clazz    解析的Class
     * @param dataList 数据实体
     * @param fileName 导出文件名
     * @param pathNum  上传路径
     * @param <T>      <T>
     * @return 文件存储中的路径
     */
    public static <T> String exportAndUpload(Class<T> clazz, List<T> dataList, String fileName, Integer pathNum) {
        try {
            HttpServletResponse response = ((ServletRequestAttributes) Objects.
                    requireNonNull(RequestContextHolder.getRequestAttributes())).getResponse();
            if (response != null && !CollectionUtils.isEmpty(dataList)) {
                LOGGER.info("当前导出文件为:{}, size:{}", fileName, dataList.size());
                // 创建临时文件
                File unUploadFile = File.createTempFile(fileName, ".xlsx");
                // 设置头的样式
                WriteCellStyle headWriteCellStyle = new WriteCellStyle();
                WriteFont headWriteFont = new WriteFont();
                headWriteFont.setFontHeightInPoints((short) 11);
                headWriteFont.setBold(false);
                headWriteCellStyle.setWriteFont(headWriteFont);
                // 设置内容的样式
                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                EasyExcel.write(unUploadFile, clazz)
                        .registerWriteHandler(new EasyExcelAutoWidthStrategy())
                        .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                        .sheet("Sheet")
                        .doWrite(dataList);
                byte[] fileData = FileUtil.readBytes(unUploadFile);
                // 这里编写你上传文件存储的逻辑，可使用fileData或File，上文已进行文件流写入
                String uploadedUrl = "";
                FileUtil.del(unUploadFile);
                return uploadedUrl;
            } else {
                LOGGER.info("获取不到response，dataList:{}", dataList);
                return "";
            }
        } catch (Exception e) {
            throw ExceptionFactory.bizException("导出异常", e);
        }
    }
    
    /**
     * 解析Excel文件并校验
     *
     * @param file           前端上传的文件
     * @param clazz          解析后的实体
     * @param readListener   读监听者
     * @param <T>            <T>
     */
    public static <T> void upload(MultipartFile file, Class<T> clazz, ReadListener<T> readListener) {
        try {
            if (file != null && !file.isEmpty()) {
                byte[] fileData = file.getBytes();
                EasyExcel.read(file.getInputStream(), clazz, readListener).sheet().doRead();
                // 这里编写你上传至存储服务器的代码，传递file即可，这里的file已经进行了读取并校验
            }
            LOGGER.info("file is empty，skip upload");
            return "";
        } catch (IOException ioException) {
            throw ExceptionFactory.sysException("上传时异常", ioException);
        } catch (Exception e) {
            throw ExceptionFactory.sysException("上传未知异常", e);
        }
    }
    
    /**
     * 读取Excel数据到class
     * 
     * @param inputStream inputStream
     * @param clazz clazz
     * @return List<T>
     * @param <T> <T>
     */
    public static <T> List<T> parseExcel(InputStream inputStream, Class<T> clazz) {
        List<T> parsedData = new ArrayList<>();
        EasyExcel.read(inputStream, clazz, new AnalysisEventListener<T>() {
            @Override
            public void invoke(T data, AnalysisContext context) {
                parsedData.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                LOGGER.info("解析excel完成");
            }
        }).sheet().doRead();
        return parsedData;
    }
    
}
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
159
160
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
159
160

# 自适应宽度策略

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * EasyExcel自适应宽度策略
 */
public class EasyExcelAutoWidthStrategy extends AbstractColumnWidthStyleStrategy {

    /**
     * 可以根据这里的最大宽度，按自己需要进行调整
     */
    private static final int MAX_COLUMN_WIDTH = 30;
    private final Map<Integer, Map<Integer, Integer>> cache = new HashMap<>(8);

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = cache
                    .computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>(16));
            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > MAX_COLUMN_WIDTH) {
                    columnWidth = MAX_COLUMN_WIDTH;
                }
                Integer maxColumnWidth = (Integer) ((Map<?, ?>) maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }

    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (Boolean.TRUE.equals(isHead)) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData<?> cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }

}
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
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

# 样例导出实体

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * 导出DTO
 */
@ExcelIgnoreUnannotated
@Data
public class ExportDTO implements Serializable {

    private static final long serialVersionUID = 3470002146742757218L;

    /**
     * 第一列
     */
    @ExcelProperty(value = "第一列")
    private String test;

    /**
     * 第二列
     */
    @ExcelProperty(value = "第二列")
    private Long test2;

    /**
     * 第三列，指定列宽20
     */
    @ExcelProperty(value = "第三列")
    @ColumnWidth(20)
    private String test3;

    /**
     * 性别 [man=男，woman=女]
     */
    @ExcelProperty(value = "性别", converter = GenderConverter.class)
    private String gender;

    /**
     * 金额，指定保留2位小数
     */
    @ExcelProperty(value = "金额")
    @NumberFormat("0.00")
    private BigDecimal price;
}
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
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

# 自定义Converter

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;

/**
 * 转换性别字段
 */
public class GenderConverter implements Converter<String> {

    /**
     * 支持的Java类型
     *
     * @return Class<?>
     */
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    /**
     * 支持的Excel内容类型
     *
     * @return CellDataTypeEnum
     */
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 转化数据到Excel数据的策略
     *
     * @param context context
     * @return WriteCellData<?>
     * @throws Exception
     */
    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) throws Exception {
        return new WriteCellData<>(GenderEnum.getEnumsByCode(context.getValue()).getDesc());
    }
    
    /**
     * 转换为Java数据，用于导入
     *
     * @param context 上下文
     * @return {@link String}
     * @throws Exception 异常
     */
    @Override
    public String convertToJavaData(ReadConverterContext<?> context) throws Exception {
        return GenderEnum.getEnumsByDesc(context.getReadCellData().getStringValue()).getCode();
    }
    
}
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
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

# 样例性别枚举类

/**
 * 性别枚举类
 */
public enum GenderEnum {
    /**
     * 男
     */
    MAN("man", "男"),
    /**
     * 女
     */
    WOMAN("woman", "女");

    GenderEnum(String code, String desc) {
        this.code = code;
        this.desc = desc;
    }

    private static final Map<String, GenderEnum> CODE_MAP = new ConcurrentHashMap<>();
    
    private static final Map<String, GenderEnum> DESC_MAP = new ConcurrentHashMap<>();

    static {
        for (GenderEnum genderEnum : EnumSet.allOf(GenderEnum.class)) {
            CODE_MAP.put(genderEnum.getCode(), genderEnum);
            DESC_MAP.put(genderEnum.getDesc(), genderEnum);
        }
    }

    public static GenderEnum getEnumsByCode(String code) {
        return CODE_MAP.get(code);
    }
    
    public static GenderEnum getEnumsByDesc(String desc) {
        return DESC_MAP.get(code);
    }

    /**
     * code
     */
    private String code;

    /**
     * message
     */
    private String desc;

    public String getCode() {
        return code;
    }

    public String getDesc() {
        return desc;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public void setDesc(String desc) {
        this.desc = desc;
    }
}
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
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

# 自定义ReadListener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.holder.ReadRowHolder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import java.util.Set;

/**
 * Listener
 * 用于上传Excel时，对Excel文件的校验或落库
 *
 */
public class ExcelBatchAuditValidListener implements ReadListener<UploadDTO> {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelBatchAuditValidListener.class);

    private static final Validator VALIDATOR = Validation.buildDefaultValidatorFactory().getValidator();

    @Override
    public void invoke(UploadDTO uploadDTO, AnalysisContext analysisContext) {
        // 获取当前解析行
        ReadRowHolder readRowHolder = analysisContext.readRowHolder();
        Integer rowIndex = readRowHolder.getRowIndex();
        LOGGER.info("当前解析数据: {}, 行号: {}", uploadDTO.toString(), rowIndex);
        // 执行javax校验
        Set<ConstraintViolation<UploadDTO>> validateSet = VALIDATOR.validate(uploadDTO);
        for (ConstraintViolation<UploadDTO> result : validateSet) {
            throw ExceptionFactory.bizNoStackException("第" + rowIndex + "行数据格式错误, " + result.getMessage());
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        LOGGER.info("所有数据校验完成");
    }
}
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
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

# 样例上传实体

/**
 * 样例上传DTO
 */
public class UploadDTO implements Serializable {

    private static final long serialVersionUID = -4321582750193296175L;

    /**
     * id
     */
    @NotNull(message = "id为必填")
    @ExcelProperty(value = "Id")
    private Long Id;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
1
2
3
4
5
6
7
8
9
10
11
12
13
14
编辑 (opens new window)
#EasyExcel#导入#导出
上次更新: 2023/09/20, 14:19:24
编程式事务工具类
本地锁工具类

← 本地锁工具类→

最近更新
01
SpringCache基本配置类
05-16
02
DSTransactional与Transactional事务混用死锁场景分析
03-04
03
Rpamis-security-原理解析
12-13
更多文章>
Theme by Vdoing | Copyright © 2018-2024 benym | MIT License
 |   |   | 
渝ICP备18012574号 | 渝公网安备50010902502537号