Trendline is an additional line that indicates the slope (or trend) in a particular data series and is also known as a line of best fit.
Trendlines can be helpful when you are analyzing data because they can forecast future values based upon your current data.
Users can create 6 different types of trendlines for their charts: including linear, exponential, logarithmic, polynomial, power, and moving average.
Linear: a best fit straight line for simple linear data sets.
Exponential: a best-fit curved line that illustrates how data values increase or decrease and then level out.
Logarithmic: a best-fit curved line that illustrates the data increases or decreases quickly and then levels out.
Polynomial: a curved line illustrating fluctuations in the data values based on the order property.
Power: a curved line to compare measurements that increase at a specific rate.
MovingAverage: averages a specific number of data points, and uses the value as a point in the line.
Trendline supports the following chart types:
Column
Bar
Line
Scatter
Area
You can create a linear trendline using the following code:
Customize
order: Specify the number of terms in the Polynomial equation. The order is a integer with range 2 to 6.
intercept: Specify the intercept for linear, exponential, polynomial trendline.
displayEquation & displayRSquared: Specify whether to use equation or R squared for the trendline.
forward & backward: use forward or backward to project the data.
The displayEquation, displayRSquared, forward, backward supports linear, exponential, logarithmic, polynomial, power trendline.
style: Specify the line style of the trendline, including color, width, and dash line.
name: Specify the name of the trendline. The built-in name will be used if no name is provided.
Period: Specify the period of the MovingAverage Trendline. The order is a integer with range 2 to data set count minus 1.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import './styles.css';
import { AppFunc } from './app-func';
// import { App } from './app-class';
// 1. Functional Component sample
createRoot(document.getElementById('app')).render(<AppFunc />);
// 2. Class Component sample
// createRoot(document.getElementById('app')).render(<App />);
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import '@mescius/spread-sheets-shapes';
import '@mescius/spread-sheets-charts';
const advData = [
['Advertising', 'Items sold'],
[28, 17],
[34, 19],
[41, 18],
[47, 20],
[52, 24],
[59, 26],
[65, 29],
[72, 31],
[80, 34],
[87, 39],
[94, 40],
[102, 42],
];
const salesData = [
['', 'Sales'],
['Jan', 54],
['Feb', 60],
['Mar', 86],
['Apr', 92],
['May', 112],
['Jun', 157],
['Jul', 202],
['Aug', 195],
['Sep', 187],
['Oct', 194],
['Nov', 238],
['Dec', 289],
];
export function AppFunc() {
let spread = null;
const initSpread = (currSpread) => {
spread = currSpread;
spread.setSheetCount(2);
initSheet1();
initSheet2();
}
const initSheet1 = () => {
let sheet1 = spread.getSheet(0);
sheet1.name('Basic');
sheet1.setArray(0, 0, advData);
sheet1.setArray(18, 0, salesData);
for (let i = 0; i < 12; i++) {
sheet1.getCell(i + 1, 0).formatter('$#,##0');
}
// Choose a suitable trendline type from GC.Spread.Sheets.Charts.TrendlineType to fit your chart
let chart1 = sheet1.charts.add("chart1", GC.Spread.Sheets.Charts.ChartType.xyScatter, 130, 5, 500, 350, "A1:B13", GC.Spread.Sheets.Charts.RowCol.columns);
let axes = chart1.axes();
axes.primaryValue.title.text = 'Items sold';
axes.primaryCategory.title.text = 'Advertising';
axes.primaryCategory.majorGridLine.visible = true;
axes.primaryCategory.majorUnit = 10;
chart1.axes(axes);
let targetSeriesIndex = 0;
let targetSeries = chart1.series().get(targetSeriesIndex);
let linearTrendline = {
type: GC.Spread.Sheets.Charts.TrendlineType.linear,
style: {
color: 'red',
width: 2
}
};
targetSeries.trendlines = [linearTrendline];
chart1.series().set(targetSeriesIndex, targetSeries);
let chart2 = sheet1.charts.add("chart2", GC.Spread.Sheets.Charts.ChartType.columnClustered, 130, 360, 500, 350, "A19:B31", GC.Spread.Sheets.Charts.RowCol.columns);
targetSeriesIndex = 0;
targetSeries = chart2.series().get(targetSeriesIndex);
let exponentialTrendline = {
type: GC.Spread.Sheets.Charts.TrendlineType.exponential,
style: {
color: 'orange',
width: 2,
dashStyle: GC.Spread.Sheets.Charts.LineDashStyle.dash
}
};
targetSeries.trendlines = [exponentialTrendline];
chart2.series().set(targetSeriesIndex, targetSeries);
}
const initSheet2 = () => {
// More settings
let sheet2 = spread.getSheet(1);
sheet2.name('Advance');
sheet2.setArray(0, 0, advData);
sheet2.setArray(18, 0, salesData);
for (let i = 0; i < 12; i++) {
sheet2.getCell(i + 1, 0).formatter('$#,##0');
}
// Change the order(the highest power for the independent variable) of polynomial trendline to adjust R-squared value
// Also you could show the equation and R-squared value in chart area if you want
let chart3 = sheet2.charts.add("chart3", GC.Spread.Sheets.Charts.ChartType.xyScatter, 130, 5, 500, 350, "A1:B13", GC.Spread.Sheets.Charts.RowCol.columns);
let axes = chart3.axes();
axes.primaryValue.title.text = 'Items sold';
axes.primaryCategory.title.text = 'Advertising';
axes.primaryCategory.majorGridLine.visible = true;
axes.primaryCategory.majorUnit = 10;
chart3.axes(axes);
let targetSeriesIndex = 0;
let targetSeries = chart3.series().get(targetSeriesIndex);
let polynomialTrendline = {
type: GC.Spread.Sheets.Charts.TrendlineType.polynomial,
order: 4,
displayEquation: true,
displayRSquared: true,
style: {
color: 'red',
width: 2
}
};
targetSeries.trendlines = [polynomialTrendline];
chart3.series().set(targetSeriesIndex, targetSeries);
// Set a value in the Forward and Backward fields to project your data into the future.
let chart4 = sheet2.charts.add("chart4", GC.Spread.Sheets.Charts.ChartType.columnClustered, 130, 360, 500, 350, "A19:B31", GC.Spread.Sheets.Charts.RowCol.columns);
targetSeriesIndex = 0;
targetSeries = chart4.series().get(targetSeriesIndex);
let exponentialTrendline = {
type: GC.Spread.Sheets.Charts.TrendlineType.exponential,
forward: 3,
style: {
color: 'orange',
width: 2,
dashStyle: GC.Spread.Sheets.Charts.LineDashStyle.dash
}
};
targetSeries.trendlines = [exponentialTrendline];
chart4.series().set(targetSeriesIndex, targetSeries);
}
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
<Worksheet />
</SpreadSheets>
</div>
</div>;
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import '@mescius/spread-sheets-shapes';
import '@mescius/spread-sheets-charts';
const Component = React.Component;
const advData = [
['Advertising', 'Items sold'],
[28, 17],
[34, 19],
[41, 18],
[47, 20],
[52, 24],
[59, 26],
[65, 29],
[72, 31],
[80, 34],
[87, 39],
[94, 40],
[102, 42],
];
const salesData = [
['', 'Sales'],
['Jan', 54],
['Feb', 60],
['Mar', 86],
['Apr', 92],
['May', 112],
['Jun', 157],
['Jul', 202],
['Aug', 195],
['Sep', 187],
['Oct', 194],
['Nov', 238],
['Dec', 289],
];
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
}
render() {
return <div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet />
</SpreadSheets>
</div>
</div>;
}
initSpread(spread) {
this.spread = spread;
spread.setSheetCount(2);
this.initSheet1();
this.initSheet2();
}
initSheet1() {
let sheet1 = this.spread.getSheet(0);
sheet1.name('Basic');
sheet1.setArray(0, 0, advData);
sheet1.setArray(18, 0, salesData);
for (let i = 0; i < 12; i++) {
sheet1.getCell(i + 1, 0).formatter('$#,##0');
}
// Choose a suitable trendline type from GC.Spread.Sheets.Charts.TrendlineType to fit your chart
let chart1 = sheet1.charts.add("chart1", GC.Spread.Sheets.Charts.ChartType.xyScatter, 130, 5, 500, 350, "A1:B13", GC.Spread.Sheets.Charts.RowCol.columns);
let axes = chart1.axes();
axes.primaryValue.title.text = 'Items sold';
axes.primaryCategory.title.text = 'Advertising';
axes.primaryCategory.majorGridLine.visible = true;
axes.primaryCategory.majorUnit = 10;
chart1.axes(axes);
let targetSeriesIndex = 0;
let targetSeries = chart1.series().get(targetSeriesIndex);
let linearTrendline = {
type: GC.Spread.Sheets.Charts.TrendlineType.linear,
style: {
color: 'red',
width: 2
}
};
targetSeries.trendlines = [linearTrendline];
chart1.series().set(targetSeriesIndex, targetSeries);
let chart2 = sheet1.charts.add("chart2", GC.Spread.Sheets.Charts.ChartType.columnClustered, 130, 360, 500, 350, "A19:B31", GC.Spread.Sheets.Charts.RowCol.columns);
targetSeriesIndex = 0;
targetSeries = chart2.series().get(targetSeriesIndex);
let exponentialTrendline = {
type: GC.Spread.Sheets.Charts.TrendlineType.exponential,
style: {
color: 'orange',
width: 2,
dashStyle: GC.Spread.Sheets.Charts.LineDashStyle.dash
}
};
targetSeries.trendlines = [exponentialTrendline];
chart2.series().set(targetSeriesIndex, targetSeries);
}
initSheet2() {
// More settings
let sheet2 = this.spread.getSheet(1);
sheet2.name('Advance');
sheet2.setArray(0, 0, advData);
sheet2.setArray(18, 0, salesData);
for (let i = 0; i < 12; i++) {
sheet2.getCell(i + 1, 0).formatter('$#,##0');
}
// Change the order(the highest power for the independent variable) of polynomial trendline to adjust R-squared value
// Also you could show the equation and R-squared value in chart area if you want
let chart3 = sheet2.charts.add("chart3", GC.Spread.Sheets.Charts.ChartType.xyScatter, 130, 5, 500, 350, "A1:B13", GC.Spread.Sheets.Charts.RowCol.columns);
let axes = chart3.axes();
axes.primaryValue.title.text = 'Items sold';
axes.primaryCategory.title.text = 'Advertising';
axes.primaryCategory.majorGridLine.visible = true;
axes.primaryCategory.majorUnit = 10;
chart3.axes(axes);
let targetSeriesIndex = 0;
let targetSeries = chart3.series().get(targetSeriesIndex);
let polynomialTrendline = {
type: GC.Spread.Sheets.Charts.TrendlineType.polynomial,
order: 4,
displayEquation: true,
displayRSquared: true,
style: {
color: 'red',
width: 2
}
};
targetSeries.trendlines = [polynomialTrendline];
chart3.series().set(targetSeriesIndex, targetSeries);
// Set a value in the Forward and Backward fields to project your data into the future.
let chart4 = sheet2.charts.add("chart4", GC.Spread.Sheets.Charts.ChartType.columnClustered, 130, 360, 500, 350, "A19:B31", GC.Spread.Sheets.Charts.RowCol.columns);
targetSeriesIndex = 0;
targetSeries = chart4.series().get(targetSeriesIndex);
let exponentialTrendline = {
type: GC.Spread.Sheets.Charts.TrendlineType.exponential,
forward: 3,
style: {
color: 'orange',
width: 2,
dashStyle: GC.Spread.Sheets.Charts.LineDashStyle.dash
}
};
targetSeries.trendlines = [exponentialTrendline];
chart4.series().set(targetSeriesIndex, targetSeries);
}
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height:100%;
overflow: hidden;
float: left;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js',
'@mescius/spread-sheets-charts': 'npm:@mescius/spread-sheets-charts/index.js',
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/cjs/react.production.js',
'react-dom': 'npm:react-dom/cjs/react-dom.production.js',
'react-dom/client': 'npm:react-dom/cjs/react-dom-client.production.js',
'scheduler': 'npm:scheduler/cjs/scheduler.production.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
