JSON Serialization

SpreadJS supports JSON serialization and de-serialization. You can save a spreadsheet to JSON and open the JSON object to initialize Spread. This is useful for when you want an easy way to save and load entire Spread or Sheet instances to/from a database.

<p>You can set a few options — like the ones listed in code below — when you serialize the JSON object:</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> serializationOption = { <span class="hljs-attr">ignoreStyle</span>: <span class="hljs-literal">true</span>, <span class="hljs-comment">// ignore styles when converting workbook to json, default value is false</span> <span class="hljs-attr">ignoreFormula</span>: <span class="hljs-literal">true</span>, <span class="hljs-comment">// ignore formulas when converting workbook to json, default value is false</span> <span class="hljs-attr">rowHeadersAsFrozenColumns</span>: <span class="hljs-literal">true</span>, <span class="hljs-comment">// treat row headers as frozen columns when converting workbook to json, default value is false</span> <span class="hljs-attr">columnHeadersAsFrozenRows</span>: <span class="hljs-literal">true</span> <span class="hljs-comment">// treat column headers as frozen rows when converting workbook to json, default value is false</span> } </code></pre> <p>Then, you can use the <strong>JSON.stringify</strong> function to serialize the JSON object, which is returned from <strong>toJSON</strong> with the following options:</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> spread1 = GC.Spread.Sheets.findControl(<span class="hljs-built_in">document</span>.getElementById(<span class="hljs-string">'ss'</span>)); <span class="hljs-keyword">var</span> jsonString = <span class="hljs-built_in">JSON</span>.stringify(spread1.toJSON(serializationOption)); </code></pre> <p>You can also set a few options — like the ones in code below — when deserializing the JSON string.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> jsonOptions = { <span class="hljs-attr">ignoreFormula</span>: <span class="hljs-literal">true</span>, <span class="hljs-comment">// ignore styles when converting json to workbook, default value is false</span> <span class="hljs-attr">ignoreStyle</span>: <span class="hljs-literal">true</span>, <span class="hljs-comment">// ignore the formulas when converting json to workbook, default value is false</span> <span class="hljs-attr">frozenColumnsAsRowHeaders</span>: <span class="hljs-literal">true</span>, <span class="hljs-comment">// treat the frozen columns as row headers when converting json to workbook, default value is false</span> <span class="hljs-attr">frozenRowsAsColumnHeaders</span>: <span class="hljs-literal">true</span>, <span class="hljs-comment">// treat the frozen rows as column headers when converting json to workbook, default value is false</span> <span class="hljs-attr">doNotRecalculateAfterLoad</span>: <span class="hljs-literal">true</span> <span class="hljs-comment">// disable recalculation after loading the json, default value is false</span> } </code></pre> <p>Use the <strong>JSON.parse</strong> function to deserialize the JSON string, and use the <strong>fromJSON</strong> function to initialize the Spread object with the options listed below to create a new spreadsheet that matches <strong>spread1</strong>.</p> <pre><code class="hljs js language-js"> <span class="hljs-keyword">var</span> spread2 = GC.Spread.Sheets.findControl(<span class="hljs-built_in">document</span>.getElementById(<span class="hljs-string">'ss1'</span>)); spread2.fromJSON(<span class="hljs-built_in">JSON</span>.parse(jsonStr), jsonOptions); </code></pre>
window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 1 }); var spread2 = new GC.Spread.Sheets.Workbook(_getElementById('ss1'), { sheetCount: 1 }); initSpread(spread); }; function initSpread(spread) { var sheet = spread.getSheet(0); fillStyle(sheet); _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('#CCCCFF'); sheet.getRange(4, 1, 15, 3).backColor('#CCFFCC'); 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 _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/@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 class="sample-spreadsheets" style="overflow: auto;"> <label style="font:bold 10pt arial">ToJson:</label> <div id="ss" style="height: 260px"></div> <br/> <label style="font:bold 10pt arial">FromJson:</label> <div id="ss1" style="height: 260px"></div> </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"/> </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"/> <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 style="margin-top: 10px"> <input type="checkbox" id="import_rowHeaders"/> <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"/> <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"/> <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"/> <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 style="margin-top: 10px"> <input type="checkbox" id="SaveCustomRowHeaders"/> <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"/> <label style="text-align: left" for="SaveCustomColumnHeaders">Treat the column headers as frozen rows</label> </div> </div> </div> </div> </div> </div> </div> </div> </body> </html>
input[type="checkbox"] { margin-left: 20px; } .colorLabel { background-color: lavender; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 302px); height: 100%; overflow: hidden; float: left; } .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; }