package com.ld.igds.inout.manager; import com.bstek.dorado.annotation.Expose; import com.ld.igds.constant.FoodVariety; import com.ld.igds.constant.RespCodeEnum; import com.ld.igds.data.PageResponse; import com.ld.igds.file.CoreFileService; import com.ld.igds.inout.dto.InoutParam; import com.ld.igds.m.dto.NoticeInData; import com.ld.igds.m.service.InoutCommonService; import com.ld.igds.m.service.HInoutCustomerService; import com.ld.igds.models.InoutCustomer; import com.ld.igds.util.ContextUtil; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * 解析Excel文件 * * @author */ @Component(ExcelManager.BEAN_ID) public class ExcelManager { public static final String BEAN_ID = "sys.execlManager"; private static final String XLS = "xls"; private static final String XLSX = "xlsx"; @Autowired private CoreFileService fileService; @Autowired private InoutCommonService inoutManagerService; @Autowired private HInoutCustomerService customerService; /** * sys.execlManager#updateExcelData * * @param fileName * @return */ @Expose public PageResponse updateExcelData(String fileName) { // 读取的Excel文件数据 List readResult = readExcel(fileName); if (null == readResult) { return new PageResponse(RespCodeEnum.CODE_1111.getCode(), "导入失败:没有解析到文件中数据!"); } // 查询到的所有供应商信息 InoutParam param = new InoutParam(); List allCustomer = inoutManagerService.listCustomer(param); //新建任务存放集合 List newCustomerTaskList = new ArrayList<>(); Map newMap = new HashMap<>(); //存放编码和名称不一致的信息 StringBuilder stringBuilder = new StringBuilder(); //用tempFlag在后面来判断解析到的客户是否在客户表中存在 boolean tempFlag; int max = 0; for (NoticeInData noticeInData : readResult) { //获取客户任务数据中的客户名称和编码 String customerName = noticeInData.getCustomerName(); String customerId = noticeInData.getCustomerId(); //判断编码是否为空,为空则给出提示,不进行操作 if(StringUtils.isEmpty(customerName)){ stringBuilder.append("客户‘").append(customerName).append("’信息不完整,不导入此条数据;\n"); continue; } tempFlag = true; for (InoutCustomer customer : allCustomer) { //获取供应商名称和编码 String name = customer.getName(); String id = customer.getId(); //判断名称是否相同 if(customerName.equals(name)){ //名称相同,则客户在表中存在 tempFlag = false; //判断编码是否相同 if(StringUtils.isEmpty(customerId) || !id.equals(customerId)){ noticeInData.setCustomerId(id); } noticeInData.setCompanyId(customer.getCompanyId()); //身份证号 if(StringUtils.isEmpty(noticeInData.getCardId())){ noticeInData.setCardId(customer.getCardId()); } //地址 if(StringUtils.isEmpty(noticeInData.getAddress())){ noticeInData.setAddress(customer.getAddress()); } //电话 if(StringUtils.isEmpty(noticeInData.getPhone())){ noticeInData.setPhone(customer.getPhone()); } //一卡通号 if(StringUtils.isEmpty(noticeInData.getBankNum())){ noticeInData.setBankNum(customer.getBankNum()); } } } if(tempFlag){ if(max == 0){ max = Integer.parseInt(inoutManagerService.getMaxCustomerId(null)); } max += 1; noticeInData.setCustomerId(max + ""); } newCustomerTaskList.add(noticeInData); newMap.putIfAbsent(noticeInData.getCustomerName(), noticeInData); } //更新客户信息表 if(newMap.size() > 0){ for (NoticeInData noticeInData : newMap.values()) { int i = inoutManagerService.updateCustomer(noticeInData); if (i == 0) { //说明没有更新到客户信息,进行新增 InoutCustomer data = new InoutCustomer(); data.setId(noticeInData.getCustomerId()); data.setName(noticeInData.getCustomerName()); data.setCardId(noticeInData.getCardId()); data.setBankNum(noticeInData.getBankNum()); data.setAddress(noticeInData.getAddress()); data.setPhone(noticeInData.getPhone()); customerService.saveOrUpdateData(data); } } } //判断任务集合是否为空 if (newCustomerTaskList.isEmpty()) { return new PageResponse(RespCodeEnum.CODE_1111.getCode(), "导入失败!\n" + stringBuilder.toString()); } else { //更新任务表 int temp = 1; for (NoticeInData noticeInData : newCustomerTaskList) { //设置客户通知单的组织编码等信息 noticeInData.setCompanyId(ContextUtil.getCompanyId()); noticeInData.setDeptId(ContextUtil.subDeptId(null)); noticeInData.setCreateUser(ContextUtil.getLoginUserCName()); if(temp < 10){ noticeInData.setId(ContextUtil.getTimeId() + "00" + temp); }else if(temp < 100){ noticeInData.setId(ContextUtil.getTimeId() + "0" + temp); }else { noticeInData.setId(ContextUtil.getTimeId() + temp); } //更新客户任务信息,如果更新失败,则进行插入操作 inoutManagerService.updateNoticeIn(noticeInData); temp += 1; } if(StringUtils.isEmpty(stringBuilder.toString())){ return new PageResponse(RespCodeEnum.CODE_0000.getCode(), "数据全部导入成功!"); }else { String message = "数据部分导入成功!\n"+ stringBuilder.toString(); return new PageResponse(RespCodeEnum.CODE_0000.getCode(), message); } } } /** * 读取Excel文件内容 * * @param * @return */ public List readExcel(String fileName) { //获取文件后缀名并判断 String fileType = fileName.substring(fileName.lastIndexOf(".") + 1); //获取保存路径 String path = fileService.getTempFilePath(); path = path + fileName; Workbook workbook = null; FileInputStream inputStream = null; try { // 获取Excel文件 File excelFile = new File(path); if (!excelFile.exists()) { return null; } // 获取Excel工作簿 inputStream = new FileInputStream(excelFile); workbook = getWorkbook(inputStream, fileType); // 读取excel中的数据 return parseExcel(workbook); } catch (Exception e) { return null; } finally { try { if (null != inputStream) { inputStream.close(); } } catch (IOException e) { e.printStackTrace(); } } } /** * 根据文件后缀名类型获取对应的工作簿对象 * @param inputStream 读取文件的输入流 * @param fileType 文件后缀名类型(xls或xlsx) * @return 包含文件数据的工作簿对象 * @throws IOException */ public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException { Workbook workbook = null; if (fileType.equalsIgnoreCase(XLS)) { workbook = new HSSFWorkbook(inputStream); } else if (fileType.equalsIgnoreCase(XLSX)) { workbook = new XSSFWorkbook(inputStream); } return workbook; } /** * 解析Excel数据 * * @param workbook * @return */ private static List parseExcel(Workbook workbook) { List resultDataList = new ArrayList<>(); //int numberOfSheets = workbook.getNumberOfSheets(); // 解析sheet for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { Sheet sheet = workbook.getSheetAt(sheetNum); // 校验sheet是否合法 if (sheet == null) { continue; } // 获取第一行数据,不用处理 int firstRowNum = sheet.getFirstRowNum(); // 解析每一行的数据,构造数据对象 int rowStart = firstRowNum + 1; int rowEnd = sheet.getPhysicalNumberOfRows(); //定义变量,用于判断数据是否解析完 boolean flag = false; for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) { Row row = sheet.getRow(rowNum); if (null == row) { //此行为空,则之后不再解析,判定为解析完成 flag = true; break; } NoticeInData resultData = convertRowToData(row); String customerName = resultData.getCustomerName(); String customerId = resultData.getCustomerId(); if(customerName.isEmpty() && customerId.isEmpty()){ //此行数据中如果用户名和编码都为空,则之后不解析,判定解析完成 flag = true; break; } resultDataList.add(resultData); } if(flag){ break; } } return resultDataList; } /** * 提取每一行中需要的数据,构造成为一个结果数据对象 * * @param row * @return */ private static NoticeInData convertRowToData(Row row) { NoticeInData resultData = new NoticeInData(); Cell cell; int cellNum = 0; //获取并设置用户编码 cell = row.getCell(cellNum++); if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){ resultData.setCustomerId(""); }else { cell.setCellType(Cell.CELL_TYPE_STRING); String customerId = cell.getStringCellValue().trim(); if(StringUtils.isNotEmpty(customerId)){ resultData.setCustomerId(customerId); } } //获取并设置姓名 cell = row.getCell(cellNum++); if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){ resultData.setCustomerName(""); }else { cell.setCellType(Cell.CELL_TYPE_STRING); String name = cell.getStringCellValue().trim().replaceAll(" ",""); if(StringUtils.isNotEmpty(name)){ resultData.setCustomerName(name); } } //获取并设置身份证号 cell = row.getCell(cellNum++); if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){ resultData.setCardId(""); }else { cell.setCellType(Cell.CELL_TYPE_STRING); String cardId = cell.getStringCellValue().trim(); if(StringUtils.isNotEmpty(cardId)){ resultData.setCardId(cardId); } } //获取并设置一卡通号 cell = row.getCell(cellNum++); if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){ resultData.setBankNum(""); }else { cell.setCellType(Cell.CELL_TYPE_STRING); String bankNum = cell.getStringCellValue().trim(); if(StringUtils.isNotEmpty(bankNum)){ resultData.setBankNum(bankNum); } } //获取并设置地址 cell = row.getCell(cellNum++); if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){ resultData.setAddress(""); }else { cell.setCellType(Cell.CELL_TYPE_STRING); String address = cell.getStringCellValue().trim(); if(StringUtils.isNotEmpty(address)){ resultData.setAddress(address); } } //获取并设置电话 cell = row.getCell(cellNum++); if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){ resultData.setPhone(""); }else { cell.setCellType(Cell.CELL_TYPE_STRING); String phoneNum = cell.getStringCellValue().trim(); if(StringUtils.isNotEmpty(phoneNum)){ resultData.setPhone(phoneNum); } } //获取并设置任务数量 cell = row.getCell(cellNum++); if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){ resultData.setTargetNumber(0.0); }else { cell.setCellType(Cell.CELL_TYPE_STRING); String taskNum = cell.getStringCellValue().trim(); if(StringUtils.isNotEmpty(taskNum)){ resultData.setTargetNumber(Double.valueOf(taskNum)); } } //获取并设置面积 cell = row.getCell(cellNum++); if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){ resultData.setFoodArea(0.0); }else { cell.setCellType(Cell.CELL_TYPE_STRING); String area = cell.getStringCellValue().trim(); if(StringUtils.isNotEmpty(area)){ resultData.setFoodArea(Double.valueOf(area)); } } //获取并设置品种 cell = row.getCell(cellNum++); if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){ resultData.setFoodVariety(""); }else { cell.setCellType(Cell.CELL_TYPE_STRING); String breed = cell.getStringCellValue().trim(); if(StringUtils.isNotEmpty(breed)){ resultData.setFoodVariety(FoodVariety.getCode(breed.trim())); } } //获取并设置年份 cell = row.getCell(cellNum++); if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){ resultData.setYear(""); }else { cell.setCellType(Cell.CELL_TYPE_STRING); String year = cell.getStringCellValue().trim(); if(StringUtils.isNotEmpty(year)){ resultData.setYear(year); } } return resultData; } }