This example uses a predefined workbook that is loaded into SpreadJS.
It highlights the use of formulas, sparklines, charts, conditional formatting and hyperlinks.
This example also uses WEBSERVICE and FILTERJSON functions to get data from a webservice and parse the data into a value, an object or an array of objects.
*Data provided for free by Alpha Vantage.
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 GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import '@mescius/spread-sheets-shapes';
import '@mescius/spread-sheets-charts';
export function AppFunc() {
const initSpread = (spread) => {
spread.fromJSON(data);
initsheet1(spread.sheets[1]);
initsheet2(spread.sheets[2]);
initsheet3(spread.sheets[3]);
initsheet4(spread.sheets[4]);
};
const initsheet1 = (sheet) => {
//summary
sheet.suspendPaint();
var properties = [["Symbol", "Address", "Sector", "Industry", "FiscalYearEnd", "EBITDA", "ProfitMargin"],
["BookValue", "SharesOutstanding", "Beta", "PERatio", "DividendYield", "ExDividendDate", "ShortPercentFloat"],
["MarketCapitalization", "AnalystTargetPrice", "RevenuePerShareTTM", "EPS", "DividendPerShare", "ShortRatio", "PayoutRatio"]];
sheet.setFormula(35, 0, '=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=OVERVIEW&symbol=IBM&apikey=demo"))');
var index = 0;
for (var i = 9; i < 28; i = i + 3) {
sheet.setFormula(i, 2, '=IFERROR(PROPERTY(A36,"' + properties[0][index] + '"),"")');
sheet.setFormula(i, 12, '=IFERROR(PROPERTY(A36,"' + properties[1][index] + '"),"")');
sheet.setFormula(i, 16, '=IFERROR(PROPERTY(A36,"' + properties[2][index] + '"),"")');
index++;
}
sheet.setRowVisible(35, false);
sheet.resumePaint();
};
const initsheet2 = (sheet) => {
//income statement
sheet.suspendPaint();
var properties = ["fiscalDateEnding", "totalRevenue", "costOfRevenue", "costofGoodsAndServicesSold", "researchAndDevelopment",
"sellingGeneralAndAdministrative", "depreciationAndAmortization", "interestIncome", "interestExpense", "otherNonOperatingIncome", "incomeTaxExpense"];
var index = 0;
sheet.setFormula(39, 0, '=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol=IBM&apikey=demo"))');
for (var j = 4; j < 22; j++) {
if ([5, 8, 9, 11, 12, 16, 17].includes(j)) {
continue;
}
else {
for (var i = 0; i < 5; i++) {
sheet.setFormula(j, 8 - i, '=IFERROR(1*PROPERTY(A40,"annualReports.' + i + '.' + properties[index] + '"),0)');
}
index++;
}
}
sheet.resumePaint();
};
const initsheet3 = (sheet) => {
//balance sheet
sheet.suspendPaint();
var properties = ["fiscalDateEnding", "cashAndCashEquivalentsAtCarryingValue", "cashAndShortTermInvestments", "otherCurrentAssets", "inventory",
"currentNetReceivables", "investments", "propertyPlantEquipment", "accumulatedDepreciationAmortizationPPE",
"intangibleAssets", "intangibleAssetsExcludingGoodwill", "longTermInvestments", "goodwill",
"otherNonCurrrentAssets", "currentAccountsPayable", "shortTermDebt", "deferredRevenue",
"otherCurrentLiabilities", "longTermDebt", "otherNonCurrentLiabilities", "treasuryStock", "retainedEarnings"];
var index = 0;
sheet.setFormula(42, 0, '=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=BALANCE_SHEET&symbol=IBM&apikey=demo"))');
for (var j = 5; j < 38; j++) {
if ([6, 12, 13, 21, 22, 23, 24, 29, 30, 33, 34, 35].includes(j)) {
continue;
}
else {
for (var i = 0; i < 5; i++) {
sheet.setFormula(j, 6 - i, '=IFERROR(1*PROPERTY(A43,"annualReports.' + i + '.' + properties[index] + '"),0)');
}
index++;
}
}
sheet.setRowVisible(42, false);
sheet.resumePaint();
};
const initsheet4 = (sheet) => {
//cash flow
sheet.suspendPaint();
var properties = ["fiscalDateEnding", "netIncome", "paymentsForOperatingActivities",
"proceedsFromOperatingActivities", "depreciationDepletionAndAmortization", "changeInReceivables",
"changeInInventory", "changeInOperatingLiabilities", "changeInOperatingAssets",
"capitalExpenditures", "proceedsFromRepaymentsOfShortTermDebt", "paymentsForRepurchaseOfCommonStock",
"paymentsForRepurchaseOfEquity", "paymentsForRepurchaseOfPreferredStock", "dividendPayout",
"proceedsFromIssuanceOfCommonStock", "proceedsFromIssuanceOfLongTermDebtAndCapitalSecuritiesNet",
"proceedsFromIssuanceOfPreferredStock", "proceedsFromRepurchaseOfEquity", "proceedsFromSaleOfTreasuryStock",
"changeInCashAndCashEquivalents", "operatingCashflow"];
var index = 0;
sheet.setFormula(40, 0, '=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=CASH_FLOW&symbol=IBM&apikey=demo"))');
for (var j = 4; j < 35; j++) {
if ([5, 14, 15, 16, 18, 19, 20, 31, 32, 35, 36].includes(j)) {
continue;
}
else {
for (var i = 0; i < 5; i++) {
sheet.setFormula(j, 6 - i, '=IFERROR(1*PROPERTY(A41,"annualReports.' + i + '.' + properties[index] + '"),0)');
}
index++;
}
}
sheet.setRowVisible(40, false);
sheet.resumePaint();
};
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={initSpread}>
<Worksheet>
</Worksheet>
</SpreadSheets>
</div>
</div>
);
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import '@mescius/spread-sheets-shapes';
import '@mescius/spread-sheets-charts';
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.fromJSON(data);
this.initsheet1(spread.sheets[1]);
this.initsheet2(spread.sheets[2]);
this.initsheet3(spread.sheets[3]);
this.initsheet4(spread.sheets[4]);
}
initsheet1(sheet) {
//summary
sheet.suspendPaint();
var properties = [["Symbol", "Address", "Sector", "Industry", "FiscalYearEnd", "EBITDA", "ProfitMargin"],
["BookValue", "SharesOutstanding", "Beta", "PERatio", "DividendYield", "ExDividendDate", "ShortPercentFloat"],
["MarketCapitalization", "AnalystTargetPrice", "RevenuePerShareTTM", "EPS", "DividendPerShare", "ShortRatio", "PayoutRatio"]];
sheet.setFormula(35, 0, '=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=OVERVIEW&symbol=IBM&apikey=demo"))');
var index = 0;
for (var i = 9; i < 28; i = i + 3) {
sheet.setFormula(i, 2, '=IFERROR(PROPERTY(A36,"' + properties[0][index] + '"),"")');
sheet.setFormula(i, 12, '=IFERROR(PROPERTY(A36,"' + properties[1][index] + '"),"")');
sheet.setFormula(i, 16, '=IFERROR(PROPERTY(A36,"' + properties[2][index] + '"),"")');
index++;
}
sheet.setRowVisible(35, false);
sheet.resumePaint();
}
initsheet2(sheet) {
//income statement
sheet.suspendPaint();
var properties = ["fiscalDateEnding", "totalRevenue", "costOfRevenue", "costofGoodsAndServicesSold", "researchAndDevelopment",
"sellingGeneralAndAdministrative", "depreciationAndAmortization", "interestIncome", "interestExpense", "otherNonOperatingIncome", "incomeTaxExpense"];
var index = 0;
sheet.setFormula(39, 0, '=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=INCOME_STATEMENT&symbol=IBM&apikey=demo"))');
for (var j = 4; j < 22; j++) {
if ([5, 8, 9, 11, 12, 16, 17].includes(j)) {
continue;
}
else {
for (var i = 0; i < 5; i++) {
sheet.setFormula(j, 8 - i, '=IFERROR(1*PROPERTY(A40,"annualReports.' + i + '.' + properties[index] + '"),0)');
}
index++;
}
}
sheet.resumePaint();
}
initsheet3(sheet) {
//balance sheet
sheet.suspendPaint();
var properties = ["fiscalDateEnding", "cashAndCashEquivalentsAtCarryingValue", "cashAndShortTermInvestments", "otherCurrentAssets", "inventory",
"currentNetReceivables", "investments", "propertyPlantEquipment", "accumulatedDepreciationAmortizationPPE",
"intangibleAssets", "intangibleAssetsExcludingGoodwill", "longTermInvestments", "goodwill",
"otherNonCurrrentAssets", "currentAccountsPayable", "shortTermDebt", "deferredRevenue",
"otherCurrentLiabilities", "longTermDebt", "otherNonCurrentLiabilities", "treasuryStock", "retainedEarnings"];
var index = 0;
sheet.setFormula(42, 0, '=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=BALANCE_SHEET&symbol=IBM&apikey=demo"))');
for (var j = 5; j < 38; j++) {
if ([6, 12, 13, 21, 22, 23, 24, 29, 30, 33, 34, 35].includes(j)) {
continue;
}
else {
for (var i = 0; i < 5; i++) {
sheet.setFormula(j, 6 - i, '=IFERROR(1*PROPERTY(A43,"annualReports.' + i + '.' + properties[index] + '"),0)');
}
index++;
}
}
sheet.setRowVisible(42, false);
sheet.resumePaint();
}
initsheet4(sheet) {
//cash flow
sheet.suspendPaint();
var properties = ["fiscalDateEnding", "netIncome", "paymentsForOperatingActivities",
"proceedsFromOperatingActivities", "depreciationDepletionAndAmortization", "changeInReceivables",
"changeInInventory", "changeInOperatingLiabilities", "changeInOperatingAssets",
"capitalExpenditures", "proceedsFromRepaymentsOfShortTermDebt", "paymentsForRepurchaseOfCommonStock",
"paymentsForRepurchaseOfEquity", "paymentsForRepurchaseOfPreferredStock", "dividendPayout",
"proceedsFromIssuanceOfCommonStock", "proceedsFromIssuanceOfLongTermDebtAndCapitalSecuritiesNet",
"proceedsFromIssuanceOfPreferredStock", "proceedsFromRepurchaseOfEquity", "proceedsFromSaleOfTreasuryStock",
"changeInCashAndCashEquivalents", "operatingCashflow"];
var index = 0;
sheet.setFormula(40, 0, '=FILTERJSON(WEBSERVICE("https://www.alphavantage.co/query?function=CASH_FLOW&symbol=IBM&apikey=demo"))');
for (var j = 4; j < 35; j++) {
if ([5, 14, 15, 16, 18, 19, 20, 31, 32, 35, 36].includes(j)) {
continue;
}
else {
for (var i = 0; i < 5; i++) {
sheet.setFormula(j, 6 - i, '=IFERROR(1*PROPERTY(A41,"annualReports.' + i + '.' + properties[index] + '"),0)');
}
index++;
}
}
sheet.setRowVisible(40, false);
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">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="$DEMOROOT$/spread/source/data/annualReport.js" type="text/javascript"></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;
}
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-charts': 'npm:@mescius/spread-sheets-charts/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);
