在使用ColdFusion在excel中创建大型下拉列表时出错 [英] Error while creating large dropdown in excel using ColdFusion

问题描述

这段代码我写的是在ColdFusion中创建大的下拉列表，但它不工作在我的结束。任何一个，请帮助我纠正我的问题。新代码是

 < cfquery name =getPOPdatasource =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  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> 
   

更新1：

$ b b

（来自其他主题）I得到此错误消息：

FUNCTION声明中缺少函数关键字。 CFML编译器
正在处理：一个以HSSFWorkbook开头的脚本语句
32，第1列。从第31行
第9列的函数开始的脚本语句。从第一行开始的cfscript标记30，第2列。

更新2：

再次，我修改了此代码，现在新的错误是

隐藏的值A1：A不能转换为数字。

我编辑了注释中提到的对象，并将脚本更改为cfscript。请帮助我纠正此错误。

 < 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> 
  

更新3：

$ b b

我更新了代码以修复上述问题，现在得到此错误

找不到setSheetHidden方法...

：

  workbook.setSheetHidden（1，true）; 
  

解决方案

您的代码有几个问题。虽然java语法是类似的，你不能只是复制和粘贴 java示例，并期望它在cfscript中运行。您需要先进行一些调整。 （注意：我假设脚本只是一个拼写错误 cfscript ）。

• 在java中，可以使用关键字new即 new SomeClassName（）实例化一个对象。在CF中， new 关键字只能与cfc一起使用。要创建一个java对象，必须使用 createObject 。要实例化它，请调用 init（...）方法。这是一个 CF 中的特殊方法，它调用构造函数一个java类的任何参数你提供，即

  
  
  

  createObject（java，path.to.SomeClassName ;

  poi / hssf / usermodel / DVConstraint.html＃createFormulaListConstraint％28java.lang.String％29rel =nofollow> DVConstraint.createFormulaListConstraint（） ，you还必须使用 createObject 。虽然java代码不会创建该类的新实例，但您仍然必须使用createObject来获取对 DVConstraint class，在CF中，然后才能调用它的任何方法。注意：因为它是静态的，所以不需要先调用 init（）。即
  
  
  

  dv = createObject（java，org.apache.poi.hssf.usermodel.DVConstraint）;
dv.createFormulaListConstraint（...）;




• 导入软件包。在java类中，任何引用类的完整路径都会导入到java代码的顶部（在您使用的示例中不可见）。在CF中，您需要在 createObject 调用中使用完整路径。 （重要：路径是cAsE sEnsItIvE）。

  
  
  

  例如，代替 new HSSFWorkbook（）使用：

  
  
  

  createObject（java，org.apache.poi.hssf.usermodel.HSSFWorkbook）;

  
  
  

  如果您不确定路径，只需搜索POI TheClassName。赔率是第一个结果将是 POI JavaDocs ，它们显示每个页面顶部的完整路径，如下所示：

  
  
  

  java.lang.Object

  
  
  

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




• 与CF不同，java是强类型的，这意味着您必须声明变量的类型及其名称。例如，此行声明一个变量 row 为 HSSFRow

  
  
  

  HSSFRow row = hidden.createRow（i）;

  
  
  

  由于CF是无类型的，因此不需要类型。所以在cfscript中运行相同的代码将导致隐藏的错误function关键字丢失...。解决方案是删除变量类型，只是做一个直接的变量赋值：

  
  
  

  row = hidden.createRow（i）;




• Java数组索引从零（0）开始，而CF从一个（1）开始，因此您需要修复for循环中的索引：

  
  
  

  for（i = 1; i <= arrayLen（countryName）; i ++）



• p> Java使用 + 来连接字符串，而CF使用& 。所以你需要在这里改变操作符hidden！A1：A+ countryName.length 。否则CF会认为你正在尝试添加两个数字，这显然会抛出一个错误，因为第一部分是一个字符串。

假设没有版本冲突，在您做出这些调整之后，Java示例应该可以工作。

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); 

解决方案

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.

这篇关于在使用ColdFusion在excel中创建大型下拉列表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！