ChartJS integration
Jspreadsheet offers an integration with ChartJS that simplifies integrating charts into the data grid. To see more working examples of this integration, please refer to the ChartJS documentation.This plugin is used through the "CHART" formula, which has two forms that are explained below.
JavaScript Example
<html> <script src="https://jspreadsheet.com/v10/jspreadsheet.js"></script> <script src="https://jsuites.net/v5/jsuites.js"></script> <link rel="stylesheet" href="https://jspreadsheet.com/v10/jspreadsheet.css" type="text/css" /> <link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" /> <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 id="spreadsheet"></div> <script> jspreadsheet.setLicense('OGYxMDM5YjVmYTkzNzNiZWRkM2E2ZWEzYTY1NmVkMDhjZjFkMDJiMzA4NGVkNzBjY2E3MDQ0MTE0YTA3ZTZiNTAyMmE2ZmJiNDY2NzJjYzdhNjczZDdhMmZmNjFmM2Q4ZTRkNDRjMjM1Mzg3ZDE2YWMzMGIwZTM0ZjQ0MzY5MjQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTkRRME9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5'); jspreadsheet(document.getElementById('spreadsheet'), { worksheets: [ { data: [ ['=CHART("pie", A4:A6, B4:B6)'], [], ['Proposals', 'Votes (%)'], ['Proposal 1', 0.36], ['Proposal 2', 0.29], ['Proposal 3', 0.35], ], minDimensions: [10, 1], mergeCells: { 'A1': [10, 1], }, defaultColWidth: '60px', columns: [ {width: '100px'}, {width: '100px'}, ], rows: {0: {height: '380px'}}, } ] }); </script> </html>
React example
See this React example on codesandboximport React, { useRef } from "react"; import { Spreadsheet } from "@jspreadsheet/react"; import "@jspreadsheet/formula-charts"; const license = 'OGYxMDM5YjVmYTkzNzNiZWRkM2E2ZWEzYTY1NmVkMDhjZjFkMDJiMzA4NGVkNzBjY2E3MDQ0MTE0YTA3ZTZiNTAyMmE2ZmJiNDY2NzJjYzdhNjczZDdhMmZmNjFmM2Q4ZTRkNDRjMjM1Mzg3ZDE2YWMzMGIwZTM0ZjQ0MzY5MjQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTkRRME9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5'; export default function App() { // Spreadsheet array of worksheets const spreadsheet = useRef(); // Worksheets const worksheets = [ { data: [ ['=CHART("pie", A4:A6, B4:B6)'], [], ['Proposals', 'Votes (%)'], ['Proposal 1', 0.36], ['Proposal 2', 0.29], ['Proposal 3', 0.35], ], minDimensions: [10, 1], mergeCells: { 'A1': [10, 1], }, defaultColWidth: '30px', columns: [ {width: '100px'}, {width: '100px'}, ], rows: {0: {height: '380px'}}, } ]; // Render data grid component return (<Spreadsheet ref={spreadsheet} license={license} worksheets={worksheets} />); }
Vue example
<template> <Jspreadsheet :options="Options" /> </template> <script> import "@jspreadsheet/formula-charts"; import Jspreadsheet from "./components/Jspreadsheet"; export default { components: { Jspreadsheet, }, setup() { const license = "OGYxMDM5YjVmYTkzNzNiZWRkM2E2ZWEzYTY1NmVkMDhjZjFkMDJiMzA4NGVkNzBjY2E3MDQ0MTE0YTA3ZTZiNTAyMmE2ZmJiNDY2NzJjYzdhNjczZDdhMmZmNjFmM2Q4ZTRkNDRjMjM1Mzg3ZDE2YWMzMGIwZTM0ZjQ0MzY5MjQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTkRRME9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5"; const Options = { worksheets: [ { data: [ ['=CHART("pie", A4:A6, B4:B6)'], [], ["Proposals", "Votes (%)"], ["Proposal 1", 0.36], ["Proposal 2", 0.29], ["Proposal 3", 0.35], ], minDimensions: [10, 1], mergeCells: { A1: [10, 1], }, defaultColWidth: "60px", columns: [{ width: "100px" }, { width: "100px" }], rows: { 0: { height: "380px" } }, }, ], license, }; return { Options }; }, }; </script>
Angular example
import { Component, ViewChild, ElementRef } from "@angular/core"; import * as jspreadsheet from "jspreadsheet"; import "@jspreadsheet/formula-charts"; jspreadsheet.setLicense('OGYxMDM5YjVmYTkzNzNiZWRkM2E2ZWEzYTY1NmVkMDhjZjFkMDJiMzA4NGVkNzBjY2E3MDQ0MTE0YTA3ZTZiNTAyMmE2ZmJiNDY2NzJjYzdhNjczZDdhMmZmNjFmM2Q4ZTRkNDRjMjM1Mzg3ZDE2YWMzMGIwZTM0ZjQ0MzY5MjQsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UY3dNak0zTkRRME9Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklsMHNJbVJsYlc4aU9uUnlkV1Y5'); @Component({ selector: "app-root", template: `<div #spreadsheet></div>` }) export class AppComponent { @ViewChild("spreadsheet") spreadsheet: ElementRef; worksheets: jspreadsheet.worksheetInstance[]; ngAfterViewInit() { this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, { worksheets: [ { data: [ ['=CHART("pie", A4:A6, B4:B6)'], [], ["Proposals", "Votes (%)"], ["Proposal 1", 0.36], ["Proposal 2", 0.29], ["Proposal 3", 0.35] ], minDimensions: [10, 1], mergeCells: { A1: [10, 1] }, defaultColWidth: "60px", columns: [{ width: 100 }, { width: 100 }], rows: { 0: { height: 380 } } } ] }); } }
Main Form
CHART(Type, Labels, Datasets, Options)
Arguments:
Argument | Description |
---|---|
Type | The type of the chart. A text that can vary between "line", "pie", "bar" or any other chart type allowed in ChartJS. |
Labels | The chart labels. Must be a single row or single column range. |
Datasets | The chart datasets. It can be a range, an object (whose properties are explained later) or a list made up of these two. |
Options | Chart options. Its properties are explained further below. |
Property mapping
Both the dataset configuration object and the options object have a property mapping that aims to enable their use without knowledge of ChartJs settings. If this functionality is not useful for your case, just use thedisableJssSimplification: TRUE
setting on the object where you want to disable the mapping.
By disabling property mapping for an object, that object receives as little treatment as possible before being passed to ChartJs. This object must therefore follow the ChartJS v4.4.0 documentation.
Obs: Even with this option enabled, it's still worth reading about the
data
property of the dataset configuration object. Because it is a good example of how cell coordinates are replaced by their values. Which occurs regardless of property mapping.
Dataset properties
Property | Description | Example |
---|---|---|
data | A range enclosed in parentheses or a list of cell names. | "data": (A1:A4) "data": [A1, A2, A3, A4] |
label | The label for this dataset | "label": "Service A" |
color | The color(s) used to represent the dataset. It can be a single color or a list of colors. The default value is defined by the PALETTE formula. | "color": "#D05D5B" "color": ["#C1D37F","#95A3B3","#995D81"] |
Options properties
Property | Description | Example |
---|---|---|
title | The title of the chart. If provided, it must be text. | "title": "my first chart" |
titleAxesX | Chart x-axis title. If provided, it must be text. | "titleAxesX": "Month" |
titleAxesY | Chart y-axis title. If provided, it must be text. | "titleAxesX": "Units" |
legend | Informs whether dataset labels should be displayed. If provided, it must be boolean. | "legend": FALSE |
legendPosition | The position on the chart where dataset labels should be displayed. Accepted values are: "top", "left", "bottom" or "right". | "legendPosition": "left" |
minAxesY | Suggested minimum y-axis value. If provided, it must be a number. | "minAxesY": -5 |
maxAxesY | Suggested maximum y-axis value. If provided, it must be a number. | "maxAxesY": 50 |
gridLineX | Informs whether the chart's x-axis separators should be displayed. If provided, it must be a boolean. | "gridLineX": FALSE |
gridLineY | Informs whether the chart's y-axis separators should be displayed. If provided, it must be a boolean. | "gridLineY": FALSE |