1: 导入maven依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
2:定义接受excel数据Dto
package cn.evun.bjtms.bd.dto;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.validator.constraints.NotBlank;
import java.util.Objects;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@HeadRowHeight(value = 40)
public class DemoExcel {
@ExcelProperty(value = "姓名(必填)", index = 0)
@ColumnWidth(value = 20)
private String name;
@ExcelProperty(value = "年龄(必填)", index = 1)
@ColumnWidth(value = 20)
private String age;
@Override
public boolean equals(Object o) {
}
@Override
public int hashCode() {
}
}
3:Controller层
@RequestMapping(value = "/importExcelCourier")
public void importExcelCourier(HttpServletRequest request, HttpServletResponse response, @RequestBody MultipartFile file) {
return service.importExcelCourier(file);
}
4: 监听器,用于校验excel模板和数据
package cn.evun.bjtms.bd.lintener;
import cn.evun.bjtms.bd.dto.BdBasicPriceCourierExcel;
import cn.evun.sweet.framework.common.util.StringUtils;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.lotus.platform.common.model.exception.BusinessException;
import lombok.extern.slf4j.Slf4j;
import java.lang.reflect.Field;
import java.util.Map;
@Slf4j
public class EasyExcelListener extends AnalysisEventListener<DemoExcel > {
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
int count = 0;
Field[] fields = BdBasicPriceCourierExcel.class.getDeclaredFields();
for (Field field : fields) {
ExcelProperty fieldAnnotation = field.getAnnotation(ExcelProperty.class);
if (fieldAnnotation != null) {
++count;
String headName = headMap.get(fieldAnnotation.index());
if (StringUtils.isEmpty(headName) || !headName.equals(fieldAnnotation.value()[0])) {
throw new BusinessException("模板错误,请检查导入模板");
}
}
}
}
@Override
public void invoke(DemoExcel excel, AnalysisContext context) {
Integer rowIndex = context.readRowHolder().getRowIndex();
String str = "第" +rowIndex+ "行";
if (StringUtils.isEmpty(excel.getName())) {
throw new BusinessException(str + "姓名不能为空");
}
if (StringUtils.isEmpty(excel.getAge())) {
throw new BusinessException(str +"年龄不能为空");
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
}
5: service 层
List<DemoExcel> excelList;
try {
excelList = EasyExcel.read(new BufferedInputStream(file.getInputStream()), DemoExcel.class,
new EasyExcelListener ()).sheet().doReadSync();
} catch (Exception e) {
return ResultUtils.failResultMessage(e.getCause().getMessage());
}
List<DemoExcel> distinctList = excelList.stream().distinct().collect(Collectors.toList());
6:官方网址
EasyExcel GitHub地址点击跳转