公式

SpreadJS 支持在单元格中设置公式, 并且你可以给一个公式添加一个自定义的名称。

<p>通过如下代码可以给一个或者一片区域中的单元格设置公式。</p> <pre><code class="hljs js language-js">sheet.setFormula(<span class="hljs-number">1</span>, <span class="hljs-number">1</span>, <span class="hljs-string">'=SUM(A1,C3)'</span>); sheet.setArrayFormula(<span class="hljs-number">1</span>, <span class="hljs-number">1</span>, <span class="hljs-number">7</span>, <span class="hljs-number">7</span>, <span class="hljs-string">'=SUM(A1,C3)'</span>); </code></pre> <p>你可以通过如下代码获取指定单元格中是否有公式存在:</p> <pre><code class="hljs js language-js">sheet.hasFormula(<span class="hljs-number">7</span>, <span class="hljs-number">7</span>); </code></pre> <p>如果一个公式的长度非常长或者它的使用频次非常多的话, 你可以添加一个自定义名称给它以便方便使用。</p> <pre><code class="hljs js language-js">sheet.addCustomName(<span class="hljs-string">'customName1'</span>,<span class="hljs-string">'=SUM(A1,C3)'</span>, <span class="hljs-number">1</span>, <span class="hljs-number">1</span>); </code></pre> <p>可以从自定义名称集合中删除某个自定义名称:</p> <pre><code class="hljs js language-js">sheet.removeCustomName(<span class="hljs-string">'customName1'</span>); </code></pre> <p>也可以通过调用如下方法删除所有的自定义名称:</p> <pre><code class="hljs js language-js">sheet.clearCustomNames(); </code></pre> <p>可以通过设置表单的<strong>ShowFormulas</strong>属性来控制表单是否显示公式。使用<strong>ShowFormulas</strong>属性,你可以从SpreadJS中直接拷贝公式字符串,并粘贴到其他应用中,亦或者直接将公式打印。</p> <pre><code class="hljs js language-js"> sheet.options.showFormulas = <span class="hljs-literal">true</span>; </code></pre>
window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss")); var sheet = spread.getSheet(0); sheet.suspendPaint(); sheet.setArray(0, 0, [ ["Product", "Item Price", "Quantity", "Sales"], ['Kraft Real Mayo', 5.71, 1], ['Smartfood Popcorn', 2.5, 4], ['Teddy Grahams Crackers', 35, 5], ['Parmesan Cheese', 14.89, 9], ['Planter Deluxe Whole Cashew', 8.52, 3], ['Total'] ]); sheet.setColumnWidth(0, 190); sheet.setColumnWidth(1, 80); sheet.setColumnWidth(2, 80); sheet.setFormula(1, 3, "B2*C2"); sheet.setFormula(2, 3, "B3*C3"); sheet.setFormula(3, 3, "B4*C4"); sheet.setFormula(4, 3, "B5*C5"); sheet.setFormula(5, 3, "B6*C6"); sheet.addCustomName('customerName1', '=$B$2:$B$6', 0, 0); sheet.addCustomName('customerName2', '=$C$2:$C$6', 0, 0); sheet.setFormula(6, 1, "=SUM(customerName1)"); sheet.setFormula(6, 2, "=SUM(customerName2)"); sheet.getRange(6, 0, 1, 4).foreColor('red'); sheet.setFormula(6, 3, "B7*C7"); var style = sheet.getStyle(4, 7) || new GC.Spread.Sheets.Style();; style.cellButtons= [{ useButtonStyle: true, caption: "Show Formulas", width: 120, command: function() { sheet.options.showFormulas = !sheet.options.showFormulas; if (sheet.options.showFormulas) { style.cellButtons[0].imageType = GC.Spread.Sheets.ButtonImageType.ok; } else { style.cellButtons[0].imageType = GC.Spread.Sheets.ButtonImageType.none; } sheet.setStyle(4, 7, style); }, }]; sheet.setStyle(4, 7, style); sheet.setColumnWidth(7, 122); sheet.resumePaint(); _getElementById('btnSetFormula').addEventListener('click', function() { var sheet = spread.getActiveSheet(); if (_getElementById("formula").value) { var formula = _getElementById("formula").value; if (_getElementById("rowIndex").value && _getElementById("columnIndex").value) { var rowIndex = parseInt(_getElementById("rowIndex").value); var columnIndex = parseInt(_getElementById("columnIndex").value); if (_getElementById("rowCount").value && _getElementById("columnCount").value) { var rowCount = Math.max(parseInt(_getElementById("rowCount").value), 1); var columnCount = Math.max(parseInt(_getElementById("columnCount").value), 1); sheet.setArrayFormula(rowIndex, columnIndex, rowCount, columnCount, formula); } else { sheet.setFormula(rowIndex, columnIndex, formula); } } } }); }; function _getElementById(id) { return document.getElementById(id); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta name="spreadjs culture" content="zh-cn" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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"> <p style="font-size:16px;padding:1px 0px" >Setting a formula via code</p> <p style="padding:2px 10px">Specify the row and column index for the formula cell. Set the row count and column count to specify the number of rows and columns to add the formula to. Add the formula to the Formula box and then click SetFormula to apply the formula.</p> <div class="option-row"> <label for="rowIndex">Row Index:</label> <input type="text" id="rowIndex"/> </div> <div class="option-row"> <label for="columnIndex">Column Index:</label> <input type="text" id="columnIndex"/> </div> <div class="option-row"> <label for="rowCount">Row Count:</label> <input type="text" id="rowCount"/> </div> <div class="option-row"> <label for="columnCount">Column Count:</label> <input type="text" id="columnCount"/> </div> <div class="option-row"> <label for="formula">Formula:</label> <input type="text" id="formula" value="=SUM(B2,B6)"/> <input type="button" id="btnSetFormula" value="SetFormula" /> </div> </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: 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; } 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; }