EasyExcel导入存在合并单元格的Excel

文章目录

- Excel表格
- 实现思路
- 相关代码

Excel表格

下面是Excel表格，成果简介前可以作为主表数据存储(存在合并)，成果简介后的数据作为从表数据存储。

实现思路

通过EasyExcel读取Excel数据，用List list接收
因为POI/EasyExcel对合并单元格的数据只读取一次，需要把为空的单元格也赋值，这是核心技术难点。
循环使用hibernate-validator对数据进行校验
存在校验不通过的数据返回给前端,所有数据校验通过才写入数据库
封装然后写入数据库
- 用Java8 对List< DetailFeiled> list根据多字段进行分组，得到Map map
- 循环map，封装主表和详细表插入list
- 批量插入
导入时要求同一年数据可以分多次导入，但同一年内，成果名称不允许重复
这个可以在数据库中根据获奖类型+获奖年份+成果名称建立联合约束
```
ALTER TABLE t_awards ADD CONSTRAINT award_type_year_product UNIQUE (award_type,award_year,award_product);
```
参考链接

相关代码

下面贴出的是通过EasyExcel读取Excel数据的测试代码

pom依赖：


   
       com.alibaba
       easyexcel
       2.2.6
   
   
       com.alibaba
       fastjson
       1.2.76
   
   
   
       org.projectlombok
       lombok
       1.18.20
       provided
   
   
   
       junit
       junit
       4.12
       test
   
   
   
       org.slf4j
       slf4j-simple
       1.7.25
       compile

AwardsDetailField类：

package com.importexcel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.math.BigDecimal;


@Data
public class AwardsDetailField {

    @ExcelProperty(value="奖项等级（一级分类）",index = 0)
    private String topClass;

    @ExcelProperty(value="奖项等级（二级分类）",index = 1)
    private String secondClass;

    @ExcelProperty(value="获奖年份",index = 2)
    private String awardYear;

    @ExcelProperty(value="获奖产品/成果名称",index = 3)
    private String awardProduct;

    @ExcelProperty(value="成果简介",index = 4)
    private String briefIntroduction;

    @ExcelProperty(value="获奖单位",index = 5)
    private String company;

    @ExcelProperty(value="获奖个人姓名",index = 6)
    private String name;

    @ExcelProperty(value="员工编号",index = 7)
    private String employeeNo;

    @ExcelProperty(value="获奖金额（元）",index = 8)
    private BigDecimal money;
}

importExcelHelper类(导入Excel入口、对合并单元格处理)：

package com.importexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.util.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.util.List;

public class importExcelHelper {

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

    
    public List getList(String fileName, Class clazz, Integer sheetNo, Integer headRowNumber) {
        importExcelListener listener = new importExcelListener<>(headRowNumber);
        try {
            EasyExcel.read(fileName, clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead();
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
        }
        List extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        List data = explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
        return data;
    }

    
    private List explainMergeData(List data, List extraMergeInfoList, Integer headRowNumber) {
        //循环所有合并单元格信息
        extraMergeInfoList.forEach(cellExtra -> {
            int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;
            int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;
            int firstColumnIndex = cellExtra.getFirstColumnIndex();
            int lastColumnIndex = cellExtra.getLastColumnIndex();
            //获取初始值
            Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);
            //设置值
            for (int i = firstRowIndex; i <= lastRowIndex; i++) {
                for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
                    setInitValueToList(initValue, i, j, data);
                }
            }
        });
        return data;
    }

    
    public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List data) {
        T object = data.get(rowIndex);

        for (Field field : object.getClass().getDeclaredFields()) {
            //提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == columnIndex) {
                    try {
                        field.set(object, filedValue);
                        break;
                    } catch (IllegalAccessException e) {
                        LOGGER.error("设置合并单元格的值异常："+e.getMessage());
                    }
                }
            }
        }
    }


    
    private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List data) {
        Object filedValue = null;
        T object = data.get(firstRowIndex);
        for (Field field : object.getClass().getDeclaredFields()) {
            //提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == firstColumnIndex) {
                    try {
                        filedValue = field.get(object);
                        break;
                    } catch (IllegalAccessException e) {
                        LOGGER.error("设置合并单元格的初始值异常："+e.getMessage());
                    }
                }
            }
        }
        return filedValue;
    }
}

Excel模板的读取监听类：

package com.importexcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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


public class importExcelListener extends AnalysisEventListener {
    private static final Logger LOGGER = LoggerFactory.getLogger(importExcelListener.class);
    
    List list = new ArrayList<>();

    
    private Integer headRowNumber;
    
    private List extraMergeInfoList = new ArrayList<>();

    public importExcelListener(Integer headRowNumber) {
        this.headRowNumber = headRowNumber;
    }

    
    @Override
    public void invoke(T data, AnalysisContext context) {
        LOGGER.info("解析到一条数据: ", JSON.toJSONString(data));
        list.add(data);
    }

    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        LOGGER.info("所有数据解析完成！");
    }

    
    public List getData() {
        return list;
    }

    
    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        LOGGER.info("读取到了一条额外信息:{}", JSON.toJSONString(extra));
        switch (extra.getType()) {
            case MERGE: {
                LOGGER.info(
                        "额外信息是合并单元格,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
                        extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                        extra.getLastColumnIndex());
                if (extra.getRowIndex() >= headRowNumber) {
                    extraMergeInfoList.add(extra);
                }
                break;
            }
            default:
        }
    }

    
    public List getExtraMergeInfoList() {
        return extraMergeInfoList;
    }
}

测试类：

package com.importexcel;
import com.alibaba.fastjson.JSON;
import java.util.List;


public class importTest {


    public static void main(String[] args) {
        String PATH = "E:\Downloads\";
        String fileName = PATH + "各奖励网站导入模版.xlsx";
        importExcelHelper helper = new importExcelHelper<>();
        List list = helper.getList(fileName,AwardsDetailField.class,0,1);
        System.out.println(JSON.toJSONString(list));
    }
}

运行后打印的JSON字符串,用JSON工具解析发现完全是想要的效果，成功搞定！

参考链接

EasyExcel导入存在合并单元格的Excel

Java相关栏目本月热门文章