Range Select

Sometimes, you may need to allow end-users to enter ranges (e.g. “Sheet1!$A$5:$C$10”) by selecting the ranges on the spreadsheet instead of manually typing them. The result or returned value of this feature would be the text representing the address of one or more ranges in a textbox (example of two ranges: “Sheet1!A1:B5,Sheet2!G11”).

<p>If you want to use range select mode, you just need to create a formulaTextBox that has this option <strong>{rangeSelectMode: true}.</strong> For example:</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> rangeSelect = createElement(<span class="hljs-string">"div"</span>); <span class="hljs-keyword">var</span> fbx = <span class="hljs-keyword">new</span> GC.Spread.Sheets.FormulaTextBox.FormulaTextBox(rangeSelect, {<span class="hljs-attr">rangeSelectMode</span>: <span class="hljs-literal">true</span>}); </code></pre> <p>You can use the workbook method of FormulaTextBox to attach this formulaTextBox to a spread. For example:</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> workbook = <span class="hljs-keyword">new</span> GC.Spread.Sheets.workbook(<span class="hljs-built_in">document</span>.getElementById(<span class="hljs-string">'ss'</span>)) fbx.workbook(workbook); </code></pre> <p>If fbx1.workbook() doesn't have arguments, range select does not work.</p> <p>You can use the text method of FormulaTextBox to get the text of the formula text box.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> str = fbx.text(); </code></pre> <p>You can use the destroy method to remove the formula text box elements.</p> <pre><code class="hljs js language-js"> fbx.destroy(); </code></pre>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2}); initSpread(spread); var fbx = new GC.Spread.Sheets.FormulaTextBox.FormulaTextBox(document.getElementById('formulaBar'), {rangeSelectMode: true}); fbx.workbook(spread); document.getElementById('getValue').onclick = function () { document.getElementById('rangeText').textContent = fbx.text(); }; }; function initSpread(spread) { var spreadNS = GC.Spread.Sheets; var sheet = spread.getActiveSheet(); sheet.suspendPaint(); var cfs = sheet.conditionalFormats; var Range = spreadNS.Range; var row = 0, col = 1; sheet.getCell(row, ++col).value("Icon Set").font("700 11pt Calibri"); sheet.addSpan(row, col, 1, 10); sheet.addSpan(row + 6, col, 1, 10); row++; for (var column = col; column < col + 10; column++) { sheet.getCell(row, column).value(-50); sheet.getCell(row + 1, column).value(-25); sheet.getCell(row + 2, column).value(0); sheet.getCell(row + 3, column).value(25); sheet.getCell(row + 4, column).value(50); sheet.getCell(row + 6, column).value(-50); sheet.getCell(row + 7, column).value(-25); sheet.getCell(row + 8, column).value(0); sheet.getCell(row + 9, column).value(25); sheet.getCell(row + 10, column).value(50); } var rowCount = 5; cfs.addIconSetRule(0, [new Range(row, col, rowCount, 1)]); cfs.addIconSetRule(1, [new Range(row, ++col, rowCount, 1)]); cfs.addIconSetRule(2, [new Range(row, ++col, rowCount, 1)]); cfs.addIconSetRule(3, [new Range(row, ++col, rowCount, 1)]); cfs.addIconSetRule(4, [new Range(row, ++col, rowCount, 1)]); cfs.addIconSetRule(5, [new Range(row, ++col, rowCount, 1)]); cfs.addIconSetRule(6, [new Range(row, ++col, rowCount, 1)]); cfs.addIconSetRule(7, [new Range(row, ++col, rowCount, 1)]); cfs.addIconSetRule(8, [new Range(row, ++col, rowCount, 1)]); cfs.addIconSetRule(9, [new Range(row, ++col, rowCount, 1)]); col = 1; cfs.addIconSetRule(10, [new Range(row + 6, ++col, rowCount, 1)]); cfs.addIconSetRule(11, [new Range(row + 6, ++col, rowCount, 1)]); cfs.addIconSetRule(12, [new Range(row + 6, ++col, rowCount, 1)]); cfs.addIconSetRule(13, [new Range(row + 6, ++col, rowCount, 1)]); cfs.addIconSetRule(14, [new Range(row + 6, ++col, rowCount, 1)]); cfs.addIconSetRule(15, [new Range(row + 6, ++col, rowCount, 1)]); cfs.addIconSetRule(16, [new Range(row + 6, ++col, rowCount, 1)]); cfs.addIconSetRule(17, [new Range(row + 6, ++col, rowCount, 1)]); cfs.addIconSetRule(18, [new Range(row + 6, ++col, rowCount, 1)]); cfs.addIconSetRule(19, [new Range(row + 6, ++col, rowCount, 1)]); sheet.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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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 class="options-container"> <div class="top-options"> <p>Click the icon of the rangeSelector, then select a range, then Click the 'Get Value' button.This can be useful for providing users the ability to select ranges for their formulas rather than manually typing them.</p> <div id="formulaBar" spellcheck="false" style="border: 1px solid #808080;width:100%;"></div> </div> <div class="options-row" style="margin-top: 10px"> <input type="button" id="getValue" value="Get Value" style=" font-size: 14px; height: 30px"> <label> Range Text: <span id="rangeText" style="border: 0px; outline: none; font-size: 14px;padding: 0px"></span> </label> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } input, select { width: 100%; padding: 4px, 8px; margin: 8px 0; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }