当前位置：首页 > news >正文

Execl数据导入 EasyExcel实现

news 来源：原创 2024/9/30 4:32:54

官网

1. 需求简介

读取下面表格数据
在这里插入图片描述
第一行和第二行是计划信息
第三行是计划详情的抬头信息,以下行是计划详情信息
总段包含多个分段,总段使用了单元格合并功能

2. 实现读取功能

2.1 引入easyexcel依赖

        <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.0</version></dependency>

2.2 创建计划详情行信息对象

package com.gkdz.server.modules.shipyard.domain;import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;import java.util.Date;@Getter
@Setter
@EqualsAndHashCode
public class Summary {@ExcelProperty(value = "序号", index = 0)private String number;@ExcelProperty(value = "总段", index = 1)private String totalSection;@ExcelProperty(value = "分段", index = 2)private String subSection;@ExcelProperty(value = "分段预估重量(T)", index = 3)private Double subExpectWeight;@ExcelProperty(value = "长", index = 4)private Double length;@ExcelProperty(value = "宽", index = 5)private Double width;@ExcelProperty(value = "高", index = 6)private Double height;@ExcelProperty(value = "分段类型", index = 7)private String type;@ExcelProperty(value = "总段预估重量(T）", index = 8)private Double totalExpectWeight;@ExcelProperty(value = "分段交付时间", index = 9)private String subSectionDeliveryTime;@ExcelProperty(value = "总组吊装日期", index = 10)private String hoistDate;@ExcelProperty(value = "总组焊前", index = 11)private String totalBeWeldDate;@ExcelProperty(value = "总组焊前周期", index = 12)private String totalBeWeldCycle;@ExcelProperty(value = "总组完工日期", index = 13)private String totalCompletionDate;@ExcelProperty(value = "总组完工周期", index = 14)private String totalCompletionCycle;@ExcelProperty(value = "搭载吊装日期", index = 15)private String carryLiftDate;@ExcelProperty(value = "搭载焊前日期", index = 16)private String carryBeWeldDate;@ExcelProperty(value = "搭载焊前周期", index = 17)private String carryBeWeldCycle;@ExcelProperty(value = "搭载完工日期", index = 18)private String carryCompletionDate;@ExcelProperty(value = "搭载完工周期", index = 19)private String carryCompletionCycle;@ExcelProperty(value = "吊装顺序", index = 20)private String hoistOrder;@ExcelProperty(value = "封舱设备", index = 21)private String sealingEquipment;@ExcelProperty(value = "备注", index = 22)private String remark;//标记,用于分组,ExcelIgnore为忽略注解@ExcelIgnoreprivate String elementStr;/**行号*/@ExcelIgnoreprivate int rowNo;
}

2.3 读取execl工具类

