SpringBoot系列之集成EasyExcel导入合并行数据

SpringBoot系列之集成EasyExcel导入合并行数据

最近在做Excel导入功能，是一种一对多的数据，涉及到合并单元格的，考虑到使用poi去学，要自己去做处理，所以，看看有什么开源的框架，找到两个合适的框架，一个是easypoi是能支持这种的，这个框架提供了特定注解；还有一种是EasyExcel，阿里开源的，不过功能相对没easypoi齐全，比如这种合并单元格数据导入，就没有特定的注解，不过通过搜索资料，是可以实现的，不过要自己写工具类做处理，工具类整理自网上教程

com.alibaba easyexcel 2.2.10

加上​​ExcelProperty​​注解，加上value和index，index是excel对应的列

package com.example.easyexcel.model.dto;import com.alibaba.excel.annotation.ExcelProperty;import lombok.Data;import org.hibernate.validator.constraints.Length;import javax.validation.constraints.NotBlank;@Datapublic class UserExcelDto { @ExcelProperty(value = "序号",index = 0) @NotBlank(message = "序号必须填！") private String seq; @ExcelProperty(value = "用户名" ,index = 1) @NotBlank(message = "用户名必须填！") private String name; @ExcelProperty(value = "密码", index = 2) @Length(min = 0 , max = 8 , message = "密码最多8位！") private String password; @ExcelProperty(value = "描述",index = 3) private String addressName; @ExcelProperty(value = "邮政编码",index = 4) private String code;}

package com.common.excel;import cn.hutool.core.util.ReflectUtil;import cn.hutool.core.util.StrUtil;import cn.hutool.json.JSONUtil;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.event.AnalysisEventListener;import com.alibaba.excel.metadata.CellExtra;import lombok.extern.slf4j.Slf4j;import java.util.ArrayList;import java.util.List;@Slf4jpublic class EasyExcelListener extends AnalysisEventListener { private List datas; private Integer rowIndex; private List extraMergeInfoList; public EasyExcelListener(Integer rowIndex) { this.rowIndex = rowIndex; datas = new ArrayList<>(); extraMergeInfoList = new ArrayList<>(); } @Override public void invoke(T data, AnalysisContext context) { // 是否忽略空行数据，因为自己要做数据校验，所以还是加上，可以根据业务情况使用 context.readWorkbookHolder().setIgnoreEmptyRow(false); ReflectUtil.invoke(data, "setIndex", StrUtil.toString(context.readRowHolder().getRowIndex())); datas.add(data); } @Override public void doAfterAllAnalysed(AnalysisContext context) { log.info("所有数据解析完成！"); } @Override public void extra(CellExtra extra, AnalysisContext context) { switch (extra.getType()) { case MERGE: if (extra.getRowIndex() >= rowIndex) { extraMergeInfoList.add(extra); } break; default: } } public List getData() { return datas; } public List getExtraMergeInfoList() { return extraMergeInfoList; }}

EasyExcel.read(file.getInputStream(), UserExcelDto.class, easyExcelListener) .extraRead(CellExtraTypeEnum.MERGE) .sheet(sheetNo) .headRowNumber(headRowNumber) .doRead(); System.out.println(JSONUtil.toJsonPrettyStr(easyExcelListener.getData()));List extraMergeInfoList = easyExcelListener.getExtraMergeInfoList();

EasyExcel Merge默认数据读取，这种情况，那些非合并的数据，比如姓名，密码这些数据，只会在第一个附上，框架之后的读取，不能读取到

[ { "code": "510000", "password": "********", "name": "user1", "addressName": "广州天河区", "seq": "1" }, { "code": "510001", "addressName": "广州天河区" }, { "code": "510002", "password": "***", "name": "user2", "addressName": "广州天河区", "seq": "2" }]

所以借助网上一个工具类实现，具体参考ExcelMergeHelper

工具类，思路也是读取excel数据，然后读取​​ExcelProperty​​注解，注意必须加上index，通过反射机制读取数据

package com.example.easyexcel.core.excel;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 ExcelMergeHelper { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelMergeHelper.class); /** * 返回解析后的List * * @param: fileName 文件名 * @param: clazz Excel对应属性名 * @param: sheetNo 要解析的sheet * @param: headRowNumber 正文起始行 * @return java.util.List 解析后的List */ public List getList(String fileName, Class clazz, Integer sheetNo, Integer headRowNumber) { UserEasyExcelListener listener = new UserEasyExcelListener<>(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; } /** * 处理合并单元格 * * @param data 解析数据 * @param extraMergeInfoList 合并单元格信息 * @param headRowNumber 起始行 * @return 填充好的解析数据 */ public 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; } /** * 设置合并单元格的值 * * @param filedValue 值 * @param rowIndex 行 * @param columnIndex 列 * @param 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()); } } } } } /** * 获取合并单元格的初始值 * rowIndex对应list的索引 * columnIndex对应实体内的字段 * * @param firstRowIndex 起始行 * @param firstColumnIndex 起始列 * @param data 列数据 * @return 初始值 */ 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; }}

对数据进行处理：

List data = new ExcelMergeHelper().explainMergeData(easyExcelListener.getData(), extraMergeInfoList, headRowNumber);

封装后的数据，这种数据，我们就可以进行业务处理，过程相对比较麻烦

[ { "code": "510000", "password": "********", "name": "user1", "addressName": "广州天河区", "seq": "1" }, { "code": "510001", "password": "********", "name": "user1", "addressName": "广州天河区", "seq": "1" }, { "code": "510002", "password": "***", "name": "user2", "addressName": "广州天河区", "seq": "2" }]

package com.common.excel;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.metadata.CellExtra;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.lang.reflect.Field;import java.util.List;public class ExcelMergeHelper { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelMergeHelper.class); /** * 处理合并单元格 * * @param data 解析数据 * @param extraMergeInfoList 合并单元格信息 * @param headRowNumber 起始行 * @return 填充好的解析数据 */ public 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; } /** * 设置合并单元格的值 * * @param filedValue 值 * @param rowIndex 行 * @param columnIndex 列 * @param data 解析数据 */ public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List data) { if (!(data.size()>rowIndex)) return; 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()); } } } } } /** * 获取合并单元格的初始值 * rowIndex对应list的索引 * columnIndex对应实体内的字段 * * @param firstRowIndex 起始行 * @param firstColumnIndex 起始列 * @param data 列数据 * @return 初始值 */ 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; }}

本博客代码例子可以在​​GitHub​​找到-链接

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。