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.
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 3 });
var spread2 = new GC.Spread.Sheets.Workbook(_getElementById('ss1'), { sheetCount: 1 });
initSpread(spread);
};
function initSpread(spread) {
var sheet = spread.getSheet(0);
fillStyle(sheet);
var pivotSourceSheet = spread.getSheet(1);
pivotSourceSheet.name("PivotSourceData");
var pivotSourceTableName = fillPivotSourceData(pivotSourceSheet);
var pivotTableSheet = spread.getSheet(2);
pivotTableSheet.name("PivotTable");
fillPivotTable(pivotTableSheet, pivotSourceTableName);
_getElementById('fromtoJsonBtn').addEventListener('click', function() {
var jsonOptions = {
ignoreFormula: !!_getElementById('import_noFormula').checked,
ignoreStyle: !!_getElementById('import_noStyle').checked,
frozenColumnsAsRowHeaders: !!_getElementById('import_rowHeaders').checked,
frozenRowsAsColumnHeaders: !!_getElementById('import_columnHeaders').checked,
doNotRecalculateAfterLoad: !!_getElementById('import_donotrecalculateafterload').checked
};
var serializationOption = {
ignoreFormula: !!_getElementById('noFormula').checked,
ignoreStyle: !!_getElementById('noStyle').checked,
rowHeadersAsFrozenColumns: !!_getElementById('SaveCustomRowHeaders').checked,
columnHeadersAsFrozenRows: !!_getElementById('SaveCustomColumnHeaders').checked
};
//ToJson
var spread1 = GC.Spread.Sheets.findControl(document.getElementById('ss'));
var jsonStr = JSON.stringify(spread1.toJSON(serializationOption));
//FromJson
var spread2 = GC.Spread.Sheets.findControl(document.getElementById('ss1'));;
spread2.fromJSON(JSON.parse(jsonStr), jsonOptions);
});
}
function fillStyle(sheet) {
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 });
fillSampleData(sheet, new spreadNS.Range(4, 4, 6, 6));
fillSampleData(sheet, new spreadNS.Range(11, 4, 6, 6));
sheet.resumePaint();
}
function fillSampleData(sheet, 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));
}
}
}
function fillPivotSourceData(sheet) {
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();
}
function fillPivotTable (sheet, tableName) {
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();
}
function _getElementById(id){
return document.getElementById(id);
}
<!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$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/pivot-data.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-container">
<label class="spread-label">ToJson:</label>
<div id="ss" class="sample-spreadsheets"></div>
<label class="spread-label">FromJson:</label>
<div id="ss1" class="sample-spreadsheets"></div>
</div>
<aside class="options-container" aria-label="JSON serialization settings">
<div class="panel-header">
<h1>JSON Serialization</h1>
<p class="panel-description">Serialize the first workbook to JSON and load it into the second workbook.</p>
</div>
<div class="panel-body">
<div class="option-row">
<input type="button" value="Json Serialize" id="fromtoJsonBtn"/>
</div>
<div class="container">
<div class="row">
<div class="col-xs-12">
<div class="options-title">FromJSON Options</div>
<div class="check-list">
<div class="check-row check-row-inline">
<input type="checkbox" id="import_noFormula"/>
<label style="text-align: left" for="import_noFormula">Ignore Formula</label>
<input type="checkbox" id="import_noStyle"/>
<label style="text-align: left" for="import_noStyle">Ignore Style</label>
</div>
<div class="check-row">
<input type="checkbox" id="import_rowHeaders"/>
<label style="text-align: left" for="import_rowHeaders">Treat the frozen columns as row headers</label>
</div>
<div class="check-row">
<input type="checkbox" id="import_columnHeaders"/>
<label style="text-align: left" for="import_columnHeaders">Treat the frozen rows as column headers</label>
</div>
<div class="check-row">
<input type="checkbox" id="import_donotrecalculateafterload"/>
<label style="text-align: left" for="import_donotrecalculateafterload">Avoid recalculation after load</label>
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-xs-12">
<div class="options-title">ToJSON Options</div>
<div class="check-list">
<div class="check-row check-row-inline">
<input type="checkbox" id="noFormula"/>
<label style="text-align: left" for="noFormula">Ignore Formula</label>
<input type="checkbox" id="noStyle"/>
<label style="text-align: left" for="noStyle">Ignore Style</label>
</div>
<div class="check-row">
<input type="checkbox" id="SaveCustomRowHeaders"/>
<label style="text-align: left" for="SaveCustomRowHeaders">Treat the row headers as frozen columns</label>
</div>
<div class="check-row">
<input type="checkbox" id="SaveCustomColumnHeaders"/>
<label style="text-align: left" for="SaveCustomColumnHeaders">Treat the column headers as frozen rows</label>
</div>
</div>
</div>
</div>
</div>
</div>
</aside>
</div>
</body>
</html>
html,
body {
height: 100%;
margin: 0;
}
body {
position: absolute;
inset: 0;
color: #1f2933;
background: #f7f8fa;
}
.sample-tutorial {
display: flex;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets-container {
display: flex;
flex: 1 1 auto;
flex-direction: column;
min-width: 0;
height: 100%;
padding: 10px;
box-sizing: border-box;
overflow: hidden;
}
.spread-label {
flex: 0 0 auto;
margin: 0 0 6px;
color: #111827;
font: 600 13px Arial, sans-serif;
}
.sample-spreadsheets {
flex: 1 1 0;
min-height: 0;
margin-bottom: 10px;
overflow: hidden;
}
.sample-spreadsheets:last-child {
margin-bottom: 0;
}
.options-container {
flex: 0 0 320px;
width: 320px;
height: 100%;
padding: 18px 16px;
box-sizing: border-box;
overflow: auto;
overflow-x: hidden;
background: #fff;
border-left: 1px solid #e5e7eb;
}
.panel-header {
padding-bottom: 14px;
margin-bottom: 16px;
border-bottom: 1px solid #edf0f3;
}
.panel-header h1 {
margin: 0 0 6px;
font-size: 18px;
font-weight: 600;
line-height: 1.25;
}
.panel-description {
margin: 0;
color: #5f6b7a;
font-size: 12px;
line-height: 1.4;
}
.panel-body,
.container,
.col-xs-12 {
display: grid;
gap: 14px;
min-width: 0;
}
.row {
padding-top: 14px;
border-top: 1px solid #edf0f3;
}
.row:first-child {
padding-top: 0;
border-top: 0;
}
.option-row {
margin: 0;
}
.options-title {
color: #111827;
font-size: 13px;
font-weight: 600;
line-height: 1.35;
}
.check-list {
display: grid;
gap: 9px;
}
.check-row {
display: grid;
grid-template-columns: 16px minmax(0, 1fr);
gap: 7px;
align-items: start;
}
.check-row-inline {
grid-template-columns: 16px minmax(0, 1fr) 16px minmax(0, 1fr);
}
.options-container input[type="checkbox"] {
width: 14px;
height: 14px;
margin: 2px 0 0;
}
.options-container label {
margin: 0;
color: #4b5563;
font-size: 12px;
line-height: 1.35;
}
.options-container input[type="button"] {
display: inline-flex;
align-items: center;
justify-content: center;
width: 100%;
height: 32px;
padding: 0 12px;
color: #1d4ed8;
background: #fff;
border: 1px solid #9db7f5;
border-radius: 6px;
box-sizing: border-box;
font-size: 13px;
font-weight: 500;
line-height: 1;
white-space: nowrap;
cursor: pointer;
}
.options-container input[type="button"]:hover {
color: #123f9d;
border-color: #5f86e8;
background: #f7faff;
}
@media (max-width: 760px) {
.sample-tutorial {
flex-direction: column;
}
.sample-spreadsheets-container {
flex: 1 1 55%;
height: 55%;
}
.options-container {
flex: 1 1 45%;
width: 100%;
height: 45%;
border-top: 1px solid #e5e7eb;
border-left: 0;
}
}
