[{"id":"1b88a165-2563-437e-99bb-ae30bd4b56db","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"a244ead7-a2c6-47a3-ac17-c5dbfa337362","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"33638a1c-7196-42c1-a96d-38b2d9ba8ac4","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"8e72e33e-b4ab-4fb3-98fc-a0b148134aed","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"86662220-9b9e-4940-9ced-d22642ea49a8","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"55fd3bb8-18d4-4edb-9640-ca3a365b798f","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"760b37c7-c713-4b24-b9ba-4bfe7d8437a6","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"3a083583-1d5f-492b-b450-34b2b5c775b8","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"04e73782-aa78-4dfe-a4f9-e72ed4c78a11","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"57c94653-8893-403b-a5b1-0d1e33a0bd0f","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"09a66339-64c0-415c-b142-0691587a8e4a","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"5b7aeac5-c755-426a-95c7-1ae8e547179a","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"790bc77a-9216-48fd-b8f9-fbc374ebb155","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"900e6cab-065d-4f1c-844d-efa3c074e270","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"0baaf91d-84f6-404c-a487-735226b6d5b6","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"5d37413b-3600-4da9-9700-feea54355f59","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"7ded3a22-15eb-49b8-a488-e83c2cd872eb","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"89c707ed-9841-4e53-96fb-940cc3214804","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"a570c8e7-07a2-47da-965b-da44fd1fa5cf","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"a5a73576-16ea-4cbc-925c-ef547389eaa5","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"69e79655-e015-4f9a-a230-2a25c988c926","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"adf3817f-7667-4a4c-8a5f-767b0b7e1e3e","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"1126cfab-6210-4e28-bee0-02c113fb7a0c","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"63c577e6-6cf9-497e-94e1-2307f7d3f498","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"24769ecd-2b08-4a85-b318-4f533bbf8393","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"2605431b-dc80-491d-886e-28981595d277","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"4f41e22e-eb51-49e5-aeae-a42dd6bf352c","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"aa71a884-c5bc-4842-8d6a-873dfd645167","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"b0576ca2-cb84-4390-9f95-9354ec20eda5","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"dacc7931-6785-4675-be31-80930403cf7b","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"c90d5fdf-420f-4978-8bf5-c9a2bb4334b3","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"6e6ac5b1-1501-4e28-89cc-525139488537","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"ff052704-1389-4029-bcdd-73c6cbe9f807","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"21c6131f-0f2d-41d1-9284-6ad9ee803c1f","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"64e2f4a3-2303-4bfa-8a93-6c23ef01de58","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"4a0842a3-20b1-40c3-8e00-cd5941ffdf53","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]}]
输入掩码是一串字符,它定义了用户期望的输入格式。屏蔽输入可减少数据出错的可能性,并有助于保持数据库的一致性,从而更容易分析和处理数据。您可以使用由模式、占位符、屏蔽字符等组成的用户生成规则来创建屏蔽。
SpreadJS 提供了一个掩码属性和一个模式选项来设置输入掩码。您可以使用占位符选项来指定占位符。占位符是显示在输入字段中的一串字符,代表预期的输入格式,引导用户按照格式进行输入。如果用户没有指定占位符,SpreadJS 默认使用“_”。您可以使用excludePlaceholder选项选择是否在输入字符串中显示占位符。
例如,如果屏蔽模式为“000000”,“_”为占位符,则空白输入字段显示“______”以指示所需的 6 位格式。如果 excludePlaceholder 选项设置为 false(默认值)并且用户输入“1234”,则在退出编辑模式后输入将保存为“1234____”。当该选项为真时,输入存储为“1234”,即排除占位符。
同样,您也可以通过将excludeLiteral设置为 true 来排除文字字符。文字是在特定位置显示的字符,不能被任何其他字符替换。电话号码中使用的括号和连字符或日期格式中的正斜杠就是几个例子。
以下代码显示了如何设置输入掩码:
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);
如果用户在光标位于占位符处时输入字符,则新字符将替换占位符字符。在其他情况下,插入新字符。如果用户输入与模式不匹配,SpreadJS 会从左到右保留与模式匹配的部分,并丢弃剩余的无效部分。但是,当用户删除某些文本时,它只会删除当前的限制内容。例如,在模式 'a{3}0{3}' 的情况下,如果用户从输入的最左边的字符开始删除,SpreadJS 允许删除当前限制的前三个字符。要删除剩余的输入,用户必须将光标放在下一个限制的开头。
为了确认模式是否有效,SpreadJS 提供了一个静态方法validatePattern。以下代码显示了如何使用 validatePattern 方法来验证模式:
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);
}
遮罩图案有两种类型:
字符串模式
日期模式
字符串模式是定义输入格式的最简单方法,它是使用最小模式单元构造的。最小模式单元由字符限制和可选的字符长度限制组成。当没有指定字符长度限制时,它被认为是一个字符。下表定义了使用这两种类型的限制创建模式的各种规则。
限制类型 | 分类 | 模式字符 | 解释 | 示例模式 | 显示('_’ as placeholder) | 输入 | 输出 |
---|---|---|---|---|---|---|---|
Character length | Letters | a | Letter | aaa | ___ | abc | abc |
> | Letter auto-converted to uppercase | > | _ | a | A | ||
><<<< | _____ | chris | Chris | ||||
< | Letter auto-converted to lowercase | < | _ | A | a | ||
Numbers | 0 | Digit (0-9) | 000 | ___ | 123 | 123 | |
___ | 12 | 12_ | |||||
Optional | () | Any of the strings in braces () should match. | 0{1,3} (kg|lb) | _ kg | 12 k | 12kg | |
| | Delimiter between strings. Used within braces(). Characters in braces() are static strings; an escape character is not required inside braces. | Aut\\hor:(Chris|Icey|Victor|Ian) or (Author):(Chris|Icey|Victor|Ian) | Author:Chris | V | Author:Victor | ||
[] | Combine the keywords and literals as a whole. | [>0]{5} | _____ | a7osn | A7OSN | ||
- | Indicates the character range. Used within square braces[]. | [\\a-f] | _ | b | b | ||
Special Char | \(should use “\\” when construct pattern string) | Escape Character | 0\>0 | _>_ | 32 | 3>2 | |
# | Any Character | ### | ___ | a1& | a1& | ||
Character length restriction | Dynamic | {n} | "n" times repeat | 0{4}/0{2}/0{2} | ____/__/__ | 20230101 | 2023/01/01 |
{n,m} | Minimum "n" times and maximum "m" times repeat | 0{1,3}.0{1,3}.0{1,3}.0{1,3} | _._._._ | 1270.0.1 | 127.0.0.1 | ||
[\\01]{0,} | 1010 | 1010 | |||||
{,m} | Upto "m" times repeat | 0{,3} | 123 | 123 | |||
{n,} | Minimum "n" times repeat | 0{1,}.0{2} | _.__ | 1234.56 | 1234.56 |
type=warning
在动态模式中:
占位符仅代表所需的最小长度。例如:0{2,10} 显示“__”,0{0,1} 什么都不显示。
如果未指定“m”,则动态模式的最大长度为 2147483648。
日期模式是一种指定日期和时间值格式的方法。它是固定格式模式,不接受字符长度限制。对于日期模式,SpreadJS 会根据日期规则自动修复用户输入。但是,您不得在单个模式中重复日期时间项,例如 yyyy、MM 等。例如,“ yyyy/MM/dd yyyy ”是无效模式,因为“yyyy”被使用了两次。
下表描述了日期模式的各种掩码字符及其在示例模式帮助下的用法。
模式字符 | 描述 | 示例模式 | 显示'_' as placeholder | 输入 | 输出 |
---|---|---|---|---|---|
y | Years | yy/MM/dd | __/__/__ | 23317 | 23/03/17 |
yyyy/MM/dd | ____/__/__ | 2023317 | 2023/03/17 | ||
M | Months | yyyy/M/dd | ____/_/__ | 2023317 | 2023/3/17 |
yyyy/MM/dd | ____/__/__ | 2023317 | 2023/03/17 | ||
d | Days | yyyy/MM/d | ____/__/_ | 202337 | 2023/03/7 |
yyyy/MM/dd | ____/__/__ | 202337 | 2023/03/07 | ||
H | 24 Hours | HH:mm:ss | __:__:__ | 2277 | 22:07:07 |
H:mm:ss | _:__:__ | 577 | 5:07:07 | ||
h | 12 Hours | hh:mm:ss (am|pm) | __:__:__ am | 277 p | 02:07:07 pm |
h:mm:ss (pm|am) | _:__:__ pm | 277 a | 2:07:07 am | ||
m | Minutes | HH:mm:ss | __:__:__ | 2277 | 22:07:07 |
HH:m:ss | __:_:__ | 2277 | 22:7:07 | ||
s | Seconds | HH:mm:ss | __:__:__ | 2277 | 22:07:07 |
HH:mm:s | __:__:_ | 2277 | 22:07:7 | ||
t | Hour Format | hh:mm:ss t | __:__:__ A | 2277p | 22:07:07 P |
hh:mm:ss tt | __:__:__ AM | 2277p | 22:07:07 PM |
您还可以定义命名模式并在其他模式中重用它们。但是,如果模式中有一个字符串与 namedPatterns 中的任何名称匹配,则掩码会将其替换为命名模式。因此,建议使用具有最小重复机会的名称来定义模式。为了获取所有名称模式的列表,SpreadJS 提供了一个静态方法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);
SpreadJS还支持公式模式,即以公式形式出现的以“=”开头的模式。例如,如果工作表的单元格 A1-A3 的作者姓名为“Chris”、“Icey”和“Ian”,则以下两种模式会给出相同的结果。
公式模式 | 等效模式 |
---|---|
'="Aut\\hor:("&TEXTJOIN("|",TRUE,A1:A3)&")"' | 'Aut\\hor:(Chris|Icey|Ian)' |
确认模式是否有效的静态方法validatePattern不支持公式模式。要评估公式模式,请在调用 validatePattern 方法之前使用GC.Spread.Sheets.CalcEngine.evaluateFormula 。
() 模式中的字符是一个静态字符串,因此 (am|pm) 可以不使用转义字符而直接写入。
当组限制只有一个组项时,掩码将其视为静态字符串。
默认情况下,掩码在输入中显示组限制的第一项。在接收到用户输入时,掩码会自动完成组项文本。
为了更好的输入体验,群组限制不区分大小写。
对于静态字符串或组文本,SpreadJS 不支持量词。因此,您不得设置 1234{1,5}、(12|34){1,5} 等模式。
SpreadJS 不支持组“()”和可选的“[]”的嵌套。
当被屏蔽的单元格进入编辑模式时,内部公式文本框被阻止,外部公式文本框为只读。
当公式单元格进入编辑模式时,掩码不会生效。
使用掩码编辑后,如果编辑器值以“=”、“-”、“+”开头,则自动添加 quotePrefix。
下表列出了常用的模式以供快速参考:
案件 | 模式或选项 | 展示 | 输入 | 输出 |
---|---|---|---|---|
电子邮件 | [a0]{1,}@[a0]{1,}.(com|cn|gov|edu) | _@_.com | chris@grapecity.e | |
姓名 | ><{1,} ><{1,} | __ __ | chris diao | Chris Diao |
长日期 | dd-MM-yyyy HH:mm | __-__-____ __:__ | 16720231620 | 16-07-2023 16:20 |
短日期 | 日-月-年 | __-__-____ | 1672023 | 16-07-2023 |
验证码 | [>0]{5} | _____ | a7osn | A7OSN |
十进制数 | [\-]{,1}0{0,}[.]{,1}0{0,} | -12.34 | -12.34 | |
12.34 | 12.34 | |||
二进制数 | [\\01]{0,} | 10101 | 10101 | |
美国电话号码 | [\\(]0{3}[\\)][-]0{3}[-]0{4} | (___)-___-____ | 1234567890 | (123)-456-7890 |