一个标准的会计专用格式类似: _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
会计专用格式的不同模式:
添加空格('_'): 要创建一个以数字格式显示字符宽度的空格，包括一个下划线字符(_)，后面是您想要使用的字符。
重复字符('*'): 要在格式中重复下一个字符以填充列的宽度，请在数字格式中包含星号(*)。
千分符与数值缩放(','): 在一个数字中显示数千分隔符。如果格式包含一个由数字符号(#)或0所包围的逗号，则以逗号分隔。数字占位符后面的逗号将数字缩放到1000。例如，如果格式是。0，在单元格中输入12,200,000，显示的是12.2000。
百分数('%'): 显示数字为100的百分数，例如，显示0.08为8%或2.8为280%，包括数字格式的百分号(%)。例如，要显示(2,2)的百分比为100，您还可以将该数字显示为10000的百分比。
数字占位符('?'): 在数字占位符中添加数字占位符，这个数字占位符?遵循与0相同的规则。然而在小数点的两边增加了一个零的空间。
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 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-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/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" 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;
}
