You can create a GaugeKPI sparkline using the GAUGEKPISPARKLINE function in a formula: =GAUGEKPISPARKLINE(targetValue,currentValue,minValue,maxValue,showLabel?,targetValueLabel?,currentValueLabel?,minValueLabel?,maxValueLabel?,fontArray?,minAngle?,maxAngle?,radiusRatio?,gaugeType?,colorRange?).
The function has the following parameters:
targetValue: (Required) [Number] The target value in the gaugeKPI sparkline, the target value is between minValue and maxValue.
currentValue: (Required) [Number] The current value in the gaugeKPI sparkline, the current value is between minValue and maxValue.
minValue: (Required) [Number] The min value in the gaugeKPI sparkline, the minValue is less than maxValue.
maxValue: (Required) [Number] The max value in the KPI sparkline. the maxValue is bigger than minValue.
showLabel: (optional) [Boolean] Whether should show the label of targetValue, currentValue, minValue and maxValue. If the showLabel is false, will never show the label. If the showLabel is true, when the cell width is not wide enough or the cell height is not high enough to show one label, will show the graph and other label, until the cell has enough width and height, will show both graph and label. The default value is true.
targetValueLabel: (optional) [String] The displayed label of target value. The default value is the same as the targetValue.
currentValueLabel: (optional) [String] The displayed label of current value. The default value is the same as the currentValue.
minValueLabel: (optional) [String] The displayed label of min value. The default value is the same as the minValue.
maxValueLabel: (optional) [String] The displayed label of max value. The default value is the same as the maxValue.
fontArray: (optional) [CalcArray] The fontArray have four font string items that follow the CSS font format, each font string matches font of target value label(default value is "16px Calibri"), current value label(default value is "bold 22px Calibri"), min value label(default value is "12px Calibri") and max value label(default value is "12px Calibri") label correspondingly. Will only work while the showLabel is true.
minAngle: (optional) [Number] The min angle value of circle type, the minAngle should be less than maxAngle. (0 is the 12 o'clock position, -90 is the 9 o'clock position, 90 is the 3 o'clock position, -180 and 180 is the 6 o'clock position). The default value is -90. Will only work while the gaugeType is 0(The circle type). The default value is -90.
maxAngle: (optional) [Number] The max angle value, the maxAngle should be bigger than minAngle. (0 is the 12 o'clock position, -90 is the 9 o'clock position, 90 is the 3 o'clock position, -180 and 180 is the 6 o'clock position). The default value is 90. Will only work while the gaugeType is 0(The circle type). The default value is 90.
radiusRatio: (optional) [Number] The inner circle radius divided by the outer circle radius is the radiusRatio(between 0 and 1), the outer circle radius value is decided by the cell size.Will only work while the gaugeType is 0(The circle type). The default value of radiusRatio is 0.
gaugeType: (optional) [Number] The KPI sprakline type, 0 stands the circle type, 1 stands the verticalBar and 2 stands the horizontalBar.The default type is circle type.
colorRange: (optional Repeatable) [CalcArray] The special color range. The first item is the start value of the range. The second item is the end value of the range. The third item is the color of the range between startValue and endValue. The start value should be less than the end value and both are between minValue and maxValue.The default color range is from minValue to maxValue filling with light grey color.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import './styles.css';
import { AppFunc } from './app-func';
// import { App } from './app-class';
// 1. Functional Component sample
createRoot(document.getElementById('app')).render(<AppFunc />);
// 2. Class Component sample
// createRoot(document.getElementById('app')).render(<App />);
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
let spread = null;
export function AppFunc() {
const [gaugeInfo, setGaugeInfo] = React.useState({
minValue: 0,
maxValue: 50000,
color1: '#F7A711',
startValue1: 0,
endValue1: 10000,
color2: '#BBBBBB',
startValue2: 10000,
endValue2: 25000,
color3: '#82BC00',
startValue3: 25000,
endValue3: 50000
});
React.useEffect(() => {
applyChanges();
}, [gaugeInfo]);
const maxValueChange = (e) => {
let value = e.target.value;
setGaugeInfo({
...gaugeInfo,
maxValue: value,
endValue3: value
});
}
const minValueChange = (e) => {
let value = e.target.value;
setGaugeInfo({
...gaugeInfo,
minValue: value,
startValue1: value
});
}
const endValue1Change = (e) => {
let value = e.target.value;
setGaugeInfo({
...gaugeInfo,
endValue1: value,
startValue2: value
});
}
const endValue2Change = (e) => {
let value = e.target.value;
setGaugeInfo({
...gaugeInfo,
endValue2: value,
startValue3: value
});
}
const color1Change = (e) => {
let value = e.target.value;
setGaugeInfo({
...gaugeInfo,
color1: value,
});
}
const color2Change = (e) => {
let value = e.target.value;
setGaugeInfo({
...gaugeInfo,
color2: value,
});
}
const color3Change = (e) => {
let value = e.target.value;
setGaugeInfo({
...gaugeInfo,
color3: value,
});
}
const applyChanges = () => {
let sheet = spread.getActiveSheet();
var gaugeType = 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 + ',' + gaugeInfo.minValue
+ ',' + gaugeInfo.maxValue
+ ',TRUE,TEXT(' + goalcolumn + ',"$0,K"),TEXT(' + actualcolumn + ',"$0,K"),TEXT(' + gaugeInfo.minValue
+ ',"$0,K"),TEXT(' + gaugeInfo.maxValue
+ ',"$0,K"),,-90,90,0.5,' + gaugeType + ',{'
+ gaugeInfo.startValue1 + ',' + gaugeInfo.endValue1 + ',"' + gaugeInfo.color1 + '"},{'
+ gaugeInfo.startValue2 + ',' + gaugeInfo.endValue2 + ',"' + gaugeInfo.color2 + '"},{'
+ gaugeInfo.startValue3 + ',' + gaugeInfo.endValue3 + ',"' + gaugeInfo.color3 + '"})');
}
}
const initSpread = (currSpread) => {
spread = currSpread;
spread.options.newTabVisible = false;
spread.setSheetCount(3);
spread.sheets[0].name("Circle");
spread.sheets[1].name("Vertical");
spread.sheets[2].name("Horizontal");
initGaugeKPISparklineCircle(spread.sheets[0]);
initGaugeKPISparklineVertical(spread.sheets[1]);
initGaugeKPISparklineHorizontal(spread.sheets[2]);
}
const 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();
}
const 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();
}
const 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();
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)} newTabVisible={false}>
<Worksheet>
</Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<Panel panelInfo={gaugeInfo}
maxValueChange={(e) => { maxValueChange(e) }}
minValueChange={(e) => { minValueChange(e) }}
endValue1Change={(e) => { endValue1Change(e) }}
endValue2Change={(e) => { endValue2Change(e) }}
color1Change={(e) => { color1Change(e) }}
color2Change={(e) => { color2Change(e) }}
color3Change={(e) => { color3Change(e) }}
/>
</div>
);
}
const Panel = (props) => {
const { panelInfo,
maxValueChange,
minValueChange,
endValue1Change,
endValue2Change,
color1Change,
color2Change,
color3Change } = props;
return (
<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" value={panelInfo.minValue} onChange={(e) => { minValueChange(e) }} />
</div>
<div class="option-row">
<label for="barSize"><u>Max Value:</u></label>
<input type="text" value={panelInfo.maxValue} onChange={(e) => { maxValueChange(e) }} />
</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 id="color1" value={panelInfo.color1} onChange={(e) => { color1Change(e) }} >
<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" id="startValue1" value={panelInfo.startValue1} disabled />
</div>
<div class="option-row">
<label for="barSize">End Value:</label>
<input type="text" id="endValue1" value={panelInfo.endValue1} onChange={(e) => { endValue1Change(e) }} />
</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 id="color2" value={panelInfo.color2} onChange={(e) => { color2Change(e) }}>
<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" id="startValue2" value={panelInfo.startValue2} disabled />
</div>
<div class="option-row">
<label for="barSize">End Value:</label>
<input type="text" id="endValue2" value={panelInfo.endValue2} onChange={(e) => { endValue2Change(e) }} />
</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" value={panelInfo.color3} onChange={(e) => { color3Change(e) }}>
<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" id="startValue3" value={panelInfo.startValue3} disabled />
</div>
<div class="option-row">
<label for="barSize">End Value:</label>
<input type="text" id="endValue3" value={panelInfo.endValue3} disabled />
</div>
</div>
)
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.state = {
minValue: 0,
maxValue: 50000,
color1: '#F7A711',
startValue1: 0,
endValue1: 10000,
color2: '#BBBBBB',
startValue2: 10000,
endValue2: 25000,
color3: '#82BC00',
startValue3: 25000,
endValue3: 50000
};
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)} newTabVisible={false}>
<Worksheet>
</Worksheet>
<Worksheet></Worksheet>
<Worksheet></Worksheet>
</SpreadSheets>
</div>
<Panel panelInfo={this.state}
maxValueChange={(e) => { this.maxValueChange(e) }}
minValueChange={(e) => { this.minValueChange(e) }}
endValue1Change={(e) => { this.endValue1Change(e) }}
endValue2Change={(e) => { this.endValue2Change(e) }}
color1Change={(e) => { this.color1Change(e) }}
color2Change={(e) => { this.color2Change(e) }}
color3Change={(e) => { this.color3Change(e) }}
/>
</div>
)
}
maxValueChange(e) {
let value = e.target.value;
this.setState(() => ({ maxValue: value, endValue3: value }), () => { this.applyChanges() })
}
minValueChange(e) {
let value = e.target.value;
this.setState(() => ({ minValue: value, startValue1: value }), () => { this.applyChanges() })
}
endValue1Change(e) {
let value = e.target.value;
this.setState(() => ({ endValue1: value, startValue2: value }), () => { this.applyChanges() })
}
endValue2Change(e) {
let value = e.target.value;
this.setState(() => ({ endValue2: value, startValue3: value }), () => { this.applyChanges() })
}
color1Change(e) {
let value = e.target.value;
this.setState(() => ({ color1: value }), () => { this.applyChanges() })
}
color2Change(e) {
let value = e.target.value;
this.setState(() => ({ color2: value }), () => { this.applyChanges() })
}
color3Change(e) {
let value = e.target.value;
this.setState(() => ({ color3: value }), () => { this.applyChanges() })
}
applyChanges() {
let sheet = this.spread.getActiveSheet();
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.state.minValue
+ ',' + this.state.maxValue
+ ',TRUE,TEXT(' + goalcolumn + ',"$0,K"),TEXT(' + actualcolumn + ',"$0,K"),TEXT(' + this.state.minValue
+ ',"$0,K"),TEXT(' + this.state.maxValue
+ ',"$0,K"),,-90,90,0.5,' + gaugeType + ',{'
+ this.state.startValue1 + ',' + this.state.endValue1 + ',"' + this.state.color1 + '"},{'
+ this.state.startValue2 + ',' + this.state.endValue2 + ',"' + this.state.color2 + '"},{'
+ this.state.startValue3 + ',' + this.state.endValue3 + ',"' + this.state.color3 + '"})');
}
}
initSpread(spread) {
this.spread = spread;
spread.options.newTabVisible = false;
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();
}
}
const Panel = (props) => {
const { panelInfo,
maxValueChange,
minValueChange,
endValue1Change,
endValue2Change,
color1Change,
color2Change,
color3Change } = props;
return (
<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" value={panelInfo.minValue} onChange={(e) => { minValueChange(e) }} />
</div>
<div class="option-row">
<label for="barSize"><u>Max Value:</u></label>
<input type="text" value={panelInfo.maxValue} onChange={(e) => { maxValueChange(e) }} />
</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 id="color1" value={panelInfo.color1} onChange={(e) => { color1Change(e) }} >
<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" id="startValue1" value={panelInfo.startValue1} disabled />
</div>
<div class="option-row">
<label for="barSize">End Value:</label>
<input type="text" id="endValue1" value={panelInfo.endValue1} onChange={(e) => { endValue1Change(e) }} />
</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 id="color2" value={panelInfo.color2} onChange={(e) => { color2Change(e) }}>
<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" id="startValue2" value={panelInfo.startValue2} disabled />
</div>
<div class="option-row">
<label for="barSize">End Value:</label>
<input type="text" id="endValue2" value={panelInfo.endValue2} onChange={(e) => { endValue2Change(e) }} />
</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" value={panelInfo.color3} onChange={(e) => { color3Change(e) }}>
<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" id="startValue3" value={panelInfo.startValue3} disabled />
</div>
<div class="option-row">
<label for="barSize">End Value:</label>
<input type="text" id="endValue3" value={panelInfo.endValue3} disabled />
</div>
</div>
)
}
<!DOCTYPE html>
<html lang="en">
<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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</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,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
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-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/cjs/react.production.js',
'react-dom': 'npm:react-dom/cjs/react-dom.production.js',
'react-dom/client': 'npm:react-dom/cjs/react-dom-client.production.js',
'scheduler': 'npm:scheduler/cjs/scheduler.production.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'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
