依賴

        <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;}
}