As web application developers we're used to adding drop-downs to our web UIs via <select> HTML elements. They're a reliable way of making sure people make a valid choice from a limited set of options - countries or credit card providers, for instance.

Drop-down lists are also supported in the world of spreadsheets via "Data Validation" or"Validity" menu options, depending on the software you are using.

If you are creating spreadsheets programmatically in CFML, you now have the ability to add drop-downs to your sheets via the latest release of the Spreadsheet CFML library.

Data validations are implemented as objects returned by the library which you can configure and then apply to your workbook object. There are currently two approaches to populating the list of options:

• Pass in a list of values as an array.
• Point to another part of the workbook which contains the values.

(For these examples I'm going to use the new chainable syntax to build the spreadsheet, but you can do all of this with separate library method calls too.)

Passing in a list

This is the most straightforward approach. Simply specify the values as an array.

spreadsheet = New spreadsheet.Spreadsheet();
myDropdownObject = spreadsheet.newDataValidation()
  .onCells( "B2:B4" )
  .withValues( [ "Visa", "Mastercard", "Amex" ] );
spreadsheet.newChainable( "xlsx" )
  .addRow( [ "Order number", "Card type" ] )
  .formatRow( { bold: true }, 1 )
  .addColumn( data=[ 1, 2, 3 ], startRow=2 )
  .addDataValidation( myDropdownObject )
  .write( filepath="c:/temp/my.xlsx", overwrite=true );

If we open up my.xlsx and click on any of the cells in the "Card type" column, we'll get a drop-down.

Getting the values from other cells

You may want your list to contain values from another part of the workbook, either in the same or a different worksheet.

In this example we'll store the card types in a separate sheet in the workbook and then pull them into the drop-downs.

spreadsheet = New Spreadsheet();
myDropdownObject = spreadsheet.newDataValidation()
	.onCells( "B2:B4" )
	.withValuesFromSheetName( "Card_types" )
	.withValuesFromCells( "A1:A3" );
spreadsheet.newChainable( "xlsx" )
	.createSheet( "Card_types" )
	.setActiveSheetNumber( 2 )
	.addColumn( [ "Visa", "Mastercard", "Amex" ] )
	.setActiveSheetNumber( 1 )
	.addRow( [ "Order number", "Card type" ] )
	.formatRow( { bold: true }, 1 )
	.addColumn( data=[ 1, 2, 3 ], startRow=2 )
	.addDataValidation( myDropdownObject )
	.write( filepath="c:/temp/my.xlsx", overwrite=true );

Customizing the error message

Unlike HTML select elements, it is possible to enter values which are not in the list, but you will get a modal error pop-up.

By default this will probably say "Invalid value" (again depending on the spreadsheet software), but you can control both the title at the top of the error box and the message.

spreadsheet = New Spreadsheet();
myDropdownObject = spreadsheet.newDataValidation()
  .onCells( "B2:B4" )
  .withValues( [ "Visa", "Mastercard", "Amex" ] )
  .withErrorTitle( "Invalid card type" )
  .withErrorMessage( "Please choose from the available options" )
spreadsheet.newChainable( "xlsx" )
  .addRow( [ "Order number", "Card type" ] )
  .formatRow( { bold: true }, 1 )
  .addColumn( data=[ 1, 2, 3 ], startRow=2 )
  .addDataValidation( myDropdownObject )
  .write( filepath="c:/temp/my.xlsx", overwrite=true );

Syntax options

In the examples above I've created the drop-down object before the spreadsheet, attaching the drop-down as the workbook is created. But you can also do it the other way around if that makes more sense to you.

spreadsheet = New Spreadsheet();
myChainableXlsx = spreadsheet.newChainable( "xlsx" )
  .addRow( [ "Order number", "Card type" ] )
  .formatRow( { bold: true }, 1 )
  .addColumn( data=[ 1, 2, 3 ], startRow=2 );
 myDropdownObject = spreadsheet.newDataValidation()
  .onCells( "B2:B4" )
  .withValues( [ "Visa", "Mastercard", "Amex" ] )
  .addToWorkbook( myChainableXlsx.getWorkbook() );
 myChainableXlsx.write( filepath="c:/temp/my.xlsx", overwrite=true );

As mentioned above, separate library calls are also possible if that's more familiar.

spreadsheet = New Spreadsheet();
myWorkbook = spreadsheet.newXlsx();
spreadsheet.addRow( myWorkbook, [ "Order number", "Card type" ] );
spreadsheet.formatRow( myWorkbook, { bold: true }, 1 );
spreadsheet.addColumn( workbook=myWorkbook, data=[ 1, 2, 3 ], startRow=2 );
myDropdownObject = spreadsheet.newDataValidation()
  .onCells( "B2:B4" )
  .withValues( [ "Visa", "Mastercard", "Amex" ] )
  .addToWorkbook( myWorkbook );
 spreadsheet.write( workbook=myWorkbook, filepath="c:/temp/my.xlsx", overwrite=true );

For more detail, see the documentation for newDataValidation().