poi实现execl2007两级联动

@Override
public 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);//设置下execlcell的宽度
      }
      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 2017825 */
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;
}
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值