1.需求說明
通過黨組織全稱和上級黨組織全稱作為資料唯一key更新其它資料。
2.添加 EasyExcel 依賴
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency> 3.提供給前端的接口
public Long uploadBatchUpdateOrgFile(@RequestParam("file") MultipartFile file) throws IOException {
String operator = InvocationContexts.getContext().getUserId();
IccAssert.notNull(file, "上傳的導入檔案不能為空");
IccAssert.notEmpty(file.getOriginalFilename(), "上傳的黨組織檔案名不能為空");
// 插入導入任務
ObExcelScheduleTask taskParam = new ObExcelScheduleTask();
// taskParam.set
...
ObExcelScheduleTask task = taskService.add(taskParam);
if (task == null) {
throw new IccException(IccError.ICC_ERROR);
}
InputStream inputStream = file.getInputStream();
// 啟用線程池異步導入資料
ThreadPoolBusService.IMPORT_TASK_POOL.submit(new Runnable() {
@Override
public void run() {
// 資料解析 實作AnalysisEventListener
OrgBatchUpdateExcelListener orgBatchUpdateExcelListener = new OrgBatchUpdateExcelListener(commonDictService,
orgQueryService, taskService, tenantCode, file.getOriginalFilename(), orgBusinessId, task, partyUserService);
EasyExcel.read(inputStream, OrgBatchUpdateExcelModel.class, orgBatchUpdateExcelListener).sheet().doRead();
// 更新資料
orgService.batchUpdateImportOrg(operator, tenantCode, file.getOriginalFilename(), task,
orgBatchUpdateExcelListener.getSuccessResultList(), orgBatchUpdateExcelListener.getFailResultList());
}
});
return task.getId();
} 5.講解AnalysisEventListener< T>
監聽器有哪些方法
public abstract class AnalysisEventListener<T> implements ReadListener<T> {
// 這是監聽器的構造方法，一般我們可以通過構造方法傳入一些我們需要在解析excel時使用的資料
public AnalysisEventListener() {}
// 調用invokeHeadMap來擷取表頭資料
public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
this.invokeHeadMap(ConverterUtils.convertToStringMap(headMap, context), context);
}
// 擷取表頭資料
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {}
// 讀取條額外資訊:批注、超連結、合并單元格資訊等
public void extra(CellExtra extra, AnalysisContext context) {}
// 在轉換異常 擷取其他異常下會調用本接口。抛出異常則停止讀取。如果這裡不抛出異常則 繼續讀取下一行。
public void onException(Exception exception, AnalysisContext context) throws Exception {throw exception;}
public boolean hasNext(AnalysisContext context) {return true;}
} 其中可以看到AnalysisEventListener 實作了 ReadListener
然後看到ReadListener後 發現其中還有兩個方法是AnalysisEventListener沒有實作的，而且這兩個方法還是很重要的
public interface ReadListener<T> extends Listener {
void onException(Exception var1, AnalysisContext var2) throws Exception;
void invokeHead(Map<Integer, CellData> var1, AnalysisContext var2);
// 一行行讀取表格内容
void invoke(T var1, AnalysisContext var2);
void extra(CellExtra var1, AnalysisContext var2);
// 讀取完成後的操作
void doAfterAllAnalysed(AnalysisContext var1);
boolean hasNext(AnalysisContext var1);
} 6.實作AnalysisEventListener過濾成功和失敗的資料
public class OrgBatchUpdateExcelListener extends AnalysisEventListener<OrgBatchUpdateExcelModel> {
private CommonDictService commonDictService;
private OrgQueryService orgQueryService;
private TaskService taskService;
private PartyUserService partyUserService;
private ObExcelScheduleTask task;
/**
* 源檔案名
*/
private String originalFilename;
/**
* 組織編碼
*/
private String orgBusinessId;
/**
* 驗證成功的資料
*/
private List<OrgBatchUpdateExcelModel> successResultList;
/**
* 驗證失敗的資料
*/
private List<OrgBatchUpdateExcelModel> failResultList;
/**
* 黨組織類别
*/
private Map<String, String> dzzlbRemarkMap;
/**
* 黨組織所在行政區劃
*/
private Map<String, String> xzqhMap;
/**
* 黨組織所在機關情況
*/
private Map<String, String> dwqkMap;
/**
* 黨組織類别
*/
private Map<String, String> dzzlbMap;
/**
* 删除操作
*/
private static final String OPERATE_DELETE = "删除";
/**
* 修改操作
*/
private static final String OPERATE_UPDATE = "修改";
private static final String EXCEL_HEAD_ERROR = "檔案不正确，請重新下載下傳模闆";
private static final String EXCEL_EMPTY_ERROR = "檔案裡沒有資料";
private static final String EXCEL_DATA_T00_LONG = "excel裡資料不能大于1000條";
/**
* excel頭
*/
private static final String HEAD = "{0=黨組織全稱, 1=上級黨組織全稱, 2=黨組織聯系人, 3=黨組織聯系電話(手機号), 4=黨組織類别, 5=黨組織所在機關情況, 6=黨組織所在行政區劃, 7=主要業務, 8=操作}";
/**
* excel頭
*/
private static final String HEAD_TWO = "{0=黨組織全稱, 1=上級黨組織全稱, 2=黨組織聯系人, 3=黨組織聯系電話(手機号), 4=黨組織類别, 5=黨組織所在機關情況, 6=黨組織所在行政區劃, 7=主要業務, 8=操作, 9=錯誤原因}";
/**
* excel頭
*/
private Map<Integer, String> HEAD_MAP = null;
/**
* 導入的起始行
*/
private static final Integer START_ROW_INDEX = 3;
/**
* 最大導入數量
*/
private static final Integer MAX_ROWS = 1000;
/**
* 租戶
*/
private String tenantCode;
public OrgBatchUpdateExcelListener(CommonDictService commonDictService, OrgQueryService orgQueryService, TaskService taskService,
String tenantCode, String originalFilename, String orgBusinessId, ObExcelScheduleTask task,
PartyUserService partyUserService) {
this.commonDictService = commonDictService;
this.orgQueryService = orgQueryService;
this.taskService = taskService;
this.tenantCode = tenantCode;
this.originalFilename = originalFilename;
this.orgBusinessId = orgBusinessId;
this.task = task;
this.partyUserService = partyUserService;
init();
}
private void init() {
Map<String, String> dzzlbMap = commonDictService.getValueKeyByTypeCode(OrgConst.DICT_DZZ_ZZLB);
Map<String, String> dzzlbRemarkMap = commonDictService.getValueRemarkByTypeCode(OrgConst.DICT_DZZ_ZZLB);
Map<String, String> xzqhMap = commonDictService.getValueKeyByTypeCode(OrgConst.D_DZZ_XZQH);
Map<String, String> dwqkMap = commonDictService.getValueKeyByTypeCode(OrgConst.D_DZZ_DWQK);
this.dzzlbRemarkMap = dzzlbRemarkMap;
this.xzqhMap = xzqhMap;
this.dwqkMap = dwqkMap;
this.dzzlbMap = dzzlbMap;
this.successResultList = new ArrayList<>();
this.failResultList = new ArrayList<>();
}
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
log.info("fileName:" + originalFilename);
// 驗證excel是否合規
context.readSheetHolder().setHeadRowNumber(START_ROW_INDEX);
HEAD_MAP = headMap;
int rowIndex = context.readRowHolder().getRowIndex();
if (rowIndex == START_ROW_INDEX - 1) {
String head = String.valueOf(HEAD_MAP);
if (!StringUtils.equals(head, HEAD) && !StringUtils.equals(head, HEAD_TWO)) {
log.info("head:{}", head);
throw new ExcelAnalysisException(EXCEL_HEAD_ERROR);
}
int totalRows = context.readSheetHolder().getApproximateTotalRowNumber();
if (totalRows <= START_ROW_INDEX) {
throw new ExcelAnalysisException(EXCEL_EMPTY_ERROR);
}
if (totalRows - START_ROW_INDEX > MAX_ROWS) {
throw new ExcelAnalysisException(EXCEL_DATA_T00_LONG);
}
}
}
@Override
public void invoke(OrgBatchUpdateExcelModel orgBatchUpdateExcelModel, AnalysisContext analysisContext) {
StringBuffer errorMsg = new StringBuffer();
int rowIndex = analysisContext.readRowHolder().getRowIndex();
if (rowIndex >= START_ROW_INDEX) {
log.info("====目前資料========:{}", orgBatchUpdateExcelModel);
// 驗證資料是否正确
if (StringUtils.isBlank(orgBatchUpdateExcelModel.getOrgName())) {
errorMsg.append("|黨組織全稱不能為空");
}
if (StringUtils.isBlank(orgBatchUpdateExcelModel.getParentOrgName())) {
errorMsg.append("|上級黨組織全稱不能為空");
}
if (StringUtils.isBlank(orgBatchUpdateExcelModel.getOperate())) {
errorMsg.append("|操作列不能未空");
} else if (StringUtils.equals(orgBatchUpdateExcelModel.getOperate(), OPERATE_DELETE)) {
// 删除
orgBatchUpdateExcelModel.setDeleteFlag(OrgConst.YES_STATE);
} else {
// 更新
String linker = orgBatchUpdateExcelModel.getLinker();
String linkPhone = orgBatchUpdateExcelModel.getLinkPhone();
String partyTypeName = orgBatchUpdateExcelModel.getPartyTypeName();
String partyCompanyIntroName = orgBatchUpdateExcelModel.getPartyCompanyIntroName();
String xzqhName = orgBatchUpdateExcelModel.getXzqhName();
String mainBusiness = orgBatchUpdateExcelModel.getMainBusiness();
if (StringUtils.isBlank(linker) && StringUtils.isBlank(linkPhone) && StringUtils.isBlank(partyTypeName)
&& StringUtils.isBlank(partyCompanyIntroName) && StringUtils.isBlank(xzqhName)
&& StringUtils.isBlank(mainBusiness)) {
errorMsg.append("|請至少輸入一個修改項");
}
if (StringUtils.isNotBlank(linkPhone)
&& !Validator.isMobile(linkPhone)) {
errorMsg.append("|黨組織聯系電話格式錯誤");
}
if (StringUtils.isNotBlank(partyTypeName)) {
String partyType = dzzlbMap.get(orgBatchUpdateExcelModel.getPartyTypeName());
if (StringUtils.isBlank(partyType)) {
errorMsg.append("|黨組織類别錯誤");
} else {
orgBatchUpdateExcelModel.setPartyType(partyType);
orgBatchUpdateExcelModel.setPartyTypeSimple(dzzlbRemarkMap.get(orgBatchUpdateExcelModel.getPartyTypeName()));
}
}
if (StringUtils.isNotBlank(partyCompanyIntroName)) {
String partyCompanyIntro = dwqkMap.get(orgBatchUpdateExcelModel.getPartyCompanyIntroName());
if (StringUtils.isBlank(partyCompanyIntro)) {
errorMsg.append("|黨組織所在機關情況錯誤");
} else {
orgBatchUpdateExcelModel.setPartyCompanyIntro(partyCompanyIntro);
}
}
if (StringUtils.isNotBlank(xzqhName)) {
String xzqh = xzqhMap.get(orgBatchUpdateExcelModel.getXzqhName());
if (StringUtils.isBlank(xzqh)) {
errorMsg.append("|黨組織所在行政區劃錯誤");
} else {
orgBatchUpdateExcelModel.setXzqh(xzqh);
}
}
}
String errorMsgStr = errorMsg.toString();
if (StringUtils.isNotBlank(errorMsgStr)) {
orgBatchUpdateExcelModel.setErrorMsg(errorMsgStr);
this.clear(orgBatchUpdateExcelModel);
failResultList.add(orgBatchUpdateExcelModel);
} else {
successResultList.add(orgBatchUpdateExcelModel);
}
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
if (CollectionUtils.isEmpty(successResultList) && CollectionUtils.isEmpty(failResultList)) {
throw new ExcelAnalysisException(EXCEL_EMPTY_ERROR);
}
if (!CollectionUtils.isEmpty(successResultList)) {
List<String> orgNameList = successResultList.stream().map(e -> e.getOrgName()).collect(Collectors.toList());
String pids = orgQueryService.getPids(orgBusinessId, tenantCode);
List<ObOrgDTO> obOrgDTOList = orgQueryService.queryForBatchUpdateOrg(orgNameList, orgBusinessId, pids, tenantCode);
// map 用來擷取組織編碼
Map<String, ObOrgDTO> map = obOrgDTOList.stream()
.collect(Collectors.toMap(ObOrgDTO::getOrgName, o -> o, (v1, v2) -> v1));
// 上級黨組織的資訊
List<String> pidList = obOrgDTOList.stream().map(e -> e.getPid()).collect(Collectors.toList());
Map<String, ObPartyOrg> parentOrgMap = orgQueryService.batchQueryPartyOrg(pidList, tenantCode, OrgConst.NO_STATE);
// 本級黨組織的資訊
List<String> currentOrgIdList = obOrgDTOList.stream().map(e -> e.getOrgBusinessId()).collect(Collectors.toList());
Map<String, ObPartyOrg> currentOrgMap = orgQueryService.batchQueryPartyOrg(currentOrgIdList, tenantCode, OrgConst.NO_STATE);
boolean errorFlag = false;
for (OrgBatchUpdateExcelModel model : successResultList) {
String key = model.getOrgName() + model.getParentOrgName();
ObOrgDTO obOrgDTO = map.get(key);
if (obOrgDTO != null) {
model.setOrgBusinessId(obOrgDTO.getOrgBusinessId());
if (StringUtils.equals(model.getPartyCompanyIntro(), OrgConst.IN_THE_SAME_COMPANY_CODE)) {
model.setCompanyBusinessId(parentOrgMap.get(obOrgDTO.getPid()) == null ? null : parentOrgMap.get(obOrgDTO.getPid()).getCompanyBusinessId());
model.setCompanyType(parentOrgMap.get(obOrgDTO.getPid()) == null ? null : parentOrgMap.get(obOrgDTO.getPid()).getCompanyType());
} else {
if (currentOrgMap.get(model.getOrgBusinessId()) != null && !StringUtils.equals(currentOrgMap.get(model.getOrgBusinessId()).getPartyCompanyIntro(), OrgConst.IN_THE_SAME_COMPANY_CODE)) {
model.setCompanyType(currentOrgMap.get(model.getOrgBusinessId()).getCompanyType());
model.setCompanyBusinessId(currentOrgMap.get(model.getOrgBusinessId()).getOrgBusinessId());
}
}
if (OrgConst.YES_STATE.equals(model.getDeleteFlag())) {
// 删除需要判斷 下級有沒有組織或黨員
List<String> childOrgBusinessId = orgQueryService.getAllChildOrgBusinessId(model.getOrgBusinessId(), tenantCode);
if (!CollectionUtils.isEmpty(childOrgBusinessId)) {
if (!currentOrgIdList.containsAll(childOrgBusinessId)) {
model.setErrorMsg("|該黨組織下存在下級組織或黨員資料");
this.clear(model);
failResultList.add(model);
errorFlag = true;
}
}
if(StringUtils.isBlank(model.getErrorMsg())){
PartyUserCriteria partyUserCriteria = new PartyUserCriteria();
partyUserCriteria.setPid(model.getOrgBusinessId());
long userCount = partyUserService.countPartyUser(partyUserCriteria);
if(userCount > 0){
model.setErrorMsg("|該黨組織下存在下級組織或黨員資料");
this.clear(model);
failResultList.add(model);
errorFlag = true;
}
}
}
} else {
model.setErrorMsg("|黨組織全稱或上級上組織全稱錯誤");
this.clear(model);
failResultList.add(model);
errorFlag = true;
}
}
if (errorFlag) {
List<OrgBatchUpdateExcelModel> successList = successResultList.stream().filter(e -> StringUtils.isBlank(e.getErrorMsg())).collect(Collectors.toList());
successResultList = new ArrayList<>();
if (!CollectionUtils.isEmpty(successList)) {
successResultList.addAll(successList);
}
}
}
}
/**
* 清楚不需要字段
*
* @param model
*/
private void clear(OrgBatchUpdateExcelModel model) {
model.setPartyTypeSimple(null);
model.setPartyType(null);
model.setDeleteFlag(null);
model.setXzqh(null);
model.setPartyCompanyIntro(null);
model.setCompanyType(null);
model.setOrgBusinessId(null);
model.setCompanyBusinessId(null);
}
@Override
public void onException(Exception exception, AnalysisContext context) {
task.setTaskEnd(new Date());
task.setTaskStatus(EnumTaskStatus.FAIL);
task.setTaskErrorInfo(exception.getMessage());
taskService.updateStatusById(task);
}
public List<OrgBatchUpdateExcelModel> getSuccessResultList() {
return successResultList;
}
public List<OrgBatchUpdateExcelModel> getFailResultList() {
return failResultList;
} 7.插入成功資料，上傳失敗資料到OSS供前端下載下傳
/**
* 導入檔案-批量修改黨組織
*
* @param operator 操作人
* @param tenantCode 租戶
* @param originalFilename 檔案名
* @param task 任務
* @param successList 成功的資料
* @param failList 失敗的資料
*/
@Transactional(rollbackFor = Exception.class)
public void batchUpdateImportOrg(String operator, String tenantCode, String originalFilename, ObExcelScheduleTask task,
List<OrgBatchUpdateExcelModel> successList,
List<OrgBatchUpdateExcelModel> failList) {
task.setTaskStatus(EnumTaskStatus.DONE);
// 修改資料
if (!CollectionUtils.isEmpty(successList)) {
try {
obPartyOrgMapper.updateBatchForImportOrg(successList, operator, tenantCode);
List<String> orgBusinessIdList = successList.stream().filter(e -> OrgConst.YES_STATE.equals(e.getDeleteFlag())).map(e -> e.getOrgBusinessId()).collect(Collectors.toList());
if(!CollectionUtils.isEmpty(orgBusinessIdList)){
obOrgMapper.batchDeleteObOrg(orgBusinessIdList, operator, new Date(), tenantCode);
}
} catch (Exception e) {
task.setTaskStatus(EnumTaskStatus.FAIL);
task.setTaskErrorInfo(CommonUtil.stringAppend("生成和上傳黨組織批量修改錯誤檔案時異常 errorId=", ErrorIdUtil.getErrorId(), " error=", ExceptionUtil.stacktraceToString(e, 150)));
}
}
// 上傳錯誤資料檔案到oss
if (!CollectionUtils.isEmpty(failList)) {
InputStream ossInputStream = null;
try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
EasyExcel.write(outputStream, OrgBatchUpdateExcelVO.class).sheet(OrgConst.BATCH_UPDATE_EXCEL_SHEET).doWrite(failList);
ossInputStream = new ByteArrayInputStream(outputStream.toByteArray());
//上傳檔案
FileUploadResult result = fileStorageService.uploadFile(ossInputStream, UploadResourceType.EXCEL, System.currentTimeMillis() + originalFilename);
if (result != null) {
task.setTaskStatus(EnumTaskStatus.FAIL);
task.setOssFileUrl(result.getUrl());
task.setOssFileStatus(EnumOssFileStatus.NORMAL);
}
} catch (Exception e) {
task.setTaskErrorInfo(CommonUtil.stringAppend("生成和上傳黨組織批量修改錯誤檔案時異常 errorId=", ErrorIdUtil.getErrorId(), " error=", ExceptionUtil.stacktraceToString(e, 150)));
}
}
task.setTaskEnd(new Date());
taskService.updateStatusById(task);
} ![css雪碧圖（css sprite）[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)