Error while creating validation dropdown in spread

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>

回答1:

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.

回答2:

Not sure if this is any help. This will give you a very basic yes or no dropdown list in a cell

Please Note...this is for xssf NOT hssf (.xlsx file format)

<!--- Create an instance of a new spreadsheet --->
<cfset local.spreadsheet = spreadsheetNew("My Spreadsheet", true)>

<!--- we need all this so we can have dropdowns --->
<cfset local.workbook = local.spreadsheet.getWorkBook()>
<cfset local.worksheet = local.workbook.getSheet("My Spreadsheet")>
<cfset local.dataValidationConstraint = createObject("java","org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint")>
<cfset local.cellRangeList = createObject("java","org.apache.poi.ss.util.CellRangeAddressList")>
<cfset local.dataValidationHelper = createObject("java","org.apache.poi.xssf.usermodel.XSSFDataValidationHelper").init(local.worksheet)>

<!--- Define cell list rowstart, rowend, column start, column end --->
<cfset local.list = local.cellRangeList.init(0, 1, 1, 1)>
<cfset local.dvconstraint = local.dataValidationHelper.createExplicitListConstraint(["YES","NO"])>
<cfset local.dataValidation = local.dataValidationHelper.createValidation(local.dvconstraint, local.list)>
<cfset local.dataValidation.setSuppressDropDownArrow(true)>

<!--- add it to the spreadsheet --->
<cfset local.worksheet.addValidationData(dataValidation)>