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

问题描述

我为在 ColdFusion 中创建大型下拉列表而编写的这段代码，但它不适用于我的目的.任何人都可以帮我纠正我的问题.新代码是

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>

更新 1:

(来自 其他线程)我是收到此错误消息:

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

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

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.

更新 2:

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

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

隐藏不是A1的值:A不能转换为数字."

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

我编辑了评论中提到的对象，并将脚本更改为 cfscript.请帮我纠正这个错误.

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>    

更新 3:

我已更新代码以解决上述问题，现在出现此错误

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

未找到 setSheetHidden 方法..."

"The setSheetHidden method was not found ..."

在下面一行:

workbook.setSheetHidden(1, true); 

推荐答案

你的代码有几个问题.尽管 java 语法相似，但您不能只复制和粘贴 java 示例 并期望它在 cfscript 中运行.你需要先做一些调整.(注意:我假设 script 只是 cfscript 的错字).

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

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

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

使用静态方法，例如 DVConstraint.createFormulaListConstraint()，你还必须使用createObject.虽然 java 代码不会创建该类的 new 实例，但您仍必须使用 createObject 获取对 CF 中 DVConstraint 类的引用，然后才能调用任何其方法.注意:因为是静态的，所以不需要先调用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 类被组织成 包.在 java 类中，任何引用类的完整路径都导入到 java 代码的顶部(在您使用的示例中不可见).在 CF 中，您需要在 createObject 调用中使用完整路径.(重要提示:路径是区分大小写的).

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

例如，代替 new HSSFWorkbook() 使用:

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

如果您不确定路径，只需搜索POI TheClassName"即可.第一个结果可能是 POI JavaDocs，在每个页面的顶部显示完整路径，如下所示:

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

与 CF 不同，java 是强类型的，这意味着您必须声明变量的类型以及它的名称.例如，这一行将变量 row 声明为类型 HSSFRow

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

由于 CF 是无类型的，它不需要类型.因此，在 cfscript 中运行相同的代码将导致神秘错误缺少函数关键字...".解决方案是删除变量类型，直接进行变量赋值:

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 数组索引 从零开始(0)，而 CF 从一 (1) 开始，因此您需要修复 for 循环中的索引:

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 使用 + 连接字符串，而 CF 使用 &.所以这里需要改操作符"hidden!A1:A" + countryName.length.否则 CF 会认为你正在尝试添加两个数字，这显然会引发错误，因为第一部分是一个字符串.

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.

假设没有版本冲突，那么在您进行这些调整后，java 示例应该可以工作.

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

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