Spread JSON 导入/导出

SpreadJS 支持 JSON 格式的序列化与反序列化。你可以将当前 spread 保存为 JSON 格式的数据进行存储, 也可以导入这些 JSON 格式数据到 spread 中来进行场景恢复。当你想要把整个Spread或者Sheet存储在数据库中或者从数据库中恢复,这个功能就很实用。

有一些选项会控制序列化的结果,例如: 使用 JSON.stringify 把 spread toJSON 的返回的对象序列化成JSON字符串。 还有一些选项会控制反序列化的结果,例如: 使用 JSON.parse 把JSON字符串反序列化成对象, 然后再用 fromJSON 来初始化 spread 对象。这样你就得到了一个和原来的 spread1 对象一样的新的 spread2 对象。例如:
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 name="spreadjs culture" content="zh-cn" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/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" 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: #F4F8EB; } .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; }