使用 ColdFusion 在电子表格中创建验证下拉列表时出错 [英] Error while creating validation dropdown in spreadsheet using ColdFusion
问题描述
我只是使用 ColdFusion 从数据库查询中创建电子表格中的验证下拉列表.但我收到以下错误
I am just creating a validation dropdown in a spreadsheet, from a database query, using ColdFusion. But I am getting the following error
公式中的字符串文字不能大于 255 个字符的 ASCII"
"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>
推荐答案
错误信息非常清楚.您尝试添加到验证列表的某些值超过 Excel 允许的最大长度 255 个字符.要么缩短值,要么尝试此建议使用隐藏范围来解决限制.
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 在电子表格中创建验证下拉列表时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!