Custom Formatter

You can use FormatterBase to help create a custom formatter with specified rules to format and color cell text. This essentially builds on top of the formatters that are already in SpreadJS: the rules and colors allow further customization, similar to conditional formatting.

Create a class base on FormatterBase. For example: Provide format(obj, formattedData) to define the rule for format and color for cell text, where: obj: The raw cell value. formattedData.conditionalForeColor: Helps color text (optional); set its value to a color string to apply the rule. The format method returns a formatted string. You can color text based on a value range at the same time. For example: Provide parse(str) to return a value parsed from the specified string.
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 ($.isNumeric(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 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" 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 10<br /> - Blue if the date is after 1990<br /> Otherwise, the background 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; }