Vari

Use the VariSparkline function to compare two sets of data and calculate the difference, or variance, between the values.

Description
app.component.ts
index.html
app.component.html
styles.css
Copy to CodeMine

You can create a Variance sparkline using the VariSparkline function in a formula: =VARISPARKLINE(variance, reference?, mini?, maxi?, mark?, tickunit?, legend?, colorPositive?, colorNegative?, vertical?).

The function has the following parameters:

  • variance: Number or reference that represents the length of the bar, such as 2 or "A1".

  • reference: (optional) Number or reference that represents the location of the reference line, such as 0 or "A2"; default value is 0.

  • mini: (optional) Number or reference that represents the minimum values of the sparkline, such as -5 or "A3"; default value is -1.

  • maxi: (optional) Number or reference that represents the maximum values of the sparkline, such as 5 or "A4"; default value is 1.

  • mark: (optional) Number or reference that represents the position of the mark line, such as 3 or "A5"; default value is 0.

  • tickunit: (optional) Number or reference that represents the tick unit, such as 1 or "A6"; default value is 0.

  • legend: (optional) Boolean that represents whether to display the text. The default value is false.

  • colorPositive: (optional) String that represents the color scheme for when the variance is larger than the reference; default value is "green".

  • colorNegative: (optional) String that represents the color scheme for when the variance is smaller than the reference; default value is "red".

  • vertical: (optional) Boolean that represents whether the box's direction is vertical or horizontal; default value is false.

You can create a Variance sparkline using the VariSparkline function in a formula: =VARISPARKLINE(variance, reference?, mini?, maxi?, mark?, tickunit?, legend?, colorPositive?, colorNegative?, vertical?). The function has the following parameters: variance: Number or reference that represents the length of the bar, such as 2 or "A1". reference: (optional) Number or reference that represents the location of the reference line, such as 0 or "A2"; default value is 0. mini: (optional) Number or reference that represents the minimum values of the sparkline, such as -5 or "A3"; default value is -1. maxi: (optional) Number or reference that represents the maximum values of the sparkline, such as 5 or "A4"; default value is 1. mark: (optional) Number or reference that represents the position of the mark line, such as 3 or "A5"; default value is 0. tickunit: (optional) Number or reference that represents the tick unit, such as 1 or "A6"; default value is 0. legend: (optional) Boolean that represents whether to display the text. The default value is false. colorPositive: (optional) String that represents the color scheme for when the variance is larger than the reference; default value is "green". colorNegative: (optional) String that represents the color scheme for when the variance is smaller than the reference; default value is "red". vertical: (optional) Boolean that represents whether the box's direction is vertical or horizontal; default value is false.
import { Component, NgModule, enableProdMode } from '@angular/core'; import { BrowserModule } from '@angular/platform-browser'; import { platformBrowserDynamic } from '@angular/platform-browser-dynamic'; import { SpreadSheetsModule } from '@mescius/spread-sheets-angular'; import GC from '@mescius/spread-sheets'; import './styles.css'; const GCsheets = GC.Spread.Sheets; @Component({ selector: 'app-component', templateUrl: 'src/app.component.html' }) export class AppComponent { spread: GC.Spread.Sheets.Workbook; hostStyle = { width: '100%', height: '100%', overflow: 'hidden', float: 'left' } initSpread($event: any) { this.spread = $event.spread; let spread = this.spread; let spreadNS = GC.Spread.Sheets; let sheet = spread.getSheet(0); let table1 = sheet.tables.add("table1", 0, 0, 13, 5, spreadNS.Tables.TableThemes.medium4); table1.rowFilter().filterButtonVisible(false); sheet.setArray(0, 0, [ ["Sales", "Budget", "Actual", "Variance", "Variance %"], ["Jan",305554 ,336431], ["Feb",280311 ,370582 ], ["Mar",309347 ,322689 ], ["Apr",502688 ,386790 ], ["May",351267 ,363122 ], ["Jun",351922 ,314589 ], ["Jul",364770 ,342515 ], ["Aug",373980 ,317154 ], ["Sep",397717 ,360083 ], ["Oct",275849 ,370909 ], ["Nov",426031 ,376791 ], ["Dec",267710 ,398198 ] ]); for (let i = 2; i < 14; i++) { sheet.setFormula((i-1),3,'=(C'+ i +'-B'+ i+')'); sheet.setFormula((i-1),4,'=VARISPARKLINE(ROUND((D'+ i+')/(B'+i+'),2),0,,,,0.2,TRUE)'); } //some cell formatting sheet.getRange(1, 1, 12, 3) .hAlign(GC.Spread.Sheets.HorizontalAlign.right) .formatter("$#,000;$-#,000"); sheet.getRange(1, 4, 12, 1) .hAlign(GC.Spread.Sheets.HorizontalAlign.right) .formatter("0%"); sheet.setColumnWidth(1, 120); sheet.setColumnWidth(2, 120); sheet.setColumnWidth(3, 120); sheet.setColumnWidth(4, 200); } } @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/@mescius/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/fesm2015/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.import('@angular/compiler'); 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)" [newTabVisible] = false> <gc-worksheet></gc-worksheet> </gc-spread-sheets> </div>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-spreadsheets { height: 100%; }
(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/fesm2015/zone.min.js', 'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js', '@angular/core': 'npm:@angular/core/fesm2022', '@angular/common': 'npm:@angular/common/fesm2022/common.mjs', '@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs', '@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs', '@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs', '@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs', '@angular/router': 'npm:@angular/router/fesm2022/router.mjs', '@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs', 'jszip': 'npm:jszip/dist/jszip.min.js', 'typescript': 'npm:typescript/lib/typescript.js', 'ts': './plugin.js', 'tslib':'npm:tslib/tslib.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', '@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js', '@mescius/spread-sheets-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs', '@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' }, "node_modules/@angular": { defaultExtension: 'mjs' }, "@mescius/spread-sheets-angular": { defaultExtension: 'mjs' }, '@angular/core': { defaultExtension: 'mjs', main: 'core.mjs' } } }); })(this);