Mask Input

An input mask is a string of characters that defines the format of input expected from the user. Masking an input reduces the chances of errors in data and helps maintain consistency in the database, making it easier to analyze and process data. You can create masks using user-generated rules consisting of patterns, placeholders, masking characters, etc.

SpreadJS provides a mask property and a pattern option to set the input mask. You can use the placeholder option to specify the placeholder. A placeholder is a string of characters that displays in the input field, represents the expected input format, and guides the user to provide input as per the format. If the user does not specify a placeholder, SpreadJS uses "_" by default. You can choose whether to display the placeholder character in the input string using the excludePlaceholder option.

For instance, if a mask pattern is "000000" and "_" is a placeholder, the blank input field displays "______" to indicate the required format of 6 digits. If the excludePlaceholder option is set to false (default) and the user inputs "1234", the input is saved as "1234____" after exiting edit mode. And when the option is true, the input is stored as "1234", that is, the placeholder is excluded.

Similarly, you can also exclude the literal characters by setting the excludeLiteral to true. Literals are characters that are displayed as it is at a specific position and cannot be replaced by any other character. Parentheses and hyphens used in phone numbers or a forward slash in date formats are a few examples.

The following code shows how to set an input mask:

    style = new GC.Spread.Sheets.Style();
    style.mask = {
        pattern: "[a0]{8}",
        excludePlaceholder: true
    };
    sheet.setStyle(14, 0, style);

    style1 = new GC.Spread.Sheets.Style();
    style1.mask = {
        pattern: "Au\\t\\hor: (Chris|Icey|Victor|Ian|Johnson|Ivan)",
        excludeLiteral: true
    };
    sheet.setStyle(15, 0, style1);

If a user inputs a character when the cursor is at placeholder, the new character replaces the placeholder character. In other cases, the new character is inserted. In case user input does not match the pattern, SpreadJS keeps the parts that match the pattern from left to right and drops the remaining invalid parts. However, when a user deletes some text, it deletes the current restriction content only. For instance, in the case of pattern ‘a{3}0{3}’, if a user starts deleting from the left most character of input, SpreadJS allows deletion of first three characters of the current restriction. To delete the remaining input, the user has to place the cursor in the beginning of the next restriction.

To confirm if a pattern is valid or not, SpreadJS provides a static method validatePattern. The following code shows how to use the validatePattern method to validate a pattern:

let sheet = spread.getActiveSheet();
let style = new GC.Spread.Sheets.Style();
let pattern = 'ISBN 0-00000-000-0';
let verifyResult = GC.Spread.Sheets.InputMask.validatePattern('[a0_]{8}');
if (verifyResult.success) {
  style.mask = {
    pattern: 'ISBN 0-00000-000-0'
  };
  sheet.setStyle(0,0,style);
}

Mask patterns are of two types:

• String pattern

• Date pattern

String Pattern

A string pattern is the simplest way of defining input format and is constructed using the minimum pattern unit. A minimum pattern unit consists of a character restriction and, optionally, a character length restriction. When character length restriction is not specified, it is considered to be one character. The following table defines various rules for creating patterns using these two types of restrictions.

In dynamic patterns:

• The placeholder only represents the minimum length required. For example: 0{2,10} displays "__" and 0{0,1} displays nothing.

• If "m" is not specified, dynamic patterns take maximum length of 2147483648.

Date Pattern

The date pattern is a way to specify the format of date and time values. It is a fixed format pattern and does not accept character length restrictions. In the case of a date pattern, SpreadJS automatically fixes the user input by the date rule. However, you must not repeat date time items such as yyyy, MM etc. in a single pattern. For example, "yyyy/MM/dd yyyy" is an invalid pattern because "yyyy" is used twice.

The following table describes various masking characters of the date pattern and their usage with the help of sample patterns.

Named Pattern

You can also define named patterns and reuse them in other patterns. However, if there is a string in the pattern that matches any name in the namedPatterns, the mask replaces it with the named pattern. Hence, it is recommended to define patterns with names that have minimum chances of repetition. To get the list of all name patterns, SpreadJS provides a static method getNamedPatterns.

// define a named pattern
GC.Spread.Sheets.InputMask.namedPattern('enterdate', 'MM/dd/yyyy hh:mm tt');
let style = new GC.Spread.Sheets.Style();
style.mask = {
  // reuse it in another pattern
  pattern: '(start): enterdate'
};
sheet.setStyle(0, 0, style);

Formula Pattern

SpreadJS also supports formula patterns, that is, the patterns that are in the form of formula and start from "=". For example, if cell A1-A3 of a worksheet are names of the authors"Chris", "Icey", and "Ian", then the following two patterns give the same result.

The static method validatePattern that confirms if a pattern is valid or not, does not support formula patterns. To evaluate formula patterns, use GC.Spread.Sheets.CalcEngine.evaluateFormula before calling the validatePattern method.

• The characters in () pattern are a static string, so (am|pm) can be written as it is without using an escape character.

• When a group restriction has only one group item, the mask treats it as a static string.

• By default, the mask displays first item of the group restriction in input. On receiving user input, the mask automatically completes the group item text.

• For better input experience, group restrictions are not case-sensitive.

• For a static string or group text, SpreadJS does not support quantifiers. Hence, you must not set patterns like 1234{1,5}, (12|34){1,5} etc.

• SpreadJS does not support nesting of group “()” and optional “[]”.

• When a masked cell enters the edit mode, the internal formula textbox is prevented, and the external formula textbox is readonly.

• A mask does not take effect when a formula cell enters edit mode.

• After editing with a mask, if the editor value starts with “=”, “-”, “+”, quotePrefix is added automatically.

Example Patterns

The following table lists commonly used patterns for quick reference: