newDataValidation - cfsimplicity/spreadsheet-cfml GitHub Wiki

Returns a new dataValidation object to be applied to a workbook.

newDataValidation()

Notes

• DataValidations allow you to restrict the values which can be entered in specific cells, typically via a drop-down UI.
• The allowed values can be specified as an array, or can be pulled from existing cells in the same or a different sheet in the workbook.
• The object allows you to customize the error box which pops up when invalid values are entered.
• The drop-down UI is optional.
• DataValidations are created using a "builder" syntax (see examples below).
• You can apply the validation object either by passing in the workbook to the addToWorkbook() builder method, or by returning the new DataValidation object and passing it to the library's addDataValidation() method.
• New in version 4.0: Date and integer range validations - specify minimum and maximum dates or integers (see examples 6 and 7 below).

Examples

Example 1: passing in allowed values

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withValues( [ "London", "Paris", "New York" ] )
  .addToWorkbook( wb );

Example 2: using values from other cells in the same sheet

wb = spreadsheet.new();
// the first column will contain the allowed values
spreadsheet.addColumn( wb, [ "London", "Paris", "New York" ] );
spreadsheet.newDataValidation()
  .onCells( "B1:C1" )
  .withValuesFromCells( "A1:A3" )
  .addToWorkbook( wb );

Example 3: using values from other cells in a different sheet

wb = spreadsheet.new();
// create a sheet with the valid values in the first column
spreadsheet.createSheet( wb, "cities" );
spreadsheet.setActiveSheetNumber( wb, 2 );
spreadsheet.addColumn( wb, [ "London", "Paris", "New York" ] );
// create and apply the dataValidation object to the first sheet
spreadsheet.setActiveSheetNumber( wb, 1 );
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withValuesFromSheetName( "cities" )
  .withValuesFromCells( "A1:A3" )
  .addToWorkbook( wb );

Example 4: using a custom error message

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withValues( [ "London", "Paris", "New York" ] )
  .withErrorTitle( "City not allowed" )
  .withErrorMessage( "Please choose from the list of allowed cities" )
  .addToWorkbook( wb );

Example 5: omitting the drop-down UI

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withValues( [ "London", "Paris", "New York" ] )
  .withNoDropdownArrow()
  .addToWorkbook( wb );

Example 6: limit input to a date range

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withMinDate( CreateDate( 2020, 1, 1 ) )
  .withMaxDate( CreateDate( 2020, 12, 31 ) )
  .withErrorTitle( "Invalid date" )
  .withErrorMessage( "Please enter a date in 2020" )
  .addToWorkbook( wb );

Example 7: limit input to an integer range

wb = spreadsheet.new();
spreadsheet.newDataValidation()
  .onCells( "A1:B1" )
  .withMinInteger( 1 )
  .withMaxInteger( 100 )
  .withErrorTitle( "Invalid number" )
  .withErrorMessage( "Please enter a whole number between 1 and 100" )
  .addToWorkbook( wb );