Format String

Format String is an update to the style formatter in SpreadJS. Cells in the workbook can have both formulas and text as a part of text value templates. These can be combined with other spreadsheet features like sparkline formulas to create custom formats inside worksheet cells. The developer can write JavaScript code to set the format string as the formatter for a style.

<p>The formatter will be divided into the following three cases</p> <ul> <li>normal cell formatter: Standard formatter syntax format.</li> <li>formula format string: Standard formula syntax format</li> <li>text template format string: Text string where formula is contained by "{{" and "}}".</li> </ul> <p>In the past, you could not set values and formulas in one cell at the same time, but now you can set the formula to the formatter of Style.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> style = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Style(); style.formatter = <span class="hljs-string">"=BC_CODE49(CONCAT(A1,A2))"</span> sheet.setStyle(<span class="hljs-number">0</span>, <span class="hljs-number">0</span>, style); sheet.getCell(<span class="hljs-number">1</span>, <span class="hljs-number">1</span>, <span class="hljs-number">3</span>).formatter(<span class="hljs-string">'=BC_CODE128(B2,,,"TRUE","top","B","Arial","normal")'</span>); </code></pre> <p>This template is a string of text values and formula, use "{{" and "}}" to wrap the formula in the string, in the formula you still can reference the current cell.</p> <pre><code class="hljs js language-js"> sheet.addCustomName(<span class="hljs-string">'Sales'</span>,<span class="hljs-string">"=A1:A5"</span>, <span class="hljs-number">1</span>, <span class="hljs-number">1</span>); <span class="hljs-keyword">var</span> style = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Style(); style.formatter = <span class="hljs-string">"On {{=TEXT(TODAY(),A4)}} Total Sales Were: {{=SUM(Sales)}}"</span>; sheet.setStyle(<span class="hljs-number">5</span>, <span class="hljs-number">0</span>, style); </code></pre> <p>We provide the '@' symbol, this symbol represents the current cell reference. you can use it directly in the formula or text value template </p> <pre><code class="hljs js language-js"> sheet.addCustomName(<span class="hljs-string">'Sales'</span>,<span class="hljs-string">"=A1:A5"</span>, <span class="hljs-number">1</span>, <span class="hljs-number">1</span>); <span class="hljs-keyword">var</span> style = <span class="hljs-keyword">new</span> GC.Spread.Sheets.Style(); style.formatter = <span class="hljs-string">"On {{=TEXT(TODAY(),@)}} Total Sales Were: {{=SUM(Sales)}}"</span>; sheet.setStyle(<span class="hljs-number">5</span>, <span class="hljs-number">0</span>, style); sheet.getCell(<span class="hljs-number">0</span>, <span class="hljs-number">0</span>, <span class="hljs-number">3</span>).formatter(<span class="hljs-string">'=BC_CODE128(@,,,"TRUE","top","B","Arial","normal")'</span>); </code></pre> <p>We provide an option <strong>saveAsView</strong> for workbook json serialization, which helps you to save the format string result to excel.</p> <pre><code class="hljs js language-js"> spread.toJSON({ <span class="hljs-attr">saveAsView</span>: <span class="hljs-literal">true</span> }); </code></pre>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { var gcns = GC.Spread.Sheets; spread.suspendPaint(); var sheet = spread.getActiveSheet(); var data = [ [, "FY 2019"], [, "Sales"], [, "Monthly", "Cumulative"], ["Apr", 188897, 188897], ["May", 208146, 397043], ["Jun", 226196, 623239], ["Jul", 277318, 900557], ["Aug", 263273, 1163830], ["Sep", 259845, 1423675], ["Oct", 241047, 1664722], ["Nov", 256306, 1921028], ["Dec", 195845, 2116873], ["Jan", 204934, 2321808], ["Feb", 257852, 2579660], ["Mar", 227779, 2807439] ]; sheet.setArray(3, 1, data); sheet.setColumnWidth(2, 110); sheet.setColumnWidth(3, 110); sheet.setRowCount(20); sheet.setColumnCount(9); sheet.options.gridline.showHorizontalGridline = false; sheet.options.gridline.showVerticalGridline = false; sheet.getRange(3, 1, 15, 3).setBorder( new gcns.LineBorder("black", gcns.LineStyle.medium), { all: true }); sheet.addSpan(3, 2, 1, 2); sheet.addSpan(4, 2, 1, 2); sheet.getRange(3, 2, 3, 2).backColor('#CFE1F3').hAlign(gcns.HorizontalAlign.center); sheet.getRange(6, 1, 12, 1).backColor('#CCC1DA'); var cMapSource = [ { "Currency": "USD", "Value": 1, "Symbol": "$" }, { "Currency": "CNY", "Value": 7.02, "Symbol": "¥" }, { "Currency": "JPY", "Value": 108.8, "Symbol": "¥" }, { "Currency": "EURO", "Value": 0.91, "Symbol": "€" }, ]; sheet.tables.addFromDataSource('cT', 3, 5, cMapSource); [5, 6, 7].forEach((col) => { sheet.setColumnWidth(col, 80); }); sheet.getCell(1, 2).value("Unit:").hAlign(gcns.HorizontalAlign.right); sheet.getRange(1, 3, 1, 1).backColor("yellow").setBorder( new gcns.LineBorder("blue", gcns.LineStyle.medium), { all: true }); var dv1 = gcns.DataValidation.createFormulaListValidator('=cT[[#Data], [Currency]]'); sheet.setDataValidator(1, 3, dv1); sheet.getCell(1, 3).hAlign(gcns.HorizontalAlign.center).value("USD"); sheet.getRange(6, 2, 12, 2) .hAlign(gcns.HorizontalAlign.center) .formatter('=VLOOKUP($D$2,cT[#Data],3,FALSE)&" "&TEXT(@*VLOOKUP($D$2,cT[#Data],2,FALSE),"###,###")'); 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/@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"> By changing the value of D2 to select the currency calculation strategy, you will notice that the table on the left has changed. </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; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }