HBar and VBar

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

The demo is being dynamically compiled to support real-time code editing... For quicker access to features, switch to the "JavaScript" tab for a smoother experience! :)
Description
app.vue
index.html
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 setup> import { ref } from "vue"; import GC from "@mescius/spread-sheets"; import '@mescius/spread-sheets-vue' const spreadRef = ref(null); let initHorizontalSparkline = function (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, 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(); } let getFormula = function (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))"; } let getVBarFormula = function (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))"; } let initVerticalSparkline = function (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, 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(); } let initSpread = function (spread) { spreadRef.value = spread; initHorizontalSparkline(spread.sheets[0], "Horizontal"); initVerticalSparkline(spread.sheets[1], "Vertical"); } </script> <style scoped> #app { height: 100%; } .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; } </style>
<!DOCTYPE html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta http-equiv="X-UA-Compatible" content="IE=edge" /> <title>SpreadJS VUE</title> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script> <script src="$DEMOROOT$/spread/source/data/statement-for-cascade.js" type="text/javascript"></script> <script src="./systemjs.config.js"></script> <script src="./compiler.js" type="module"></script> <script> var System = SystemJS; System.import("./src/app.js"); System.import('$DEMOROOT$/en/lib/vue3/license.js'); </script> </head> <body> <div id="app"></div> </body> </html>
(function (global) { SystemJS.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, packageConfigPaths: [ './node_modules/*/package.json', "./node_modules/@mescius/*/package.json", "./node_modules/@babel/*/package.json", "./node_modules/@vue/*/package.json" ], map: { 'vue': "npm:vue/dist/vue.esm-browser.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", '@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js', '@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js' }, meta: { '*.css': { loader: 'systemjs-plugin-css' }, '*.vue': { loader: "../plugin-vue/index.js" } } }); })(this);