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.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet></gc-worksheet>
</gc-spread-sheets>
</div>
</template>
<script setup>
import GC from "@mescius/spread-sheets";
import { ref } from "vue";
import "@mescius/spread-sheets-vue";
import '@mescius/spread-sheets-shapes';
import '@mescius/spread-sheets-charts';
const spreadRef = ref(null);
const initSpread = (spread) => {
spreadRef.value = 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) => {
//cashflow
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();
}
</script>
<style scoped>
.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%;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge"/>
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/annualReport.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@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-vue': 'npm:@mescius/spread-sheets-vue/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
