@Service public class AppInfoUploadServiceImpl implements AppInfoUploadService { @Autowired private AppInfoMapper appInfoMapper; @Autowired private CommonMapper commonMapper; @Override public void uploadAppInfo(MultipartFile file) { LogUtil.info("处理app信息数据..."); String originalFilename = file.getOriginalFilename(); if (!originalFilename.endsWith("xlsx")) { throw new BizException(FailedStatusEnum.MUST_EXCEL_FILE, "originalFilename:" + originalFilename); } XSSFWorkbook xwb = null; try { List<AppTypePojo> appTypeList = new ArrayList<AppTypePojo>(); List<AppPojo> appList = new ArrayList<AppPojo>(); // 读取excel工作簿 xwb = new XSSFWorkbook(file.getInputStream()); // 读取excel的词库页 XSSFSheet sheet = xwb.getSheet("Sheet1"); Integer sellpointId=null; String gradeName = null; String subjectName = null; String typeName = null; String studyProblem = null; String scenePic = null; String mainAppName = null; String mainAppPackageName = null; String mainAppIntroduction = null; String otherAppName = null; String otherAppIntroduction = null; String sellpointLatitude = null; String addTime=null; String lastTime=null; AppTypePojo appTypePojo=null; AppPojo mainAppPojo=null; AppPojo otherAppPojo=null; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); for (int i = 1; i <= sheet.getLastRowNum(); i++) { List<String> otherAppList = new ArrayList<String>(); XSSFRow row = sheet.getRow(i); if (row != null) { Boolean mergedRegion1 = isMergedRegion(sheet, i, 1); if (mergedRegion1) { gradeName = getMergedRegionValue(sheet, i, 1); }else{ XSSFCell cell = row.getCell(1); if (cell!=null) { gradeName=cell.getStringCellValue(); } } if (gradeName!=null) { if (gradeName.contains("一年级")||gradeName.contains("二年级")) { gradeName="一年级#二年级"; }else if (gradeName.contains("三年级")||gradeName.contains("四年级")) { gradeName="三年级#四年级"; }else if (gradeName.contains("五年级")||gradeName.contains("六年级")) { gradeName="五年级#六年级"; } } Boolean mergedRegion2 = isMergedRegion(sheet, i, 2); if (mergedRegion2) { subjectName = getMergedRegionValue(sheet, i, 2); }else{ XSSFCell cell = row.getCell(2); if (cell!=null) { subjectName=cell.getStringCellValue(); } } Boolean mergedRegion3 = isMergedRegion(sheet, i, 3); if (mergedRegion3) { typeName = getMergedRegionValue(sheet, i, 3); }else{ XSSFCell cell = row.getCell(3); if (cell!=null) { typeName=cell.getStringCellValue(); } } Boolean mergedRegion4 = isMergedRegion(sheet, i, 4); if (mergedRegion4) { scenePic = getMergedRegionValue(sheet, i, 4); }else{ XSSFCell cell = row.getCell(4); if (cell!=null) { scenePic=cell.getStringCellValue(); } } Boolean mergedRegion9 = isMergedRegion(sheet, i, 9); if (mergedRegion9) { studyProblem = getMergedRegionValue(sheet, i, 9); }else{ XSSFCell cell = row.getCell(9); if (cell!=null) { studyProblem=cell.getStringCellValue(); } } Boolean mergedRegion5 = isMergedRegion(sheet, i, 5); if (mergedRegion5) { mainAppName = getMergedRegionValue(sheet, i, 5); }else{ XSSFCell cell = row.getCell(5); if (cell!=null) { mainAppName=cell.getStringCellValue(); } } Boolean mergedRegion6 = isMergedRegion(sheet, i, 6); if (mergedRegion6) { mainAppPackageName = getMergedRegionValue(sheet, i, 6); }else{ XSSFCell cell = row.getCell(6); if (cell!=null) { mainAppPackageName=cell.getStringCellValue(); } } Boolean mergedRegion7 = isMergedRegion(sheet, i, 7); if (mergedRegion7) { mainAppIntroduction = getMergedRegionValue(sheet, i, 7); }else{ XSSFCell cell = row.getCell(7); if (cell!=null) { mainAppIntroduction=cell.getStringCellValue(); } } Boolean mergedRegion8 = isMergedRegion(sheet, i, 8); if (mergedRegion8) { otherAppName = getMergedRegionValue(sheet, i, 8); }else{ XSSFCell cell = row.getCell(8); if (cell!=null) { otherAppName=cell.getStringCellValue(); } } Integer typeId=appInfoMapper.selectTypeIdByGnameSnameTname(gradeName,subjectName,typeName); if (typeId==null) { CommonQueryVo commonQueryVo = new CommonQueryVo(CommonConstant.TABLE_NAME_APP_TYPE); CommonQueryVo comm = commonMapper.selectMaxOrderNoBytableName(commonQueryVo); commonQueryVo.setFieldName("type_id"); CommonQueryVo comm2 = commonMapper.selectMaxFieldBytableName(commonQueryVo); Integer orderNo = comm.getOrderNo(); if (orderNo==null) { orderNo=1; } String maxFieldValue = comm2.getMaxFieldValue(); Integer typeIdValue = Integer.valueOf(maxFieldValue); if (typeIdValue==null) { typeIdValue=1; } appTypePojo=new AppTypePojo(typeIdValue+1,gradeName,subjectName,typeName,1,orderNo+1); appInfoMapper.insertAppTypeInfo(appTypePojo); typeId=appTypePojo.getId(); appTypeList.add(appTypePojo); } CommonQueryVo commonMainApp = new CommonQueryVo(CommonConstant.TABLE_NAME_APP); CommonQueryVo commMainOrder = commonMapper.selectMaxOrderNoBytableName(commonMainApp); Integer orderNoMain=1; if (commMainOrder!=null) { orderNoMain=commMainOrder.getOrderNo(); } mainAppPojo=new AppPojo(typeId,studyProblem,scenePic,mainAppName,mainAppPackageName,mainAppIntroduction,1,1,orderNoMain+1); Date d = new Date(); String parseDate = sdf.format(d); Date createTime = sdf.parse(parseDate); mainAppPojo.setCreateTime(createTime); //判断该app信息是否存在 Integer isExist=appInfoMapper.selectAppinfoIsExist(mainAppPojo); if (isExist>0) { Integer idMainAPP=appInfoMapper.updateAppInfo(mainAppPojo); }else{ Integer idMainAPP=appInfoMapper.insertAppInfo(mainAppPojo); } appList.add(mainAppPojo); System.out.println(mainAppPojo.toString()); System.out.println("otherAppName="+otherAppName); if ("".equals(otherAppName)) { System.out.println("otherAppName="+otherAppName); } if (otherAppName!=null&&!"".equals(otherAppName)) { CommonQueryVo commonOtherApp = new CommonQueryVo(CommonConstant.TABLE_NAME_APP); String[] splitOtherApp = otherAppName.split("\n"); for (int j = 0; j < splitOtherApp.length; j++) { String[] splitMap = splitOtherApp[j].split(" "); System.out.println("splitMap="+splitMap.toString()); String otherAppName2=""; String otherAppPackage2=""; if (splitMap.length>0) { otherAppName2 = splitMap[0]; if (splitMap.length>1) { otherAppPackage2 = splitMap[1]; } CommonQueryVo commOtherOrder = commonMapper.selectMaxOrderNoBytableName(commonMainApp); Integer orderNoOther=1; if (commOtherOrder!=null) { orderNoOther=commOtherOrder.getOrderNo(); } otherAppPojo=new AppPojo(typeId,studyProblem,null,otherAppName2,otherAppPackage2,otherAppIntroduction,0,1,orderNoOther+1); Date dOther = new Date(); String parsedOtherDate = sdf.format(dOther); Date createdOtherTime = sdf.parse(parsedOtherDate); otherAppPojo.setCreateTime(createdOtherTime); //判断该app信息是否存在 Integer isExistO=appInfoMapper.selectAppinfoIsExist(otherAppPojo); if (isExistO>0) { Integer idOtherAPP=appInfoMapper.updateAppInfo(otherAppPojo); }else{ Integer idOtherAPP=appInfoMapper.insertAppInfo(otherAppPojo); } appList.add(otherAppPojo); } } System.out.println(otherAppList.toString()); } } } LogUtil.info("appTypeList:" + JsonTool.toJson(appTypeList)); LogUtil.info("appList:" + JsonTool.toJson(appList)); } catch (Exception e) { e.printStackTrace(); } finally { try { xwb.close(); } catch (IOException e) { e.printStackTrace(); } } } /** * @author * TODO判断是否为合并单元格 * @method isMergedRegion * @param sheet * @param row * @param column * @return * @return Boolean * @date */ private Boolean isMergedRegion(XSSFSheet sheet,int row,int column){ int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if (row>=firstRow&&row<=lastRow) { if (column>=firstColumn&&column<=lastColumn) { return true; } } } return false; } /** * @author * TODO获取合并单元格的值 * @method getMergedRegionValue * @param sheet * @param row * @param column * @return * @return String * @date */ public String getMergedRegionValue(XSSFSheet sheet ,int row , int column){ int sheetMergeCount = sheet.getNumMergedRegions(); for(int i = 0 ; i < sheetMergeCount ; i++){ CellRangeAddress ca = sheet.getMergedRegion(i); int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); int firstRow = ca.getFirstRow(); int lastRow = ca.getLastRow(); if(row >= firstRow && row <= lastRow){ if(column >= firstColumn && column <= lastColumn){ XSSFRow xRow = sheet.getRow(firstRow); XSSFCell xCell = xRow.getCell(firstColumn); return getCellValue(xCell); } } } return null ; } /** * @author * TODO获取单元格的值 * @method getCellValue * @param cell * @return * @return String * @date */ public String getCellValue(XSSFCell cell){ if(cell == null) return ""; if(cell.getCellType() == XSSFCell.CELL_TYPE_STRING){ return cell.getStringCellValue(); }else if(cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN){ return String.valueOf(cell.getBooleanCellValue()); }else if(cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA){ return cell.getCellFormula() ; }else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){ return String.valueOf(cell.getNumericCellValue()); } return ""; }