而错误使用ColdFusion以s preadsheet创建一个验证下拉 [英] Error while creating validation dropdown in spreadsheet using ColdFusion

问题描述

我只是创造了A S preadsheet验证下拉列表中，从数据库中查询，使用ColdFusion。但我收到以下错误

  

在公式中字符串不能更大然后255个字符的ASCII

任何一个可以请帮助纠正这个，因为我迫切需要这个功能。

 ＆LT; CFSET pop_array = ArrayNew（1）＆GT;
＆所述; CFSET provider_name_array = ArrayNew（1）＆GT;
＆LT; H1＆GT;您好＆LT; / H1＆GT;
＆LT; CFQUERY NAME =qryGetPOP数据源=webalc＆GT;
    选择alc_pop＆LT鲜明中心_ code;！---在那里ROWNUM＆LT; = 10 ---＆GT;
＆LT; / CFQUERY＆GT;
＆LT;！---变换的qryGetPOP到一个数组。 ---＆GT;
＆LT; CFLOOP查询=qryGetPOP＆GT;
    ＆LT; CFSET arrayAppend（pop_array，中心_ code）＆GT;
＆LT; / CFLOOP＆GT;＆LT; CFQUERY NAME =qryGetProviderName数据源=webalc＆GT;
    = 10;从cmt_access_provider那里ROWNUM＆LT选择不同telcoofficialname
＆LT; / CFQUERY＆GT;＆LT; H1＆GT; Hello1＆LT; / H1＆GT;
＆LT;！---变换的qryGetProviderName到一个数组。 ---＆GT;
＆LT; CFLOOP查询=qryGetProviderName＆GT;
    ＆LT; CFSET arrayAppend（provider_name_array，telcoofficialname）GT;
＆LT; / CFLOOP＆GT;＆所述; cfdump变种=＃qryGetPOP＃＆GT;
＆所述; cfdump变种=＃pop_array＃＆GT;
＆所述; cfdump变种=＃provider_name_array＃＆GT;＆LT;＆CFSCRIPT GT;
    //创建全新S preadsheet
    US preadsheetObj = S preadsheetNew（testsheet）;
        S$p$padSheetAddRow(s$p$padsheetObj,'Request_ID,Requested_Services,Target_Cost,MRC_cost,NRC_Cost,MRC_Margin,NRC_Margin,Carrier_On_Net,Currency_information,Access_Technology,Speed,CSAID,Provider_Name,Centre_$c$c,Need_for_optimization,Contract_Duration,Budgetary_OR_Firm,Carrier_Quote_Reference,Quote_Received_Date,Optimization_Cost_From,Quote_Validity_Days,Expiry_Term_Date,Comments_to_Sales');
    //获取工作簿对象
    工作簿= S preadsheetObj.getWorkBook（）;    //获取按名称表，你liek添加列表验证
    片= workbook.getSheet（testsheet）;
    //创建所需的类对象
    dvconstraint =的CreateObject（Java的，org.apache.poi.hssf.usermodel.DVConstraint）;
    cellRangeList =的CreateObject（Java的，org.apache.poi.ss.util.CellRangeAddressList）;
    dataValidation =的CreateObject（Java的，org.apache.poi.hssf.usermodel.HSSFDataValidation）;
    //定义小区列表rowstart，rowend，柱开始，柱端的弹出列表
    AddressList中= cellRangeList.init（1，50，0，0）; //前10行的第一列
    dvConstraint = dvconstraint.createExplicitListConstraint（pop_array）; //设置约束实现价值
    dataValidation = dataValidation.init（AddressList中，dvConstraint）; //应用验证地址列表
    dataValidation.setSup pressDropDownArrow（假）; //启用/禁用下拉箭头。
    sheet.addValidationData（dataValidation）; //添加验证表。    //创建提供所需的类对象
    dvconstraint_provider =的CreateObject（Java的，org.apache.poi.hssf.usermodel.DVConstraint）;
    cellRangeList_provider =的CreateObject（Java的，org.apache.poi.ss.util.CellRangeAddressList）;
    dataValidation_provider =的CreateObject（Java的，org.apache.poi.hssf.usermodel.HSSFDataValidation）;
    //定义小区列表rowstart，rowend，柱开始，柱端的供应商名单
    addressList_provider = cellRangeList.init（1，50，1，1）; //前10行的第一列
    dvConstraint_provider = dvconstraint_provider.createExplicitListConstraint（provider_name_array）; //设置约束实现价值
    dataValidation_provider = dataValidation_provider.init（addressList_provider，dvConstraint_provider）; //应用验证地址列表
    dataValidation_provider.setSup pressDropDownArrow（假）; //启用/禁用下拉箭头。
    sheet.addValidationData（dataValidation_provider）; //添加验证表。    //用s preadsheet对象
    US preadsheetwrite（S preadsheetObj，＃expandpath（'./ downloadsheet.xls'）＃，真正的）;
