spreadSheetAddDataValidationRule

Search
ColdFusion

ColdFusion

Open app

Description

Data validation allows you to enforce restrictions on the type of data you enter in specific spreadsheet cells. You can use rules to ensure that the data you enter your spreadsheet is accurate, consistent, and adheres to the rules.

How is validation useful

Adding a data validation rule in a spreadsheet allows you to: 

  • Prevent errors: Minimizes errors in data entry by restricting the data one can enter. 

  • Maintain data integrity: Ensures the data is in a consistent format. 

  • Improve data quality: Ensures one can enter only valid data, critical for accurate reporting.

Types of data validation in ColdFusion:

  • Integer: Allows only integers. For example, the age or size of an apartment. 

  • Double: Allows only decimal numbers. For example, commodity prices or distance between two places. 

  • List: Restricts entry to a pre-defined list of values. For example, selecting from department names or product categories. 

  • Date: Ensures the entry is valid depending on specified date conditions. For example, setting project deadlines or ensuring that date of birth is within a range.  

  • Text length: Restricts the number of characters in a cell. For example, zip codes, phone numbers, or employee IDs.  

Returns

None

History

  • ColdFusion (2025 release): Added the function.

Syntax 

spreadSheetAddDataValidationRule(spreadSheetObject, validationRule)
spreadSheetAddDataValidationRule(spreadSheetObject, validationRule)
spreadSheetAddDataValidationRule(spreadSheetObject, validationRule)

Parameters

Name

Required

Type

Description

spreadSheetObject

Yes

ExcelInfo

The Excel spreadsheet object to which to add the data validation.

validationRule

Yes

Struct

A struct containing the following rules:

  • validationType: Select one of the following:
    • Date
    • Double
    • Integer
    • List
    • Text_Length
  • regions: Defines the area in the spreadsheet you’ll enter data subject to validation. It is an array of structs defining the start and end rows, and the start and end columns of the data entry area. 
    • If the area is in a single column, specify regions as {startRow:2, startColumn:1, endRow: 1, endColumn: 1}
    • If the area spans more than one column, specify regions as [ { startRow : 1, startColumn : 1, endRow : 1, endColumn : 1 }, { startRow : 2, startColumn : 1, endRow : 2, endColumn : 4}]
  • value: the value that the data entered in the range is validated against.
  • operator: Select one of the following:
    • greater_than
    • less_than
    • greater_or_equal
    • less_or_equal
    • equal
    • not_equal
    • between
    • not_between
  • minValue: Defines the minimum value of the validation range. Use it when the operator is between or not_between. The key doesn’t apply to the List validation type.
  • maxValue: Defines the maximum value of the validation range. Use it when the operator is between or not_between. The key doesn’t apply to the List validation type.
  • alertTitle: The title of the error message box.
  • alertText: The error message that appears when validation fails.
  • cellSelectTitle: The title of the message box when you select a cell.
  • cellSelectText: The text that appears in a box when you select a cell.

Example - Integer validation

Example 1- using greater_than 

<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-gt.xls";
// create a spreadsheet object
theSheet=spreadsheetNew("SampleData")
// add a column
spreadsheetAddRow(theSheet,"Integer",1,1)
//Format Header
spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1)
// add validation for integer
// add validation struct
validationStruct={
validationType : "integer",
regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }],
value : 94,
operator : "greater_than",
alertTitle:"Data validation failed",
alertText:"The number you've entered is invalid. Check the number and re-try.",
cellSelectTitle:"Enter a number",
cellSelectText:"Make sure you enter an integer greater than 94."
}
try{
SpreadsheetAddDataValidationRule(theSheet,validationStruct)
writeOutput("Data validation rule applied successfully")
}
catch(any e){
writeOutput(e.message)
}
// write the spreadsheet
spreadsheetWrite(theSheet,theFile,"yes")
</cfscript>
<cfscript> theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-gt.xls"; // create a spreadsheet object theSheet=spreadsheetNew("SampleData") // add a column spreadsheetAddRow(theSheet,"Integer",1,1) //Format Header spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) // add validation for integer // add validation struct validationStruct={ validationType : "integer", regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], value : 94, operator : "greater_than", alertTitle:"Data validation failed", alertText:"The number you've entered is invalid. Check the number and re-try.", cellSelectTitle:"Enter a number", cellSelectText:"Make sure you enter an integer greater than 94." } try{ SpreadsheetAddDataValidationRule(theSheet,validationStruct) writeOutput("Data validation rule applied successfully") } catch(any e){ writeOutput(e.message) } // write the spreadsheet spreadsheetWrite(theSheet,theFile,"yes") </cfscript> 
<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-gt.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "greater_than", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer greater than 94." 
    } 

    try{ 
        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 
    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 2- using less_than

<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-lt.xls";
// create a spreadsheet object
theSheet=spreadsheetNew("SampleData")
// add a column
spreadsheetAddRow(theSheet,"Integer",1,1)
//Format Header
spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1)
// add validation for integer
// add validation struct
validationStruct={
validationType : "integer",
regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }],
value : 94,
operator : "less_than",
alertTitle:"Data validation failed",
alertText:"The number you've entered is invalid. Check the number and re-try.",
cellSelectTitle:"Enter a number",
cellSelectText:"Make sure you enter an integer less than 94."
}
try{
SpreadsheetAddDataValidationRule(theSheet,validationStruct)
writeOutput("Data validation rule applied successfully")
}
catch(any e){
writeOutput(e.message)
}
// write the spreadsheet
spreadsheetWrite(theSheet,theFile,"yes")
</cfscript>
<cfscript> theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-lt.xls"; // create a spreadsheet object theSheet=spreadsheetNew("SampleData") // add a column spreadsheetAddRow(theSheet,"Integer",1,1) //Format Header spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) // add validation for integer // add validation struct validationStruct={ validationType : "integer", regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], value : 94, operator : "less_than", alertTitle:"Data validation failed", alertText:"The number you've entered is invalid. Check the number and re-try.", cellSelectTitle:"Enter a number", cellSelectText:"Make sure you enter an integer less than 94." } try{ SpreadsheetAddDataValidationRule(theSheet,validationStruct) writeOutput("Data validation rule applied successfully") } catch(any e){ writeOutput(e.message) } // write the spreadsheet spreadsheetWrite(theSheet,theFile,"yes") </cfscript> 
<cfscript> 

    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-lt.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "less_than", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer less than 94." 

    } 

    try{ 
        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 
    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 3- using greater_or_equal

<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-gte.xls";
// create a spreadsheet object
theSheet=spreadsheetNew("SampleData")
// add a column
spreadsheetAddRow(theSheet,"Integer",1,1)
//Format Header
spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1)
// add validation for integer
// add validation struct
validationStruct={
validationType : "integer",
regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }],
value : 94,
operator : "greater_or_equal",
alertTitle:"Data validation failed",
alertText:"The number you've entered is invalid. Check the number and re-try.",
cellSelectTitle:"Enter a number",
cellSelectText:"Make sure you enter an integer greater or equal to 94."
}
try{
SpreadsheetAddDataValidationRule(theSheet,validationStruct)
writeOutput("Data validation rule applied successfully")
}
catch(any e){
writeOutput(e.message)
}
// write the spreadsheet
spreadsheetWrite(theSheet,theFile,"yes")
</cfscript>
<cfscript> theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-gte.xls"; // create a spreadsheet object theSheet=spreadsheetNew("SampleData") // add a column spreadsheetAddRow(theSheet,"Integer",1,1) //Format Header spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) // add validation for integer // add validation struct validationStruct={ validationType : "integer", regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], value : 94, operator : "greater_or_equal", alertTitle:"Data validation failed", alertText:"The number you've entered is invalid. Check the number and re-try.", cellSelectTitle:"Enter a number", cellSelectText:"Make sure you enter an integer greater or equal to 94." } try{ SpreadsheetAddDataValidationRule(theSheet,validationStruct) writeOutput("Data validation rule applied successfully") } catch(any e){ writeOutput(e.message) } // write the spreadsheet spreadsheetWrite(theSheet,theFile,"yes") </cfscript> 
<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-gte.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 

    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "greater_or_equal", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer greater or equal to 94." 
    } 

    try{ 

        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 

    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 4- using less_or_equal

<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-lte.xls";
// create a spreadsheet object
theSheet=spreadsheetNew("SampleData")
// add a column
spreadsheetAddRow(theSheet,"Integer",1,1)
//Format Header
spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1)
// add validation for integer
// add validation struct
validationStruct={
validationType : "integer",
regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }],
value : 94,
operator : "less_or_equal",
alertTitle:"Data validation failed",
alertText:"The number you've entered is invalid. Check the number and re-try.",
cellSelectTitle:"Enter a number",
cellSelectText:"Make sure you enter an integer less than or equal to 94."
}
try{
SpreadsheetAddDataValidationRule(theSheet,validationStruct)
writeOutput("Data validation rule applied successfully")
}
catch(any e){
writeOutput(e.message)
}
// write the spreadsheet
spreadsheetWrite(theSheet,theFile,"yes")
</cfscript>
<cfscript> theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-lte.xls"; // create a spreadsheet object theSheet=spreadsheetNew("SampleData") // add a column spreadsheetAddRow(theSheet,"Integer",1,1) //Format Header spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) // add validation for integer // add validation struct validationStruct={ validationType : "integer", regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], value : 94, operator : "less_or_equal", alertTitle:"Data validation failed", alertText:"The number you've entered is invalid. Check the number and re-try.", cellSelectTitle:"Enter a number", cellSelectText:"Make sure you enter an integer less than or equal to 94." } try{ SpreadsheetAddDataValidationRule(theSheet,validationStruct) writeOutput("Data validation rule applied successfully") } catch(any e){ writeOutput(e.message) } // write the spreadsheet spreadsheetWrite(theSheet,theFile,"yes") </cfscript> 
<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-lte.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 

    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "less_or_equal", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer less than or equal to 94." 

    } 

    try{ 
        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 
    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 5- using equal

<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-equal.xls";
// create a spreadsheet object
theSheet=spreadsheetNew("SampleData")
// add a column
spreadsheetAddRow(theSheet,"Integer",1,1)
//Format Header
spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1)
// add validation for integer
// add validation struct
validationStruct={
validationType : "integer",
regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }],
value : 94,
operator : "equal",
alertTitle:"Data validation failed",
alertText:"The number you've entered is invalid. Check the number and re-try.",
cellSelectTitle:"Enter a number",
cellSelectText:"Make sure you enter an integer equal to 94."
}
try{
SpreadsheetAddDataValidationRule(theSheet,validationStruct)
writeOutput("Data validation rule applied successfully")
}
catch(any e){
writeOutput(e.message)
}
// write the spreadsheet
spreadsheetWrite(theSheet,theFile,"yes")
</cfscript>
<cfscript> theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-equal.xls"; // create a spreadsheet object theSheet=spreadsheetNew("SampleData") // add a column spreadsheetAddRow(theSheet,"Integer",1,1) //Format Header spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) // add validation for integer // add validation struct validationStruct={ validationType : "integer", regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], value : 94, operator : "equal", alertTitle:"Data validation failed", alertText:"The number you've entered is invalid. Check the number and re-try.", cellSelectTitle:"Enter a number", cellSelectText:"Make sure you enter an integer equal to 94." } try{ SpreadsheetAddDataValidationRule(theSheet,validationStruct) writeOutput("Data validation rule applied successfully") } catch(any e){ writeOutput(e.message) } // write the spreadsheet spreadsheetWrite(theSheet,theFile,"yes") </cfscript> 
<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-equal.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "equal", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer equal to 94." 
    } 

    try{ 
        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 
    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 6- using not_equal

<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-not-equal.xls";
// create a spreadsheet object
theSheet=spreadsheetNew("SampleData")
// add a column
spreadsheetAddRow(theSheet,"Integer",1,1)
//Format Header
spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1)
// add validation for integer
// add validation struct
validationStruct={
validationType : "integer",
regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }],
value : 94,
operator : "not_equal",
alertTitle:"Data validation failed",
alertText:"The number you've entered is invalid. Check the number and re-try.",
cellSelectTitle:"Enter a number",
cellSelectText:"Make sure you enter an integer not equal to 94."
}
try{
SpreadsheetAddDataValidationRule(theSheet,validationStruct)
writeOutput("Data validation rule applied successfully")
}
catch(any e){
writeOutput(e.message)
}
// write the spreadsheet
spreadsheetWrite(theSheet,theFile,"yes")
</cfscript>
<cfscript> theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-not-equal.xls"; // create a spreadsheet object theSheet=spreadsheetNew("SampleData") // add a column spreadsheetAddRow(theSheet,"Integer",1,1) //Format Header spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) // add validation for integer // add validation struct validationStruct={ validationType : "integer", regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], value : 94, operator : "not_equal", alertTitle:"Data validation failed", alertText:"The number you've entered is invalid. Check the number and re-try.", cellSelectTitle:"Enter a number", cellSelectText:"Make sure you enter an integer not equal to 94." } try{ SpreadsheetAddDataValidationRule(theSheet,validationStruct) writeOutput("Data validation rule applied successfully") } catch(any e){ writeOutput(e.message) } // write the spreadsheet spreadsheetWrite(theSheet,theFile,"yes") </cfscript> 
<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-not-equal.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        value : 94, 
        operator : "not_equal", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer not equal to 94." 
    } 

    try{ 

        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 
    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 7- using between

<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-between.xls";
// create a spreadsheet object
theSheet=spreadsheetNew("SampleData")
// add a column
spreadsheetAddRow(theSheet,"Integer",1,1)
//Format Header
spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1)
// add validation for integer
// add validation struct
validationStruct={
validationType : "integer",
regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }],
minValue : 50,
maxValue: 100,
operator : "between",
alertTitle:"Data validation failed",
alertText:"The number you've entered is invalid. Check the number and re-try.",
cellSelectTitle:"Enter a number",
cellSelectText:"Make sure you enter an integer between 50-100."
}
try{
SpreadsheetAddDataValidationRule(theSheet,validationStruct)
writeOutput("Data validation rule applied successfully")
}
catch(any e){
writeOutput(e.message)
}
// write the spreadsheet
spreadsheetWrite(theSheet,theFile,"yes")
</cfscript>
<cfscript> theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-between.xls"; // create a spreadsheet object theSheet=spreadsheetNew("SampleData") // add a column spreadsheetAddRow(theSheet,"Integer",1,1) //Format Header spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) // add validation for integer // add validation struct validationStruct={ validationType : "integer", regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], minValue : 50, maxValue: 100, operator : "between", alertTitle:"Data validation failed", alertText:"The number you've entered is invalid. Check the number and re-try.", cellSelectTitle:"Enter a number", cellSelectText:"Make sure you enter an integer between 50-100." } try{ SpreadsheetAddDataValidationRule(theSheet,validationStruct) writeOutput("Data validation rule applied successfully") } catch(any e){ writeOutput(e.message) } // write the spreadsheet spreadsheetWrite(theSheet,theFile,"yes") </cfscript> 
<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-between.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        minValue : 50, 
        maxValue: 100, 
        operator : "between", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer between 50-100." 
    } 

    try{ 

        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 

    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Example 8- using not_between

<cfscript>
theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-not-between.xls";
// create a spreadsheet object
theSheet=spreadsheetNew("SampleData")
// add a column
spreadsheetAddRow(theSheet,"Integer",1,1)
//Format Header
spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1)
// add validation for integer
// add validation struct
validationStruct={
validationType : "integer",
regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }],
minValue : 50,
maxValue: 100,
operator : "not_between",
alertTitle:"Data validation failed",
alertText:"The number you've entered is invalid. Check the number and re-try.",
cellSelectTitle:"Enter a number",
cellSelectText:"Make sure you enter an integer not between 50-100."
}
try{
SpreadsheetAddDataValidationRule(theSheet,validationStruct)
writeOutput("Data validation rule applied successfully")
}
catch(any e){
writeOutput(e.message)
}
// write the spreadsheet
spreadsheetWrite(theSheet,theFile,"yes")
</cfscript>
<cfscript> theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-not-between.xls"; // create a spreadsheet object theSheet=spreadsheetNew("SampleData") // add a column spreadsheetAddRow(theSheet,"Integer",1,1) //Format Header spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) // add validation for integer // add validation struct validationStruct={ validationType : "integer", regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], minValue : 50, maxValue: 100, operator : "not_between", alertTitle:"Data validation failed", alertText:"The number you've entered is invalid. Check the number and re-try.", cellSelectTitle:"Enter a number", cellSelectText:"Make sure you enter an integer not between 50-100." } try{ SpreadsheetAddDataValidationRule(theSheet,validationStruct) writeOutput("Data validation rule applied successfully") } catch(any e){ writeOutput(e.message) } // write the spreadsheet spreadsheetWrite(theSheet,theFile,"yes") </cfscript> 
<cfscript> 
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "sheet-validate-integer-not-between.xls"; 
    // create a spreadsheet object 
    theSheet=spreadsheetNew("SampleData")  
    // add a column 
    spreadsheetAddRow(theSheet,"Integer",1,1) 
    //Format Header 
    spreadsheetformatRow(theSheet,{bold=true,alignment='center'},1) 
    // add validation for integer 
    // add validation struct 
    validationStruct={ 
        validationType : "integer", 
        regions : [ { startRow : 2, startColumn : 1, endRow : 11, endColumn : 1 }], 
        minValue : 50, 
        maxValue: 100, 
        operator : "not_between", 
        alertTitle:"Data validation failed", 
        alertText:"The number you've entered is invalid. Check the number and re-try.", 
        cellSelectTitle:"Enter a number", 
        cellSelectText:"Make sure you enter an integer not between 50-100." 
    } 

    try{ 

        SpreadsheetAddDataValidationRule(theSheet,validationStruct) 
        writeOutput("Data validation rule applied successfully") 

    } 

    catch(any e){ 
        writeOutput(e.message) 
    } 

    // write the spreadsheet 
    spreadsheetWrite(theSheet,theFile,"yes") 
</cfscript>

Get help faster and easier

New user?

Quick links

View all your plans Manage your plans
View quick links
Hide quick links

Legal Notices    |    Online Privacy Policy