Function Wildcards

SpreadJS allows the user to use wildcard characters in the function which have a criteria argument.

Wildcard characters (?, *, ~) can be used as comparison criteria for functions when searching. Wildcard character Match ? Any single character * Any number of characters ~ followed by ?, *, or ~ ?, *, or ~ character Note: The wildcard can only be used in comparison strings that use equals (=); it cannot be used in comparisons that use the other operators (>,<,!=,<=,>=). Wildcard characters work in all the functions that have a criteria argument, as follows: AVERAGEIF AVERAGEIFS COUNTIF COUNTIFS SUMIF SUMIFS MATCH SEARCH VLOOKUP HLOOKUP DAVERAGE DGET DMAX DMIN DPRODUCT DSTDEV DSTDEVP DSUM DVAR DVARP
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 }); initSpread(spread); }; function initSpread(spread) { var spreadNS = GC.Spread.Sheets; var sheet = spread.sheets[0]; spread.suspendPaint(); sheet.options.allowCellOverflow = true; var array = [ ["Tree", "Height", "Age", "Yield", "Profit", "Height"], ["=App?e", ">10", null, null, null, "<16"], ["=P*"], ["Tree", "Height", "Age", "Yield", "Profit"], ["Apple", 18, 20, 14, 105], ["Pear", 12, 12, 10, 96], ["Cherry", 13, 14, 9, 105], ["AppLe", 14, 15, 10, 75], ["Pear", 9, 8, 8, 76.8], ["Apple", 8, 9, 6, 45] ]; sheet.setArray(0, 0, array); sheet.setColumnWidth(0, 100); var table = sheet.tables.add("Table1", 0, 0, 3, 6, spreadNS.Tables.TableThemes.medium4); table.rowFilter().filterButtonVisible(false); table = sheet.tables.add("Table2", 4, 0, 7, 5, spreadNS.Tables.TableThemes.medium4); table.rowFilter().filterButtonVisible(false); table = sheet.tables.add("Table3", 12, 0, 5, 6, spreadNS.Tables.TableThemes.medium4); table.rowFilter().filterButtonVisible(false); sheet.addSpan(12, 1, 1, 5); sheet.setValue(12, 0, 'Formula Result'); sheet.setValue(12, 1, 'Formula with wildcard'); sheet.addSpan(13, 1, 1, 5); sheet.setValue(13, 1, '=DCOUNT(A4:E10,"Age",A1:F3)'); sheet.setFormula(13, 0, 'DCOUNT(A4:E10,"Age",A1:F3)'); sheet.addSpan(14, 1, 1, 5); sheet.setValue(14, 1, '=SEARCH("?t", B1)'); sheet.setFormula(14, 0, 'SEARCH("?t", B1)'); sheet.addSpan(15, 1, 1, 5); sheet.setValue(15, 1, '=VLOOKUP("Ch*",A5:B10,2,0)'); sheet.setFormula(15, 0, 'VLOOKUP("Ch*",A5:B10,2,0)'); sheet.addSpan(17, 1, 1, 5); sheet.setValue(17, 1, '=COUNTIF(A5:B11,"App?e")'); sheet.setFormula(17, 0, 'COUNTIF(A5:B11,"App?e")'); sheet.setValue(18, 1, '=SEARCH("=P~*",A3)'); sheet.setFormula(18, 0, 'SEARCH("=P~*",A3)'); spread.resumePaint(); }
<!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>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; border: 1px solid gray; }