最近有个需求，导入多个sheet页的数据

excel如下:

excel多sheet页的导入_导入

实现方式: 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框架自带的批量保存。

效果

可以看到数据库中都被保存进来了。

excel多sheet页的导入_java_02

大功告成!!