Java使用FastExcel实现Excel文件导入

 依赖配置 (Maven pom.xml)

<dependencies><!-- FastExcel 核心库 --><dependency><groupId>cn.idev.excel</groupId><artifactId>fastexcel</artifactId><version>1.0.0</version></dependency><!-- Apache POI 依赖 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.3</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version></dependency>
</dependencies>

映射实体类SetMealDetailsVo

package com.fantaibao.module.vo.appDish;import cn.idev.excel.annotation.ExcelIgnoreUnannotated;
import cn.idev.excel.annotation.ExcelProperty;
import cn.idev.excel.annotation.write.style.ColumnWidth;
import cn.idev.excel.annotation.write.style.HeadFontStyle;
import cn.idev.excel.annotation.write.style.HeadStyle;
import cn.idev.excel.enums.BooleanEnum;
import cn.idev.excel.enums.poi.FillPatternTypeEnum;
import com.fantaibao.module.po.DishAppManagementOriginal;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;import java.math.BigDecimal;
import java.util.Date;@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ExcelIgnoreUnannotated
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 22)
@HeadFontStyle(fontName = "Microsoft YaHei", fontHeightInPoints = 11, bold = BooleanEnum.TRUE)
public class SetMealDetailsVo {/*** 套餐ID*/@ColumnWidth(30)@ExcelProperty("套餐ID")private String code;/*** 标准套餐名称*/@ColumnWidth(30)@ExcelProperty("标准套餐名称")private String menuName;/*** 套餐定价*/private BigDecimal price;/*** 套餐定价*/@ColumnWidth(20)@ExcelProperty("套餐定价")private String priceStr;/*** 套餐毛利率(%)*/@ColumnWidth(20)@ExcelProperty("套餐毛利率(%)")private String grossMarginStr;/*** 毛利率(%)*/private BigDecimal grossMargin;/*** 明细菜编码*/@ColumnWidth(30)@ExcelProperty("明细菜编码")private String detailsCode;/*** 明细菜品名称*/@ColumnWidth(30)@ExcelProperty("明细菜品名称")private String detailsMenuName;/*** 明细菜单位*/@ColumnWidth(30)@ExcelProperty("明细菜品单位")private String detailsUnit;/*** 明细菜定价*/@ColumnWidth(20)@ExcelProperty("明细菜定价")private String detailsPriceStr;private BigDecimal detailsPrice;/*** 明细菜毛利率(%)*/@ColumnWidth(20)@ExcelProperty("明细菜毛利率(%)")private String detailsGrossMarginStr;/*** 明细菜毛利率(%)*/private BigDecimal detailsGrossMargin;/*** 明细菜分类*/@ColumnWidth(20)@ExcelProperty("明细菜分类")private String detailsClass;}

自定义监听器CustomSheetListener（带校验表头）

package com.fantaibao.listener;import cn.hutool.core.collection.CollUtil;
import cn.idev.excel.annotation.ExcelProperty;
import cn.idev.excel.context.AnalysisContext;
import cn.idev.excel.metadata.data.ReadCellData;
import cn.idev.excel.read.listener.ReadListener;
import cn.idev.excel.util.StringUtils;
import lombok.Getter;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.List;
import java.util.Map;@Slf4j
@Getter
@RequiredArgsConstructor
public class CustomSheetListener<T> implements ReadListener<T> {/*** 返回最终解析的数据*/private List<T> dataList;private List<String> fields;public CustomSheetListener(List<T> dataList, Class clazz) {this.dataList = dataList;this.fields = getFields(clazz);}/*** 根据class通过反射获取字段上@ExcelProperty注解的值** @param clazz 类名* @return 注解的值*/private static List<String> getFields(Class clazz) {List<String> fields = CollUtil.newArrayList();//根据class通过反射获取字段上@ExcelProperty注解的值Field[] declaredFields = clazz.getDeclaredFields();for (Field field : declaredFields) {ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);if (annotation != null) {String[] value = annotation.value();// 添加到列表中fields.addAll(Arrays.asList(value));}}return fields;}@Overridepublic void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {// 在这里检查表头是否正确for (Map.Entry<Integer, ReadCellData<?>> entry : headMap.entrySet()) {String headerValue = entry.getValue().getStringValue();// 根据你的需求检查表头值if (!isValidHeader(headerValue)) {throw new RuntimeException("模版错误，请重新上传");}}//if(!fields.isEmpty() && context.readSheetHolder().getHeadRowNumber()-1 == context.readSheetHolder().getRowIndex()){throw new RuntimeException("模版错误，请重新上传");}}@Overridepublic void invoke(T data, AnalysisContext context) {dataList.add(data);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {log.info("excel解析完成,sheet名为：{}", context.readSheetHolder().getSheetName());}/**** @param header 表头名称* @return 是否存在 存在则移除掉*/private boolean isValidHeader(String header) {if (StringUtils.isBlank(header)) {return true;}if(this.fields.contains(header)){fields.removeIf(field->field.equals(header));return true;}return false;}
}

业务菜调用

//fileUrl是文件全路径(我这里是腾讯云OSS文件路径)
List<SetMealDetailsVo> setMealDetails = CollUtil.newArrayList();
try {EasyExcel.read(new URL(fileUrl).openStream(),SetMealDetailsVo.class,new CustomSheetListener(setMealDetails, SetMealDetailsVo.class)).sheet(0).doRead();
} catch (Exception e) {throw new RuntimeException("模版错误，请重新上传");
}