最近有個需求,導入多個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架構自帶的批量儲存。
效果
可以看到資料庫中都被儲存進來了。