(Showing Draft Content)

Add Data Validation

Data validation helps ensure that the data entered into cells meets certain criteria or rules. You can set the data validation on the TemplateSheet in the same way as you set the data validation on the worksheet, but the scope of the data validation may change when the report is generated.

In the TemplateSheet, you can set some data validation. This data validation is effective in the TemplateSheet, but its performance improves when you switch the ReportSheet to the Preview render mode.

Notes:
If the cell where the data validation is located on the TemplateSheet is not an extended cell, the size of the current data validation in the report sheet range is not changed, but there may be some offset (the top left exists cell for expansion).
If the cell where the data validation is placed on the TemplateSheet is an extended cell, the size of the current data validation in the report sheet range will vary, and the range will expand in the same manner as the template cell. There could also be some offset (the upper left cell for expansion).

The following code sample uses a number validator where the number should be greater than or equal to 5.

const dv = GC.Spread.Sheets.DataValidation.createNumberValidator(
 GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.greaterThanOrEqualsTo, 5, 5, false
);
// Configure the Data Validator.
dv.showInputMessage(true);
dv.inputTitle("Tip");
dv.inputMessage("Value must be greater than or equal to 5");
// Apply the Number Validator on the Cell(2, 3).
// The validator will expand to the whole column.
templateSheet.getRange(2, 3, 1, 1).validator(dv);
// Configure the style for cells where the validation fails.
const invalidStyle = new GC.Spread.Sheets.Style();
const redBorder = new GC.Spread.Sheets.LineBorder('red', 5);
invalidStyle.borderTop = redBorder;
invalidStyle.borderBottom = redBorder;
invalidStyle.borderLeft = redBorder;
invalidStyle.borderRight = redBorder;
const range = new GC.Spread.Sheets.Range(2, 3, 1, 1);
// Add style for the Cells that have "Invalid" states.
templateSheet.cellStates.add(range, GC.Spread.Sheets.CellStatesType.invalid, invalidStyle);
// The ValidatorError Event is triggered when a validation fails.
spread.bind(GC.Spread.Sheets.Events.ValidationError, function (sender, args) {
 console.log(`Data Validation fails: RowIndex: ${args.row}, ColIndex: ${args.col}`);
});

The output of the above code will look like below.

RS-data validation