You can set a few options — like the ones listed in code below — when you serialize the JSON object:
Then, you can use the JSON.stringify function to serialize the JSON object, which is returned from toJSON with the following options:
You can also set a few options — like the ones in code below — when deserializing the JSON string.
Use the JSON.parse function to deserialize the JSON string, and use the fromJSON function to initialize the Spread object with the options listed above to create a new spreadsheet that matches spread1.
import { Component, NgModule, enableProdMode } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { SpreadSheetsModule } from '@mescius/spread-sheets-angular';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-pivot-addon";
import './styles.css';
@Component({
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
spread: GC.Spread.Sheets.Workbook;
spread2: GC.Spread.Sheets.Workbook;
import_noFormula = false;
import_noStyle = false;
import_rowHeaders = false;
import_columnHeaders = false;
import_donotrecalculateafterload = false;
noFormula = false;
noStyle = false;
SaveCustomRowHeaders = false;
SaveCustomColumnHeaders = false;
hostStyle = {
width: '100%',
height: '260px',
overflow: 'hidden',
float: 'left'
};
constructor() {
}
initSpread($event: any) {
this.spread = $event.spread;
let spread = this.spread;
let sheet = spread.getSheet(0);
var spreadNS = GC.Spread.Sheets;
sheet.suspendPaint();
sheet.frozenRowCount(4);
sheet.frozenColumnCount(4);
sheet.addSpan(1, 1, 1, 3);
sheet.setValue(1, 1, 'Store');
sheet.addSpan(1, 4, 1, 7);
sheet.setValue(1, 4, 'Goods');
sheet.addSpan(2, 1, 1, 2);
sheet.setValue(2, 1, 'Area');
sheet.addSpan(2, 3, 2, 1);
sheet.setValue(2, 3, 'ID');
sheet.addSpan(2, 4, 1, 2);
sheet.setValue(2, 4, 'Fruits');
sheet.addSpan(2, 6, 1, 2);
sheet.setValue(2, 6, 'Vegetables');
sheet.addSpan(2, 8, 1, 2);
sheet.setValue(2, 8, 'Foods');
sheet.addSpan(2, 10, 2, 1);
sheet.setValue(2, 10, 'Total');
sheet.setValue(3, 1, 'State');
sheet.setValue(3, 2, 'City');
sheet.setValue(3, 4, 'Grape');
sheet.setValue(3, 5, 'Apple');
sheet.setValue(3, 6, 'Potato');
sheet.setValue(3, 7, 'Tomato');
sheet.setValue(3, 8, 'SandWich');
sheet.setValue(3, 9, 'Hamburger');
sheet.addSpan(4, 1, 7, 1);
sheet.addSpan(4, 2, 3, 1);
sheet.addSpan(7, 2, 3, 1);
sheet.addSpan(10, 2, 1, 2);
sheet.setValue(10, 2, 'Sub Total:');
sheet.addSpan(11, 1, 7, 1);
sheet.addSpan(11, 2, 3, 1);
sheet.addSpan(14, 2, 3, 1);
sheet.addSpan(17, 2, 1, 2);
sheet.setValue(17, 2, 'Sub Total:');
sheet.addSpan(18, 1, 1, 3);
sheet.setValue(18, 1, 'Total:');
sheet.setValue(4, 1, 'NC');
sheet.setValue(4, 2, 'Raleigh');
sheet.setValue(7, 2, 'Charlotte');
sheet.setValue(4, 3, '001');
sheet.setValue(5, 3, '002');
sheet.setValue(6, 3, '003');
sheet.setValue(7, 3, '004');
sheet.setValue(8, 3, '005');
sheet.setValue(9, 3, '006');
sheet.setValue(11, 1, 'PA');
sheet.setValue(11, 2, 'Philadelphia');
sheet.setValue(14, 2, 'Pittsburgh');
sheet.setValue(11, 3, '007');
sheet.setValue(12, 3, '008');
sheet.setValue(13, 3, '009');
sheet.setValue(14, 3, '010');
sheet.setValue(15, 3, '011');
sheet.setValue(16, 3, '012');
sheet.setFormula(10, 4, '=SUM(E5:E10)');
sheet.setFormula(10, 5, '=SUM(F5:F10)');
sheet.setFormula(10, 6, '=SUM(G5:G10)');
sheet.setFormula(10, 7, '=SUM(H5:H10)');
sheet.setFormula(10, 8, '=SUM(I5:I10)');
sheet.setFormula(10, 9, '=SUM(J5:J10)');
sheet.setFormula(17, 4, '=SUM(E12:E17)');
sheet.setFormula(17, 5, '=SUM(F12:F17)');
sheet.setFormula(17, 6, '=SUM(G12:G17)');
sheet.setFormula(17, 7, '=SUM(H12:H17)');
sheet.setFormula(17, 8, '=SUM(I12:I17)');
sheet.setFormula(17, 9, '=SUM(J12:J17)');
for (var i = 0; i < 14; i++) {
sheet.setFormula(4 + i, 10, '=SUM(E' + (5 + i).toString() + ':J' + (5 + i).toString() + ')');
}
sheet.setFormula(18, 4, '=E11+E18');
sheet.setFormula(18, 5, '=F11+F18');
sheet.setFormula(18, 6, '=G11+G18');
sheet.setFormula(18, 7, '=H11+H18');
sheet.setFormula(18, 8, '=I11+I18');
sheet.setFormula(18, 9, '=J11+J18');
sheet.setFormula(18, 10, '=K11+K18');
sheet.getRange(1, 1, 3, 10).backColor('#F2F2F2');
sheet.getRange(4, 1, 15, 3).backColor('#CFCFCF');
sheet.getRange(1, 1, 3, 10).hAlign(spreadNS.HorizontalAlign.center);
sheet.getRange(1, 1, 18, 10).setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.thin), { all: true });
sheet.getRange(4, 4, 3, 6).setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.dotted), { inside: true });
sheet.getRange(7, 4, 3, 6).setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.dotted), { inside: true });
sheet.getRange(11, 4, 3, 6).setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.dotted), { inside: true });
sheet.getRange(14, 4, 3, 6).setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.dotted), { inside: true });
this.fillSampleData(sheet, new spreadNS.Range(4, 4, 6, 6));
this.fillSampleData(sheet, new spreadNS.Range(11, 4, 6, 6));
sheet.resumePaint();
let pivotSourceSheet = spread.getSheet(1);
pivotSourceSheet.name("PivotSourceData");
let pivotSourceTableName = this.fillPivotSourceData(pivotSourceSheet);
let pivotTableSheet = spread.getSheet(2);
pivotTableSheet.name("PivotTable");
this.fillPivotTable(pivotTableSheet, pivotSourceTableName);
}
fillSampleData(sheet: GC.Spread.Sheets.Worksheet, range: GC.Spread.Sheets.Range) {
for (var i = 0; i < range.rowCount; i++) {
for (var j = 0; j < range.colCount; j++) {
sheet.setValue(range.row + i, range.col + j, Math.ceil(Math.random() * 300));
}
}
}
fillPivotSourceData(sheet: GC.Spread.Sheets.Worksheet) {
sheet.setRowCount(117);
sheet.getCell(-1, 0).formatter("YYYY-mm-DD");
sheet.getRange(-1,4,0,2).formatter("$ #,##0");
let table = sheet.tables.add('table', 0, 0, 117, 6);
for(let i=2;i<=117;i++)
{
sheet.setFormula(i-1,5,'=D'+i+'*E'+i)
}
table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
sheet.setArray(0, 0, pivotSales);
return table.name();
}
fillPivotTable (sheet: GC.Spread.Sheets.Worksheet, tableName: string) {
sheet.setRowCount(1000);
let pivotTableOptions = {bandRows:true,bandColumns:true};
let pivotTable = sheet.pivotTables.add("PivotTable", tableName, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium1, pivotTableOptions);
pivotTable.suspendLayout();
pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
pivotTable.group(groupInfo);
pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
let style = new GC.Spread.Sheets.Style();
style.formatter = "$ #,##0";
pivotTable.setStyle({dataOnly: true}, style);
pivotTable.resumeLayout();
pivotTable.autoFitColumn();
}
initSpread2($event: any) {
this.spread2 = $event.spread;
}
changeImport_noFormula(e: any) {
this.import_noFormula = e.target.checked;
}
changeImport_noStyle(e: any) {
this.import_noStyle = e.target.checked;
}
changeImport_rowHeaders(e: any) {
this.import_rowHeaders = e.target.checked;
}
changeImport_columnHeaders(e: any) {
this.import_columnHeaders = e.target.checked;
}
changeImport_donotrecalculateafterload(e: any) {
this.import_donotrecalculateafterload = e.target.checked;
}
changeNoFormula(e: any) {
this.noFormula = e.target.checked;
}
changeNoStyle(e: any) {
this.noStyle = e.target.checked;
}
changeSaveCustomRowHeaders(e: any) {
this.SaveCustomRowHeaders = e.target.checked;
}
changeSaveCustomColumnHeaders(e: any) {
this.SaveCustomColumnHeaders = e.target.checked;
}
serialization(e: any) {
var jsonOptions = {
ignoreFormula: this.import_noFormula,
ignoreStyle: this.import_noStyle,
frozenColumnsAsRowHeaders: this.import_rowHeaders,
frozenRowsAsColumnHeaders: this.import_columnHeaders,
doNotRecalculateAfterLoad: this.import_donotrecalculateafterload
};
var serializationOption = {
ignoreFormula: this.noFormula,
ignoreStyle: this.noStyle,
rowHeadersAsFrozenColumns: this.SaveCustomRowHeaders,
columnHeadersAsFrozenRows: this.SaveCustomColumnHeaders
};
//ToJson
var spread1 = this.spread;
var jsonStr = JSON.stringify(spread1.toJSON(serializationOption));
//FromJson
var spread2 = this.spread2;
spread2.fromJSON(JSON.parse(jsonStr), jsonOptions);
}
}
@NgModule({
imports: [BrowserModule, SpreadSheetsModule],
declarations: [AppComponent],
exports: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule { }
enableProdMode();
// Bootstrap application with hash style navigation and global services.
platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<!-- Polyfills -->
<script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/en/angular/node_modules/zone.js/fesm2015/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script>
<script src="systemjs.config.js"></script>
<script>
// workaround to load 'rxjs/operators' from the rxjs bundle
System.import('rxjs').then(function (m) {
System.import('@angular/compiler');
System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators));
System.import('$DEMOROOT$/en/lib/angular/license.ts');
System.import('./src/app.component');
});
</script>
</head>
<body>
<app-component></app-component>
</body>
</html>
<div class="sample-tutorial">
<div class="sample-spreadsheets-container">
<label style="font:bold 10pt arial">ToJson:</label>
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)">
<gc-worksheet></gc-worksheet>
<gc-worksheet></gc-worksheet>
<gc-worksheet></gc-worksheet>
</gc-spread-sheets>
<br />
<label style="font:bold 10pt arial">FromJson:</label>
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread2($event)">
<gc-worksheet></gc-worksheet>
</gc-spread-sheets>
</div>
<div class="options-container">
<div style="width:290px">
<label>This serializes the first Spread instance to a JSON object, and then deserializes that object into
the second Spread instance.</label>
<div class="option-row">
<input type="button" value="Json Serialize" id="fromtoJsonBtn" (click)="serialization($event)" />
</div>
<div>
<div class="container">
<div class="row" style="margin-top: 10px">
<div class="col-xs-12">
<label>FromJSON Options:</label>
<div style="margin-top: 10px">
<input type="checkbox" id="import_noFormula"
(change)="changeImport_noFormula($event)" />
<label style="text-align: left" for="import_noFormula">Ignore Formula</label>
<input type="checkbox" id="import_noStyle" (change)="changeImport_noStyle($event)" />
<label style="text-align: left" for="import_noStyle">Ignore Style</label>
</div>
<div style="margin-top: 10px">
<input type="checkbox" id="import_rowHeaders"
(change)="changeImport_rowHeaders($event)" />
<label style="text-align: left" for="import_rowHeaders">Treat the frozen columns as row
headers</label>
</div>
<div style="margin-top: 10px">
<input type="checkbox" id="import_columnHeaders"
(change)="changeImport_columnHeaders($event)" />
<label style="text-align: left" for="import_columnHeaders">Treat the frozen rows as
column headers</label>
</div>
<div style="margin-top: 10px">
<input type="checkbox" id="import_donotrecalculateafterload"
(change)="changeImport_donotrecalculateafterload($event)" />
<label style="text-align: left" for="import_donotrecalculateafterload">Avoid
recalculation after load</label>
</div>
</div>
</div>
<div class="row" style="margin-top: 20px">
<div class="col-xs-12">
<label>ToJSON Options:</label>
<div style="margin-top: 10px">
<input type="checkbox" id="noFormula" (change)="changeNoFormula($event)" />
<label style="text-align: left" for="noFormula">Ignore Formula</label>
<input type="checkbox" id="noStyle" (change)="changeNoStyle($event)" />
<label style="text-align: left" for="noStyle">Ignore Style</label>
</div>
<div style="margin-top: 10px">
<input type="checkbox" id="SaveCustomRowHeaders"
(change)="changeSaveCustomRowHeaders($event)" />
<label style="text-align: left" for="SaveCustomRowHeaders">Treat the row headers as
frozen columns</label>
</div>
<div style="margin-top: 10px">
<input type="checkbox" id="SaveCustomColumnHeaders"
(change)="changeSaveCustomColumnHeaders($event)" />
<label style="text-align: left" for="SaveCustomColumnHeaders">Treat the column headers
as frozen rows</label>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
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;
}
(function (global) {
System.config({
transpiler: 'ts',
typescriptOptions: {
tsconfig: true
},
meta: {
'typescript': {
"exports": "ts"
},
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'core-js': 'npm:core-js/client/shim.min.js',
'zone': 'npm:zone.js/fesm2015/zone.min.js',
'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js',
'@angular/core': 'npm:@angular/core/fesm2022',
'@angular/common': 'npm:@angular/common/fesm2022/common.mjs',
'@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs',
'@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs',
'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs',
'@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs',
'@angular/router': 'npm:@angular/router/fesm2022/router.mjs',
'@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs',
'jszip': 'npm:jszip/dist/jszip.min.js',
'typescript': 'npm:typescript/lib/typescript.js',
'ts': './plugin.js',
'tslib':'npm:tslib/tslib.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',
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/index.js',
'@mescius/spread-sheets-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'ts'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
},
"node_modules/@angular": {
defaultExtension: 'mjs'
},
"@mescius/spread-sheets-angular": {
defaultExtension: 'mjs'
},
'@angular/core': {
defaultExtension: 'mjs',
main: 'core.mjs'
}
}
});
})(this);
