Error while creating large dropdown in excel using ColdFusion

Question

This code I have written for creating large dropdown in ColdFusion, but it is not working on my end. Could any one please help me rectify my problem. The new code is

    <cfquery name="getPOP" datasource="l_webalc">
    select distinct center_code from alc_pop
    </cfquery>
    <cfset countryName= ArrayNew(1)>
      <cfloop query="getPOP">
      <cfset arrayappend(countryName, getPOP.center_code)>
    </cfloop>

    <script>
      workbook = new HSSFWorkbook();
      realSheet = workbook.createSheet("Sheet xls");
      hidden = workbook.createSheet("hidden");

      for (int i = 0, length= countryName.length; i < length; i++) {
        String name = countryName[i];
        HSSFRow row = hidden.createRow(i);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue(name);
      }

      namedCell = workbook.createName();
      namedCell.setNameName("hidden");
      namedCell.setRefersToFormula("hidden!A1:A" + countryName.length);
      constraint = DVConstraint.createFormulaListConstraint("hidden");
      addressList = new CellRangeAddressList(0, 0, 0, 0);
      validation = new HSSFDataValidation(addressList, constraint);
      workbook.setSheetHidden(1, true);
      realSheet.addValidationData(validation);
      stream = new FileOutputStream("c:\\range.xls");
      workbook.write(stream);
      stream.close();
    </script>

Update 1:

(From other thread) I am getting this error message:

function keyword is missing in FUNCTION declaration. The CFML compiler was processing: A script statement beginning with HSSFWorkbook on line 32, column 1. A script statement beginning with function on line 31, column 9. A cfscript tag beginning on line 30, column 2.

Update 2:

Again I have modified this code and now the new error is

"The value hidden not A1:A cannot be converted to a number."

I edited the objects as mentioned in the comments and also changed the script to cfscript. Please help me to rectify this error.

<cfscript>
workbook = createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook");
realSheet = workbook.createSheet("Sheet xls");
 hidden = workbook.createSheet("hidden");
 for (i = 1; i <= arrayLen(countryName); i++){
   name = countryName[i];
    row = hidden.createRow(i);
    cell = row.createCell(0);
   cell.setCellValue(name);
 }
 namedCell = workbook.createName();
 namedCell.setNameName("hidden");
 namedCell.setRefersToFormula("hidden!A1:A"+arrayLen(countryName));
 dv = createObject("java", "org.apache.poi.hssf.usermodel.DVConstraint");
 constraint = dv.createFormulaListConstraint("hidden");
 addressList = cellRangeList.init(0, 0, 0, 0);
 validation = dataValidation.init(addressList, constraint);
 workbook.setSheetHidden(1, true);
 realSheet.addValidationData(validation);
  stream = new FileOutputStream("c:\\range.xls");
 workbook.write(stream);
 stream.close();
</cfscript>    

Update 3:

I have updated the code to fix the mentioned issues and and am now getting this error

"The setSheetHidden method was not found ..."

on the following line:

workbook.setSheetHidden(1, true); 

Answer 1

There are several problems with your code. Though java syntax is similar, you cannot just copy and paste a java example and expect it to run in cfscript. You need to make some adjustments first. (Note: I am assuming script was just a typo for cfscript).

• In java, you can instantiate an object using the keyword "new" ie new SomeClassName(). In CF, the new keyword can only be used with cfc's. To create a java object, you must use createObject instead. To instantiate it, call the init(...) method. It is a special method in CF that invoke's the constructor of a java class with whatever parameters you supply, ie

  createObject("java", "path.to.SomeClassName").init();

  To use static methods such as DVConstraint.createFormulaListConstraint(), you must also use createObject. While the java code does not create an new instance of that class, you must still use createObject to get a reference to the DVConstraint class, in CF, before you can invoke any of its methods. Note: Because it is static, no need to call init() first. ie

  dv = createObject("java", "org.apache.poi.hssf.usermodel.DVConstraint"); dv.createFormulaListConstraint(...);

• Java classes are organized into packages. In java classes, the full path to any referenced classes are imported at the top of the java code (not visible in the example you are using). In CF you need to use the full path in your createObject call. (Important: Paths are cAsE sEnsItIvE).

  For example, instead of new HSSFWorkbook() use:

  createObject("java", "org.apache.poi.hssf.usermodel.HSSFWorkbook");

  If you are not sure of the path, just do a search on "POI TheClassName". Odds are the first result will be the POI JavaDocs, which show the full path at the top of each page like this:

  java.lang.Object

  |---org.apache.poi.ss.util.CellRangeAddressList

• Unlike CF, java is strongly typed, which means you must declare a variable's type as well as it's name. For example, this line declares a variable row as type HSSFRow

  HSSFRow row = hidden.createRow(i);

  Since CF is typeless, it does not require a type. So running that same code in cfscript will cause the cryptic error "function keyword is missing...". The solution is to drop the variable type and just do a straight variable assignment:

  row = hidden.createRow(i);

• Java array indexes start at zero (0), while CF starts at one (1), so you need to fix the indexes in your for loop:

  for (i = 1; i <= arrayLen(countryName); i++)

• Java uses + to concatenate strings, whereas CF uses &. So you need to change the operator here "hidden!A1:A" + countryName.length. Otherwise CF will think you are trying to add two numbers, which will obviously throw an error because the first part is a string.

Assuming no version conflicts, the java example should work after you make those those adjustments.

Answer 2

"The setSheetHidden method was not found ..."

Just use Javacast function for boolean arguments:

workbook.setSheetHidden(1, javacast("boolean",true));