A standard accounting format looks like this: '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'
Different Format Patterns within the Accounting Format:
Add space ('_'): To create a space that is the width of a character in a number format, include an underscore character ('_'), followed by the character that you want to use.
Repeat characters ('*'): To repeat the next character in the format to fill the column width, include an asterisk (*) in the number format.
Thousands separator and scale (','): 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.
Percentages ('%'): 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.
Digital placeholder ('?'): 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.
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/@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$/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;
}
