Accounting Formatter

SpreadJS supports the Accounting format like Excel.

<p>A standard accounting format looks like this: <code>'_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'</code></p> <pre><code class="hljs js language-js"> sheet.setFormatter(<span class="hljs-number">2</span>, <span class="hljs-number">2</span>, <span class="hljs-string">'_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'</span>); </code></pre> <p><strong>Different Format Patterns within the Accounting Format:</strong></p> <p><strong>Add space (<code>'_'</code>)</strong>: To create a space that is the width of a character in a number format, include an underscore character (<code>'_'</code>), followed by the character that you want to use.</p> <pre><code class="hljs js language-js"> sheet.setFormatter(<span class="hljs-number">2</span>, <span class="hljs-number">2</span>, <span class="hljs-string">'#_)'</span>); </code></pre> <p><strong>Repeat characters (<code>'*'</code>)</strong>: To repeat the next character in the format to fill the column width, include an asterisk (*) in the number format.</p> <pre><code class="hljs js language-js"> sheet.setFormatter(<span class="hljs-number">2</span>, <span class="hljs-number">2</span>, <span class="hljs-string">'@*-'</span>); </code></pre> <p><strong>Thousands separator and scale (',')</strong>: Displays the thousands separator in a number. Spread separates thousands by commas if the format contains a comma that is enclosed by number signs (#) or by zeros. A comma that follows a digit placeholder scales the number by 1,000. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number 12.200.0 is displayed.</p> <pre><code class="hljs js language-js"> sheet.setFormatter(<span class="hljs-number">2</span>, <span class="hljs-number">2</span>, <span class="hljs-string">'#,###'</span>); sheet.setFormatter(<span class="hljs-number">2</span>, <span class="hljs-number">3</span>, <span class="hljs-string">'#,,'</span>); </code></pre> <p><strong>Percentages ('%')</strong>: To display numbers as a percentage of 100 — for example, to display 0.08 as 8% or 2.8 as 280% — include the percent sign (%) in the number format. For example, to display the number of (2,2) as a percentage of 100, you can also display the number as a percentage of 10000.</p> <pre><code class="hljs js language-js"> sheet.setFormatter(<span class="hljs-number">2</span>, <span class="hljs-number">2</span>, <span class="hljs-string">'0%'</span>) sheet.setFormatter(<span class="hljs-number">2</span>, <span class="hljs-number">2</span>, <span class="hljs-string">'#%%'</span>) </code></pre> <p><strong>Digital placeholder ('?')</strong>: To add a digital placeholder in your number value, you can use the question mark symbol (?), which follows the same rules as zero (0). Spread also adds a space for insignificant zeros on either side of the decimal point.</p> <pre><code class="hljs js language-js"> sheet.setFormatter(<span class="hljs-number">2</span>, <span class="hljs-number">2</span>, <span class="hljs-string">'#.???'</span>) </code></pre>
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2}); initSpread(spread); }; function initSpread(spread) { var sheet = spread.getActiveSheet(); sheet.suspendPaint(); sheet.setColumnWidth(0, 150); sheet.setColumnWidth(1, 200); sheet.setColumnWidth(2, 150); sheet.setColumnWidth(3, 150); sheet.getRange(-1, 1, 200, 1).hAlign(1); //set standard accounting formatter initStandardAccountingFormat(sheet); //set repeat chartacters formatter initRepeatCharacters(sheet); //set placeholder formatter initPlaceholder(sheet); //set text formatter initText(sheet); //set thousands separator formatter thousandsSeparator(sheet); // set percentages formatter initPercentages(sheet); //set digital placeholder formatter initDigitalPlaceholder(sheet); sheet.resumePaint(); } function inintHeaderStyle(sheet, rowIndex) { sheet.setRowHeight(rowIndex, 30); sheet.addSpan(rowIndex, 0, 1, 4); sheet.getRange(rowIndex, 0, 1, 4).backColor('grey').foreColor('white').vAlign(1); } function initStandardAccountingFormat(sheet) { inintHeaderStyle(sheet, 1); sheet.setValue(1, 0, 'Standard Accounting Format : _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'); sheet.setValue(2, 0, 'Value'); sheet.setValue(3, 0, 12); sheet.setValue(4, 0, -12); sheet.setValue(5, 0, 0); sheet.setValue(6, 0, 'Text'); sheet.setValue(2, 2, 'Formatted Result'); sheet.getRange(3, 2, 4, 1).formatter('_($* #,##0.000_);_($* (#,##0.000);_($* "-"??_);_(@_)'); sheet.setValue(3, 2, 12); sheet.setValue(4, 2, -12); sheet.setValue(5, 2, 0); sheet.setValue(6, 2, 'Text'); } function initRepeatCharacters(sheet) { var rowIndex = 8; inintHeaderStyle(sheet, rowIndex); sheet.setValue(rowIndex, 0, 'Repeat Characters : *'); rowIndex++; sheet.setValue(rowIndex, 0, 'Value'); sheet.setValue(rowIndex, 1, 'Format'); sheet.setValue(rowIndex, 2, 'Formatted Result'); rowIndex++; sheet.setValue(rowIndex, 0, 'Sign'); sheet.setValue(rowIndex, 1, '@*.'); sheet.setValue(rowIndex, 2, 'Sign'); sheet.setFormatter(rowIndex, 2, '@*.'); rowIndex++; sheet.setValue(rowIndex, 0, 'Sign'); sheet.setValue(rowIndex, 1, '*.@'); sheet.setValue(rowIndex, 2, 'Sign'); sheet.setFormatter(rowIndex, 2, '*.@'); rowIndex++; sheet.setValue(rowIndex, 0, 12.34); sheet.setValue(rowIndex, 1, '$* #.##'); sheet.setValue(rowIndex, 2, 12.34); sheet.setFormatter(rowIndex, 2, '$* #.##'); rowIndex++; sheet.setValue(rowIndex, 0, 12.34); sheet.setValue(rowIndex, 1, '$#.##*_'); sheet.setValue(rowIndex, 2, 12.34); sheet.setFormatter(rowIndex, 2, '$#.##*_'); } function initPlaceholder(sheet) { var rowIndex = 15; inintHeaderStyle(sheet, rowIndex); sheet.setValue(rowIndex, 0, "Add Space(use the follow char's width as a space ) : _"); rowIndex++; sheet.setValue(rowIndex, 0, 'Value'); sheet.setValue(rowIndex, 1, 'Format'); sheet.setValue(rowIndex, 2, 'Formatted Result'); rowIndex++; sheet.setValue(rowIndex, 0, 'Sign'); sheet.setValue(rowIndex, 1, '_W@'); sheet.setValue(rowIndex, 2, 'Sign'); sheet.setFormatter(rowIndex, 2, '_W@'); rowIndex++; sheet.setValue(rowIndex, 0, 'Sign'); sheet.setValue(rowIndex, 1, '_.@'); sheet.setValue(rowIndex, 2, 'Sign'); sheet.setFormatter(rowIndex, 2, '_.@'); rowIndex++; sheet.setValue(rowIndex, 0, 12); sheet.setValue(rowIndex, 1, '#_W'); sheet.setValue(rowIndex, 2, 12); sheet.setFormatter(rowIndex, 2, '#_W'); rowIndex++; sheet.setValue(rowIndex, 0, 12); sheet.setValue(rowIndex, 1, '#_.'); sheet.setValue(rowIndex, 2, 12); sheet.setFormatter(rowIndex, 2, '#_.'); } function initText(sheet) { var rowIndex = 22; inintHeaderStyle(sheet, rowIndex); sheet.setValue(rowIndex, 0, "Text/Label : \"\"& \\"); rowIndex++; sheet.setValue(rowIndex, 0, 'Value'); sheet.setValue(rowIndex, 1, 'Format'); sheet.setValue(rowIndex, 2, 'Formatted Result'); rowIndex++; sheet.setValue(rowIndex, 0, 15); sheet.setValue(rowIndex, 1, '#,##0 "Km"'); sheet.setValue(rowIndex, 2, 15); sheet.setFormatter(rowIndex, 2, '#,##0 "Km"'); rowIndex++; sheet.setValue(rowIndex, 0, 2573); sheet.setValue(rowIndex, 1, '#,##0 "Over";#,##0 "Under"'); sheet.setValue(rowIndex, 2, 2573); sheet.setFormatter(rowIndex, 2, '#,##0 "Over";#,##0 "Under"'); rowIndex++; sheet.setValue(rowIndex, 0, -2573); sheet.setValue(rowIndex, 1, '#,##0 "Over";#,##0 "Under"'); sheet.setValue(rowIndex, 2, -2573); sheet.setFormatter(rowIndex, 2, '#,##0 "Over";#,##0 "Under"'); rowIndex++; sheet.setValue(rowIndex, 0, 'ExcelRocks'); sheet.setValue(rowIndex, 1, '\\a@'); sheet.setValue(rowIndex, 2, 'ExcelRocks'); sheet.setFormatter(rowIndex, 2, '\\a@'); } function thousandsSeparator(sheet) { var rowIndex = 29; inintHeaderStyle(sheet, rowIndex); sheet.setValue(rowIndex, 0, 'Thousands Separator (comma) and Scaling : ,'); rowIndex++; sheet.setValue(rowIndex, 0, 'Value'); sheet.setValue(rowIndex, 1, 'Format'); sheet.setValue(rowIndex, 2, 'Formatted Result'); rowIndex++; sheet.setValue(rowIndex, 0, 12000); sheet.setValue(rowIndex, 1, '#,###'); sheet.setValue(rowIndex, 2, 12000); sheet.setFormatter(rowIndex, 2, '#,###'); rowIndex++; sheet.setValue(rowIndex, 0, 12000000); sheet.setValue(rowIndex, 1, '#,###'); sheet.setValue(rowIndex, 2, 12000000); sheet.setFormatter(rowIndex, 2, '#,###'); rowIndex++; sheet.setValue(rowIndex, 0, 12000); sheet.setValue(rowIndex, 1, '#,'); sheet.setValue(rowIndex, 2, 12000); sheet.setFormatter(rowIndex, 2, '#,'); rowIndex++; sheet.setValue(rowIndex, 0, 12000000); sheet.setValue(rowIndex, 1, '#,,'); sheet.setValue(rowIndex, 2, 12000000); sheet.setFormatter(rowIndex, 2, '#,,'); rowIndex++; sheet.setValue(rowIndex, 0, 12000); sheet.setValue(rowIndex, 1, '#,\\K'); sheet.setValue(rowIndex, 2, 12000); sheet.setFormatter(rowIndex, 2, '#,\\K'); rowIndex++; sheet.setValue(rowIndex, 0, 12000000); sheet.setValue(rowIndex, 1, '#,###,\\K'); sheet.setValue(rowIndex, 2, 12000000); sheet.setFormatter(rowIndex, 2, '#,###,\\K'); } function initPercentages(sheet) { var rowIndex = 38; inintHeaderStyle(sheet, rowIndex); sheet.setValue(rowIndex, 0, 'Percentages : %'); rowIndex++; sheet.setValue(rowIndex, 0, 'Value'); sheet.setValue(rowIndex, 1, 'Format'); sheet.setValue(rowIndex, 2, 'Formatted Result'); rowIndex++; sheet.setValue(rowIndex, 0, 0.09); sheet.setValue(rowIndex, 1, '0%'); sheet.setValue(rowIndex, 2, 0.09); sheet.setFormatter(rowIndex, 2, '0%'); rowIndex++; sheet.setValue(rowIndex, 0, 0.952); sheet.setValue(rowIndex, 1, '0.0%'); sheet.setValue(rowIndex, 2, 0.952); sheet.setFormatter(rowIndex, 2, '0.0%'); rowIndex++; sheet.setValue(rowIndex, 0, 1); sheet.setValue(rowIndex, 1, '#%'); sheet.setValue(rowIndex, 2, 1); sheet.setFormatter(rowIndex, 2, '#%'); rowIndex++; sheet.setValue(rowIndex, 0, 1); sheet.setValue(rowIndex, 1, '#%%'); sheet.setValue(rowIndex, 2, 1); sheet.setFormatter(rowIndex, 2, '#%%'); } function initDigitalPlaceholder(sheet) { var rowIndex = 45; inintHeaderStyle(sheet, rowIndex); sheet.setValue(rowIndex, 0, 'Digital Placeholder : #, 0 and ?'); rowIndex++; sheet.setValue(rowIndex, 0, 'Value'); sheet.setValue(rowIndex, 1, 'Format'); sheet.setValue(rowIndex, 2, 'Formatted Result'); rowIndex++; sheet.setValue(rowIndex, 0, 123.456); sheet.setValue(rowIndex, 1, '#.####'); sheet.setValue(rowIndex, 2, 123.456); sheet.setFormatter(rowIndex, 2, '#.####'); rowIndex++; sheet.setValue(rowIndex, 0, 123.456); sheet.setValue(rowIndex, 1, '0.0000'); sheet.setValue(rowIndex, 2, 123.456); sheet.setFormatter(rowIndex, 2, '0.0000'); rowIndex++; sheet.setValue(rowIndex, 0, 123.456); sheet.setValue(rowIndex, 1, '?.????'); sheet.setValue(rowIndex, 2, 123.456); sheet.setFormatter(rowIndex, 2, '?.????'); rowIndex++; sheet.setValue(rowIndex, 0, 123.456); sheet.setValue(rowIndex, 1, '####.###'); sheet.setValue(rowIndex, 2, 123.456); sheet.setFormatter(rowIndex, 2, '#####.###'); rowIndex++; sheet.setValue(rowIndex, 0, 123.456); sheet.setValue(rowIndex, 1, '0000.000'); sheet.setValue(rowIndex, 2, 123.456); sheet.setFormatter(rowIndex, 2, '0000.000'); rowIndex++; sheet.setValue(rowIndex, 0, 123.456); sheet.setValue(rowIndex, 1, '????.???'); sheet.setValue(rowIndex, 2, 123.456); sheet.setFormatter(rowIndex, 2, '????.???'); rowIndex++; rowIndex++; sheet.setValue(rowIndex, 0, 1.1); sheet.setValue(rowIndex, 1, '#.???'); sheet.setValue(rowIndex, 2, 1.1); sheet.setFormatter(rowIndex, 2, '#.???'); rowIndex++; sheet.setValue(rowIndex, 0, 11.12); sheet.setValue(rowIndex, 1, '#.???'); sheet.setValue(rowIndex, 2, 11.12); sheet.setFormatter(rowIndex, 2, '#.???'); rowIndex++; sheet.setValue(rowIndex, 0, 111.123); sheet.setValue(rowIndex, 1, '#.???'); sheet.setValue(rowIndex, 2, 111.123); sheet.setFormatter(rowIndex, 2, '#.???'); }
<!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" style="width:100%; height: 100%"></div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }