Hi,
Please find below the answers to your queries:
Scenario1: Yes, currently only one validatior is applied to a cell in SpreadJS. This is the same behavior as Microsoft Excel.
For your use case, you could create a formula validator and inside the formula validator, pass a custom function that returns true or false, based on whatever criteria you want to check.
You could check if the value is of type Date or other validations. To check if the value is not empty, you could use the ignoreBlank() method and pass false as its argument.
Kindly refer to the following code snippet and the sample:
Create Custom Function
// Create Custom Function
function CustomFunction() {
this.name = "CUSTOMFN";
this.maxArgs = 1;
this.minArgs = 1;
}
CustomFunction.prototype = new GC.Spread.CalcEngine.Functions.Function();
// Return true or false from the custom function based on the value
CustomFunction.prototype.evaluate = function (arg) {
// Check if the argument is null
if (!arg) {
return false;
}
// Try to create a new Date object using the value
const date = new Date(arg);
// Check if the Date object is valid and the value is not 'Invalid Date'
return date instanceof Date && !isNaN(date);
};
var customFn = new CustomFunction();
Apply the Data Validator to the Cell:
// Add the formula validator to the cell
let dv = GC.Spread.Sheets.DataValidation.createFormulaValidator("CUSTOMFN(C3)");
dv.showInputMessage(true);
dv.inputMessage("Must be in a date format and not empty");
dv.inputTitle("Must be in a date format and not empty");
// Set the ignore blank to false
dv.ignoreBlank(false);
sheet.setDataValidator(2, 2, 1, 1, dv, GC.Spread.Sheets.SheetArea.viewport);
References:
Custom Function Demo: https://www.grapecity.com/spreadjs/demos/features/calculation/custom-functions/purejs
Custom Validation Demo: https://www.grapecity.com/spreadjs/demos/features/cells/data-validation/custom-data-validator/purejs
createFormulaValidator: https://www.grapecity.com/spreadjs/api/v15/modules/GC.Spread.Sheets.DataValidation#createdatevalidator
ignoreBlank method: https://www.grapecity.com/spreadjs/api/v15/classes/GC.Spread.Sheets.DataValidation.DefaultDataValidator#ignoreblank
Sample: https://jscodemine.grapecity.com/share/ErbUM8K18EWi3SUJgni3aA/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"%2C"%2Fpackage.json"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}
Scenario2: I couldn’t understand your requirement clearly. As per my understanding, you want to have different value in a column based on a value in some other column. If this is the case, you might use the “value” method and pass it to the TableColumn Class.
You could refer to the following API reference on value method: https://www.grapecity.com/spreadjs/api/v16/classes/GC.Spread.Sheets.Tables.TableColumn#value
You could also refer to the following demo on Data Binding Demo that uses the value method to add “$” to the cost column: https://www.grapecity.com/spreadjs/demos/features/tables/table-binding/purejs
Further, you could apply the CustomFormat on the cell and show whatever value you want to show in the cell. Refer to the following demo on Custom Format: https://www.grapecity.com/spreadjs/demos/features/cells/formatter/custom-formatter#demo_source_name
If your issue is still not resolved, I request you to kindly elaborate your use case with an example so that we could have a better understanding of your use case and could assist you accordingly.
Scenario3: You could create the custom function as defined above in Scenario1. The custom function can be used a function designed to validate the cell values. You could use the same approach as defined in Scenaio1.
Regarding changing the color of the highlight color of DataValidator, currently it is not supported in SpreadJS(not supported in Microsoft Excel) as well.
Please let us know if you face any issues.
Regards,
Ankit
