概述
本 Demo 全面展示了 SpreadJS 的会计格式化功能，包括标准会计格式、重复字符、空格占位符、文本标签、千分符、百分比和数字占位符等多种格式化模式。通过实际示例演示了如何使用格式化字符控制数字、文本和符号的显示效果。
实现思路
初始化工作表，设置列宽和对齐方式
分别调用 7 个函数，演示不同的格式化特性：标准会计格式、重复字符、空格占位符、文本标签、千分符、百分比、数字占位符
每个函数中设置表头、原始值、格式字符串和格式化后的结果
使用 setFormatter 方法为单元格应用格式化字符串
代码解析
设置单元格格式
使用 setFormatter 方法为单元格设置格式化字符串：
row、col：单元格位置
formatterString：格式化字符串，支持多种格式化字符
返回值：无
标准会计格式
标准会计格式使用分号分隔四种情况：正数、负数、零值、文本。下划线 _ 创建与后续字符等宽的空格，星号 * 重复后续字符填充列宽，货币符号 $ 对齐显示。
重复字符
星号 * 重复后续字符以填充列宽，常用于创建分隔线或对齐显示。
添加空格
下划线 _ 后跟一个字符，创建与该字符等宽的空格，用于对齐不同格式的数字。
添加文本标签
双引号 "" 包围的内容会原样显示，反斜杠 \ 转义特殊字符。
千分符与数值缩放
逗号 , 在数字占位符之间时显示千分符，在占位符末尾时缩放数值（每个逗号缩放 1000 倍）。
百分比格式
百分号 % 将数值乘以 100 并显示为百分比，多个 % 可累积放大。
数字占位符
#：可选数字占位符，不显示无意义的零
0：固定数字占位符，不足位数补零
?：为零值保留空间，便于对齐不同位数的数字
运行效果
表格分为 7 个部分，每个部分展示一种格式化特性
每个部分包含原始值、格式字符串和格式化后的结果三列
标准会计格式：正数显示货币符号和小数，负数用括号包围，零值显示为破折号
重复字符：文本和货币符号后自动填充字符以对齐列宽
添加空格：不同格式的数字通过等宽空格实现对齐
文本标签：数字后显示单位，文本前添加前缀
千分符与缩放：数值显示千分符或按比例缩放显示
百分比：数值自动转换为百分比格式
数字占位符：演示三种占位符对数字显示的不同影响，特别是小数位的对齐效果
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;
}
