前几天业务需求,需要导入公司数据和项目数据(excel格式),运用easyexcel导入实现如下:
excel文件映射实体
@Data public class ExcelItem { @ExcelProperty(value = "序号") private Integer index; /** * 公司名称 */ @ExcelProperty(value = "企业名称") private String companyName; /** * 公司地址 */ @ExcelProperty(value = "镇街") private String address; /** * 项目名称 */ @ExcelProperty(value = "项目名称") private String itemName; /** * 固定资产总投资 */ @ExcelProperty(value = "固定资产总投资") private String fixedTotalInvest; /** * 设备投资 */ @ExcelProperty(value = "其中设备投资") private String deviceInvest; /** * 已完工数量 */ @ExcelProperty(value = "已完成投资") private String completedInvest; @ExcelProperty("2022年拟投资") private String nowYearInvest; /** * 项目审批情况 */ @ExcelProperty(value = "项目审批情况") private String authState; /** * 拟开完工时间 */ @ExcelProperty(value = "拟开完工时间") private String date; /** * 项目标签 */ @ExcelProperty(value = "项目标签1") private String label; public Integer getIndex() { return index; } public void setIndex(Integer index) { this.index = index; } public String getItemName() { return itemName; } public void setItemName(String itemName) { this.itemName = itemName; } public String getFixedTotalInvest() { return fixedTotalInvest; } public void setFixedTotalInvest(String fixedTotalInvest) { this.fixedTotalInvest = fixedTotalInvest; } public String getDeviceInvest() { return deviceInvest; } public void setDeviceInvest(String deviceInvest) { this.deviceInvest = deviceInvest; } public String getCompletedInvest() { return completedInvest; } public void setCompletedInvest(String completedInvest) { this.completedInvest = completedInvest; } public String getAuthState() { return authState; } public void setAuthState(String authState) { this.authState = authState; } public String getDate() { return date; } public void setDate(String date) { this.date = date; } public String getLabel() { return label; } public void setLabel(String label) { this.label = label; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getCompanyName() { return companyName; } public void setCompanyName(String companyName) { this.companyName = companyName; } public String getNowYearInvest() { return nowYearInvest; } public void setNowYearInvest(String nowYearInvest) { this.nowYearInvest = nowYearInvest; }
监听器
@Slf4j
@Service
public class ExcelItemListener extends AnalysisEventListener<ExcelItem> {
/**
* 批处理阈值
*/
private static final int BATCH_COUNT = 5;
List<ExcelItem> list = new ArrayList<>(BATCH_COUNT);
@Override
public void invoke(ExcelItem excelItem, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(excelItem));
list.add(excelItem);
if (list.size() >= BATCH_COUNT) {
importItemInfo(list);
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
importItemInfo(list);
log.info("所有数据解析完成!");
}
public void importItemInfo(List<ExcelItem> infoList) {
//导入操作---新增公司和项目数据
}
}
测试类调用
//文件路径 String fileName = "D:\\导入文件.xlsx"; //调用方法实现读取 EasyExcel.read(fileName,ExcelItem.class,new ExcelItemListener()).sheet().doRead();