easyExcel 数据导入

1
2
3
4
5
6
7
8
@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

1
2
3
4
5
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {    

}

1
2
 @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {}

1
EasyExcel.read(file.getInputStream(), YclJzLqJcVo.class, yclJzLqExcelDataListener).sheet().headRowNumber(3).doRead();

1
2
@Override
public void invoke(YclJzLqJcVo data, AnalysisContext context) {}

1
2
@Override
public void doAfterAllAnalysed(AnalysisContext context) {}

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
package easttrans.pitchdatacore.domain.core;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;

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

/**
 * <p>
 * 
 * </p>
 *
 * @author guankong
 * @since 2022-10-26
 */
@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="改性沥青模板映射类", description="")
public class YclGxLqJcVo extends CommonVo implements Serializable {

    private static final long serialVersionUID=1L;

    @ApiModelProperty(value = "id")
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @ExcelProperty(index = 0, value = "序号")
    @ApiModelProperty(value = "序号")
    private Integer serialNum;

    @ExcelProperty(index = 1, value = "进场日期")
    @ApiModelProperty(value = "进场日期")
    private Date enterDate;

    @ExcelProperty(index = 2, value = "品牌及标号")
    @ApiModelProperty(value = "品牌及标号")
    private String brand;

    @ExcelProperty(index = 3, value = "堆放地点")
    @ApiModelProperty(value = "堆放地点")
    private String place;

    @ExcelProperty(index = 4, value = "用途")
    @ApiModelProperty(value = "用途面层")
    private String ratio;

    @ExcelProperty(index = 5, value = "质保单编号")
    @ApiModelProperty(value = "质保单编号")
    private String qaNum;

    @ExcelProperty(index = 6, value = "同批数量（t）")
    @ApiModelProperty(value = "同批数量（t）")
    private Double count;

    @ExcelProperty(index = 7, value = "委托单或任务单编号")
    @ApiModelProperty(value = "委托单或任务单编号")
    private String taskNum;

    @ExcelProperty(index = 8, value = "针入度0.1mm")
    @ApiModelProperty(value = "针入度0.1mm")
    private Double zrd;


}

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

@Slf4j
public final  class YclJzLqExcelDataListener extends AnalysisEventListener<YclJzLqJcVo> {

    private final YclLqJcVo yclLqJcVo;

    private YclLqJcService yclLqJcService;
    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    private List<YclJzLqJcVo> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 存储异常提醒消息
     */
    private Map<String, Object> result = new HashMap<>();
    /**
     * excel表头是否正确,false 有误,true 正确。
     */
    private boolean format = false;

    /**
     * 头信息
     */
    Map<Integer, String> headMap = new HashMap<>();

    /**
     * 返回提示语
     */
    public List<ImportTips> tips = new ArrayList<>();

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param
     */
    public YclJzLqExcelDataListener(YclLqJcService yclLqJcService,YclLqJcVo yclLqJcVo) {
        this.yclLqJcService = yclLqJcService;
        this.yclLqJcVo = yclLqJcVo;
    }


    /**
     * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
     *
     * @param exception
     * @param context
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context)   {
        log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
        int col = 0,row =0;
        String title = "";
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
            log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
                    excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
            col = excelDataConvertException.getColumnIndex();
            row = excelDataConvertException.getRowIndex();
            title = this.headMap.get(col);

        }
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        this.headMap = headMap;
    }


    /**
     * 这里会一行行的返回头
     *
     * @param headMap
     * @param context
     */
    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        log.info("解析到一条头数据:{}", JSON.toJSONString(headMap));
        // 如果想转成 Map<Integer,String>
        // 方案1： 不要implements ReadListener 而是 extends AnalysisEventListener
        // 方案2： 调用 ConverterUtils.convertToStringMap(headMap, context) 自动会转换
        Map<Integer, String> integerStringMap = ConverterUtils.convertToStringMap(headMap, context);
        String s = integerStringMap.get(0);
        if (s != null && s.equals(Constants.JZ_Asp_Mob_Ins_Account)){
            //如果表头包含该内容，则为true
            format = true;
        }
        
    }

    @Override
    public void invoke(YclJzLqJcVo data, AnalysisContext context) {
        result.put("format",format);
        if (!format){
            //format 为false 时
            throw new ExcelAnalysisStopException(Constants.formatTemplateNoTrue);
        }

        //查询改性沥青数据库,判断该数据库中的报告编号是否与cachedDataList 中的报告编号重复，重复不导入
        LambdaQueryWrapper<YclLqJcVo> yclJzLqJcVoLambdaQueryWrapper = new LambdaQueryWrapper<>();
        yclJzLqJcVoLambdaQueryWrapper.eq(YclLqJcVo::getReportNum,data.getReportNum());
        yclJzLqJcVoLambdaQueryWrapper.eq(YclLqJcVo::getType,Constants.JZLQ_TYPE);

        List<YclLqJcVo> list = yclLqJcService.list(yclJzLqJcVoLambdaQueryWrapper);
        if (list.size()>0){
            //表示有数据,该数据不能新增进数据库
            saveTips(context.readRowHolder().getRowIndex(),Constants.Duplicate_Import_Information,tips);
            return;
        }

        
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

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

        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
    
        yclLqJcService.saveBatch(collect);
        log.info("存储数据库成功！");
    }

    /**
     * 返回数据
     * @return 返回读取的数据集合
     **/
    public Map<String,Object> getResult(){
        return result;
    }

    /**
     * 设置读取的数据集合
     * @param result 设置读取的数据集合
     **/
    public void setResult(Map<String,Object> result) {
        this.result = result;
    }

  

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

    @RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    @ApiOperation(value = "沥青进场检验台账导入数据", notes = "沥青进场检验台账导入数据")
    public EastTransResult importExcel(@RequestParam("file") MultipartFile file, YclLqJcVo yclLqJcVo) throws IOException {
        EastTransResult<Object> eastTransResult = new EastTransResult<>();
        //两个判断，第一个是表头，第二个是内容（哪一行的数据不对）
            //type 等于2 为改性沥青,先判断第一行导入模板的表头是否准确
            YclGxLqExcelDataListener yclGxLqExcelDataListener = new YclGxLqExcelDataListener(yclLqJcService, yclLqJcVo);
            EasyExcel.read(file.getInputStream(), YclGxLqJcVo.class, yclGxLqExcelDataListener).sheet().headRowNumber(3).doRead();
            Map<String, Object> result = yclGxLqExcelDataListener.getResult();
            boolean format = (boolean) result.get("format");
            eastTransResult = new EastTransResult(200, null, "请求成功");
            if (!format) {
                //表头错误
                eastTransResult = new EastTransResult(403, null, "该模板不正确");
            }

        }
        return eastTransResult;
    }