JSON serialization and deserialization
TableSheet JSON serialization and deserialization is done using the workbook's toJSON
and fromJSON
methods.
SJS serialization and deserialization
TableSheet SJS serialization and deserialization is done using the workbook's open
and save
methods.
Export Excel
Exporting TableSheet to Excel is done using the workbook's export method.
Export PDF
Exporting TableSheet to PDF is done using the workbook's savePDF
method. For printing or exporting to PDF, a user can customize the print information using the printInfo
method.
Print
TableSheet printing is done using the workbook's print
method.
TableSheet supports most properties of print info, except columnStart/columnEnd/rowStart/rowEnd, repeatColumnStart/repeatColumnEnd/repeatRowStart/repeatRowEnd and showRowHeader.
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'));
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-io";
import '@mescius/spread-sheets-print';
import '@mescius/spread-sheets-pdf';
import "@mescius/spread-sheets-tablesheet";
import { SpreadSheets } from '@mescius/spread-sheets-react';
import './styles.css';
const useState = React.useState;
export function AppFunc() {
const [spread, setSpread] = useState(null);
const initSpread = (spread) => {
setSpread(spread);
spread.suspendPaint();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
spread.options.highlightInvalidData = true;
//init a data manager
var baseApiUrl = getBaseApiUrl();
var dataManager = spread.dataManager();
//add product table
var productTable = dataManager.addTable("productTable", {
remote: {
read: {
url: baseApiUrl + "/Product"
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet);
sheet.options.allowAddNew = false; //hide new row
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
//bind a view to the table sheet
var numericStyle = {};
numericStyle.formatter = "$ 0.00";
var formulaRule = {
ruleType: "formulaRule",
formula: "@>=50",
style: {
font: "bold 12pt Calibri",
backColor: "#F7D3BA",
foreColor: "#F09478"
}
};
var positiveNumberValidator = {
type: "formula",
formula: '@<50',
inputTitle: 'Data validation:',
inputMessage: 'Enter a number smaller than 50.',
highlightStyle: {
type: 'icon',
color: "#F09478",
position: 'outsideRight',
}
};
var myView = productTable.addView("myView", [
{ value: "Id", caption: "ID", width: 46 },
{ value: "ProductName", caption: "Name", width: 200 },
{ value: "UnitPrice", caption: "Unit Price", width: 140, conditionalFormats: [formulaRule], validator: positiveNumberValidator, style: numericStyle },
{ value: "=SUM([@UnitsInStock] , [@UnitsOnOrder])", caption: "Total Units", width: 140 },
{ value: "Discontinued", width: 120, style: { formatter: "[green]✔;;[red]✘" } }
]);
myView.fetch().then(function () {
sheet.setDataView(myView);
});
spread.resumePaint();
}
const onToJSON = () => {
var json = spread.toJSON({
includeBindingSource: true,
saveAsView: true
});
saveAs(new Blob([JSON.stringify(json)], {
type: "text/plain;charset=utf-8"
}), 'download.ssjson');
}
const onToSJS = () => {
spread.save(function (blob) {
// save blob to a file
saveAs(blob, 'download.sjs');
}, function (e) {
console.log(e);
});
}
const onFromJSONOrSJS = () => {
var file = document.getElementById("fileDemo").files[0];
if (file) {
var suffix = file.name.substr(file.name.lastIndexOf(".") + 1).toLowerCase();
if (suffix === "ssjson" || suffix === "json") {
var reader = new FileReader();
reader.onload = function () {
var json = JSON.parse(this.result);
spread.fromJSON(json);
};
reader.readAsText(file);
} else if (suffix === 'sjs') {
spread.open(file, function () {
// success callback to do something
}, function (e) {
console.log(e); // error callback
});
}
}
}
const onSaveExcel = () => {
spread.export(function (blob) {
// save blob to a file
saveAs(blob, "export.xlsx");
}, function (e) {
console.log(e);
}, {
fileType: GC.Spread.Sheets.FileType.excel
});
}
const onExportPDF = () => {
spread.savePDF(function (blob) {
saveAs(blob, "export.pdf");
}, function (error) {
console.log(error);
}, null, spread.getSheetCount() + spread.getActiveSheetTabIndex());
}
const onPrint = () => {
spread.print();
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
</SpreadSheets>
</div>
<div className="options-container">
<fieldset>
<legend>Save</legend>
<div class="field-line">
<input type="button" id="toJSON" defaultValue="JSON" class="button" onClick={(e) => onToJSON(e)} />
</div>
<div class="field-line">
<input type="button" id="toSJS" defaultValue="SJS" class="button" onClick={(e) => onToSJS(e)} />
</div>
<div class="field-line">
<input type="button" id="saveExcel" defaultValue="Excel" class="button" onClick={(e) => onSaveExcel(e)} />
</div>
</fieldset>
<fieldset>
<legend>Load</legend>
<div class="field-line">
<input type="file" id="fileDemo" class="input" />
</div>
<div class="field-line">
<input type="button" id="fromJSONOrSJS" defaultValue="JSON or SJS" class="button" onClick={(e) => onFromJSONOrSJS(e)} />
</div>
</fieldset>
<fieldset>
<legend>Render</legend>
<div class="field-line">
<input type="button" id="exportPDF" defaultValue="PDF" class="button" onClick={(e) => onExportPDF(e)} />
</div>
<div class="field-line">
<input type="button" id="print" defaultValue="PRINT" class="button" onClick={(e) => onPrint(e)} />
</div>
</fieldset>
</div>
</div>
);
}
function getBaseApiUrl() {
return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
}
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-io";
import '@mescius/spread-sheets-print';
import '@mescius/spread-sheets-pdf';
import "@mescius/spread-sheets-tablesheet";
import { SpreadSheets } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
</SpreadSheets>
</div>
<div className="options-container">
<fieldset>
<legend>Save</legend>
<div class="field-line">
<input type="button" id="toJSON" defaultValue="JSON" class="button" onClick={(e) => this.onToJSON(e)} />
</div>
<div class="field-line">
<input type="button" id="toSJS" defaultValue="SJS" class="button" onClick={(e) => this.onToSJS(e)} />
</div>
<div class="field-line">
<input type="button" id="saveExcel" defaultValue="Excel" class="button" onClick={(e) => this.onSaveExcel(e)} />
</div>
</fieldset>
<fieldset>
<legend>Load</legend>
<div class="field-line">
<input type="file" id="fileDemo" class="input" />
</div>
<div class="field-line">
<input type="button" id="fromJSONOrSJS" defaultValue="JSON or SJS" class="button" onClick={(e) => this.onFromJSONOrSJS(e)} />
</div>
</fieldset>
<fieldset>
<legend>Render</legend>
<div class="field-line">
<input type="button" id="exportPDF" defaultValue="PDF" class="button" onClick={(e) => this.onExportPDF(e)} />
</div>
<div class="field-line">
<input type="button" id="print" defaultValue="PRINT" class="button" onClick={(e) => this.onPrint(e)} />
</div>
</fieldset>
</div>
</div>
);
}
initSpread(spread) {
this.spread = spread;
spread.suspendPaint();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
spread.options.highlightInvalidData = true;
//init a data manager
var baseApiUrl = getBaseApiUrl();
var dataManager = spread.dataManager();
//add product table
var productTable = dataManager.addTable("productTable", {
remote: {
read: {
url: baseApiUrl + "/Product"
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet);
sheet.options.allowAddNew = false; //hide new row
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
//bind a view to the table sheet
var numericStyle = {};
numericStyle.formatter = "$ 0.00";
var formulaRule = {
ruleType: "formulaRule",
formula: "@>=50",
style: {
font:"bold 12pt Calibri",
backColor: "#F7D3BA",
foreColor :"#F09478"
}
};
var positiveNumberValidator = {
type: "formula",
formula: '@<50',
inputTitle: 'Data validation:',
inputMessage: 'Enter a number smaller than 50.',
highlightStyle: {
type: 'icon',
color: "#F09478",
position: 'outsideRight',
}
};
var myView = productTable.addView("myView", [
{ value: "Id", caption: "ID", width: 46},
{ value: "ProductName", caption: "Name", width: 200 },
{ value: "UnitPrice", caption: "Unit Price", width: 140, conditionalFormats: [formulaRule], validator: positiveNumberValidator, style: numericStyle },
{ value: "=SUM([@UnitsInStock] , [@UnitsOnOrder])", caption: "Total Units", width: 140},
{ value: "Discontinued", width: 120, style:{formatter:"[green]✔;;[red]✘"}}
]);
myView.fetch().then(function() {
sheet.setDataView(myView);
});
spread.resumePaint();
}
onToJSON() {
var spread = this.spread;
var json = spread.toJSON({
includeBindingSource: true,
saveAsView: true
});
saveAs(new Blob([JSON.stringify(json)], {
type: "text/plain;charset=utf-8"
}), 'download.ssjson');
}
onToSJS() {
var spread = this.spread;
spread.save(function (blob) {
// save blob to a file
saveAs(blob, 'download.sjs');
}, function (e) {
console.log(e);
});
}
onFromJSONOrSJS() {
var spread = this.spread;
var file = document.getElementById("fileDemo").files[0];
if (file) {
var suffix = file.name.substr(file.name.lastIndexOf(".") + 1).toLowerCase();
if (suffix === "ssjson" || suffix === "json") {
var reader = new FileReader();
reader.onload = function () {
var json = JSON.parse(this.result);
spread.fromJSON(json);
};
reader.readAsText(file);
} else if (suffix === 'sjs') {
spread.open(file, function () {
// success callback to do something
}, function (e) {
console.log(e); // error callback
});
}
}
}
onSaveExcel() {
var spread = this.spread;
spread.export(function (blob) {
// save blob to a file
saveAs(blob, "export.xlsx");
}, function (e) {
console.log(e);
}, {
fileType: GC.Spread.Sheets.FileType.excel
});
}
onExportPDF() {
var spread = this.spread;
spread.savePDF(function (blob) {
saveAs(blob, "export.pdf");
}, function (error) {
console.log(error);
}, null, spread.getSheetCount() + spread.getActiveSheetTabIndex());
}
onPrint() {
var spread = this.spread;
spread.print();
}
}
function getBaseApiUrl() {
return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
}
<!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/js/FileSaver.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" style="height: 100%;"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 3px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.sample-options {
z-index: 1000;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
fieldset {
padding: 6px;
margin: 0;
margin-top: 10px;
}
fieldset span,
fieldset input,
fieldset select {
display: inline-block;
text-align: left;
}
fieldset input[type=text] {
width: calc(100% - 58px);
}
fieldset input[type=button] {
width: 100%;
text-align: center;
}
fieldset input[type=file] {
width: 100%;
text-align: left;
}
fieldset select {
width: calc(100% - 50px);
}
.field-line {
margin-top: 4px;
}
.field-inline {
display: inline-block;
vertical-align: middle;
}
fieldset label.field-inline {
width: 100px;
}
fieldset input.field-inline {
width: calc(100% - 100px - 12px);
}
(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-io': 'npm:@mescius/spread-sheets-io/index.js',
'@mescius/spread-sheets-print': 'npm:@mescius/spread-sheets-print/index.js',
'@mescius/spread-sheets-pdf': 'npm:@mescius/spread-sheets-pdf/index.js',
'@mescius/spread-sheets-tablesheet': 'npm:@mescius/spread-sheets-tablesheet/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);
