SpreadJS 데모
기능별 학습
스파크라인
게이지 KPI

게이지 KPI

Gauge KPI 스파크라인은 KPI 값을 기준으로 메트릭의 성능을 시각화하는 데 사용할 수 있습니다. 예를 들어, 판매 목표액과 실제 판매액을 나타내거나 회사의 특정 목표를 보고할 수 있습니다.

수식에서 다음과 같이 GAUGEKPISPARKLINE 함수를 사용하여 gaugeKPI 스파크라인을 만들 수 있습니다. =GAUGEKPISPARKLINE(targetValue,currentValue,minValue,maxValue,showLabel?,targetValueLabel?,currentValueLabel?,minValueLabel?,maxValueLabel?,fontArray?,minAngle?,maxAngle?,radiusRatio?,gaugeType?,colorRange?). 이 함수에는 다음과 같은 매개 변수가 있습니다. targetValue: (필수) [숫자] gaugeKPI 스파크라인의 대상 값으로, 대상 값의 범위는 minValue와 maxValue 사이입니다. currentValue: (필수) [숫자] gaugeKPI 스파크라인의 현재 값으로, 현재 값의 범위는 minValue와 maxValue 사이입니다. minValue: (필수) [숫자] gaugeKPI 스파크라인의 최소값으로, minValue는 maxValue보다 작습니다. maxValue: (필수) [숫자] KPI 스파크라인의 최대값으로, maxValue는 minValue보다 큽니다. showLabel: (선택 사항) [부울] targetValue, currentValue, minValue 및 maxValue의 레이블을 표시할지 여부입니다. showLabel이 false이면 레이블이 표시되지 않습니다. showLabel이 true인데 셀 너비가 좁거나 셀 높이가 낮아 레이블 하나를 표시할 수 없는 경우 그래프와 다른 레이블을 표시하고, 셀의 너비와 높이가 충분히 넓어지고 높아질 때까지 그래프와 레이블을 모두 표시합니다. 기본값은 true입니다. targetValueLabel: (선택 사항) [문자열] 대상 값에 대해 표시되는 레이블입니다. 기본값은 targetValue와 동일합니다. currentValueLabel: (선택 사항) [문자열] 현재 값에 대해 표시되는 레이블입니다. 기본값은 currentValue와 동일합니다. minValueLabel: (선택 사항) [문자열] 최소값에 대해 표시되는 레이블입니다. 기본값은 minValue와 동일합니다. maxValueLabel: (선택 사항) [문자열] 최대값에 대해 표시되는 레이블입니다. 기본값은 maxValue와 동일합니다. fontArray: (선택 사항) [CalcArray] fontArray에는 CSS 글꼴 서식을 따르는 글꼴 문자열 항목이 4개 있으며, 각 글꼴 문자열은 대상 값 레이블(기본값: "16px Calibri"), 현재 값 레이블(기본값: "bold 22px Calibri"), 최소값 레이블(기본값: "12px Calibri"), 최대값 레이블(기본값: "12px Calibri")의 글꼴과 일치합니다. showLabel이 true인 경우에만 작동합니다. minAngle: (선택 사항) [숫자] 원 종류의 최소각 값으로, minAngle은 maxAngle보다 작아야 합니다. (0은 12시 위치를, -90은 9시 위치를, 90은 3시 위치를, -180과 180은 6시 위치를 나타냅니다). 기본값은 -90입니다. gaugeType이 0(원 종류)인 경우에만 작동합니다. 기본값은 -90입니다. maxAngle: (선택 사항) [숫자] 최대각 값으로, maxAngle은 minAngle보다 커야 합니다. (0은 12시 위치를, -90은 9시 위치를, 90은 3시 위치를, -180과 180은 6시 위치를 나타냅니다). 기본값은 90입니다. gaugeType이 0(원 종류)인 경우에만 작동합니다. 기본값은 90입니다. radiusRatio: (선택 사항) [숫자] 외부 원 반경으로 내부 원 반경을 나눈 값이 radiusRatio(0~1)입니다. 외부 원 반경 값은 셀 크기로 결정됩니다. gaugeType이 0(원 종류)인 경우에만 작동합니다. radiusRatio의 기본값은 0입니다. gaugeType: (선택 사항) [숫자] KPI 스파크라인 유형으로, 0은 원 종류를, 1은 세로 막대를, 2는 가로 막대를 나타냅니다. 기본값은 원 종류입니다. colorRange: (선택적으로 반복 가능) [CalcArray] 특별한 색상 범위입니다. 첫 번째 항목은 범위의 시작 값이고, 두 번째 항목은 범위의 종료 값입니다. 세 번째 항목은 startValue ~ endValue 범위의 색입니다. 시작 값은 종료 값보다 작아야 하고 모두 minValue ~ maxValue 사이에 있어야 합니다. 기본 색 범위는 밝은 회색으로 minValue에서 maxValue까지 채우기입니다.
<template> <div class="sample-tutorial"> <gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread"> <gc-worksheet> </gc-worksheet> <gc-worksheet> </gc-worksheet> <gc-worksheet> </gc-worksheet> </gc-spread-sheets> <div class="options-container"> <div class="option-row"> <label for="color1">Gauge KPI Settings</label> </div> <hr> <div class="option-row"> <label for="barSize"><u>Min Value:</u></label> <input type="text" v-model="minValue" v-on:change = "applyChanges"/> </div> <div class="option-row"> <label for="barSize"><u>Max Value:</u></label> <input type="text" v-model="maxValue" v-on:change = "applyChanges"/> </div> <hr> <div class="option-row"> <label for="range1"><u>Range 1</u></label> </div> <div class="option-row"> <label for="colorScheme">Color: </label> <select v-model="color1" v-on:change = "applyChanges" > <option value="#FFFFFF">White</option> <option value="#000000">Black</option> <option value="#F7A711" selected>Orange</option> <option value="#DDDDDD">LightGrey</option> <option value="#BBBBBB">Grey</option> <option value="#999999">DarkGrey</option> <option value="#82BC00">Green</option> </select> </div> <div class="option-row"> <label for="startValue1">Start Value:</label> <input type="text" v-model="startValue1" disabled/> </div> <div class="option-row"> <label for="barSize">End Value:</label> <input type="text" v-model="endValue1" v-on:change = "applyChanges"/> </div> <hr> <div class="option-row"> <label for="range1"><u>Range 2</u></label> </div> <div class="option-row"> <label for="colorScheme">Color: </label> <select v-model="color2" v-on:change = "applyChanges" > <option value="#FFFFFF">White</option> <option value="#000000">Black</option> <option value="#F7A711">Orange</option> <option value="#DDDDDD">LightGrey</option> <option value="#BBBBBB" selected>Grey</option> <option value="#999999">DarkGrey</option> <option value="#82BC00">Green</option> </select> </div> <div class="option-row"> <label for="startValue2">Start Value:</label> <input type="text" v-model="startValue2" disabled/> </div> <div class="option-row"> <label for="barSize">End Value:</label> <input type="text" v-model="endValue2" v-on:change = "applyChanges"/> </div> <hr> <div class="option-row"> <label for="range3"><u>Range 3</u></label> </div> <div class="option-row"> <label for="color3">Color: </label> <select id="color3" v-model="color3" v-on:change = "applyChanges" > <option value="#FFFFFF">White</option> <option value="#000000">Black</option> <option value="#F7A711">Orange</option> <option value="#DDDDDD">LightGrey</option> <option value="#BBBBBB">Grey</option> <option value="#999999">DarkGrey</option> <option value="#82BC00" selected>Green</option> </select> </div> <div class="option-row"> <label for="startValue1">Start Value:</label> <input type="text" v-model="startValue3" disabled/> </div> <div class="option-row"> <label for="barSize">End Value:</label> <input type="text" v-model="endValue3" disabled/> </div> </div> </div> </template> <script> import Vue from 'vue'; import '@mescius/spread-sheets-vue'; import GC from '@mescius/spread-sheets'; import '@mescius/spread-sheets-resources-ko'; GC.Spread.Common.CultureManager.culture("ko-kr"); import './styles.css'; let App = Vue.extend({ name:"app", data: function() { return {minValue : 0, maxValue : 50000, color1 : '#F7A711', startValue1 : 0, endValue1 : 10000, color2 : '#BBBBBB', startValue2 : 10000, endValue2 : 25000, color3 : '#82BC00', startValue3 : 25000, endValue3 : 50000, spread: null }; }, methods: { applyChanges() { let sheet = this.spread.getActiveSheet(); this.startValue1 = this.minValue; this.startValue2 = this.endValue1; this.startValue3 = this.endValue2; this.endValue3 = this.maxValue; var gaugeType = this.spread.getActiveSheetIndex(); // same as sheet index var start = 1,end=7; if(gaugeType == 2) { start = 2; end = 8; } for (var i = start; i < end; i++) { var goalcolumn = String.fromCharCode(65 + i)+"3"; var actualcolumn = String.fromCharCode(65 + i)+"4"; var row = 4; var column = i; if(gaugeType == 2)//for horizontal gauge { goalcolumn = "B"+(i+1); actualcolumn = "C"+(i+1); row=i; column=3; } sheet.setFormula(row,column,'=GAUGEKPISPARKLINE('+goalcolumn+','+actualcolumn+','+this.minValue +','+this.maxValue +',TRUE,TEXT('+goalcolumn+',"$0,K"),TEXT('+actualcolumn+',"$0,K"),TEXT('+this.minValue +',"$0,K"),TEXT('+this.maxValue +',"$0,K"),,-90,90,0.5,'+gaugeType+',{' + this.startValue1 +','+this.endValue1 +',"'+this.color1 +'"},{' + this.startValue2 +','+this.endValue2 +',"'+this.color2 +'"},{' + this.startValue3 +','+this.endValue3 +',"'+this.color3+'"})'); } }, initSpread(spread) { this.spread=spread; spread.setSheetCount(3); spread.sheets[0].name("Circle"); spread.sheets[1].name("Vertical"); spread.sheets[2].name("Horizontal"); this.initGaugeKPISparklineCircle(spread.sheets[0]); this.initGaugeKPISparklineVertical(spread.sheets[1]); this.initGaugeKPISparklineHorizontal(spread.sheets[2]); }, initGaugeKPISparklineCircle(sheet) { sheet.suspendPaint(); sheet.setArray(1, 0, [ ["Teams","Team A","Team B","Team C","Team D","Team E","Team F"], ["Goal",25000,22000,45000,39000,49000,16000], ["Amount Raised",24000,23000,45500,29000,49500,25000], ["Diagram"] ]); //styling for(var i = 0; i<7; i++) { sheet.setColumnWidth(i, 150); } sheet.setRowHeight(4, 160); sheet.setRowHeight(0, 35); sheet.addSpan(0, 0, 1, 7); sheet.getCell(0, 0).value("Fundraising Teams KPI") .font("17px Arial") .vAlign(GC.Spread.Sheets.VerticalAlign.center) .backColor("gray") .foreColor("white"); sheet.getRange(1, 0, 4, 1) .font("bold 13px Arial") .setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { right: true }); sheet.getRange(1,1,3,6).hAlign(GC.Spread.Sheets.HorizontalAlign.center).formatter("$0,K"); sheet.setFormula(4,1,'=GAUGEKPISPARKLINE(B3,B4,0,50000,TRUE,TEXT(B3,"$0,K"),TEXT(B4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(4,2,'=GAUGEKPISPARKLINE(C3,C4,0,50000,TRUE,TEXT(C3,"$0,K"),TEXT(C4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(4,3,'=GAUGEKPISPARKLINE(D3,D4,0,50000,TRUE,TEXT(D3,"$0,K"),TEXT(D4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(4,4,'=GAUGEKPISPARKLINE(E3,E4,0,50000,TRUE,TEXT(E3,"$0,K"),TEXT(E4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(4,5,'=GAUGEKPISPARKLINE(F3,F4,0,50000,TRUE,TEXT(F3,"$0,K"),TEXT(F4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(4,6,'=GAUGEKPISPARKLINE(G3,G4,0,50000,TRUE,TEXT(G3,"$0,K"),TEXT(G4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,-90,90,0.5,0,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.resumePaint(); }, initGaugeKPISparklineVertical(sheet) { sheet.suspendPaint(); sheet.setArray(1, 0, [ ["Teams","Team A","Team B","Team C","Team D","Team E","Team F"], ["Goal",25000,22000,45000,39000,49000,16000], ["Amount Raised",24000,23000,45500,29000,49500,25000], ["Diagram"] ]); //styling for(var i = 0; i<7; i++) { sheet.setColumnWidth(i, 150); } sheet.setRowHeight(4, 160); sheet.setRowHeight(0, 35); sheet.addSpan(0, 0, 1, 7); sheet.getCell(0, 0).value("Fundraising Teams KPI") .font("17px Arial") .vAlign(GC.Spread.Sheets.VerticalAlign.center) .backColor("gray") .foreColor("white"); sheet.getRange(1, 0, 4, 1) .font("bold 13px Arial") .setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { right: true }); sheet.getRange(1,1,3,6).hAlign(GC.Spread.Sheets.HorizontalAlign.center).formatter("$0,K"); sheet.setFormula(4,1,'=GAUGEKPISPARKLINE(B3,B4,0,50000,TRUE,TEXT(B3,"$0,K"),TEXT(B4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(4,2,'=GAUGEKPISPARKLINE(C3,C4,0,50000,TRUE,TEXT(C3,"$0,K"),TEXT(C4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(4,3,'=GAUGEKPISPARKLINE(D3,D4,0,50000,TRUE,TEXT(D3,"$0,K"),TEXT(D4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(4,4,'=GAUGEKPISPARKLINE(E3,E4,0,50000,TRUE,TEXT(E3,"$0,K"),TEXT(E4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(4,5,'=GAUGEKPISPARKLINE(F3,F4,0,50000,TRUE,TEXT(F3,"$0,K"),TEXT(F4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(4,6,'=GAUGEKPISPARKLINE(G3,G4,0,50000,TRUE,TEXT(G3,"$0,K"),TEXT(G4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,1,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.resumePaint(); }, initGaugeKPISparklineHorizontal(sheet) { sheet.suspendPaint(); sheet.setArray(1, 0, [ ["Teams","Goal","Amount Raised","Diagram"], ["Team A",25000,24000],["Team B",22000,23000],["Team C",45000,45500], ["Team D",39000,29000],["Team E",49000,49500],["Team F",16000,25000] ]); //styling for(var i = 0; i<3; i++) { sheet.setColumnWidth(i, 150); } sheet.setRowHeight(0, 35); sheet.setRowHeight(1, 30); for(var i = 2; i<8; i++) { sheet.setRowHeight(i, 80); } sheet.setColumnWidth(3, 260); sheet.addSpan(0, 0, 1, 4); sheet.getCell(0, 0).value("Fundraising Teams KPI") .font("17px Arial") .vAlign(GC.Spread.Sheets.VerticalAlign.center) .backColor("gray") .foreColor("white"); sheet.getRange(1, 0, 1, 4) .font("bold 13px Arial") .setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), { bottom: true }); sheet.getRange(2,1,6,3).hAlign(GC.Spread.Sheets.HorizontalAlign.center).formatter("$0,K"); sheet.getRange(1,0,7,4).vAlign(GC.Spread.Sheets.VerticalAlign.center); sheet.setFormula(2,3,'=GAUGEKPISPARKLINE(B3,C3,0,50000,TRUE,TEXT(B3,"$0,K"),TEXT(C3,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(3,3,'=GAUGEKPISPARKLINE(B4,C4,0,50000,TRUE,TEXT(B4,"$0,K"),TEXT(C4,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(4,3,'=GAUGEKPISPARKLINE(B5,C5,0,50000,TRUE,TEXT(B5,"$0,K"),TEXT(C5,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(5,3,'=GAUGEKPISPARKLINE(B6,C6,0,50000,TRUE,TEXT(B6,"$0,K"),TEXT(C6,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(6,3,'=GAUGEKPISPARKLINE(B7,C7,0,50000,TRUE,TEXT(B7,"$0,K"),TEXT(C7,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.setFormula(7,3,'=GAUGEKPISPARKLINE(B8,C8,0,50000,TRUE,TEXT(B8,"$0,K"),TEXT(C8,"$0,K"),TEXT(0,"$0,K"),TEXT(50000,"$0,K"),,0,0,0,2,{0,10000,"#F7A711"},{10000,25000,"#BBBBBB"},{25000,50000,"#82BC00"})'); sheet.resumePaint(); }, } }) 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$/ko/vue/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- SystemJS --> <script src="$DEMOROOT$/ko/vue/node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('./src/app.vue'); System.import('$DEMOROOT$/ko/lib/vue/license.js'); </script> </head> <body> <div id="app" style="height: 100%;"></div> </body> </html>
.sample { position: relative; height: 100%; overflow: auto; } .sample::after { display: block; content: ""; clear: both; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } .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: 6px; } label { display: inline-block; min-width: 90px; margin-bottom: 6px; } select { padding: 4px 6px; box-sizing: border-box; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }
(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', '@mescius/spread-sheets-resources-ko': 'npm:@mescius/spread-sheets-resources-ko/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);