Import data from Excel and use in Chart.js

Question

Well, I've learned how to render html graphics with the tag canvas recently. But filling in the data manually is a little painful ... I wanted to know if it is possible to get this data straight from an Excel spreadsheet using just JavaScript. This is my code so far ...

<div>

 <canvas class="line-chart"></canvas>

 <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.5.0/Chart.min.js"></script>

 <script>

 var ctx = document.getElementsByClassName("line-chart");

 var dia = ["Dia 1", "Dia 2", "Dia 3", "Dia 4", "Dia 5", "Dia 6"]; //Seta os dias
 var real = [60,30,65,59,58,49]; //Seta os dados do grafico 1
 var meta = [30,45,62,47,55,11]; //Seta os dados do grafico 2

 //Type, data, options

 var chartGraph = new Chart (ctx, {
  type: 'line', //line, bar, radar, doughnut (pizza), polarArea
  data: {
    labels: dia,
    datasets: [{

     label: "OEE Real (%) ",
     data: real,
     borderWidth: 6,
     borderColor: 'rgba(146,242,42,0.85)',
     background: 'transparent',
    },

    {

     label: "OEE Meta (%)",
     data: meta,
     borderWidth: 6,
     borderColor: 'rgba(207,0,15,0.85)',
     background: 'transparent',

    },
   ]},

  options: {
   title: {
    display: true,
    fontSize: 20,
    text: "ENCARTUCHAMENTO 05"

   },
   scales: {
             yAxes: [{
                 ticks: {
                      max: 100,
                   min: 0,
                 }
             }]
         },

  }

 });

 </script>
 </div>

I'd use SheetJS to read .xlsx files and convert data to datasets. https://github.com/SheetJS/js-xlsx has a lot of examples how to load and read .xlsx files. Once you have all your rows of data in Javascript array, convert them to correct format as required by chart.js and create new chart. — Dread Boy, Sep 11 '17 at 07:26

score 2 · Answer 1 · answered Jun 03 '19 at 14:52

The easiest way is to use chartjs-plugin-datasource, which is leveraging SheetJS (js-xlsx).

Save the following excel file as mydata.xlsx in the same directory as your html file.

+--------------+-------+-------+-------+-------+-------+-------+
|              | Dia 1 | Dia 2 | Dia 3 | Dia 4 | Dia 5 | Dia 6 |
+--------------+-------+-------+-------+-------+-------+-------+
| OEE Real (%) |    60 |    30 |    65 |    59 |    58 |    49 |
+--------------+-------+-------+-------+-------+-------+-------+
| OEE Meta (%) |    30 |    45 |    62 |    47 |    55 |    11 |
+--------------+-------+-------+-------+-------+-------+-------+

Then, specify it in your script.

<script src="https://cdn.jsdelivr.net/npm/chart.js@2.8.0"></script>
<script src="https://cdn.jsdelivr.net/npm/xlsx@0.14.3/dist/xlsx.full.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/chartjs-plugin-datasource@0.1.0"></script>

<div>
    <canvas class="line-chart"></canvas>
</div>

<script>

var ctx = document.getElementsByClassName("line-chart");

//Type, data, options

var chartGraph = new Chart (ctx, {
    type: 'line',
    data: {
        datasets: [{
            borderWidth: 6,
            borderColor: 'rgba(146, 242, 42, 0.85)',
            fill: false
        }, {
            borderWidth: 6,
            borderColor: 'rgba(207, 0, 15, 0.85)',
            fill: false
        }
    ]},
    plugins: [ChartDataSource],
    options: {
        title: {
            display: true,
            fontSize: 20,
            text: 'ENCARTUCHAMENTO 05'
        },
        scales: {
            yAxes: [{
                ticks: {
                    max: 100,
                    min: 0,
                }
            }]
        },
        plugins: {
            datasource: {
                url: 'mydata.xlsx'
            }
        }
    }
});

</script>

Import data from Excel and use in Chart.js

1 Answers1

Linked