Adding Dropdown to XLSX drops first item, progressively

[K-Diderot]

This appears to be bug but me am dumb...frequently.

When adding a Dropdown to an XLSX via the following code:

local.createDV = xlsxObj.spreadsheet.newDataValidation() .onCells(targetCellRange) .withValuesFromSheetName('keySheet') .withValuesFromCells(sourceCellRange);

xlsxObj.spreadsheet.addDataValidation(xlsxObj.workbook, local.createDV);

If, for example: targetCellRange is B5:B15 and sourceCellRange is A1:A4 The following occurs: B5 has options A1:A4 B6 has options A2:A4 B7 has options A3:A4 B8 has options A4:A4 B9 has no options and so on. [Column and Row addresses are incidental and play no roll in the results]

I assume this is simply a 0/1 index issue as it truncates from the top of the list incrementally.

ColdFusion | 2018,0,19,330149 Java VM Version | 11.0.21+9-LTS-193 Spreadsheet CFML | 3.11.1

[K-Diderot]

Further inspection shows that the data validation in Excel increments up the sourceCellRange: Row 1 Column 1: keySheet:!E2:E60 Row 2 Column 1: keySheet:!E3:E61 Row 3 Column 1: keySheet:!E4:E62

[cfsimplicity]

I've tried to replicate what you describe as best I can understand it but I'm not seeing anything unexpected.

path = "c:/temp/test.xlsx";
sourceCellRange = "A1:A4";
targetCellRange = "B5:B15";
wb = spreadsheet.newXlsx();
spreadsheet
    .createSheet( wb, "keySheet" ) // create a second sheet called "keySheet"
    .setActiveSheet( wb, "keySheet" )
    .addColumn( wb, [ "A", "B", "C", "D" ] ) // add the validation values to be used
    .setActiveSheetNumber( wb, 1 ); // switch back to the first sheet where we'll add the DV
dv = spreadsheet.newDataValidation() // define the validation
    .onCells( targetCellRange ) // on the first sheet
    .withValuesFromSheetName( "keySheet" )
    .withValuesFromCells( sourceCellRange );
spreadsheet.addDataValidation( wb, dv );
spreadsheet.write( wb, path, true );

This adds dropdowns on sheet 1 column B rows 5 to 15 inclusive, which all contain the same expected values: "A", "B", "C", "D".

Perhaps I've misunderstood? If so , please provide the simplest example you can which demonstrates the issue?

[cfsimplicity]

One further thought: did the issue happen after you modified the range helper component in your previous ticket? If so that's a possible cause as your change to the regex will return faulty results.

Please make sure you test with the latest unmodified version of the library.

Thanks.

[K-Diderot]

Exceptional catch. I never considered the Regex Engine.

With this.useJavaAsRegexEngine=false, any issues involving dataValidation resolved.

I didn't see mention of the need for useJavaAsRegexEngine=false, though I could have easily overlooked it. If I didn't miss it, it may be worth addressing in the docs. It resolves both the Illegal group issue as well as the shifting list reference.

Many, many thanks for your feedback!
and a bonus 'Many!'

[cfsimplicity]

I didn't see mention of the need for useJavaAsRegexEngine=false, though I could have easily overlooked it

It's not mentioned because, like you, it never occurred to me as a potential issue. But I wouldn't want it to be a condition for using the library, so as I've hopefully done with your other issue, I'd prefer to fix things so it works with the setting on or off.

With this.useJavaAsRegexEngine=true; though, I'm still not seeing any problem with my code above. Could you provide a small example that reproduces the issue with that setting switched on?

Thanks.

[K-Diderot]

I've downloaded the dev branch and tested with this.useJavaAsRegexEngine=true; and this.useJavaAsRegexEngine=false;. In neither case does the Illegal Group Reference nor the drop down issue appear.

I'm not sure if the code, an example, or the actual spreadsheet itself would be most helpful so... The code being called is as follows:

targetCellRange = calcXLSXColAlphaRef( local.adjustedCol ) & local.row & ':' & calcXLSXColAlphaRef( local.adjustedCol ) & VAL( local.row + arrayLen( colDataResults.columnData ) - 1 );  // Build target cell range with internal column index for XLSX ref 
sourceCol       = arguments.reqs.columnSetupStruct[arguments.reqs.visibleColumns[local.col].field].qdata.colRange[arrayLen(arguments.reqs.columnSetupStruct[arguments.reqs.visibleColumns[local.col].field].qdata.colRange)];  // Last Column Referenced in column setup hold the 'Label' values
dsRowCount      = arguments.reqs.columnSetupStruct[arguments.reqs.visibleColumns[local.col].field].qdata.rowCount[1];
sourceCellRange = calcXLSXColAlphaRef( VAL(sourceCol) ) & '2:' & calcXLSXColAlphaRef( VAL(sourceCol) ) & VAL(dsRowCount+1);  // Row count of source list, minus header, +1 to account for header
addDropdown( arguments.xlsxObj, targetCellRange, sourceCellRange, 'lockedServerKeySheet'); // pass params to add dataValidation to current col.

/**
* Adds a dropdown list to a specified range of cells in a sheet.
* @param xlsxObject The object containing the spreadsheet and workbook.
* @param targetCellRange intended cell range to receive DD.
* @param sourceCellRange Origin cells of the DD values.
* @param sourceSheetName Origin sheet of the DD values.
*/
function addDropdown( xlsxObj, targetCellRange, sourceCellRange, sourceSheetName) {
        arguments.xlsxObj.ssObj.spreadsheet.newDataValidation()
            .onCells(arguments.targetCellRange)
        .withValuesFromSheetName(arguments.sourceSheetName)
    .withValuesFromCells(arguments.sourceCellRange).addToWorkbook(arguments.xlsxObj.ssObj.workbook );
}

Prior to the dev bransh install and without the chr$ addition, I simply received the Illegal Group Ref message. With the chr$ addition, while using Java RegEx, the resulting spreadsheet had the following issue:

again, the dev branch rectifies all of this, regardless of the regex engine being used.

What was particularly confounding was that the lockedServerKeySheet, the source sheet, would be populated with the selections made on the target sheet but, a la Heisenberg, only until switching to that sheet. At which point it laughed manically and ceased its behavior :), and would no longer reflect those selections.

If you would like a export sample of the spreadsheet itself, demoing the dd option loss, please let me know and I'll work something out for you.

Thanks again.

Additionally, if you were asking about the illegal group error, here is the stack trace: Illegal group reference java.lang.IllegalArgumentException: Illegal group reference at java.base/java.util.regex.Matcher.appendExpandedReplacement(Matcher.java:1068) at java.base/java.util.regex.Matcher.appendReplacement(Matcher.java:908) at coldfusion.runtime.StringFunc._REReplaceJava(StringFunc.java:989) at coldfusion.runtime.StringFunc.REReplace(StringFunc.java:1012) at coldfusion.runtime.CFPage.REReplace(CFPage.java:5040) at coldfusion.runtime.CfJspPage.invokeMethodHandle(CfJspPage.java:3783) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3710) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3604) at cfrange2ecfc1038635702$funcCONVERTRANGEREFERENCETOABSOLUTEADDRESS.runFunction(F:\GappedWebDev\USNavy\site\www\model\services\spreadsheetLibrary\helpers\range.cfc:56) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:554) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:485) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:448) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:95) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:399) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:372) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:288) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:830) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:613) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:438) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3627) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3604) at cfDataValidation2ecfc1129794444$funcGETCONSTRAINTFROMCELLS.runFunction(F:\GappedWebDev\USNavy\site\www\model\services\spreadsheetLibrary\objects\DataValidation.cfc:127) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:554) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:485) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:448) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:95) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:399) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:372) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:288) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:4175) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:4155) at cfDataValidation2ecfc1129794444$funcADDTOWORKBOOK.runFunction(F:\GappedWebDev\USNavy\site\www\model\services\spreadsheetLibrary\objects\DataValidation.cfc:72) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:554) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:485) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:448) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:95) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:399) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:372) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:288) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:830) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:613) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:438) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3627) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3604) at cfspreadsheetBuildService2ecfc67768662$funcADDDROPDOWN.runFunction(F:\GappedWebDev\USNavy\site\www\model\services\spreadsheetBuildService.cfc:828) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:554) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:448) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:95) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:399) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:372) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:288) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:4175) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:4155) at cfspreadsheetBuildService2ecfc67768662$funcBUILDSHEETMAIN.runFunction(F:\GappedWebDev\USNavy\site\www\model\services\spreadsheetBuildService.cfc:795) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:554) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:448) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:95) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:399) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:372) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:609) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:4116) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:4097) at cfspreadsheetBuildService2ecfc67768662$funcBUILDSHEETSETUP.runFunction(F:\GappedWebDev\USNavy\site\www\model\services\spreadsheetBuildService.cfc:627) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:554) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:448) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:95) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:399) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:372) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:288) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:4175) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:4155) at cfspreadsheetBuildService2ecfc67768662$funcBUILDXLSX.runFunction(F:\GappedWebDev\USNavy\site\www\model\services\spreadsheetBuildService.cfc:76) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:554) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:448) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:95) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:399) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:372) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:288) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:830) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:613) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:438) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3627) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3604) at cfreport2ecfc1280300536$funcMANUALEXPORT.runFunction(F:\GappedWebDev\USNavy\site\www\admin\controllers\report.cfc:353) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:554) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:448) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:95) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:399) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:372) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:609) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:835) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:641) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3549) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3524) at coldfusion.runtime.CFPage.invoke(CFPage.java:14491) at cfone2ecfc992214912$funcDOCONTROLLER.runFunction(F:\GappedWebDev\SharedResources\framework\one.cfc:1635) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:554) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:485) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:448) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:95) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:399) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:372) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:288) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:4175) at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:4155) at cfone2ecfc992214912$funcONREQUEST.runFunction(F:\GappedWebDev\SharedResources\framework\one.cfc:896) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:554) at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:485) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:448) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:95) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:399) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:372) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:288) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:830) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:613) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:438) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3627) at coldfusion.runtime.CfJspPage._invoke(CfJspPage.java:3604) at cfApplication2ecfc2060685450$funcONREQUEST.runFunction(F:\GappedWebDev\USNavy\site\www\Application.cfc:483) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:554) at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:448) at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:95) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:399) at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:372) at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:288) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:830) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:613) at coldfusion.runtime.TemplateProxy.invoke(TemplateProxy.java:438) at coldfusion.runtime.AppEventInvoker.invoke(AppEventInvoker.java:115) at coldfusion.runtime.AppEventInvoker.onRequest(AppEventInvoker.java:308) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:551) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:43) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:162) at coldfusion.filter.IpFilter.invoke(IpFilter.java:45) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:96) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:60) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:151) at coldfusion.CfmServlet.service(CfmServlet.java:226) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:311) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:46) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:47) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at coldfusion.inspect.weinre.MobileDeviceDomInspectionFilter.doFilter(MobileDeviceDomInspectionFilter.java:57) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:47) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at coldfusion.mobile.CORSFilter.doFilter(CORSFilter.java:55) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:47) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:377) at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:463) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:889) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.base/java.lang.Thread.run(Thread.java:834)

[cfsimplicity]

Sorry, Kevin, I didn't join the dots. This issue is clearly caused by #347 so my fix for that has resolved this too.

Thanks for reporting it. There should be a new release fairly soon.