The WEBSERVICE function returns string data from a web service on the Internet or Intranet that supports CORS (Cross-origin resource sharing).
The FILTERJSON function parses a valid json string into a value, an object or an array of objects. This result also supports vertical spill.
WEBSERVICE
If the request doesn't follow the Same origin policy and the target website doesn't support CORS(Cross-Origin Resource Sharing) from source origin, will return #VALUE! error. The function result is a string.
FILTERJSON
FILTERJSON function can parse a JSON string into a scalar value, an object or an array of objects(can spill vertically).
You can both use FILTERJSON function and WEBSERVICE function to get the json object from server and use in SpreadJS.
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 { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
import './styles.css';
export function AppFunc() {
const initSpread = (spread) => {
spread.options.allowDynamicArray = true;
spread.setSheetCount(1);
spread.suspendPaint();
spread.suspendCalcService();
initSheet1(spread.getSheet(0));
spread.resumeCalcService();
spread.resumePaint();
}
const initSheet1 = (sheet) => {
sheet.suspendPaint();
sheet.name('WebService');
sheet.setColumnWidth(0, 27);
sheet.setColumnWidth(1, 300);
sheet.setColumnWidth(2, 168);
sheet.setColumnWidth(3, 168);
sheet.setValue(0, 1, 'Enter full or partial country name');
var inputStyle = new GC.Spread.Sheets.Style();
inputStyle.backColor = "#FEF3CD";
sheet.setStyle(1, 1, inputStyle);
sheet.setValue(3, 1, "Name");
sheet.setValue(3, 2, "Population");
sheet.setValue(3, 3, "Area");
sheet.setValue(1, 1, "united");
var headerStyle = new GC.Spread.Sheets.Style();
headerStyle.backColor = "rgb(222,235,246)";
headerStyle.font = "bold 11pt Calibri";
headerStyle.hAlign = 1;
var borderBottom = new GC.Spread.Sheets.LineBorder;
borderBottom.color = "black";
borderBottom.style = GC.Spread.Sheets.LineStyle.thin;
headerStyle.borderBottom = borderBottom;
sheet.setStyle(3, 1, headerStyle);
sheet.setStyle(3, 2, headerStyle);
sheet.setStyle(3, 3, headerStyle);
for (var r = 4; r < 50; r++) {
//show the spilled objects as strings by using formatter property
sheet.setFormatter(r, 1, '=iferror(property(@,"name"), "")');
sheet.setFormatter(r, 2, '#,##0');
sheet.setFormatter(r, 3, '#,##0');
}
sheet.setFormula(4, 1, '=FILTERJSON(IF(LEN(B2)>3,WEBSERVICE("https://restcountries.com/v2/name/"&B2)))');
sheet.setFormula(4, 2, '=IFERROR(PROPERTY(B5#,"population"),"")');
sheet.setFormula(4, 3, '=IFERROR(PROPERTY(B5#,"area"),"")');
sheet.resumePaint();
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>
);
}
import * as React from 'react';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import GC from '@mescius/spread-sheets';
import './styles.css';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>
);
}
initSpread(spread) {
spread.options.allowDynamicArray = true;
spread.setSheetCount(1);
spread.suspendPaint();
spread.suspendCalcService();
this.initSheet1(spread.getSheet(0));
spread.resumeCalcService();
spread.resumePaint();
}
initSheet1(sheet) {
sheet.suspendPaint();
sheet.name('WebService');
sheet.setColumnWidth(0, 27);
sheet.setColumnWidth(1, 300);
sheet.setColumnWidth(2, 168);
sheet.setColumnWidth(3, 168);
sheet.setValue(0, 1, 'Enter full or partial country name');
var inputStyle = new GC.Spread.Sheets.Style();
inputStyle.backColor = "#FEF3CD";
sheet.setStyle(1, 1, inputStyle);
sheet.setValue(3, 1, "Name");
sheet.setValue(3, 2, "Population");
sheet.setValue(3, 3, "Area");
sheet.setValue(1, 1, "united");
var headerStyle = new GC.Spread.Sheets.Style();
headerStyle.backColor = "rgb(222,235,246)";
headerStyle.font = "bold 11pt Calibri";
headerStyle.hAlign = 1;
var borderBottom = new GC.Spread.Sheets.LineBorder;
borderBottom.color = "black";
borderBottom.style = GC.Spread.Sheets.LineStyle.thin;
headerStyle.borderBottom = borderBottom;
sheet.setStyle(3, 1, headerStyle);
sheet.setStyle(3, 2, headerStyle);
sheet.setStyle(3, 3, headerStyle);
for (var r = 4; r < 50; r++) {
//show the spilled objects as strings by using formatter property
sheet.setFormatter(r, 1, '=iferror(property(@,"name"), "")');
sheet.setFormatter(r, 2, '#,##0');
sheet.setFormatter(r, 3, '#,##0');
}
sheet.setFormula(4, 1, '=FILTERJSON(IF(LEN(B2)>3,WEBSERVICE("https://restcountries.com/v2/name/"&B2)))');
sheet.setFormula(4, 2, '=IFERROR(PROPERTY(B5#,"population"),"")');
sheet.setFormula(4, 3, '=IFERROR(PROPERTY(B5#,"area"),"")');
sheet.resumePaint();
}
}
<!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/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>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
}
input {
padding: 4px 6px;
}
.options-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
label {
display: block;
margin-bottom: 6px;
}
input[type=button] {
margin-top: 6px;
display: block;
width:216px;
}
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-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);
