poi实现execl2007两级联动

@Overridepublic String writeDataToEXCEL(XSSFWorkbook  wb,  List dataList, String string,                        String fileName, long bum, FileOutputStream out, Sheet sheet1,CsBackImportProblemRel obj) throws BOException {   try {      Row row0 = sheet1.createRow(0);           for(int a=0;a<15;a++){         sheet1.setColumnWidth(a, 20 * 256);//设置下execl的cell的宽度      }      int last = sheet1.getPhysicalNumberOfRows();      List<Object[]> resultList = (List<Object[]>) dataList;      if (resultList.size()>0){         for (Object[] objs : resultList){            Row row = sheet1.createRow(last++);               for (int k = 0; k <objs.length; k++) {                  Cell cell = row.createCell(k);                  MSWordManager.setValue(cell, objs[k], string);               }         }      }      //获取问题原因      List<CsBackProblem> problemList= csBackImportProblemRelDao.getProblem();      List<String>  btProblemList=new ArrayList<String>(); //      List<Long> idProblemList=new ArrayList<Long>();      for (int i=0;i<problemList.size();i++){         String btProblem=problemList.get(i).getProblem().toString();         btProblem=btProblem.replaceAll("，","。");         btProblem=btProblem.replaceAll("\\*","。");         btProblem=btProblem.replaceAll("（","");         btProblem=btProblem.replaceAll("）","");         btProblemList.add(btProblem);         idProblemList.add(problemList.get(i).getCreateUserId());      }      //隐藏sheet页第一行数据      String[] problemArr = btProblemList.toArray(new String[problemList.size()]);      List list=csBackImportProblemRelDao.getSul();      Map<Long,List> maps=new TreeMap<Long, List>();      for (int j=0 ;j<list.size();j++){         List<String> sets=new ArrayList<String>();         Object[] ob = (Object[])list.get(j);         Long obId = Long.parseLong(ob[0].toString());         String problem=ob[1].toString();//问题原因         //特殊字符在execl中无法实现联动，所以去掉或替换掉         problem=problem.replaceAll("，","。");         problem=problem.replaceAll("\\*","。");         problem=problem.replaceAll("（","");         problem=problem.replaceAll("）","");         String solve=ob[2].toString();//解决方法         if (maps.containsKey(obId)){                   sets=maps.get(obId);                   sets.add(solve);         }else{            sets.add(problem);            sets.add(solve);         }         maps.put(obId,sets);      }      //对map进行按键排序      List<Map.Entry<Long,List>> sortlist = new ArrayList<Map.Entry<Long,List>>(maps.entrySet());      Collections.sort(sortlist,new Comparator<Map.Entry<Long,List>>() {         //升序排序         public int compare(Map.Entry<Long,List> o1, Map.Entry<Long,List> o2) {            return o1.getKey().compareTo(o2.getKey());         }      });           //创建一个专门用来存放问题解决方案信息的隐藏sheet页      Sheet hideInfoSheet = wb.createSheet("excelhidesheetname");      //设置隐藏页标志      wb.setSheetHidden(wb.getSheetIndex("excelhidesheetname"), true);      //第一行设置问题信息      Row problemNameRow = hideInfoSheet.createRow(0);      creatRow(problemNameRow, problemArr);      //创建名称管理器      creatExcelNameList(wb, "excelhidesheetname", 1, problemArr.length, false);      int  k=0;      for (List solve:maps.values()) {         //以下行设置解决方案         Row solveNameRow = hideInfoSheet.createRow(k + 1);         List<String> solveList = solve;         String[] solveArr = solveList.toArray(new String[solve.size()]);         creatRow(solveNameRow, solveArr);         //动态大小设置问题对应的解决方案         creatExcelNameList(wb, problemArr[k].toString(), k + 2, solveArr.length, true);         solveList.clear();         solveArr = null;         ++k;      }      //增加下拉框并增加数据验证      setDataValidation(wb,dataList.size());   } catch (Exception e) {      throw new BOException(e);   }   return null;}/** * * @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列 * @param rowId 第几行 * @param colCount 一共多少列 * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1 * * @author yubing 2017年8月25日 */public String getRange(int offset, int rowId, int colCount) {   char start = (char)('A' + offset);   if (colCount <= 25) {      char end = (char)(start + colCount - 1);      return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;   } else {      char endPrefix = 'A';      char endSuffix = 'A';      if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）         if ((colCount - 25) % 26 == 0) {// 边界值            endSuffix = (char)('A' + 25);         } else {            endSuffix = (char)('A' + (colCount - 25) % 26 - 1);         }      } else {// 51以上         if ((colCount - 25) % 26 == 0) {            endSuffix = (char)('A' + 25);            endPrefix = (char)(endPrefix + (colCount - 25) / 26 - 1);         } else {            endSuffix = (char)('A' + (colCount - 25) % 26 - 1);            endPrefix = (char)(endPrefix + (colCount - 25) / 26);         }      }      return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;   }}/** * 创建一列数据 * @param currentRow * @param textList */private static void creatRow(Row currentRow,String[] textList){   if(textList!=null&&textList.length>0){      int i = 0;      for(String cellValue : textList){         Cell userNameLableCell = currentRow.createCell(i++);         userNameLableCell.setCellValue(cellValue);      }   }}/** * 创建一个名称 * @param workbook */private static void creatExcelNameList(XSSFWorkbook workbook,String nameCode,int order,int size,boolean cascadeFlag){   Name name;   name = workbook.createName();   name.setNameName(nameCode);   name.setRefersToFormula("excelhidesheetname"+"!"+creatExcelNameList(order,size,cascadeFlag));}/** * 名称数据行列计算表达式 * @param */private static String creatExcelNameList(int order,int size,boolean cascadeFlag){   char start = 'A';   if(cascadeFlag){      start = 'B';      if(size<=25){         char end = (char)(start+size-1);         return "$"+start+"$"+order+":$"+end+"$"+order;      }else{         char endPrefix = 'A';         char endSuffix = 'A';         if((size-25)/26==0||size==51){//26-51之间，包括边界（仅两次字母表计算）            if((size-25)%26==0){//边界值               endSuffix = (char)('A'+25);            }else{               endSuffix = (char)('A'+(size-25)%26-1);            }         }else{//51以上            if((size-25)%26==0){               endSuffix = (char)('A'+25);               endPrefix = (char)(endPrefix + (size-25)/26 - 1);            }else{               endSuffix = (char)('A'+(size-25)%26-1);               endPrefix = (char)(endPrefix + (size-25)/26);            }         }         return "$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order;      }   }else{      if(size<=26){         char end = (char)(start+size-1);         return "$"+start+"$"+order+":$"+end+"$"+order;      }else{         char endPrefix = 'A';         char endSuffix = 'A';         if(size%26==0){            endSuffix = (char)('A'+25);            if(size>52&&size/26>0){               endPrefix = (char)(endPrefix + size/26-2);            }         }else{            endSuffix = (char)('A'+size%26-1);            if(size>52&&size/26>0){               endPrefix = (char)(endPrefix + size/26-1);            }         }         return "$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order;      }   }}/** * 添加数据验证选项 * @param wb * @param datasize */public static void setDataValidation(Workbook wb,int datasize){   int sheetIndex = wb.getNumberOfSheets();   if(sheetIndex>0){      for(int i=0;i<sheetIndex;i++){         XSSFSheet sheet = (XSSFSheet)wb.getSheetAt(i);         if(!"excelhidesheetname".equals(sheet.getSheetName())){            DataValidation data_validation_list = null;            for(int a=2;a<datasize+3;a++){               //问题选项添加验证数据 13代表问题所在列               data_validation_list = getDataValidationByFormula("excelhidesheetname",a,13,sheet);               sheet.addValidationData(data_validation_list);               //城市选项添加验证数据 次数M对应问题的所在列               data_validation_list = getDataValidationByFormula("INDIRECT(M"+a+")",a,14,sheet);               sheet.addValidationData(data_validation_list);            }         }      }   }}/** * 使用已定义的数据源方式设置一个数据验证 * @param formulaString * @param naturalRowIndex * @param naturalColumnIndex * @return */private static DataValidation getDataValidationByFormula(String formulaString,int naturalRowIndex,int naturalColumnIndex,XSSFSheet sheet){   //加载下拉列表内容   DataValidationConstraint constraint = new XSSFDataValidationHelper(sheet).createFormulaListConstraint(formulaString);   //设置数据有效性加载在哪个单元格上。   //四个参数分别是：起始行、终止行、起始列、终止列   int firstRow = naturalRowIndex-1;   int lastRow = naturalRowIndex-1;   int firstCol = naturalColumnIndex-1;   int lastCol = naturalColumnIndex-1;   CellRangeAddressList regions=new CellRangeAddressList(firstRow,lastRow,firstCol,lastCol);   //数据有效性对象   DataValidation data_validation_list = new XSSFDataValidationHelper(sheet).createValidation(constraint,regions);   //设置输入信息提示信息   data_validation_list.createPromptBox("下拉选择提示","请使用下拉方式选择合适的值！");   //设置输入错误提示信息   data_validation_list.createErrorBox("选择错误提示","你输入的值未在备选列表中，请下拉选择合适的值！");   return data_validation_list;}