package com.gkdz.server.modules.shipyard.util;import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.fastjson.JSON;
import com.gkdz.server.modules.shipyard.domain.Summary;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.multipart.MultipartFile;import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;@Slf4j
public class EasyExcelUtil {/*** 计划详情正文起始行*/private static final Integer headRowNumber = 3;/*** 分组切割符号*/public static final String splitFlag = "<zld>";public static Map<String, List> uploadByFile(MultipartFile file) throws IOException {Map<String, List> map = new HashMap<>();//正文行数据List<Summary> saveList = new ArrayList<>();//抬头行数据List<String> planInfoList = new ArrayList<>();//合并单元格数据List<CellExtra> extraMergeInfoList = new ArrayList<>();EasyExcel.read(file.getInputStream(), Summary.class, new ReadListener<Summary>() {/*** 读取表格抬头行数据,默认为读第一行;* 可设置headRowNumber(headRowNumber):抬头行为前headRowNumber行* @param headMap* @param context*/@Overridepublic void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {planInfoList.add(headMap.get(0).getStringValue());}/*** 读取非抬头行数据* @param data 行数据格式,自定义,默认为Object* @param analysisContext*/@Overridepublic void invoke(Summary data, AnalysisContext analysisContext) {if (StrUtil.isEmptyIfStr(data.getTotalSection())) {data.setTotalSection(data.getSubSection());}saveList.add(data);}/**** @param extra* @param context*/@Overridepublic void extra(CellExtra extra, AnalysisContext context) {log.info("读取到了一条额外信息:{}", JSON.toJSONString(extra));switch (extra.getType()) {case COMMENT: {log.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(),extra.getText());break;}case HYPERLINK: {if ("Sheet1!A1".equals(extra.getText())) {log.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(),extra.getColumnIndex(), extra.getText());} else if ("Sheet2!A1".equals(extra.getText())) {log.info("额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{},"+ "内容是:{}",extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),extra.getLastColumnIndex(), extra.getText());} else {log.error("Unknown hyperlink!");}break;}case MERGE: {if (extra.getRowIndex() >= headRowNumber) {extraMergeInfoList.add(extra);}break;}default: {}}}/*** 数据读取完毕后执行的方法,可做数据整体处理* @param analysisContext*/@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}}).extraRead(CellExtraTypeEnum.MERGE).sheet().headRowNumber(headRowNumber).doRead();final List<Summary> summaries = ExcelSplitUtil.explainMergeData(saveList, extraMergeInfoList, headRowNumber);map.put("planInfo", planInfoList);map.put("summary", summaries);return map;}/*** 根据easyexcel注解给指定实体赋值** @param objects 读取的表格内容* @param clazz   需转化的实体* @param <T>     实体* @return 需转化的提示集合*/public static <T> List<T> convertList(List<LinkedHashMap> objects, Class<T> clazz) {List<T> results = new ArrayList<>(objects.size());try {Map<String, Field> objIndex = new HashMap<>();// 获取转化实体字段信息集合Field[] fields = clazz.getDeclaredFields();for (Field field : fields) {// 根据实体上Easy Excel的ExcelProperty注解中的索引值对应excel读取数据的值int index = field.getAnnotation(ExcelProperty.class).index();// 设置字段可编辑field.setAccessible(true);objIndex.put(String.valueOf(index), field);}T obj = null;for (LinkedHashMap o : objects) {obj = clazz.newInstance();for (Object key : o.keySet()) {// 如果表格索引与字段注解指定索引一样则赋值if (objIndex.containsKey(key)) {Object object = o.get(key);Object value = null;Field field = objIndex.get(key);if (ObjectUtil.isEmpty(object)) {continue;}Class<?> type = field.getType();String replace = object.toString();// 有特殊需要处理的字段类型则在此进行处理if (type == BigDecimal.class) {value = "--".equals(replace) ? null : new BigDecimal(replace.replace(",", ""));} else if (type == Integer.class) {// String强转Integer会报错，所以需要单独进行转化value = "--".equals(replace) ? null : Integer.valueOf(replace.replace(",", ""));} else {value = object;}field.set(obj, value);}}results.add(obj);}} catch (Exception e) {log.error("字段解析失败", e);}return results;}
}

2.4 拆分单元格工具

package com.gkdz.server.modules.shipyard.util;import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.CellExtra;
import com.gkdz.server.modules.shipyard.domain.Summary;
import lombok.extern.slf4j.Slf4j;import java.lang.reflect.Field;
import java.util.List;/*** @description：拆分单元格数据*/
@Slf4j
public class ExcelSplitUtil {/*** 处理合并单元格** @param data               解析数据* @param extraMergeInfoList 合并单元格信息* @param headRowNumber      起始行* @return 填充好的解析数据*/public static List<Summary> explainMergeData(List<Summary> data, List<CellExtra> 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        解析数据*/private static void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<Summary> data) {Summary 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) {log.error("解析数据时发生异常!");}}}}}/*** 获取合并单元格的初始值* rowIndex对应list的索引* columnIndex对应实体内的字段** @param firstRowIndex    起始行* @param firstColumnIndex 起始列* @param data             列数据* @return 初始值*/private static Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<Summary> data) {Object filedValue = null;Summary 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) {log.error("解析数据时发生异常!");}}}}return filedValue;}
}