SpringBoot使用EasyExcel将Excel表数据导入到数据库笔记

EasyExcel简介

EasyExcel是一款阿里开源的Excel导入导出工具，具有处理快速、占用内存小、使用方便的特点，在Github上已有22k+Star，可见其非常流行。

EasyExcel读取75M(46W行25列)的Excel，仅需使用64M内存，耗时20s，极速模式还可以更快！

我的Excel表样式

execl表的工作表是默认名字sheet1，没有修改

maven导入相关依赖

<!--EasyExcel相关依赖-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>
复制代码

1.创建一个与Excel表对应的Java对象

@Data
public class DepositDataRecordApproveImport {

    @ExcelProperty(index = 0,value = "数据源类型")
    private String depositType;

    @ExcelProperty(index = 1,value = "年度")
    private String year;

    @ExcelProperty(index = 2,value = "我行存款")
    private String bankSum;

    @ExcelProperty(index = 3,value = "市场存款")
    private String marketSum;

    @ExcelProperty(index = 4,value = "描述")
    private String recordDesc;
}

@ExcelProperty注解中的value对应表头信息

可以设置类型转换器，具体请看这位博主的

SpringBoot实现Excel导入导出，性能爆表，用起来够优雅，_普通网友的博客-CSDN博客_springboot导入excel

2.编写Controller层

@PostMapping("/import")
    public ResponseBean imports(@RequestParam("file") MultipartFile file) throws IOException {
      
        InputStream inputStream = file.getInputStream();
        List<DepositDataRecordApproveImport> reqCustomerDailyImports = EasyExcel.read(inputStream)
                .head(DepositDataRecordApproveImport.class)
                .registerConverter(new StringConverter())
                // 注册监听器，可以在这里校验字段
                .registerReadListener(new CustomerDailyImportListener())
                // 设置sheet,默认读取第一个
                .sheet(0)
                // 设置标题所在行数
                .headRowNumber(2)
                .doReadSync();
        System.out.println(reqCustomerDailyImports);
        return depositDataRecordApproveService.importDetail(reqCustomerDailyImports);
    }

注意list里面的泛型，是编写的表Java对象，和head方法读取的一样

3.StringConverter（）的编写

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

public class StringConverter implements Converter<String> {

    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    /**
     * 将excel对象转成Java对象，这里读的时候会调用
     *
     * @param cellData            NotNull
     * @param contentProperty     Nullable
     * @param globalConfiguration NotNull
     * @return
     */
    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
                                    GlobalConfiguration globalConfiguration) {
        return cellData.getStringValue();
    }

    /**
     * 将Java对象转成String对象，写出的时候调用
     *
     * @param value
     * @param contentProperty
     * @param globalConfiguration
     * @return
     */
    @Override
    public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
                                       GlobalConfiguration globalConfiguration) {
        return new CellData(value);
    }
}

4.CustomerDailyImportListener的编写

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;

public class CustomerDailyImportListener extends AnalysisEventListener {


    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
            System.out.println("CustomerDailyImportListener------->");
    }


    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        // ExcelDataConvertException:当数据转换异常的时候，会抛出该异常，此处可以得知第几行，第几列的数据
        if (exception instanceof ExcelDataConvertException) {
            Integer columnIndex = ((ExcelDataConvertException) exception).getColumnIndex() + 1;
            Integer rowIndex = ((ExcelDataConvertException) exception).getRowIndex() + 1;
            String message = "第" + rowIndex + "行，第" + columnIndex + "列" + "数据格式有误，请核实";
            throw new RuntimeException(message);
        } else if (exception instanceof RuntimeException) {
            throw exception;
        } else {
            super.onException(exception, context);
        }
    }

    /**
     * 解析完全部回调
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }
}

5.编写serviceImpl（service接口就不演示了）

@Service
public class DepositDataRecordApproveServiceImpl implements DepositDataRecordApproveService {
 @Override
    public ResponseBean importDetail(List<DepositDataRecordApproveImport> reqCustomerDailyImports) {
        //导入之前，先将原来的数据进行清空
//        LambdaQueryWrapper<BankMonitorTargetDetail> detailQueryWrapper = new LambdaQueryWrapper<>();
//        detailQueryWrapper.eq(BankMonitorTargetDetail::getTargetId, id);
//        monitorTargetDetailMapper.delete(detailQueryWrapper);
        //循环进行导入
        for(DepositDataRecordApproveImport detailImport :  reqCustomerDailyImports){
            DepositDataRecordApprove detail = new DepositDataRecordApprove();
            BeanUtils.copyProperties(detailImport, detail);
            //设置值
            detail.setDepositType(Integer.valueOf((int)Math.floor(Double.valueOf(detailImport.getDepositType()))));
            detail.setYear(String.valueOf((int)Math.floor(Double.valueOf(detailImport.getYear()))));
            detail.setBankSum(Integer.valueOf((int)Math.floor(Double.valueOf(detailImport.getBankSum()))));
            detail.setMarketSum(Integer.valueOf((int)Math.floor(Double.valueOf(detailImport.getMarketSum()))));
            detail.setRecordDesc(detailImport.getRecordDesc());
            //对百分比进行处理
            double pecentage =detail.getBankSum()/(double)detail.getMarketSum();
            System.out.println(pecentage);
            DecimalFormat df = null;
            if((pecentage*100)%1 == 0){
                df =  new DecimalFormat("0%");
            }else if((pecentage*1000)%1 == 0){
                df =  new DecimalFormat("0.0%");
            }else {
                df =  new DecimalFormat("0.00%");
            }
            detail.setPecentage(df.format(pecentage));
            depositDataRecordApproveMapper.insert(detail);
        }

        return ResponseBean.builder().build();

    }
}

注意：你创的与表对应的java对象只用与接收数据，接受到数据后遍历进行数据获取，然后赋值给你的dao层的对象（自己创建，我这里的dao层对象的值比表对象要多），然后正常用insert方法将数据遍历插入数据库就行，这里的返回值类型你们可以自己定义，我这里是封装了。