XSSFWorkbook读取合并单元格中的数据

@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 "";      
    }