Visualizing data can be a useful tool in showing trends and patterns that might otherwise be overlooked. Without quantitative information presented in easily digestible visuals, time is wasted struggling to understand the meaning and significance of data. SpreadJS is a high-speed Excel-like JavaScript spreadsheet component that allows you to work within different JavaScript frameworks. SpreadJS displays data in a way that is creative, insightful, and flexible. This tutorial demonstrates how you can add a chart and change an imported spreadsheet using SpreadJS in Vue.
In this tutorial, we'll be using VSCode to edit and run the project, as well as debug and run the project in Google Chrome. This article shows you how to setup VS Code for debugging Vue projects with Google Chrome.
The sample zip for this project.
SpreadJS Vue Setup
Here is a tutorial for setting up a SpreadJS Vue project. I'll go through the specific steps for this particular project. To start off, open up a command prompt as an administrator and navigate to the folder that you would like to for your project.
Install the Command Line Interface for Vue using the following command:
npm install -global vue-cli
That should install the required libraries needed to create and run a Vue project, so now you can use "vue init" to create the project:
vue init webpack SpreadJS-Vue-Project
This will start creating the project, and will prompt for more information to fill in. Most of the prompts can be answered with "No", like so:
Once the project has been created, the required Spread files can be installed via NPM:
npm install @grapecity/spread-sheets-vue @grapecity/spread-sheets @grapecity/spread-excelio @grapecity/spread-sheets-charts
Project Setup
For the sake of organization, I'll create a separate folder labeled "components" in the "src" folder of the project. In that folder, I'll create the Vue file that will contain the code for this sample (in this case, the file will be called "IncomeStatement.vue").
Once that file and folder have been created, we can edit the App.vue file in the "src" folder to direct it to this new file:
<template>
<div id="app">
<IncomeStatement/>
</div>
</template>
<script>
import IncomeStatement from './components/IncomeStatement'
export default {
name: 'App',
components: {
IncomeStatement
}
}
</script>
<style>
#app {
font-family: 'Avenir', Helvetica, Arial, sans-serif;
-webkit-font-smoothing: antialiased;
-moz-osx-font-smoothing: grayscale;
text-align: center;
color: #2c3e50;
}
</style>
Income Statement Code
To start writing the code for this page, we can add template and style sections, with template being the actual structure for the page. The style will be the styling of the sections in that template:
<template>
<div class="financialKPI">
<gc-spread-sheets :hostClass='hostClass' @workbookInitialized='spreadInitHandle'>
</gc-spread-sheets>
<input type="file" class="fileSelect" @change='fileChange($event)' />
</div>
</template>
<!-- Add "scoped" attribute to limit CSS to this component only -->
<style scoped>
.spread-host {
width: 100%;
height: 500px;
border: 1px solid lightgray;
}
.fileSelect {
width: 100%;
margin-top: 20px;
}
</style>
The next step is to add a script tag where we'll write all of the code for this sample. Let's start by importing the CSS and script files that we installed in the project via NPM, as well as setting our license key:
<script>
import '@grapecity/spread-sheets/styles/gc.spread.sheets.excel2016colorful.css'
import '@grapecity/spread-sheets-vue'
import * as GC from '@grapecity/spread-sheets'
import Excel from '@grapecity/spread-excelio'
import Charts from '@grapecity/spread-sheets-charts'
GC.Spread.Sheets.LicenseKey = <YOUR LICENSE KEY HERE>;
</script>
Next, let's locally register our component:
export default {
name: 'IncomeStatement',
data () {
return {
hostClass : 'spread-host'
}
}
}
After the data section, we can add the "methods" section in which we'll define the "spreadInitHandle" function that initializes SpreadJS, and "fileChange", where most of our code will go into:
methods: {
spreadInitHandle: function (spread) {
this._spread = spread;
},
fileChange: function (e) {
if (this._spread) {
let fileDom = e.target || e.srcElement;
let excelIO = new Excel.IO();
let spread = this._spread;
}
}
}
Once Spread is initialized in the function, we can run code when the user selects a file to load into Spread in the "fileChange" function. We'll set some variables for easier access later on, and then use the ExcelIO open function to load an Excel file into SpreadJS:
excelIO.open(fileDom.files[0], (data) => {
spread.fromJSON(data);
let sheet = spread.getSheet(1);
})
In the Excel file I'm using, I want to show a chart below the data, but the data takes up a lot of the screen space, so I'll group and collapse a few of the rows like so:
sheet.rowOutlines.group(14, 2);
sheet.rowOutlines.group(20, 6);
sheet.rowOutlines.group(30, 3);
sheet.rowOutlines.expand(0, false);
sheet.addRows(38, 20);
Now we are ready to add the chart, so we can reference the entire data area for the revenue area of the sheet:
<pre> <code="language-javascript">var chart = sheet.charts.add('RevenueChart', GC.Spread.Sheets.Charts.ChartType.columnClustered, 15, 600, 1600, 400, "E8:P12", GC.Spread.Sheets.Charts.RowCol.rows);</code="language-javascript"> </pre>
We can quickly set the title of the chart:
var title = chart.title();
title.text = "Revenue";
title.fontFamily = "Cambria";
title.fontSize = 28;
title.color = "#696969";
chart.title(title);
The next and final task in creating the chart is to define the names of the series in the chart. We will get from the sheet itself via cell references. In this case, there is an empty row in the data we selected that doesn't need to be included in the chart, so we can remove that particular series:
var series = chart.series();
series.remove(0);
var productSeries = series.get(0);
productSeries.name = "Sheet1!$B$10";
var servicesSeries = series.get(1);
servicesSeries.name = "Sheet1!$B$11";
var otherSeries = series.get(2);
otherSeries.name = "Sheet1!$B$12";
chart.series().set(0, productSeries);
chart.series().set(1, servicesSeries);
chart.series().set(2, otherSeries);
This is a simple use case using Spread.Sheets in a Vue application, but you can do so much more using this JavaScript spreadsheet component. With the addition of the Spread.Sheets Vue package, it's easy to add Spread.Sheets within your Vue app.
Kevin Ashley