依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency>service 读取excel文件的行数据
DataExcelListener<DeviceTemplateExcel> listener = new DataExcelListener<DeviceTemplateExcel>(); // headRowNumber(1):表示第一行为表头，从第二行取值 ExcelReader excelReader = EasyExcelFactory.read( file.getInputStream() , DeviceTemplateExcel.class, listener).headRowNumber(1).build(); excelReader.readAll(); List<DeviceTemplateExcel> data = listener.getDatas(); excelReader.finish();
@Override@Transactional(rollbackFor = Exception.class)public RP importDevice(MultipartFile file, Long productId) throws IOException {DataExcelListener<DeviceTemplateExcel> listener = new DataExcelListener<DeviceTemplateExcel>();// headRowNumber(1):表示第一行为表头，从第二行取值ExcelReader excelReader = EasyExcelFactory.read( file.getInputStream() , DeviceTemplateExcel.class, listener).headRowNumber(1).build();excelReader.readAll();List<DeviceTemplateExcel> data = listener.getDatas();excelReader.finish();if (data.size() == 3) {return RP.failure("导入失败，请检查导入文件是否正确");}Product product = productService.getById(productId);if (product == null) {throw new ServiceException("产品不存在");}List<String> deviceSnList = data.stream().map(DeviceTemplateExcel::getDeviceSn).collect(Collectors.toList());List<Device> repeatList = baseMapper.selectList(Wrappers.<Device>update().lambda().in(Device::getDeviceSn, deviceSnList));List<Device> deviceList = new ArrayList<>();//导出失败原因List<DeviceFailureExportExcel> exportExcels = new ArrayList<>();for (DeviceTemplateExcel entity : data) {if (StringUtils.isBlank(entity.getDeviceName()) || StringUtils.isBlank(entity.getDeviceSn())) {DeviceFailureExportExcel reason = new DeviceFailureExportExcel();reason.setDeviceName(entity.getDeviceName());reason.setDeviceSn(entity.getDeviceSn());reason.setFailureReason(StringUtils.isBlank(entity.getDeviceName()) ? "设备名称不能为空！" : "设备序列号不能为空！");exportExcels.add(reason);continue;}//数据库中的判重Optional<Device> deviceOpt = repeatList.stream().filter(item -> item.getDeviceSn().equals(entity.getDeviceSn())).findFirst();//还未新增的设备判重Optional<Device> deviceAddOpt = deviceList.stream().filter(item -> item.getDeviceSn().equals(entity.getDeviceSn())).findFirst();if (deviceOpt.isPresent() || deviceAddOpt.isPresent()) {DeviceFailureExportExcel reason = new DeviceFailureExportExcel();reason.setDeviceName(entity.getDeviceName());reason.setDeviceSn(entity.getDeviceSn());reason.setFailureReason("设备序列号已存在！");exportExcels.add(reason);continue;}Device device = new Device();device.setDeviceName(entity.getDeviceName());device.setDeviceSn(entity.getDeviceSn());device.setProductId(productId);device.setTenantId(AuthUtil.getTenantId()); deviceList.add(device);}if (deviceList.size() > 0) {this.saveBatch(deviceList);}if (exportExcels.size() > 0) {return RP.failure("请重新处理失败的数据！");} else {return RP.failure("操作成功");}}
监听类
package com.sinenux.iot.core.exect;import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;import java.util.ArrayList;
import java.util.List;/*** 解析监听器*/
public class DataExcelListener<T> extends AnalysisEventListener<T> {/*** 自定义用于暂时存储data* 可以通过实例获取该值*/private List<T> datas = new ArrayList<>();/*** 每解析一行都会回调invoke()方法** @param object 读取后的数据对象* @param context 内容*/@Override@SuppressWarnings("unchecked")public void invoke(Object object, AnalysisContext context) {T data = (T) object;//数据存储到list，供批量处理，或后续自己业务逻辑处理。datas.add(data);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//解析结束销毁不用的资源//注意不要调用datas.clear(),否则getDatas为null}/*** 返回数据** @return 返回读取的数据集合**/public List<T> getDatas() {return datas;}/*** 设置读取的数据集合** @param datas 设置读取的数据集合**/public void setDatas(List<T> datas) {this.datas = datas;}}
实体类 DeviceTemplateExcel
注意这里 lombok的@Data注解 和EasyExcel有冲突 不能使用 @Data 要用 get set 方法
package com.sinenux.iot.core.exect;import com.alibaba.excel.annotation.ExcelProperty;import java.io.Serializable;/*** 设备导入模板* 注意这里 lombok的@Data注解 和EasyExcel有冲突 不能使用 @Data 要用 get set 方法** @author xulk*/public class DeviceTemplateExcel implements Serializable {private static final long serialVersionUID = 1L;@ExcelProperty(value = "设备名称", index = 0)private String deviceName;@ExcelProperty(value = "设备序列号(不可重复)", index = 1)private String deviceSn;public String getDeviceName() {return deviceName;}public void setDeviceName(String deviceName) {this.deviceName = deviceName;}public String getDeviceSn() {return deviceSn;}public void setDeviceSn(String deviceSn) {this.deviceSn = deviceSn;}
}