Default Value

SpreadJS supports setting a cell default value which can be a value or a formula. You can enter data in a cell that has a default value/formula, and it will not be overridden.

The defaultValue API is used to set a default value/formula to a cell, so that when the cell is empty, it will display the default value or the value calculated from a default formula. When an empty cell has a default value/formula applied to it and a user edits that cell, the default value will not be overridden. The APIs are as follows:
window.onload = function () { let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 1}); spread.suspendPaint(); let sheet = spread.getActiveSheet(); let fbx = new GC.Spread.Sheets.FormulaTextBox.FormulaTextBox(document.getElementById('formula'), {rangeSelectMode: true}); fbx.workbook(spread); initSpread(spread, fbx); bindEvents(spread); spread.resumePaint(); }; function bindEvents(spread) { spread.bind(GC.Spread.Sheets.Events.SelectionChanged, function () { getDefault(spread.getActiveSheet()); }); spread.bind(GC.Spread.Sheets.Events.ActiveSheetChanged, function () { self.getDefault(spread.getActiveSheet()); }); } function getDefault(sheet) { let selections = sheet.getSelections(), showDefault = document.getElementById('showDefault'); if (!selections || selections.length === 0) { showDefault.value = ''; return; } let sel = selections[0], row = sel.row, col = sel.col; if (sel.rowCount === 1 && sel.colCount === 1) { showDefault.value = sheet.getDefaultValue(row, col); } else { showDefault.value = ''; } } function convertValue (value) { if (!value) { return value; } // try to convert to number let num = Number(value); if (!isNaN(num)) { return num; } // try to convert to boolean if (value.toLowerCase() === "true" || value.toLowerCase() === "false") { return value.toLowerCase() === "true"; } // try to convert to date let date = new Date(value); if (!isNaN(date.getTime())) { return date; } return value; } function initSpread(spread, fbx) { fetch(getFileUrl()).then(res => res.blob()).then(file => { spread.open(file, () => { let sheet = spread.getActiveSheet(); sheet.suspendPaint(); sheet.suspendCalcService(); for (let i = 0; i < 12; i++) { sheet.getCell(2, 3 + i, 3).defaultValue('=$B3/12'); sheet.getCell(3, 3 + i, 3).defaultValue('=$B4/12'); } sheet.resumeCalcService(); sheet.resumePaint(); getDefault(spread.getActiveSheet()); }); }); document.getElementById('btnSetDefault').onclick = function () { let value = document.getElementById('value').value; let formula = fbx.text(); sheet = spread.getActiveSheet(); let selections = sheet.getSelections(); if (!selections || selections.length === 0) { return; } sheet.suspendPaint(); let length = selections.length; for (let i = 0; i < length; i++) { let sel = selections[i], rowIndex = sel.row, colIndex = sel.col, rowCount = sel.rowCount, colCount = sel.colCount, maxRow = rowIndex + rowCount, maxColumn = colIndex + colCount, r, c; if (rowIndex !== -1 && colIndex !== -1) { for (r = rowIndex; r < maxRow; r++) { for (c = colIndex; c < maxColumn; c++) { sheet.setDefaultValue(r, c, formula || convertValue(value)); } } } } sheet.resumePaint(); getDefault(sheet); }; } function getFileUrl () { return '$DEMOROOT$/spread/source/data/defaultValue.sjs'; }
<!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$/en/purejs/node_modules/@mescius/spread-sheets-io/dist/gc.spread.sheets.io.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="option-row"> <p> <span>You can set, get the default value and formula for the active cells.</span> </p> </div> <div class="option-row"> <label for="value">Default Value: </label> <input id="value" /> </div> <div class="option-row"> <label for="formula">Default Formula: </label> <div id="formula" spellcheck="false" style="border: 1px solid #808080;width:100%;height:21px;overflow:hidden;"></div> </div> <div class="option-row"> <input type="button" id="btnSetDefault" value="Set Default" title="Set default value and formula to selected item(s)" /> </div> <div class="option-row"> <label for="showDefault">Get Default Value: </label> <textarea id="showDefault" cols="31" readonly></textarea> </div> </div> </div></body> </html>
.sample { position: relative; height: 100%; overflow: auto; } .sample::after { display: block; content: ""; clear: both; } .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: inline-block; margin-bottom: 6px; } input { padding: 4px; width: 100%; margin: 0 4px 4px 0; box-sizing: border-box; } input[type=button] { width: 30%; } p { background-color: #F4F8EB; padding: 4px; } p span { display: block; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }