Integration
Integration demo that demonstrates how the JavaScript spreadsheet component can connect seamlessly with external APIs, backend services, or cloud platforms. It supports real-time data fetching, updates, and synchronization — ideal for embedding live data from CRMs, financial APIs, or inventory systems into your web app.
<!DOCTYPE html>
<html lang="pt-BR">
<link rel="stylesheet" href="https://jspreadsheet.com/v12/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v6/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@jspreadsheet/charts/dist/style.min.css" type="text/css" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@lemonadejs/studio/dist/style.min.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@jspreadsheet/bar/dist/style.min.css" />
<script src="https://jspreadsheet.com/v12/jspreadsheet.js"></script>
<script src="https://jsuites.net/v6/jsuites.js"></script>
<script src="https://cdn.jsdelivr.net/npm/lemonadejs/dist/lemonade.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@lemonadejs/studio/dist/index.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/charts/dist/index.min.js"></script>
<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/bar/dist/index.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-charts@4.0.0/dist/index.min.js"></script>
<div class="row gap small">
<div class="column">
<div id="summary" style="height: 245px;"></div>
</div>
<div class="column f1">
<div id="charts" style="height: 230px; border: 1px solid #ddd;"></div>
</div>
</div>
<div class="row">
<div class="column f1">
<div id="grid"></div>
</div>
</div>
<script>
jspreadsheet.setLicense('NzA4N2JmZjliZTQ5YmQ1ODgwY2JmMzRkYjA3MWNmNzQwYjhlNDY2ZTk5MGM3ZDhiZDgwODIyOWMwNzI3ZTMxY2UwNmNhNjRkM2MzZjBmYzg2MGVlODZlZTFmZTAxM2E2ZDVkZDE4YjhiMzRlYTg5ZWU1MDA1YmRlNzVjMmY3YzAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnpjd056azROamM0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9');
jspreadsheet.setExtensions({ formula, bar });
jspreadsheet.calculations(false);
const data = [
['Product A', 'US', 500, 525, 550, 575, 550, 525, 525, 550, 575, 600, 650, 650, 3, 40, '=SUM(C1:N1)'],
['Product B', 'BR', 150, 100, 143, 125, 125, 150, 150, 175, 200, 250, 300, 300, 4, 60, '=SUM(C2:N2)'],
['Product C', 'AU', 460, 250, 200, 350, 400, 400, 350, 350, 250, 300, 175, 154, 4, 20, '=SUM(C3:N3)'],
['Product D', 'JP', 200, 413, 125, 350, 100, 400, 350, 350, 550, 300, 600, 516, 5, 80, '=SUM(C4:N4)']
];
function update(instance, cell, x, y, value) {
if (!chart.series[y]) {
const rowData = instance.getRowData(y, true);
const rowValues = rowData.slice(2, rowData.length - 3).map(v => parseFloat(v) || 0);
chart.addSeries({ name: rowData[0], data: rowValues });
} else {
if (x === 0) {
chart.series[y].update({ name: value });
} else if (x > 1 && x < 14) {
const v = instance.getValueFromCoords(x, y, true);
chart.series[y].data[x - 2].update({ y: parseFloat(v) || 0 });
}
}
}
const chart = Highcharts.chart('charts', {
title: { text: 'Forecast', x: -20 },
xAxis: { categories: ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'] },
yAxis: {
title: { text: 'Sales' },
plotLines: [{ value: 0, width: 1, color: '#808080' }]
},
tooltip: { valueSuffix: '$' },
legend: { layout: 'vertical', align: 'right', verticalAlign: 'middle', borderWidth: 0 },
series: data.map(row => ({
name: row[0],
data: row.slice(2,14).map(Number)
}))
});
jspreadsheet(document.getElementById('grid'), {
tabs: true,
toolbar: true,
worksheets: [{
data: data,
defaultColWidth: '48px',
tableOverflow: true,
tableWidth: 1121,
tableHeight: 242,
resizable: true,
worksheetName: 'Products',
filters: true,
nestedHeaders: [
[{ title: 'Information', colspan: 2 },{ title: '2022', colspan: 12 },{ title: 'Summary', colspan: 3 }]
],
columns: [
{ width: '120px' },
{ width: '120px', type: 'dropdown', url: 'https://jspreadsheet.com/jspreadsheet/countries/1', autocomplete: true },
{},{},{},{},{},{},{},{},{},{},{},{},
{ width: '90px', type: 'rating', title: 'Rating' },
{ width: '90px', type: 'progressbar', title: 'Target' },
{ width: '70px', title: 'Total' }
],
style: { 'O1:O4': 'font-weight: bold' }
}],
onchange: update,
onload: function () {
jspreadsheet(document.getElementById('summary'), {
bar: false,
worksheets: [{
defaultRowHeight: '50px',
data: [
['=Products!A1', true, 'red', '=SUM(Products!C1:N1)' ],
['=Products!A2', false, 'orange', '=SUM(Products!C2:N2)' ],
['=Products!A3', false, 'darkblue', '=SUM(Products!C3:N3)' ],
['=Products!A4', false, 'purple', '=SUM(Products!C4:N4)' ]
],
columns: [
{ type: 'text', title: 'Summary' },
{ type: 'checkbox', title: 'Partner', width: 60 },
{ type: 'color', title: 'Brand' },
{ type: 'number', title: 'Total', mask: 'USD #.##0,00' },
],
columnSorting: false
}],
debugFormulas: true
});
jspreadsheet.calculations(true);
}
});
</script>
</html>
import React, { useEffect, useRef } from 'react';
import jspreadsheet from 'jspreadsheet';
import formula from '@jspreadsheet/formula-pro';
import bar from '@jspreadsheet/bar';
import Highcharts from 'highcharts';
import 'jspreadsheet/dist/jspreadsheet.css';
import 'jsuites/dist/jsuites.css';
import '@jspreadsheet/bar/dist/style.css';
import './App.css';
export default function App() {
const summaryRef = useRef();
const chartsRef = useRef();
const gridRef = useRef();
let chart;
useEffect(() => {
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NzA4N2JmZjliZTQ5YmQ1ODgwY2JmMzRkYjA3MWNmNzQwYjhlNDY2ZTk5MGM3ZDhiZDgwODIyOWMwNzI3ZTMxY2UwNmNhNjRkM2MzZjBmYzg2MGVlODZlZTFmZTAxM2E2ZDVkZDE4YjhiMzRlYTg5ZWU1MDA1YmRlNzVjMmY3YzAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnpjd056azROamM0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9');
jspreadsheet.setExtensions({ formula, bar });
jspreadsheet.calculations(false);
const data = [
['Product A','US',500,525,550,575,550,525,525,550,575,600,650,650,3,40,'=SUM(C1:N1)'],
['Product B','BR',150,100,143,125,125,150,150,175,200,250,300,300,4,60,'=SUM(C2:N2)'],
['Product C','AU',460,250,200,350,400,400,350,350,250,300,175,154,4,20,'=SUM(C3:N3)'],
['Product D','JP',200,413,125,350,100,400,350,350,550,300,600,516,5,80,'=SUM(C4:N4)'],
];
const update = (instance, cell, x, y) => {
const hasSeries = !!chart.series[y];
if (!hasSeries) {
const row = instance.getRowData(y, true);
const values = row.slice(2, row.length - 3).map(v => parseFloat(v) || 0);
chart.addSeries({ name: row[0], data: values });
} else {
if (x === 0) {
chart.series[y].update({ name: cell.innerText });
} else if (x > 1 && x < 14) {
const val = instance.getValueFromCoords(x, y, true);
chart.series[y].data[x - 2].update({ y: parseFloat(val) || 0 });
}
}
};
chart = Highcharts.chart(chartsRef.current, {
title: { text: 'Forecast', x: -20 },
xAxis: { categories: ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'] },
yAxis: {
title: { text: 'Sales' },
plotLines: [{ value: 0, width: 1, color: '#808080' }]
},
tooltip: { valueSuffix: '$' },
legend: { layout: 'vertical', align: 'right', verticalAlign: 'middle', borderWidth: 0 },
series: data.map(r => ({ name: r[0], data: r.slice(2,14).map(Number) })),
});
jspreadsheet(gridRef.current, {
tabs: true,
toolbar: true,
worksheets: [{
worksheetName: 'Products',
data,
defaultColWidth: '48px',
tableOverflow: true,
tableWidth: 1121,
tableHeight: 242,
resizable: true,
filters: true,
nestedHeaders: [[
{ title:'Information', colspan:2 },
{ title:'2022', colspan:12 },
{ title:'Summary', colspan:3 },
]],
columns: [
{ width:'120px' },
{ width:'120px', type:'dropdown', url:'https://jspreadsheet.com/jspreadsheet/countries/1', autocomplete:true },
{},{},{},{},{},{},{},{},{},{},{},{},
{ width:'90px', type:'rating', title:'Rating' },
{ width:'90px', type:'progressbar', title:'Target' },
{ width:'70px', title:'Total' },
],
style: { 'O1:O4':'font-weight:bold' },
}],
onchange: update,
onload: function() {
jspreadsheet(summaryRef.current, {
bar: false,
debugFormulas: true,
worksheets: [{
defaultRowHeight: '50px',
data: [
['=Products!A1', true, 'red', '=SUM(Products!C1:N1)'],
['=Products!A2', false, 'orange', '=SUM(Products!C2:N2)'],
['=Products!A3', false, 'darkblue', '=SUM(Products!C3:N3)'],
['=Products!A4', false, 'purple', '=SUM(Products!C4:N4)'],
],
columns: [
{ type:'text', title:'Summary' },
{ type:'checkbox', title:'Partner', width:60 },
{ type:'color', title:'Brand' },
{ type:'number', title:'Total', mask:'USD #.##0,00' },
],
columnSorting: false,
}],
});
jspreadsheet.calculations(true);
},
});
return () => {
jspreadsheet.destroyAll();
chart.destroy();
};
}, []);
return (
<>
<div className="row gap small">
<div className="column">
<div ref={summaryRef} style={{ height: 245 }} />
</div>
<div className="column f1">
<div ref={chartsRef} style={{ height: 230, border: '1px solid #ddd' }} />
</div>
</div>
<div className="row">
<div className="column f1">
<div ref={gridRef} />
</div>
</div>
</>
);
}
<template>
<div>
<div class="row gap small">
<div class="column">
<div ref="summaryContainer" style="height: 245px;"></div>
</div>
<div class="column f1">
<div ref="chartContainer" class="chart-box"></div>
</div>
</div>
<div class="row">
<div class="column f1">
<div ref="gridContainer"></div>
</div>
</div>
</div>
</template>
<script>
import jspreadsheet from "jspreadsheet";
import formula from '@jspreadsheet/formula-pro';
import bar from "@jspreadsheet/bar";
import Highcharts from "highcharts";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import "@jspreadsheet/bar/dist/style.css";
export default {
name: "App",
data() {
return {
chart: null,
gridData: [
["Product A","US",500,525,550,575,550,525,525,550,575,600,650,650,3,40,"=SUM(C1:N1)"],
["Product B","BR",150,100,143,125,125,150,150,175,200,250,300,300,4,60,"=SUM(C2:N2)"],
["Product C","AU",460,250,200,350,400,400,350,350,250,300,175,154,4,20,"=SUM(C3:N3)"],
["Product D","JP",200,413,125,350,100,400,350,350,550,300,600,516,5,80,"=SUM(C4:N4)"],
]
};
},
mounted() {
const self = this;
jspreadsheet.setLicense('NzA4N2JmZjliZTQ5YmQ1ODgwY2JmMzRkYjA3MWNmNzQwYjhlNDY2ZTk5MGM3ZDhiZDgwODIyOWMwNzI3ZTMxY2UwNmNhNjRkM2MzZjBmYzg2MGVlODZlZTFmZTAxM2E2ZDVkZDE4YjhiMzRlYTg5ZWU1MDA1YmRlNzVjMmY3YzAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnpjd056azROamM0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9');
jspreadsheet.setExtensions({ formula, bar });
jspreadsheet.calculations(false);
this.chart = Highcharts.chart(this.$refs.chartContainer, {
title: { text:"Forecast", x:-20 },
xAxis: { categories:["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"] },
yAxis: {
title: { text:"Sales" },
plotLines:[{ value:0, width:1, color:"#808080" }]
},
tooltip:{ valueSuffix:"$" },
legend:{ layout:"vertical", align:"right", verticalAlign:"middle", borderWidth:0 },
series: this.gridData.map(r => ({ name:r[0], data:r.slice(2,14).map(Number) }))
});
jspreadsheet(this.$refs.gridContainer, {
tabs: true,
toolbar: true,
worksheets: [{
worksheetName: 'Products',
data: this.gridData,
defaultColWidth: '48px',
tableOverflow: true,
tableWidth: 1121,
tableHeight: 242,
resizable: true,
filters: true,
nestedHeaders: [[
{ title:"Information", colspan:2 },
{ title:"2022", colspan:12 },
{ title:"Summary", colspan:3 },
]],
columns: [
{ width:"120px" },
{ width:"120px", type:"dropdown", url:"https://jspreadsheet.com/jspreadsheet/countries/1", autocomplete:true },
{},{},{},{},{},{},{},{},{},{},{},{},
{ width:"90px", type:"rating", title:"Rating" },
{ width:"90px", type:"progressbar", title:"Target" },
{ width:"70px", title:"Total" },
],
style: { "O1:O4":"font-weight:bold" },
}],
onchange: (instance, cell, x, y) => {
if (!this.chart.series[y]) {
const row = instance.getRowData(y, true);
const vals = row.slice(2,row.length-3).map(v=>parseFloat(v)||0);
this.chart.addSeries({ name:row[0], data:vals });
} else if (x === 0) {
this.chart.series[y].update({ name: cell.innerText });
} else if (x > 1 && x < 14) {
const val = instance.getValueFromCoords(x,y,true);
this.chart.series[y].data[x-2].update({ y: parseFloat(val)||0 });
}
},
onload: function() {
jspreadsheet(self.$refs.summaryContainer, {
bar: false,
debugFormulas: true,
worksheets: [{
defaultRowHeight: '50px',
data: [
['=Products!A1', true, 'red', '=SUM(Products!C1:N1)'],
['=Products!A2', false, 'orange', '=SUM(Products!C2:N2)'],
['=Products!A3', false, 'darkblue', '=SUM(Products!C3:N3)'],
['=Products!A4', false, 'purple', '=SUM(Products!C4:N4)'],
],
columns: [
{ type:'text', title:'Summary' },
{ type:'checkbox', title:'Partner', width:60 },
{ type:'color', title:'Brand' },
{ type:'number', title:'Total', mask:'USD #.##0,00' },
],
columnSorting: false,
}],
});
jspreadsheet.calculations(true);
}
});
},
beforeDestroy() {
jspreadsheet.destroyAll();
this.chart?.destroy();
}
};
</script>
<style>
.column { box-sizing: border-box; }
.f1 { flex:1 1 auto; }
.chart-box {
height: 230px;
border: 1px solid #ddd;
}
</style>
import { Component, ElementRef, ViewChild, OnDestroy, AfterViewInit } from '@angular/core';
import jspreadsheet from 'jspreadsheet';
import formula from '@jspreadsheet/formula-pro';
import bar from '@jspreadsheet/bar';
import Highcharts from 'highcharts';
@Component({
selector: 'app-root',
standalone: true,
template: `
<div class="row gap small">
<div class="column">
<div #summary style="height: 245px;"></div>
</div>
<div class="column f1">
<div #chart class="chart-box"></div>
</div>
</div>
<div class="row">
<div class="column f1">
<div #grid></div>
</div>
</div>
`,
styles: [`
.column { box-sizing:border-box; }
.f1 { flex:1 1 auto; }
.chart-box { height:230px; border:1px solid #ddd; }
`]
})
export class AppComponent implements AfterViewInit, OnDestroy {
@ViewChild('summary', { static: true }) summaryHost!: ElementRef<HTMLDivElement>;
@ViewChild('grid', { static: true }) gridHost!: ElementRef<HTMLDivElement>;
@ViewChild('chart', { static: true }) chartHost!: ElementRef<HTMLDivElement>;
private chart!: Highcharts.Chart;
private data = [
['Product A','US',500,525,550,575,550,525,525,550,575,600,650,650,3,40,'=SUM(C1:N1)'],
['Product B','BR',150,100,143,125,125,150,150,175,200,250,300,300,4,60,'=SUM(C2:N2)'],
['Product C','AU',460,250,200,350,400,400,350,350,250,300,175,154,4,20,'=SUM(C3:N3)'],
['Product D','JP',200,413,125,350,100,400,350,350,550,300,600,516,5,80,'=SUM(C4:N4)'],
];
ngAfterViewInit(): void {
const summaryEl = this.summaryHost.nativeElement;
jspreadsheet.setLicense('NzA4N2JmZjliZTQ5YmQ1ODgwY2JmMzRkYjA3MWNmNzQwYjhlNDY2ZTk5MGM3ZDhiZDgwODIyOWMwNzI3ZTMxY2UwNmNhNjRkM2MzZjBmYzg2MGVlODZlZTFmZTAxM2E2ZDVkZDE4YjhiMzRlYTg5ZWU1MDA1YmRlNzVjMmY3YzAsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94Tnpjd056azROamM0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2liRzlqWVd4b2IzTjBJbDBzSW5Cc1lXNGlPaUl6TkNJc0luTmpiM0JsSWpwYkluWTNJaXdpZGpnaUxDSjJPU0lzSW5ZeE1DSXNJbll4TVNJc0luWXhNaUlzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElpd2ljR2wyYjNRaVhTd2laR1Z0YnlJNmRISjFaWDA9');
jspreadsheet.setExtensions({ formula, bar });
jspreadsheet.calculations(false);
this.chart = Highcharts.chart(this.chartHost.nativeElement, {
chart: { type: 'line' },
title: { text: 'Forecast', x: -20 },
xAxis: { categories: ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'] },
yAxis: { title: { text: 'Sales' }, plotLines: [{ value: 0, width: 1, color: '#808080' }] },
tooltip: { valueSuffix: '$' },
legend: { layout: 'vertical', align: 'right', verticalAlign: 'middle', borderWidth: 0 },
series: this.data.map(r => ({ type: 'line', name: r[0] as string, data: r.slice(2,14).map(Number) })),
});
jspreadsheet(this.gridHost.nativeElement, {
tabs: true,
toolbar: true,
worksheets: [{
worksheetName: 'Products',
data: this.data,
defaultColWidth: 48,
tableOverflow: true,
tableWidth: 1121,
tableHeight: 242,
resizable: true,
filters: true,
nestedHeaders: [[
{ title:'Information', colspan:2 },
{ title:'2022', colspan:12 },
{ title:'Summary', colspan:3 },
]],
columns: [
{ width:120 },
{ width:120, type:'dropdown', url:'https://jspreadsheet.com/jspreadsheet/countries/1', autocomplete:true },
{},{},{},{},{},{},{},{},{},{},{},{},
{ width:90, type:'rating', title:'Rating' },
{ width:90, type:'progressbar', title:'Target' },
{ width:70, title:'Total' },
],
style: { 'O1:O4':'font-weight:bold' },
}],
onchange: (instance: any, cell: HTMLElement, x: number | string, y: number | string) => {
this.updateChart(instance, cell, Number(x), Number(y));
},
onload: () => {
jspreadsheet(summaryEl, {
bar: false,
debugFormulas: true,
worksheets: [{
defaultRowHeight: 50,
data: [
['=Products!A1', true, 'red', '=SUM(Products!C1:N1)'],
['=Products!A2', false, 'orange', '=SUM(Products!C2:N2)'],
['=Products!A3', false, 'darkblue', '=SUM(Products!C3:N3)'],
['=Products!A4', false, 'purple', '=SUM(Products!C4:N4)'],
],
columns: [
{ type:'text', title:'Summary' },
{ type:'checkbox', title:'Partner', width:60 },
{ type:'color', title:'Brand' },
{ type:'number', title:'Total', mask:'USD #.##0,00' },
],
columnSorting: false,
}],
});
jspreadsheet.calculations(true);
},
});
}
private updateChart(instance: any, cell: HTMLElement, x: number, y: number) {
if (!this.chart.series[y]) {
const row = instance.getRowData(y, true);
const vals = row.slice(2, row.length - 3).map((v: any) => parseFloat(v) || 0);
this.chart.addSeries({ type: 'line', name: row[0], data: vals });
} else {
if (x === 0) {
const newName = (cell as any).innerText;
(this.chart.series[y] as any).update({ name: newName }, true);
} else if (x > 1 && x < 14) {
const v = instance.getValueFromCoords(x, y, true);
(this.chart.series[y].data[x - 2] as any).update({ y: parseFloat(v) || 0 });
}
}
}
ngOnDestroy(): void {
try { jspreadsheet.destroyAll(); } catch {}
try { this.chart?.destroy?.(); } catch {}
}
}