自定义格式

你可以调用 FormatterBase 来帮助创建一个含有特殊规则自定义格式用于格式化单元格和为单元格文本设置颜色。

创建一个基于 FormatterBase 的类。例如: 提供 format(obj, formattedData)来定义格式规则和单元格文本颜色。 obj: 单元格的原始值。 formattedData.conditionalForeColor: 帮助性文本颜色(可选参数)。将值设置为一个颜色字符串来应用此规则。 format 方法返回一个格式的字符串。你可以基于值的范围来为单元格文本设置颜色。例如: parse(str) 的返回一个从特殊字符串解析的值。
var spreadNS = GC.Spread.Sheets; function CustomDateFormat() { } CustomDateFormat.prototype = new GC.Spread.Formatter.FormatterBase(); CustomDateFormat.prototype.format = function (obj, formattedData) { var date = new Date(obj); if (date instanceof Date && isFinite(date)) { if (date.getFullYear() > 1990 && formattedData) { formattedData.conditionalForeColor = "blue"; } else if (formattedData) { formattedData.conditionalForeColor = "red"; } return (date.getMonth() + 1) + "/" + date.getDate() + "/" + date.getFullYear(); } else { return obj != null ? obj.toString() : ""; } }; CustomDateFormat.prototype.parse = function (str) { return new GC.Spread.Formatter.GeneralFormatter().parse(str); }; function CustomNumberFormat() { } CustomNumberFormat.prototype = new GC.Spread.Formatter.FormatterBase(); CustomNumberFormat.prototype.format = function (obj, formattedData) { if (typeof obj === "number") { return formatNumber(obj, formattedData); } else if (typeof obj === "string") { if (Number.isFinite(+obj)) { return formatNumber(parseFloat(obj), formattedData); } } return obj ? obj.toString() : ""; }; function formatNumber(value, formattedData) { if (isFinite(value) && formattedData) { if (value >= 10) { formattedData.conditionalForeColor = "green"; } else if (value > 0) { formattedData.conditionalForeColor = "gold"; } else { formattedData.conditionalForeColor = "blue"; } } else if (formattedData) { formattedData.conditionalForeColor = "red"; } return value.toString(); } CustomNumberFormat.prototype.parse = function (str) { return new GC.Spread.Formatter.GeneralFormatter().parse(str); }; window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 1}); initSpread(spread); }; function initSpread(spread) { var sheet = spread.getSheet(0); spread.suspendPaint(); sheet.name("Custom Formatter"); initData(sheet); spread.resumePaint(); }; function initData(sheet) { sheet.setArray(0, 0, [ [123, new Date(2002, 2, 6)], ["-18", "1988/3/2"], ["0", "Oct 9, 2011"], [4, 4], [NaN, NaN], [Infinity, Infinity] ]); for (var c = 0; c < 2; c++) { sheet.setColumnWidth(c, 120); } sheet.getRange(0, 0, 6, 2).formatter(new CustomNumberFormat()); sheet.getRange(0, 1, 6, 2).formatter(new CustomDateFormat()); sheet.setText(0, 0, "Custom Number", spreadNS.SheetArea.colHeader); sheet.setText(0, 1, "Custom Date", spreadNS.SheetArea.colHeader); };
<!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" class="sample-spreadsheets"></div> <div class="options-container"> <div class="option-row" style="padding:2px 10px"> <p> Change the values. The cells have custom formatters set to display certain colors according to specific conditions:<br /> - Gold if the number is positive<br /> - Green if the number is bigger than 9<br /> - Blue if the date is after 1990<br /> Otherwise, the color of the cell is red. </p> </div> </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; }