0 Replies Latest reply: May 18, 2011 7:45 AM by 836231 RSS

    I m trying to geenrate dependent combo with the help of jxl api but validat

    836231
      I m trying to geenrate dependent combo with the help of jxl api but validat once i open generated exl file open it and click on data > validation > ok it works fiine


      my code for generating combo is :

      ArrayList lArrTmp1 = new ArrayList();
      lArrTmp1.add("abc");
      //lArrTmp1.add("12");
      //lArrTmp1.add("13");
      lArrTmp1.add("Sec1");
      int rollSize1=0;
      rollSize1 = lArrTmp1.size();
      workbook.addNameArea("Section_A", ws, 1, 55, 1, 55+rollSize1-1);
      for(int i=0;i<lArrTmp1.size();i++)
      {
                          
      Label lblRole = new Label(1, 55+i, lArrTmp1.get(i).toString());
                          ws.addCell(lblRole);
      }

      ArrayList lArrTmp2 = new ArrayList();
      lArrTmp2.add("Jan");
      //lArrTmp1.add("12");
      //lArrTmp1.add("13");
      lArrTmp2.add("Sampark");
      int rollSize2=0;
      rollSize2 = lArrTmp2.size();
      workbook.addNameArea("Section_B", ws, 2, 55, 2, 55+rollSize2-1);
      for(int i=0;i<lArrTmp2.size();i++)
      {
                          
      Label lblRole = new Label(2, 55+i, lArrTmp2.get(i).toString());
                          ws.addCell(lblRole);
      }


      ArrayList lArrTmp = new ArrayList();
      lArrTmp.add("Section_A");
      lArrTmp.add("Section_B");
      //lArrTmp.add("3");
      //lArrTmp.add("4");
      int rollSize=0;
      rollSize = lArrTmp.size();
      workbook.addNameArea("cboRoll", ws, 1, 5, 1, 5+rollSize-1);
      // for(int i=0;i<lArrTmp.size();i++)
      // {
      //                    Label lblRole = new Label(1, 5, lArrTmp.get(i).toString());
      //                    ws.addCell(lblRole);
      // }
      if(lArrTmp.size()>0){
                               Label combo = new Label(1,5,"Section_A",dataFmt);
                               WritableCellFeatures cellFeatures = new WritableCellFeatures();
                               cellFeatures.setDataValidationList(lArrTmp);
                               combo.setCellFeatures(cellFeatures);
                               ws.addCell(combo);
                          }else{
                               WritableCell wCell=new Label(1,5,"",dataFmt);
                               ws.addCell(wCell);
                          }

      Label cmb = new Label(2, 5, "",dataFmt);
                          WritableCellFeatures dataFeature = new WritableCellFeatures();
                          //String indirect="=INDIRECT($B$"+(6)+")";
      String indirect= "INDIRECT(SUBSTITUTE($B$6,\" \",\"_\")) ";
      ArrayList lArrDepe = new ArrayList();
      lArrDepe.add(indirect);
      System.out.println("\n\n\n indirect::"+indirect);
      //dataFeature.setDataValidationList(lArrDepe);
      dataFeature.setDataValidationRange(indirect);
                          
      cmb.setCellFeatures(dataFeature);
                          ws.addCell(cmb);