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:
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import { useState } from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
export function AppFunc() {
const [spread, setSpread] = useState(null);
const [spread2, setSpread2] = useState(null);
const initSpread = (spread) => {
setSpread(spread);
let tagSheetIndex, rowFilterSheetIndex;
let 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) {
let 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) {
let 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) {
let sheet = args.sheet,
range = args.range,
filterdRows = args.filteredRows;
if (range.row < 0) {
range.row = 0;
range.rowCount = sheet.getRowCount();
}
if (range.col < 0) {
range.col = 0;
range.colCount = sheet.getColumnCount();
}
for (let i = 0, filterdRowCount = filterdRows.length; i < filterdRowCount; i++) {
let rowIndex = filterdRows[i];
sheet.getRange(rowIndex, range.col, 1, range.colCount).backColor("red");
}
}
};
let 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;
let 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 (let r = 0; r < 3; r++) {
for (let c = 0; c < 3; c++) {
sheet.setValue(r, c, r + c);
}
}
let filter = new MyRowFilter(new GC.Spread.Sheets.Range(0, 0, 3, 3));
sheet.rowFilter(filter);
filter.filterButtonVisible(false);
let 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);
spread.getSheet(rowFilterSheetIndex).rowFilter().filter();
spread.resumePaint();
}
const initSpread2 = (spread) => {
setSpread2(spread);
}
const serialization = (e) => {
//ToJson
let spread1 = spread;
let jsonStr = JSON.stringify(spread1.toJSON());
//FromJson
spread2.fromJSON(JSON.parse(jsonStr));
}
return <div class="sample-tutorial">
<div class="sample-spreadsheets-container">
<label style={{ font: 'bold 10pt arial' }}>Source:</label>
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<br />
<label style={{ font: 'bold 10pt arial' }}>Target:</label>
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread2(spread)}>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
</div>
<div className="options-container">
<div className="option-row">
<label>Click button to serialize the custom item in the source workbook to the target workbook.</label>
</div>
<div className="option-row">
<input type="button" id="fromtoJsonBtn" defaultValue="Json Serialize" title="Serialize source spread to JSON and restore from JSON to target spread." onClick={e => serialization(e)} />
</div>
</div>
</div>;
}
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) {
let 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();
};
window.MyCellType = MyCellType;
// 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;
}
};
window.MyTag = MyTag;
let mynamespace = window.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) {
let 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 () {
let 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;
})();
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.spread2 = null;
}
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets-container">
<label style={{ font: 'bold 10pt arial' }}>Source:</label>
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<br />
<label style={{ font: 'bold 10pt arial' }}>Target:</label>
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread2(spread)}>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
</div>
<div className="options-container">
<div className="option-row">
<label>Click button to serialize the custom item in the source workbook to the target workbook.</label>
</div>
<div className="option-row">
<input type="button" id="fromtoJsonBtn" defaultValue="Json Serialize" title="Serialize source spread to JSON and restore from JSON to target spread." onClick={e => this.serialization(e)} />
</div>
</div>
</div>;
}
initSpread(spread) {
this.spread = spread;
let tagSheetIndex, rowFilterSheetIndex;
let 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) {
let 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) {
let 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) {
let sheet = args.sheet,
range = args.range,
filterdRows = args.filteredRows;
if (range.row < 0) {
range.row = 0;
range.rowCount = sheet.getRowCount();
}
if (range.col < 0) {
range.col = 0;
range.colCount = sheet.getColumnCount();
}
for (let i = 0, filterdRowCount = filterdRows.length; i < filterdRowCount; i++) {
let rowIndex = filterdRows[i];
sheet.getRange(rowIndex, range.col, 1, range.colCount).backColor("red");
}
}
};
let 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;
let 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 (let r = 0; r < 3; r++) {
for (let c = 0; c < 3; c++) {
sheet.setValue(r, c, r + c);
}
}
let filter = new MyRowFilter(new GC.Spread.Sheets.Range(0, 0, 3, 3));
sheet.rowFilter(filter);
filter.filterButtonVisible(false);
let 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);
spread.getSheet(rowFilterSheetIndex).rowFilter().filter();
spread.resumePaint();
}
initSpread2(spread) {
this.spread2 = spread;
}
serialization(e) {
//ToJson
let spread1 = this.spread;
let jsonStr = JSON.stringify(spread1.toJSON());
//FromJson
let spread2 = this.spread2;
spread2.fromJSON(JSON.parse(jsonStr));
}
}
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) {
let 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();
};
window.MyCellType = MyCellType;
// 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;
}
};
window.MyTag = MyTag;
let mynamespace = window.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) {
let 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 () {
let 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;
})();
<!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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
input[type="checkbox"] {
margin-left: 20px;
}
.colorLabel {
background-color: #F4F8EB;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets-container {
width: calc(100% - 302px);
height: 600px;
overflow: hidden;
float: left;
}
.sample-spreadsheets {
width: 100%;
height: 260px;
}
.options-container {
float: right;
width: 302px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
label {
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;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
