To create a sparkline, use the setSparkline method to set the sparkline for a cell. You can use the getSparkline method to get the sparkline. For example:
There are three types of sparklines. The SparklineType enumeration represents the sparkline type.
line
column
winloss
You can use all three sparklines above in Excel. But for the other sparklines, such as Compatible sparkline, are not supported in Excel unless you get an add-in extension which provides that support.
If you want to remove the sparkline, use the removeSparkline method to remove the sparkline for the specified cell. For example:
You also can use formula to create a sparkline, please see Compatible for details.
import { Component, NgModule, enableProdMode } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { SpreadSheetsModule } from '@grapecity/spread-sheets-angular';
import GC from '@grapecity/spread-sheets';
import './styles.css';
@Component({
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
position = {
row: 0,
col: 0,
rowCol: '0,0'
};
type = 0;
orientation = 0;
spread: GC.Spread.Sheets.Workbook;
hostStyle = {
width: 'calc(100% - 280px)',
height: '100%',
overflow: 'hidden',
float: 'left'
};
clearSprikline() {
let sheet = this.spread.getActiveSheet();
let range = this.getActualCellRange(sheet.getSelections()[0], sheet.getRowCount(), sheet.getColumnCount());
for (let r = 0; r < range.rowCount; r++) {
for (let c = 0; c < range.colCount; c++) {
sheet.removeSparkline(r + range.row, c + range.col);
}
}
}
addSprikline() {
let sheet = this.spread.getActiveSheet();
let range = this.getActualCellRange(sheet.getSelections()[0], sheet.getRowCount(), sheet.getColumnCount());
let r = this.position.row;
let c = this.position.col;
let orientation = this.orientation;
let type = this.type;
if (!isNaN(r) && !isNaN(c)) {
sheet.setSparkline(r, c, range, orientation, type, new GC.Spread.Sheets.Sparklines.SparklineSetting());
}
}
setOrientation(e: any) {
this.orientation = parseInt(e.target.value);
}
setType(e: any) {
this.type = parseInt(e.target.value);
}
setPosition(e: any) {
let value = e.target.value, arr = value.split(','),
row = parseInt(arr[0]), col = parseInt(arr[1]);
this.position = {
row: row,
col: col,
rowCol: row + ',' + col
};
}
_selectOption(select: string, value: number) {
if (select === 'line_type') {
this.type = value;
} else {
this.orientation = value;
}
}
getActualCellRange(cellRange: any, rowCount: number, columnCount: number) {
if (cellRange.row == -1 && cellRange.col == -1) {
return new GC.Spread.Sheets.Range(0, 0, rowCount, columnCount);
}
else if (cellRange.row == -1) {
return new GC.Spread.Sheets.Range(0, cellRange.col, rowCount, cellRange.colCount);
}
else if (cellRange.col == -1) {
return new GC.Spread.Sheets.Range(cellRange.row, 0, cellRange.rowCount, columnCount);
}
return cellRange;
}
initSpread($event: any) {
this.spread = $event.spread;
let spread = this.spread, sheet = spread.getSheet(0);
let self = this;
sheet.suspendPaint();
sheet.setValue(0, 0, "Data Range is A2-A9");
sheet.setValue(1, 0, 1);
sheet.setValue(2, 0, -2);
sheet.setValue(3, 0, -1);
sheet.setValue(4, 0, 6);
sheet.setValue(5, 0, 4);
sheet.setValue(6, 0, -4);
sheet.setValue(7, 0, 3);
sheet.setValue(8, 0, 8);
sheet.setValue(0, 2, "Date axis range is C2-C9");
sheet.setValue(1, 2, new Date(2011, 0, 5));
sheet.setValue(2, 2, new Date(2011, 0, 1));
sheet.setValue(3, 2, new Date(2011, 1, 11));
sheet.setValue(4, 2, new Date(2011, 2, 1));
sheet.setValue(5, 2, new Date(2011, 1, 1));
sheet.setValue(6, 2, new Date(2011, 1, 3));
sheet.setValue(7, 2, new Date(2011, 2, 6));
sheet.setValue(8, 2, new Date(2011, 1, 19));
let data = new GC.Spread.Sheets.Range(1, 0, 8, 1);
let dateAxis = new GC.Spread.Sheets.Range(1, 2, 8, 1);
sheet.getCell(9, 0).text("Sparkline without dateAxis:");
sheet.getCell(10, 0).text("(1) Line");
sheet.getCell(10, 3).text("(2)Column");
sheet.getCell(10, 6).text("(3)Winloss");
//line
sheet.addSpan(11, 0, 4, 3);
let setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
setting.options.showMarkers = true;
setting.options.lineWeight = 3;
setting.options.displayXAxis = true;
setting.options.showFirst = true;
setting.options.showLast = true;
setting.options.showLow = true;
setting.options.showHigh = true;
setting.options.showNegative = true;
sheet.setSparkline(11, 0, data
, GC.Spread.Sheets.Sparklines.DataOrientation.vertical
, GC.Spread.Sheets.Sparklines.SparklineType.line
, setting
);
//column
sheet.addSpan(11, 3, 4, 3);
setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
setting.options.displayXAxis = true;
setting.options.showFirst = true;
setting.options.showLast = true;
setting.options.showLow = true;
setting.options.showHigh = true;
setting.options.showNegative = true;
sheet.setSparkline(11, 3, data
, GC.Spread.Sheets.Sparklines.DataOrientation.vertical
, GC.Spread.Sheets.Sparklines.SparklineType.column
, setting
);
//winloss
sheet.addSpan(11, 6, 4, 3);
setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
setting.options.displayXAxis = true;
setting.options.showNegative = true;
sheet.setSparkline(11, 6, data
, GC.Spread.Sheets.Sparklines.DataOrientation.vertical
, GC.Spread.Sheets.Sparklines.SparklineType.winloss
, setting
);
sheet.getCell(15, 0).text("Sparkline with dateAxis:");
sheet.getCell(16, 0).text("(1) Line");
sheet.getCell(16, 3).text("(2)Column");
sheet.getCell(16, 6).text("(3)Winloss");
//line
sheet.addSpan(17, 0, 4, 3);
setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
setting.options.showMarkers = true;
setting.options.lineWeight = 3;
setting.options.displayXAxis = true;
setting.options.showFirst = true;
setting.options.showLast = true;
setting.options.showLow = true;
setting.options.showHigh = true;
setting.options.showNegative = true;
sheet.setSparkline(17, 0, data
, GC.Spread.Sheets.Sparklines.DataOrientation.vertical
, GC.Spread.Sheets.Sparklines.SparklineType.line
, setting
, dateAxis
, GC.Spread.Sheets.Sparklines.DataOrientation.vertical
);
//column
sheet.addSpan(17, 3, 4, 3);
setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
setting.options.displayXAxis = true;
setting.options.showFirst = true;
setting.options.showLast = true;
setting.options.showLow = true;
setting.options.showHigh = true;
setting.options.showNegative = true;
sheet.setSparkline(17, 3, data
, GC.Spread.Sheets.Sparklines.DataOrientation.vertical
, GC.Spread.Sheets.Sparklines.SparklineType.column
, setting
, dateAxis
, GC.Spread.Sheets.Sparklines.DataOrientation.vertical
);
//winloss
sheet.addSpan(17, 6, 4, 3);
setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
setting.options.displayXAxis = true;
setting.options.showNegative = true;
sheet.setSparkline(17, 6, data
, GC.Spread.Sheets.Sparklines.DataOrientation.vertical
, GC.Spread.Sheets.Sparklines.SparklineType.winloss
, setting
, dateAxis
, GC.Spread.Sheets.Sparklines.DataOrientation.vertical
);
sheet.bind(GC.Spread.Sheets.Events.SelectionChanged, selectionChangedCallback);
sheet.resumePaint();
function selectionChangedCallback() {
let sheet = spread.getActiveSheet();
let sparkline = sheet.getSparkline(sheet.getActiveRowIndex(), sheet.getActiveColumnIndex());
if (sparkline) {
updateSetting(sparkline);
} else {
initSetting();
}
}
function updateSetting(sparkline: any) {
let type = sparkline.sparklineType(), orientation = sparkline.dataOrientation(),
row = sparkline.row, column = sparkline.column;
self.position = {
row: row,
col: column,
rowCol: row + ',' + column
};
self._selectOption('line_type', type);
self._selectOption('line_orientation', orientation);
}
function initSetting() {
self.position = {
row: undefined,
col: undefined,
rowCol: ''
};
self._selectOption('line_type', 0);
self._selectOption('line_orientation', 0);
}
}
}
@NgModule({
imports: [BrowserModule, SpreadSheetsModule],
declarations: [AppComponent],
exports: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule { }
enableProdMode();
// Bootstrap application with hash style navigation and global services.
platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- Polyfills -->
<script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/en/angular/node_modules/zone.js/dist/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script>
<script src="systemjs.config.js"></script>
<script>
// workaround to load 'rxjs/operators' from the rxjs bundle
System.import('rxjs').then(function (m) {
System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators));
System.import('$DEMOROOT$/en/lib/angular/license.ts');
System.import('./src/app.component');
});
</script>
</head>
<body>
<app-component></app-component>
</body>
</html>
<div class="sample-tutorial">
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)">
<gc-worksheet [allowCellOverflow] = true>
</gc-worksheet>
</gc-spread-sheets>
<p>Add a cell sparkline by selecting the data range in the sheet, enter the destination cell row,column index (i.e.
“2,5”) in the “Position” field, change the type and orientation then click the “Add Sparkline” button.
You can also delete a sparkline by selecting it and clicking the “Clear Sparkline” button.</p>
<div class="options-container">
<div class="option-group">
<label for="line_position">Position:</label>
<input value="{{position.rowCol}}" id="line_position" (change)="setPosition($event)" />
</div>
<div class="option-group">
<label for="line_type" style="width: auto;">Type:</label>
<select id="line_type" class="position" value="{{type}}" (change)="setType($event)">
<option value="0">line</option>
<option value="1">column</option>
<option value="2">winloss</option>
</select>
</div>
<div class="option-group">
<label for="line_orientation">Orientation:</label>
<select id="line_orientation" class="position" value="{{orientation}}" (change)="setOrientation($event)">
<option value="0">Vertical</option>
<option value="1">Horizontal</option>
</select>
</div>
<div class="option-group">
<input type="button" value="Add Sparkline" id="btnAddSparkline" (click)="addSprikline($event)">
<input type="button" value="Clear Sparkline" id="btnClearSparkline" (click)="clearSprikline($event)">
</div>
</div>
</div>
.sample {
position: relative;
height: 100%;
overflow: auto;
}
.sample::after {
display: block;
content: "";
clear: both;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
p{
padding:2px 10px;
background-color:lavender;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
.option-group {
margin-bottom: 8px;
}
input,
select {
margin-top: 6px;
padding: 4px 4px;
width: 100%;
box-sizing: border-box;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
(function (global) {
System.config({
transpiler: 'ts',
typescriptOptions: {
tsconfig: true
},
meta: {
'typescript': {
"exports": "ts"
},
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'core-js': 'npm:core-js/client/shim.min.js',
'zone': 'npm:zone.js/dist/zone.min.js',
'rxjs': 'npm:rxjs/bundles/rxjs.umd.min.js',
'@angular/core': 'npm:@angular/core/bundles/core.umd.min.js',
'@angular/common': 'npm:@angular/common/bundles/common.umd.min.js',
'@angular/compiler': 'npm:@angular/compiler/bundles/compiler.umd.min.js',
'@angular/platform-browser': 'npm:@angular/platform-browser/bundles/platform-browser.umd.min.js',
'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/bundles/platform-browser-dynamic.umd.min.js',
'@angular/http': 'npm:@angular/http/bundles/http.umd.min.js',
'@angular/common/http': 'npm:@angular/common/bundles/common-http.umd.min.js',
'@angular/router': 'npm:@angular/router/bundles/router.umd.min.js',
'@angular/forms': 'npm:@angular/forms/bundles/forms.umd.min.js',
'jszip': 'npm:jszip/dist/jszip.min.js',
'typescript': 'npm:typescript/lib/typescript.js',
'ts': 'npm:plugin-typescript/lib/plugin.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',
'@grapecity/spread-sheets': 'npm:@grapecity/spread-sheets/index.js',
'@grapecity/spread-sheets-angular': 'npm:@grapecity/spread-sheets-angular/bundles/grapecity-spread-sheets-angular.umd.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'ts'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);