Custom Item Serialization

SpreadJS supports serializing custom items including: custom cell type, function, formatter, sparklineEx, tag, and row filter. This allows saving and loading workbooks between instances and preserving the custom items.

The corresponding type should provide a typeName field in its toJSON, which tells the type's name related to the window. When deserialized, use getTypeFromString to get the type's name and create an instance, then invoke fromJSON. The following rules can help you serialize and deserialize custom items correctly: Set the full type string to the typeName field (include namespace if present). If a custom type has a circular dependency or you want to reduce JSON size, or you have other advanced requirements, the custom type also needs to override the toJSON and fromJSON methods. If a custom type is in a closure, in other words, you don't want to put the custom type on a window object, you need to override the getTypeFromString method for parsing the type string. For example:
function MyCellType(color) { GC.Spread.Sheets.CellTypes.Base.apply(this, arguments); this.color = color || "orange"; this.typeName = "MyCellType"; } MyCellType.prototype = new GC.Spread.Sheets.CellTypes.Base(); MyCellType.prototype.paint = function (ctx, value, x, y, width, height, style, context) { var MARGIN = 5, plotLeft = x + MARGIN, plotWidth = width - 2 * MARGIN, plotTop = y + MARGIN, plotHeight = height - 2 * MARGIN, halfHeight = plotHeight / 2, halfWidth = plotWidth / 2; ctx.beginPath(); ctx.moveTo(plotLeft, plotTop + halfHeight); ctx.lineTo(plotLeft + halfWidth, plotTop); ctx.lineTo(plotLeft + plotWidth, plotTop + halfHeight); ctx.lineTo(plotLeft + halfWidth, plotTop + plotHeight); ctx.lineTo(plotLeft, plotTop + halfHeight); ctx.strokeStyle = this.color; ctx.stroke(); }; // Define custom tag function MyTag(name, age) { this.name = name; this.age = age; this.typeName = "MyTag"; } MyTag.prototype.toJSON = function () { return { typeName: this.typeName, //necessary name: this.name, age: this.age }; }; MyTag.prototype.fromJSON = function (settings) { if (settings.name !== undefined) { this.name = settings.name; } if (settings.age !== undefined) { this.age = settings.age; } }; var mynamespace = {}; (function () { // Define custom function (with namespace) function MyFunction() { GC.Spread.CalcEngine.Functions.Function.apply(this, ["MyFunction", 0, 0]); this.typeName = "mynamespace.MyFunction"; } MyFunction.prototype = new GC.Spread.CalcEngine.Functions.Function(); MyFunction.prototype.evaluate = function (args) { var now = new Date(); return now.getFullYear() + "/" + (now.getMonth() + 1) + "/" + now.getDate(); }; mynamespace.MyFunction = MyFunction; // Define custom sparklineEx (with namespace) function MySparklineEx() { GC.Spread.Sheets.Sparklines.SparklineEx.apply(this, arguments); this.typeName = "mynamespace.MySparklineEx"; } MySparklineEx.prototype = new GC.Spread.Sheets.Sparklines.SparklineEx(); MySparklineEx.prototype.createFunction = function () { var func = new GC.Spread.CalcEngine.Functions.Function("CIRCLE", 0, 0); func.evaluate = function (args) { return {}; }; return func; }; MySparklineEx.prototype.paint = function (context, value, x, y, width, height) { context.beginPath(); context.arc(x + width / 2, y + height / 2, (Math.min(width, height) - 6) / 2, 0, Math.PI * 2); context.strokeStyle = "orange"; context.stroke(); }; mynamespace.MySparklineEx = MySparklineEx; })(); window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 6 }); var spread2 = new GC.Spread.Sheets.Workbook(document.getElementById("ss1"), { sheetCount: 1 }); initSpread(spread); }; function initSpread(spread) { var tagSheetIndex, rowFilterSheetIndex; var digitMap = [{ label: "Zero", value: 0 }, { label: "One", value: 1 }, { label: "Two", value: 2 }, { label: "Three", value: 3 }, { label: "Four", value: 4 }, { label: "Five", value: 5 }, { label: "Six", value: 6 }, { label: "Seven", value: 7 }, { label: "Eight", value: 8 }, { label: "Nine", value: 9 }, { label: "Ten", value: 10 } ]; // Define custom formatter (private in function) function MyFormatter(format, cultureName) { GC.Spread.Formatter.FormatterBase.apply(this, arguments); this.typeName = "MyFormatter"; } MyFormatter.prototype = new GC.Spread.Formatter.FormatterBase(); MyFormatter.prototype.parse = function (str) { var labels = digitMap.map(function (item) { return item.label; }), index = labels.indexOf(str); if (index === -1) { index = parseInt(str); } return (index < 0 || index > 10) ? NaN : index; }; MyFormatter.prototype.format = function (obj, conditionalForeColor) { var numbers = digitMap.map(function (item) { return item.value }), index = numbers.indexOf(obj); return index === -1 ? "NaN" : digitMap[index].label; }; // Define custom row filter (private in function) function MyRowFilter(range) { GC.Spread.Sheets.Filter.RowFilterBase.apply(this, arguments); this.typeName = "MyRowFilter"; } MyRowFilter.prototype = new GC.Spread.Sheets.Filter.RowFilterBase(); MyRowFilter.prototype.onFilter = function (args) { if (args.action === GC.Spread.Sheets.Filter.FilterActionType.filter) { var sheet = args.sheet, range = args.range, filterdRows = args.filteredRows, filteredOutRows = args.filteredOutRows; if (range.row < 0) { range.row = 0; range.rowCount = sheet.getRowCount(); } if (range.col < 0) { range.col = 0; range.colCount = sheet.getColumnCount(); } for (var i = 0, filterdRowCount = filterdRows.length; i < filterdRowCount; i++) { var rowIndex = filterdRows[i]; sheet.getRange(rowIndex, range.col, 1, range.colCount).backColor("red"); } } }; var oldFun = GC.Spread.Sheets.getTypeFromString; // Private types can not be accessed from window, so override getTypeFromString method. GC.Spread.Sheets.getTypeFromString = function (typeString) { switch (typeString) { case "MyFormatter": return MyFormatter; case "MyRowFilter": return MyRowFilter; default: return oldFun.apply(this, arguments); } }; spread.suspendPaint(); spread.options.allowSheetReorder = false; spread.options.tabStripRatio = 0.9; var sheet = spread.getSheet(0); sheet.name("Cell type"); sheet.setCellType(0, 0, new MyCellType("green")); sheet.setRowHeight(0, 60); sheet = spread.getSheet(1); sheet.name("Function"); sheet.addCustomFunction(new mynamespace.MyFunction()); sheet.setFormula(0, 0, "MyFunction()"); sheet.setColumnWidth(0, 150); sheet = spread.getSheet(2); sheet.name("Formatter"); sheet.setValue(0, 0, 1); sheet.setFormatter(0, 0, new MyFormatter()); sheet.setFormula(0, 1, "A1"); sheet = spread.getSheet(3); sheet.name("SparklineEx"); spread.addSparklineEx(new mynamespace.MySparklineEx()); sheet.setFormula(0, 0, "CIRCLE()"); sheet.setRowHeight(0, 60); tagSheetIndex = 4; sheet = spread.getSheet(tagSheetIndex); sheet.name("Tag"); sheet.tag(new MyTag("Ivy", 24)); sheet.setTag(0, 0, new MyTag("Yang", 25)); sheet.options.allowCellOverflow = true; sheet.setValue(0, 0, "Please check tag serialization result in console"); rowFilterSheetIndex = 5; sheet = spread.getSheet(rowFilterSheetIndex); sheet.name("Row Filter"); for (var r = 0; r < 3; r++) { for (var c = 0; c < 3; c++) { sheet.setValue(r, c, r + c); } } var filter = new MyRowFilter(new GC.Spread.Sheets.Range(0, 0, 3, 3)); sheet.rowFilter(filter); filter.filterButtonVisible(false); var condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.numberCondition, { compareType: GC.Spread.Sheets.ConditionalFormatting.GeneralComparisonOperators.greaterThan, expected: 1 }); sheet.rowFilter().addFilterItem(0, condition); var spread1 = GC.Spread.Sheets.findControl(document.getElementById('ss')); spread1.getSheet(rowFilterSheetIndex).rowFilter().filter(); spread.resumePaint(); document.getElementById("fromtoJsonBtn").addEventListener('click', function () { // ToJson var spread1 = GC.Spread.Sheets.findControl(document.getElementById('ss')); var jsonStr = JSON.stringify(spread1.toJSON()); // FromJson var spread2 = GC.Spread.Sheets.findControl(document.getElementById('ss1')); spread2.fromJSON(JSON.parse(jsonStr)); // Tag verify var sheet = spread1.getSheet(tagSheetIndex); console.log("Source spread:"); console.log("Sheet tag: ", sheet.tag()); console.log("Cell Tag: ", sheet.getTag(0, 0)); var sheet2 = spread2.getSheet(tagSheetIndex); console.log("Target spread:"); console.log("Sheet tag: ", sheet2.tag()); console.log("Cell Tag: ", sheet2.getTag(0, 0)); }); }
<!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 class="sample-spreadsheets" style="overflow: auto;"> <label style="font:bold 10pt arial">Source:</label> <div id="ss" style="height: 260px"></div> <br/> <label style="font:bold 10pt arial">Target:</label> <div id="ss1" style="height: 260px"></div> </div> <div class="options-container"> <div class="option-row"> <label >Click button to serialize the custom item in the source workbook to the target workbook.</label> </div> <div class="option-row"> <input type="button" id="fromtoJsonBtn" value="Json Serialize" title="Serialize source spread to JSON and restore from JSON to target spread."/> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 600px; 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; } label { display: block; margin-bottom: 6px; } input { padding: 4px 6px; } input[type=button] { margin-top: 6px; display: block; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }