HBar and VBar

The HBar and VBar sparklines can be used to show variations or ranges in data.

Description
app.vue
index.html
styles.css
Copy to CodeMine

You can create an HBar or VBar sparkline using the HBarSparkline or VBarSparkline function in a formula: =HBARSPARKLINE(value, colorScheme?, axisVisible?, barHeight?)* or *=VBARSPARKLINE(value, colorScheme?, axisVisible?, barWidth?).

The functions have the following parameters:

  • value: Number or reference that represents the length of the bar, such as 0.3 or "A1". If value is greater than 100% or smaller than -100%, an arrow is displayed.

  • colorScheme: (optional) String that represents the color of the bar; default value is "gray".

  • axisVisible: (optional) Boolean that represents whether to show the axis, default value is true.

  • barHeight: (optional) Number that represents the percentage of bar height according to the cell height. (value > 0 && value <= 1)

  • barWidth: (optional) Number that represents the percentage of bar width according to the cell width. (value > 0 && value <= 1)

HBarSparkline: Positive values, sparkline starts at left of cell; negative values, sparkline starts at right of cell.

VBarSparkline: Positive values, sparkline starts at bottom of cell; negative values, sparkline starts at top of cell.

You can create an HBar or VBar sparkline using the HBarSparkline or VBarSparkline function in a formula: =HBARSPARKLINE(value, colorScheme?, axisVisible?, barHeight?)* or *=VBARSPARKLINE(value, colorScheme?, axisVisible?, barWidth?). The functions have the following parameters: value: Number or reference that represents the length of the bar, such as 0.3 or "A1". If value is greater than 100% or smaller than -100%, an arrow is displayed. colorScheme: (optional) String that represents the color of the bar; default value is "gray". axisVisible: (optional) Boolean that represents whether to show the axis, default value is true. barHeight: (optional) Number that represents the percentage of bar height according to the cell height. (value > 0 && value <= 1) barWidth: (optional) Number that represents the percentage of bar width according to the cell width. (value > 0 && value <= 1) HBarSparkline: Positive values, sparkline starts at left of cell; negative values, sparkline starts at right of cell. VBarSparkline: Positive values, sparkline starts at bottom of cell; negative values, sparkline starts at top of cell.
<template> <div class="sample-tutorial"> <gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread" :newTabVisible = false> <gc-worksheet></gc-worksheet> <gc-worksheet></gc-worksheet> </gc-spread-sheets> </div> </template> <script> import Vue from 'vue'; import '@mescius/spread-sheets-vue' import GC from '@mescius/spread-sheets'; import './styles.css'; let App = Vue.extend({ name: "app", data: function() { return { spread: null }; }, methods:{ initHorizontalSparkline(sheet, name) { sheet.suspendPaint(); sheet.name(name); sheet.setArray(1,0,[["Channel","Satisfaction %", "Diagram"], ["Email",0.92], ["Social Networks",0.79], ["Forums/Message Boards",0.65], ["Phone",0.58], ["Text Message",0.55], ["Online Chat",0.39], ["Self-Serve Knowledge Base",0.28], ["On-Page Support Widget",0.19]]); for (var i = 2; i < 10; i++) { sheet.setRowHeight(i, 30); sheet.setFormula(i, 2, this.getFormula("B"+(i+1))); } sheet.addSpan(0, 0, 1, 3); sheet.getCell(0, 0).value("Customer Service Satisfaction, by Channel").font("17px Arial").backColor("#E8E8E8").vAlign(GC.Spread.Sheets.VerticalAlign.center); sheet.getRange(1, 0, 1, 3).font("bold 13px Arial").vAlign(GC.Spread.Sheets.VerticalAlign.center) .setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { bottom: true }); sheet.getRange(2,1,8,1).formatter("0%").hAlign(GC.Spread.Sheets.HorizontalAlign.left); sheet.setRowHeight(0, 35); sheet.setRowHeight(1, 35); sheet.setColumnWidth(0, 250); sheet.setColumnWidth(1, 120); sheet.setColumnWidth(2, 250); sheet.resumePaint(); }, getFormula(range) { return "=LET(ref, "+range+", color, IF(ref >= 0.8, \"#092834\", IF(ref>=0.6, \"#347B98\", IF(ref >0.4, \"#66B032\", IF(ref >= 0.2, \"#B2D732\", IF(ref >= 0, \"#F0F7D4\", \"red\"))))), HBARSPARKLINE(ref, color))"; }, getVBarFormula(range) { return "=LET(ref, "+range+", color, IF(ref >= 0.8, \"#092834\", IF(ref>=0.6, \"#347B98\", IF(ref >0.4, \"#66B032\", IF(ref >= 0.2, \"#B2D732\", IF(ref >= 0, \"#F0F7D4\", \"red\"))))), VBARSPARKLINE(ref, color, true, 0.4))"; }, initVerticalSparkline(sheet, name) { sheet.suspendPaint(); sheet.name(name); sheet.setArray(1,0,[ ["Name","Employee 1","Employee 2","Employee 3","Employee 4","Employee 5","Employee 6","Employee 7","Employee 8"], ["Progress",1.1,0.79,0.59,0.55,0.37,0.19,0.35,0.60] ]); for (var i = 0; i < 8; i++) { var columnChar = String.fromCharCode(66 + i); sheet.setColumnWidth(i+1, 80); sheet.setFormula(3, i+1, this.getVBarFormula(columnChar + "3")); } sheet.addSpan(0, 0, 1, 9); sheet.getCell(0, 0).value("Sprint 3 Progress").font("17px Arial").backColor("#E8E8E8").vAlign(GC.Spread.Sheets.VerticalAlign.center); sheet.getRange(1, 0, 2, 1).font("bold 13px Arial").vAlign(GC.Spread.Sheets.VerticalAlign.center) .setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { right: true }); sheet.getRange(2,1,1,8).formatter("0%").hAlign(GC.Spread.Sheets.HorizontalAlign.left); sheet.setRowHeight(0, 35); sheet.setRowHeight(1, 35); sheet.setRowHeight(3, 220); sheet.setColumnWidth(0, 120); sheet.resumePaint(); }, initSpread(spread) { this.spread = spread; this.initHorizontalSparkline(spread.sheets[0], "Horizontal"); this.initVerticalSparkline(spread.sheets[1], "Vertical"); } } }); new Vue({ render: h => h(App) }).$mount('#app'); </script>
<!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/vue/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- SystemJS --> <script src="$DEMOROOT$/en/vue/node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app.vue'); System.import('$DEMOROOT$/en/lib/vue/license.js'); </script> </head> <body> <div id="app" style="height: 100%;"></div> </body> </html>
.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: 'plugin-babel', babelOptions: { es2015: true }, meta: { '*.css': { loader: 'css' }, '*.vue': { loader: 'vue-loader' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { '@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js', '@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js', 'jszip': 'npm:jszip/dist/jszip.js', 'css': 'npm:systemjs-plugin-css/css.js', 'vue': 'npm:vue/dist/vue.min.js', 'vue-loader': 'npm:systemjs-vue-browser/index.js', 'tiny-emitter': 'npm:tiny-emitter/index.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: 'js' }, rxjs: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' } } }); })(this);