The SJS.REGEXMATCH function can judge whether a piece of text matches a specified regular expression.

Syntax Argument Description text (Required) The text to be tested against the regular_expression.You can input a string like “text“, a cell reference like “C4”, or a cell range reference like “C4: D4“ (work in dynamic array) regular_expression (Required) The regular expression to test the text against.You can input a string like “[0-9]+“,or a cell reference like “C4“. modifiers The modifiers to define the pattern. You can input a string like “gi“, or a cell reference like “C4“. Modifiers Modifier Name Description g global Retain the index of the last match, allowing subsequent searches to start from the end of the previous match.Without the global flag, subsequent searches will return the same match. i ignore case Makes the whole expression case-insensitive.For example, /aBc/i would match AbC. m multiline When the multiline flag is enabled, beginning and end anchors (^ and \$) will match the start and end of a line, instead of the start and end of the whole string.Note that patterns such as /^[\s\S]+$/m may return matches that span multiple lines because the anchors will match the start/end of any line. u unicode When the unicode flag is enabled, you can use extended unicode escapes in the form \x{FFFFF}.It also makes other escapes stricter, causing unrecognized escapes (ex. \j) to throw an error. y sticky The expression will only match from its lastIndex position and ignores the global (g) flag if set. Because each search in RegExr is discrete, this flag has no further impact on the displayed results. s dotAll Dot (.) will match any character, including newline. Please make sure your browser supports the modifiers you use. The modifier “y“ is not supported in SJS because sticky will lead to inconsistent calculation results.If modifier “y“ is set, it will be ignored in the calculation. Samples
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); spread.options.highlightInvalidData = true; var sheet = spread.sheets[0]; var defaultStyle = new GC.Spread.Sheets.Style(); defaultStyle.hAlign =; defaultStyle.vAlign =; sheet.setDefaultStyle(defaultStyle); sheet.setColumnWidth(0, 250); sheet.setColumnWidth(1, 250); sheet.setColumnWidth(2, 150); sheet.setColumnWidth(3, 250); sheet.setColumnWidth(4, 150); sheet.addSpan(0, 0, 2, 5); sheet.getCell(0, 0).value("Conditional Format").font("21px bold normal normal"); initRange(sheet, 2); var EQUAL_TO = GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.equalsTo; var CONDITION_RANGE = [new GC.Spread.Sheets.Range(4, 4, 4, 1)]; var true_style = new GC.Spread.Sheets.Style(); true_style.backColor = 'lightgreen'; sheet.conditionalFormats.addCellValueRule(EQUAL_TO, true, undefined, true_style, CONDITION_RANGE); var false_style = new GC.Spread.Sheets.Style(); false_style.backColor = 'pink'; sheet.conditionalFormats.addCellValueRule(EQUAL_TO, false, undefined, false_style, CONDITION_RANGE); sheet.addSpan(8, 0, 2, 5); sheet.getCell(8, 0).value("Data Validation").font("21px bold normal normal"); initRange(sheet, 10); createDataValidations(sheet, 12); createDataValidations(sheet, 13); createDataValidations(sheet, 14); spread.resumePaint(); } function initRange(sheet, baseRow) { sheet.addSpan(baseRow, 0, 2, 1); sheet.addSpan(baseRow, 1, 2, 1); sheet.addSpan(baseRow, 2, 2, 1); sheet.addSpan(baseRow, 3, 2, 1); sheet.addSpan(baseRow, 4, 2, 1); sheet.getCell(baseRow, 0).value("Text").font("21px bold normal normal"); sheet.getCell(baseRow, 1).value("Regular Expression").font("21px bold normal normal"); sheet.getCell(baseRow, 2).value("Modifiers").font("21px bold normal normal"); sheet.getCell(baseRow, 3).value("Formula Text").font("21px bold normal normal"); sheet.getCell(baseRow, 4).value("Result").font("21px bold normal normal"); var dataArr = [["SpreadJS Supports 400 functions.", "\\d+", ""], ["My Name is (John Smith)", "\\(([\\w ]+)\\)", ""], ["Red", "[^red]", "i"]]; sheet.setArray(2 + baseRow, 0, dataArr); for (var i = baseRow, len = dataArr.length; i < len + baseRow; i++) { var row = 3 + i; sheet.setFormula(row - 1, 4, `=SJS.REGEXMATCH(A${row}, B${row}, C${row})`); sheet.setFormula(row - 1, 3, `=FORMULATEXT(E${row})`); } } function createDataValidations (sheet, row) { var false_dv = GC.Spread.Sheets.DataValidation.createFormulaValidator("IF(E" + (row + 1) + ",TRUE,FALSE)"); false_dv.highlightStyle({type: GC.Spread.Sheets.DataValidation.HighlightType.dogEar, color: "pink", position: GC.Spread.Sheets.DataValidation.HighlightPosition.topLeft}); sheet.setDataValidator(row, 4, 1, 1, false_dv, 3); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> </div> </body> </html>
input[type="text"] { width: 200px; margin-right: 20px; } label { display: inline-block; width: 110px; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; } label { display: block; margin-bottom: 6px; } input { padding: 4px 6px; } input[type=button] { margin-top: 6px; display: block; width:216px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } code { border: 1px solid #000; }