java读取Excel表之easyexcel读取转换：分转元

官方地址：https://alibaba-easyexcel.github.io/quickstart/read.html
参考文章：https://blog.csdn.net/weixin_44163370/article/details/122884356

1.自定义Converter

package cn.edu.cqu.paymentservice.service;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import java.math.BigDecimal;
import java.math.RoundingMode;

/**
 * easyexcel自定义转换器
 * @Author: hjl
 * @Date: 2022/5/25 0025 10:44
 */
public class CustomFenToYuanConverter implements Converter<BigDecimal> {
    // 指定转化参数的Java类型
    @Override
    public Class<?> supportJavaTypeKey() {
        return BigDecimal.class;
    }

    // 指定转化参数对应的单元格类型
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.NUMBER;
    }

    // 单元格参数类型转化为Java类型处理逻辑
    // 注意读取格式cellData.getStringValue()
    @Override
    public BigDecimal convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new BigDecimal(cellData.getStringValue()).divide(new BigDecimal(100),2,RoundingMode.HALF_UP);
    }

}

2.字段上加注解

package cn.edu.cqu.paymentservice.dto;

import cn.edu.cqu.paymentservice.service.CustomFenToYuanConverter;
import com.alibaba.excel.annotation.ExcelProperty; 
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor; 
import java.io.Serializable;
import java.math.BigDecimal; 

/**
 * 拉卡拉结算汇总实体类
 */
@Data
@NoArgsConstructor
@AllArgsConstructor 
public class LaKaLaSettlementSummaryDTO implements Serializable {

    private static final long serialVersionUID = -2276319513977941707L;

    @ExcelProperty(value = "金额（元）",converter = CustomFenToYuanConverter.class)
    private BigDecimal fee;
}

3.工具类

package cn.edu.cqu.paymentservice.ExcelDataListener;

import cn.edu.cqu.paymentservice.dao.ExcelBaseMapper;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.List;

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class ExcelDataListener<T, M extends ExcelBaseMapper> extends AnalysisEventListener<T> {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelDataListener.class);

    private static final int BATCH_COUNT = 3000;
    List<T> list = new ArrayList<T>();

    private M mapper;

    public ExcelDataListener(M mapper) {
        this.mapper = mapper;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
//        System.out.println(list.size()+"行数据："+JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        if (list.size()>0){
            mapper.insertList(list);
        }

        LOGGER.info("存储数据库成功！");
    }

    /**
     *
     * @param exception
     * @param context
     */
    @Override
    public void onException(Exception exception, AnalysisContext context){

    }


}

4.Mapper基础接口

package cn.edu.cqu.paymentservice.dao;

import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
 * @Author: hjl
 * @Date: 2022/5/16 0016 14:42
 */
public interface ExcelBaseMapper extends Mapper {
    int insertList(List<?> list);
}

@Mapper
public interface SettlementRecordMapper  extends ExcelBaseMapper{

    int insertList(@Param("list") List<?> listRecord);
}

5.测试

/**
 * @author xuzhipeng
 * @date 2021/9/29
 */
@RestController
public class Controller {

	@Autowired
    private SettlementRecordMapper settlementRecordMapper;

	/**
     * DOTO 测试excel文件读取
     *
     * @return
     */
     @PostMapping("/SettlementFlow")
    @Transactional
    public void SettlementFlow(@RequestBody JSONObject jsonObject){
        String fileName=jsonObject.getString("fileName");
        System.out.println("fileName  "+ fileName);
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, LaKaLaSettlementRecordDTO.class,
                new cn.edu.cqu.paymentservice.ExcelDataListener.ExcelDataListener(settlementRecordMapper)).sheet().doRead(); 
    }

}