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
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
import './styles.css';
export function AppFunc() {
const initSpread = (spread) => {
spread.suspendPaint();
spread.options.highlightInvalidData = true;
var sheet = spread.sheets[0];
var defaultStyle = new GC.Spread.Sheets.Style();
defaultStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
defaultStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
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();
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>
);
}
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);
}
import * as React from 'react';
import {SpreadSheets, Worksheet} from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
import './styles.css';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>
);
}
initSpread(spread) {
this.spread = spread;
spread.suspendPaint();
spread.options.highlightInvalidData = true;
var sheet = spread.sheets[0];
var defaultStyle = new GC.Spread.Sheets.Style();
defaultStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
defaultStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
input {
padding: 4px 6px;
}
.options-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
label {
display: block;
margin-bottom: 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