＆LT; / CFSCRIPT＆GT;
＆LT;＆CFOUTPUT GT;完成＆LT; / CFOUTPUT＆GT;
 

解决方案

错误消息是pretty清楚。有些你想添加到验证列表中的值是比Excel中，255个字符允许的最大长度。无论是缩短值或也许尝试这个建议使用隐藏的范围内来解决的限制。

I am just creating a validation dropdown in a spreadsheet, from a database query, using ColdFusion. But I am getting the following error

"string literals in formulas can't be bigger then 255 character ASCII"

Can any one please help to rectify this as I need this functionality urgently.

<cfset pop_array = ArrayNew(1)>
<cfset provider_name_array = ArrayNew(1)>
<h1>Hello</h1>
<cfquery name="qryGetPOP" datasource="webalc">
    select distinct center_code from alc_pop<!--- where rownum<=10 --->
</cfquery>
<!--- Convert the qryGetPOP to an array. --->
<cfloop query="qryGetPOP">
    <cfset arrayAppend(pop_array, center_code)>
</cfloop>

<cfquery name="qryGetProviderName" datasource="webalc">
    select distinct telcoofficialname from cmt_access_provider where rownum<=10
</cfquery>

<h1>Hello1</h1>
<!--- Convert the qryGetProviderName to an array. --->
<cfloop query="qryGetProviderName">
    <cfset arrayAppend(provider_name_array, telcoofficialname)>
</cfloop>

<cfdump var="‪#‎qryGetPOP‬#">
<cfdump var="‪#‎pop_array‬#">
<cfdump var="‪#‎provider_name_array‬#">

<cfscript>
    //Create New Spreadsheet
    SpreadsheetObj = spreadsheetNew("testsheet");
        SpreadSheetAddRow(spreadsheetObj,'Request_ID,Requested_Services,Target_Cost,MRC_cost,NRC_Cost,MRC_Margin,NRC_Margin,Carrier_On_Net,Currency_information,Access_Technology,Speed,CSAID,Provider_Name,Centre_Code,Need_for_optimization,Contract_Duration,Budgetary_OR_Firm,Carrier_Quote_Reference,Quote_Received_Date,Optimization_Cost_From,Quote_Validity_Days,Expiry_Term_Date,Comments_to_Sales');
    //Get Workbook object
    workbook = SpreadsheetObj.getWorkBook();

    //Get sheet by name where you liek to add list validation
    sheet = workbook.getSheet("testsheet");


    //Create object of required class
    dvconstraint = createObject("java","org.apache.poi.hssf.usermodel.DVConstraint");
    cellRangeList = createObject("java","org.apache.poi.ss.util.CellRangeAddressList");
    dataValidation = createObject("java","org.apache.poi.hssf.usermodel.HSSFDataValidation");


    //Define cell list rowstart, rowend, column start, column end for pop list
    addressList = cellRangeList.init(1, 50, 0, 0);//First 10 rows in first column
    dvConstraint = dvconstraint.createExplicitListConstraint(pop_array); //set contraint value
    dataValidation = dataValidation.init(addressList, dvConstraint); //apply validation on address list
    dataValidation.setSuppressDropDownArrow(false);//Enable/disable dropdown arrow.
    sheet.addValidationData(dataValidation);//Add validation to sheet.

    //Create object of required class for provider
    dvconstraint_provider = createObject("java","org.apache.poi.hssf.usermodel.DVConstraint");
    cellRangeList_provider = createObject("java","org.apache.poi.ss.util.CellRangeAddressList");
    dataValidation_provider = createObject("java","org.apache.poi.hssf.usermodel.HSSFDataValidation");
    //Define cell list rowstart, rowend, column start, column end for provider list
    addressList_provider = cellRangeList.init(1, 50, 1, 1);//First 10 rows in first column
    dvConstraint_provider = dvconstraint_provider.createExplicitListConstraint(provider_name_array); //set contraint value
    dataValidation_provider = dataValidation_provider.init(addressList_provider, dvConstraint_provider); //apply validation on address list
    dataValidation_provider.setSuppressDropDownArrow(false);//Enable/disable dropdown arrow.
    sheet.addValidationData(dataValidation_provider);//Add validation to sheet.

    //write spreadsheet object
    spreadsheetwrite(SpreadsheetObj,"‪#‎expandpath‬('./downloadsheet.xls')#",true);
</cfscript>
<cfoutput>Done</cfoutput>

解决方案

The error message is pretty clear. Some of the values you are trying to add to the validation list are longer than the maximum length allowed by Excel, 255 characters. Either shorten the values or maybe try this suggestion of using hidden ranges to work around the limitation.

这篇关于而错误使用ColdFusion以s preadsheet创建一个验证下拉的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！