最近有个需求,导入多个sheet页的数据
excel如下:
实现方式: easyexcel完美解决
1.具体代码:
@Override public String importHybridFaultDict(MultipartFile file) { try { SyncReadListener listener = new SyncReadListener(); ExcelReader excelReader = EasyExcel.read(file.getInputStream(), listener).head(HybridFaultExcelDto.class).build(); List<ReadSheet> sheets = excelReader.excelExecutor().sheetList(); for (int i = 0; i < sheets.size(); i++) { ReadSheet readSheet = sheets.get(i); String sheetName = readSheet.getSheetName(); excelReader.read(readSheet); List<Object> list1 = listener.getList(); logger.info("第" + (i + 1) + "页的数据: {}", JSONObject.toJSONString(list1)); List<HybridFaultDict> list = new ArrayList<>(); for (Object o : list1) { HybridFaultExcelDto entity = (HybridFaultExcelDto) o; HybridFaultDict hybridFaultDict = new HybridFaultDict(); hybridFaultDict.setFaultCode(entity.getFaultCode()); hybridFaultDict.setFaultName(entity.getFaultName()); hybridFaultDict.setProject("W3"); hybridFaultDict.setControl(sheetName); list.add(hybridFaultDict); } this.saveBatch(list); // 清空之前的数据 listener.getList().clear(); list.clear(); } } catch (Exception e) { logger.error("读取数据,报错了"); } return "导入混动字典成功"; }
easyexcel实体映射类:
import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; /** * @description:混动故障码导入 * @author:hfl * @date:Created in 2020/05/18 */ @Data public class HybridFaultExcelDto { /** * 故障码 */ @ExcelProperty(value = "故障代码") private Integer faultCode; @ExcelProperty(value = "故障描述") private String faultName; }
数据库实体映射类
import com.baomidou.mybatisplus.annotation.TableId; import com.baomidou.mybatisplus.annotation.TableName; import lombok.Data; /** * 混动故障码 * * @author hfl * @date 2022-03-08 15:06:32 */ @Data @TableName("hybrid_fault_dict") public class HybridFaultDict extends BaseEntity { /** * 主键 */ @TableId private String faultId; /** * 项目 */ private String project; /** * 控制器名称 */ private String control; /** * 故障码 */ private Integer faultCode; /** * 故障名称 */ private String faultName; }
2.需要的jar包
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.3</version> </dependency>
备注 :
this.saveBatch(list); 使用的是mybatis-plus框架自带的批量保存。
效果
可以看到数据库中都被保存进来了。
大功告成!!