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 below to create a new spreadsheet that matches spread1.
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 "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-pivot-addon";
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 [import_noFormula, setImport_noFormula] = useState(false);
const [import_noStyle, setImport_noStyle] = useState(false);
const [import_rowHeaders, setImport_rowHeaders] = useState(false);
const [import_columnHeaders, setImport_columnHeaders] = useState(false);
const [import_donotrecalculateafterload, setImport_donotrecalculateafterload] = useState(false);
const [noFormula, setNoFormula] = useState(false);
const [noStyle, setNoStyle] = useState(false);
const [SaveCustomRowHeaders, setSaveCustomRowHeaders] = useState(false);
const [SaveCustomColumnHeaders, setSaveCustomColumnHeaders] = useState(false);
const initSpread = (spread) => {
setSpread(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 });
fillSampleData(sheet, new spreadNS.Range(4, 4, 6, 6));
fillSampleData(sheet, new spreadNS.Range(11, 4, 6, 6));
sheet.resumePaint();
let pivotSourceSheet = spread.getSheet(1);
pivotSourceSheet.name("PivotSourceData");
let pivotSourceTableName = fillPivotSourceData(pivotSourceSheet);
let pivotTableSheet = spread.getSheet(2);
pivotTableSheet.name("PivotTable");
fillPivotTable(pivotTableSheet, pivotSourceTableName);
}
const 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));
}
}
}
const 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();
}
const 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();
}
const initSpread2 = (spread) => {
setSpread2(spread);
}
const changeImport_noFormula = (e) => {
setImport_noFormula(e.target.checked);
}
const changeImport_noStyle = (e) => {
setImport_noStyle(e.target.checked);
}
const changeImport_rowHeaders = (e) => {
setImport_rowHeaders(e.target.checked);
}
const changeImport_columnHeaders = (e) => {
setImport_columnHeaders(e.target.checked);
}
const changeImport_donotrecalculateafterload = (e) => {
setImport_donotrecalculateafterload(e.target.checked);
}
const changeNoFormula = (e) => {
setNoFormula(e.target.checked);
}
const changeNoStyle = (e) => {
setNoStyle(e.target.checked);
}
const changeSaveCustomRowHeaders = (e) => {
setSaveCustomRowHeaders(e.target.checked);
}
const changeSaveCustomColumnHeaders = (e) => {
setSaveCustomColumnHeaders(e.target.checked);
}
const serialization = (e) => {
var jsonOptions = {
ignoreFormula: import_noFormula,
ignoreStyle: import_noStyle,
frozenColumnsAsRowHeaders: import_rowHeaders,
frozenRowsAsColumnHeaders: import_columnHeaders,
doNotRecalculateAfterLoad: import_donotrecalculateafterload
};
var serializationOption = {
ignoreFormula: noFormula,
ignoreStyle: noStyle,
rowHeadersAsFrozenColumns: SaveCustomRowHeaders,
columnHeadersAsFrozenRows: SaveCustomColumnHeaders
};
//ToJson
var spread1 = spread;
var jsonStr = JSON.stringify(spread1.toJSON(serializationOption));
//FromJson
spread2.fromJSON(JSON.parse(jsonStr), jsonOptions);
}
return <div class="sample-tutorial">
<div class="sample-spreadsheets-container">
<label style={{ font: 'bold 10pt arial' }}>ToJson:</label>
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<br />
<label style={{ font: 'bold 10pt arial' }}>FromJson:</label>
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread2(spread)}>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
</div>
<div className="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 className="option-row">
<input type="button" defaultValue="Json Serialize" id="fromtoJsonBtn" onClick={e => serialization(e)} />
</div>
<div>
<div className="container">
<div className="row" style={{ marginTop: '10px' }}>
<div className="col-xs-12">
<label>FromJSON Options:</label>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="import_noFormula" onChange={e => changeImport_noFormula(e)} />
<label style={{ textAlign: 'left' }} htmlFor="import_noFormula">Ignore Formula</label>
<input type="checkbox" id="import_noStyle" onChange={e => changeImport_noStyle(e)} />
<label style={{ textAlign: 'left' }} htmlFor="import_noStyle">Ignore Style</label>
</div>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="import_rowHeaders" onChange={e => changeImport_rowHeaders(e)} />
<label style={{ textAlign: 'left' }} htmlFor="import_rowHeaders">Treat the frozen columns as row headers</label>
</div>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="import_columnHeaders" onChange={e => changeImport_columnHeaders(e)} />
<label style={{ textAlign: 'left' }} htmlFor="import_columnHeaders">Treat the frozen rows as column headers</label>
</div>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="import_donotrecalculateafterload" onChange={e => changeImport_donotrecalculateafterload(e)} />
<label style={{ textAlign: 'left' }} htmlFor="import_donotrecalculateafterload">Avoid recalculation after load</label>
</div>
</div>
</div>
<div className="row" style={{ marginTop: '20px' }}>
<div className="col-xs-12">
<label>ToJSON Options:</label>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="noFormula" onChange={e => changeNoFormula(e)} />
<label style={{ textAlign: 'left' }} htmlFor="noFormula">Ignore Formula</label>
<input type="checkbox" id="noStyle" onChange={e => changeNoStyle(e)} />
<label style={{ textAlign: 'left' }} htmlFor="noStyle">Ignore Style</label>
</div>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="SaveCustomRowHeaders" onChange={e => changeSaveCustomRowHeaders(e)} />
<label style={{ textAlign: 'left' }} htmlFor="SaveCustomRowHeaders">Treat the row headers as frozen columns</label>
</div>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="SaveCustomColumnHeaders" onChange={e => changeSaveCustomColumnHeaders(e)} />
<label style={{ textAlign: 'left' }} htmlFor="SaveCustomColumnHeaders">Treat the column headers as frozen rows</label>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>;
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-shapes";
import "@mescius/spread-sheets-pivot-addon";
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;
this.import_noFormula = false;
this.import_noStyle = false;
this.import_rowHeaders = false;
this.import_columnHeaders = false;
this.import_donotrecalculateafterload = false;
this.noFormula = false;
this.noStyle = false;
this.SaveCustomRowHeaders = false;
this.SaveCustomColumnHeaders = false;
}
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets-container">
<label style={{ font: 'bold 10pt arial' }}>ToJson:</label>
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<br />
<label style={{ font: 'bold 10pt arial' }}>FromJson:</label>
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread2(spread)}>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
</div>
<div className="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 className="option-row">
<input type="button" defaultValue="Json Serialize" id="fromtoJsonBtn" onClick={e => this.serialization(e)} />
</div>
<div>
<div className="container">
<div className="row" style={{ marginTop: '10px' }}>
<div className="col-xs-12">
<label>FromJSON Options:</label>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="import_noFormula" onChange={e => this.changeImport_noFormula(e)} />
<label style={{ textAlign: 'left' }} htmlFor="import_noFormula">Ignore Formula</label>
<input type="checkbox" id="import_noStyle" onChange={e => this.changeImport_noStyle(e)} />
<label style={{ textAlign: 'left' }} htmlFor="import_noStyle">Ignore Style</label>
</div>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="import_rowHeaders" onChange={e => this.changeImport_rowHeaders(e)} />
<label style={{ textAlign: 'left' }} htmlFor="import_rowHeaders">Treat the frozen columns as row headers</label>
</div>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="import_columnHeaders" onChange={e => this.changeImport_columnHeaders(e)} />
<label style={{ textAlign: 'left' }} htmlFor="import_columnHeaders">Treat the frozen rows as column headers</label>
</div>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="import_donotrecalculateafterload" onChange={e => this.changeImport_donotrecalculateafterload(e)} />
<label style={{ textAlign: 'left' }} htmlFor="import_donotrecalculateafterload">Avoid recalculation after load</label>
</div>
</div>
</div>
<div className="row" style={{ marginTop: '20px' }}>
<div className="col-xs-12">
<label>ToJSON Options:</label>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="noFormula" onChange={e => this.changeNoFormula(e)} />
<label style={{ textAlign: 'left' }} htmlFor="noFormula">Ignore Formula</label>
<input type="checkbox" id="noStyle" onChange={e => this.changeNoStyle(e)} />
<label style={{ textAlign: 'left' }} htmlFor="noStyle">Ignore Style</label>
</div>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="SaveCustomRowHeaders" onChange={e => this.changeSaveCustomRowHeaders(e)} />
<label style={{ textAlign: 'left' }} htmlFor="SaveCustomRowHeaders">Treat the row headers as frozen columns</label>
</div>
<div style={{ marginTop: '10px' }}>
<input type="checkbox" id="SaveCustomColumnHeaders" onChange={e => this.changeSaveCustomColumnHeaders(e)} />
<label style={{ textAlign: 'left' }} htmlFor="SaveCustomColumnHeaders">Treat the column headers as frozen rows</label>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>;
}
initSpread(spread) {
this.spread = 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, 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) {
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, 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();
}
initSpread2(spread) {
this.spread2 = spread;
}
changeImport_noFormula(e) {
this.import_noFormula = e.target.checked;
}
changeImport_noStyle(e) {
this.import_noStyle = e.target.checked;
}
changeImport_rowHeaders(e) {
this.import_rowHeaders = e.target.checked;
}
changeImport_columnHeaders(e) {
this.import_columnHeaders = e.target.checked;
}
changeImport_donotrecalculateafterload(e) {
this.import_donotrecalculateafterload = e.target.checked;
}
changeNoFormula(e) {
this.noFormula = e.target.checked;
}
changeNoStyle(e) {
this.noStyle = e.target.checked;
}
changeSaveCustomRowHeaders(e) {
this.SaveCustomRowHeaders = e.target.checked;
}
changeSaveCustomColumnHeaders(e) {
this.SaveCustomColumnHeaders = e.target.checked;
}
serialization(e) {
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);
}
}
<!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">
<script src="$DEMOROOT$/spread/source/data/pivot-data.js" type="text/javascript"></script>
<!-- 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-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-pivot-addon': 'npm:@mescius/spread-sheets-pivot-addon/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);
