First, we need to include Chart.js, SheetJS (xlsx) and chartjs-plugin-datasource.js in our page.
<script type="text/javascript" src="Chart.js"></script>
<script type="text/javascript" src="xlsx.full.min.js"></script>
<script type="text/javascript" src="chartjs-plugin-datasource.js"></script>
We need to have a canvas in our page.
<canvas id="myChart"></canvas>
We create an Excel spreadsheet file as a data source for our chart.
We have two datasets that contain temperature and precipitation for each month.
The first row and column will be used as index labels and dataset labels respectively.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | January | February | March | April | May | June | July | |
| 2 | Temperature | 7 | 7 | 10 | 15 | 20 | 23 | 26 |
| 3 | Precipitation | 8.1 | 14.9 | 41.0 | 31.4 | 42.6 | 57.5 | 36.0 |
Now, we can create a chart. We add a script to our page specifying
spreadsheet's URL. Don't forget to specify ChartDataSource as a plugins option.
var ctx = document.getElementById('myChart').getContext('2d');
var chart = new Chart(ctx, {
type: 'bar',
plugins: [ChartDataSource],
options: {
daatasource: {
url: 'sample-dataset.xlsx'
}
}
});
In order to make the chart more attractive, let's mix
bars and line, use multiple Y axes and add colors.
...
data: {
datasets: [{
type: 'line',
yAxisID: 'temperature',
backgroundColor: 'transparent',
borderColor: 'rgb(255, 99, 132)',
pointBackgroundColor: 'rgb(255, 99, 132)',
tension: 0,
fill: false
}, {
yAxisID: 'precipitation',
backgroundColor: 'rgba(54, 162, 235, 0.5)',
borderColor: 'transparent'
}]
},
...
options: {
...
scales: {
yAxes: [{
id: 'temperature',
gridLines: {
drawOnChartArea: false
}
}, {
id: 'precipitation',
position: 'right',
gridLines: {
drawOnChartArea: false
}
}]
},
...
What if rows and columns in the spreadsheet are switched?
Below is an example of a spreadsheet and plugin options for
loading the data in which each row contains values for one index.
| A | B | C | |
|---|---|---|---|
| 1 | Temperature | Precipitation | |
| 2 | January | 7 | 8.1 |
| 3 | February | 7 | 14.9 |
| 4 | March | 10 | 41.0 |
| 5 | April | 15 | 31.4 |
| 6 | May | 20 | 42.6 |
| 7 | June | 23 | 57.5 |
| 8 | July | 26 | 36.0 |
...
options: {
daatasource: {
url: 'sample-index.xlsx',
rowMapping: 'index'
}
...
Data might be in form of Tidy Data. You can load this type of data
by setting the rowMapping option to 'datapoint'. The first row will be
used as datapoint labels, and you can use the datapointLabelMapping
option to map the labels to property names for Point objects.
| A | B | C | |
|---|---|---|---|
| 1 | dataset | month | value |
| 2 | Temperature | January | 7 |
| 3 | Temperature | February | 7 |
| 4 | Temperature | March | 10 |
| 5 | Temperature | April | 15 |
| 6 | Temperature | May | 20 |
| 7 | Temperature | June | 23 |
| 8 | Temperature | July | 26 |
| 9 | Precipitation | January | 8.1 |
| 10 | Precipitation | February | 14.9 |
| 11 | Precipitation | March | 41.0 |
| 12 | Precipitation | April | 31.4 |
| 13 | Precipitation | May | 42.6 |
| 14 | Precipitation | June | 57.5 |
| 15 | Precipitation | July | 36.0 |
...
options: {
daatasource: {
url: 'sample-datapoint.xlsx',
rowMapping: 'datapoint',
datapointLabelMapping: {
_dataset: 'dataset',
_index: 'month',
x: 'month',
y: 'value'
}
}
...
If you want to use only a part of cells in a spreadsheet,
you can select a range using A1 notation.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | January | February | March | April | May | June | July | |
| 2 | Temperature | 7 | 7 | 10 | 15 | 20 | 23 | 26 |
| 3 | Precipitation | 8.1 | 14.9 | 41.0 | 31.4 | 42.6 | 57.5 | 36.0 |
...
options: {
daatasource: {
url: 'sample-dataset.xlsx',
datasetLabels: 'Sheet1!A2:A3',
indexLabels: 'Sheet1!D1:G1',
data: 'Sheet1!D2:G3'
}
...
See also GitHub repository and samples