[{"id":"b66f8b1e-cc14-4e89-9679-abd5687d283d","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"569bb90a-ea68-46c6-96f1-ab151c120714","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5eb52f08-2d1a-4362-9ffc-4871bdc10f3f","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"341dd607-b97d-4d70-bde2-53acda6b6c95","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"05349273-414f-4208-9ea2-c4fc8f4ea2cb","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"86089f76-b778-4d52-821e-6f27de3df613","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b81e4fd6-1fc5-43a0-a258-b6e16a5cbec6","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8aa8ce31-43e4-438e-951f-241608435260","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"37343f41-6ec2-4c7e-b21d-2cc18d5ce1e0","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5915e52f-64f8-4146-b8bd-81bead6324a3","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"836ba889-af9e-460d-a4cc-c24d922795f2","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4ae14b06-bb68-4394-a210-a46b8f028346","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d8f42066-e9dc-4411-bdcf-43b1a203370c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"7ef86f16-b1a0-49f7-9592-612b9be02b25","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b29c7775-a9a4-451e-a1b5-01d19ed5ca5e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"e56f3989-8f81-46af-90fa-a4813eeb976f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"2526c963-f170-45a8-923e-91b0712a9810","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"24575cf0-501a-44f9-8426-c40f8f4b5552","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1a6f8d7f-acd6-42be-8c4f-f464c6218381","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"82b176fd-5cab-498c-909e-8fa7d29c38d8","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1708b3a9-4f37-44a8-8f0e-f9a2d2e5d940","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"c9ac246e-29fb-4bc4-8231-8439795bb590","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"94f2a5c3-2539-436a-af75-23fbbd1a3957","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d99594f4-2d40-4df4-9419-ba2ca6aa3f7f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"fd66e72d-0f10-4f57-9807-6db26290ab2e","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8f344863-503d-4bc3-a594-3815e7d55f5c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"9be82601-de9d-4c18-948a-23ab6f4dd431","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"6495f3c0-b463-47e0-b08a-ca949672211e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b4bede08-3f08-4839-ba4a-abc7ac195bde","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4afcdfc0-3ff5-4f2b-a223-f8fc042a5bbe","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"422062c4-fa40-4771-a86f-008efe6d86e5","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d87e8013-86a7-4840-8d25-6f62e14eb4ac","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a6acedfd-4043-4c64-a5d1-aec3326df9e7","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"dbdc0b24-06c4-48b9-8d6c-7455119dc773","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a3856849-954a-4cfc-96a6-382e530d3638","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4a39306b-ffa5-433d-80a3-28e41f929b72","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]}]
        
(Showing Draft Content)

KPI函数迷你图

SpreadJS 提供了KPI函数迷你图,可用于可视化与 KPI 值相关的指标的性能。可用于表明管理工作的有效性或提出销售目标等。

KPI函数迷你图使用以下值来显示指标:

  • 目标值:表示期望值并显示在底部。
  • 当前值:表示当前值。
  • 最小值:表示最小的期望值。它应该低于目标值和当前值。
  • 最大值:表示最大的期望值。它应该高于目标值和当前值。

KPI函数迷你图类型

SpreadJS 提供了三种类型的KPI函数迷你图,分别是 Circle、Vertical 和 Horizo​​ntal,可以使用GaugeKPISparkline 函数中的GaugeType选项进行设置。

KPI函数迷你图具有以下选项:

参数 类型 描述
targetValue Required

Number KPI函数迷你图的目标值。目标值介于 minValue 和 maxValue 之间。
currentValue Required

Number KPI函数迷你图的当前值。当前值介于 minValue 和 maxValue 之间。
minValue Required

Number KPI函数迷你图的最小值。minValue 小于 maxValue。
maxValue Required

Number KPI函数迷你图的最大值。maxValue 大于 minValue。
showLabel Optional

Boolean 指定是否显示迷你图中提供的所有值的标签。如果为 false,则不会显示标签。

如果为 true,它将只显示适合单元格宽度和高度的标签。单元格应该有足够的宽度和高度来显示图形和标签。

默认值是true。

targetValueLabel Optional

String 显示为目标值标签的字符串。默认值为 targetValue。

currentValueLabel Optional

String 显示为当前值标签的字符串。默认值为 currentValue。

mintValueLabel Optional

String 显示为最小值标签的字符串。默认值为 minValue。

maxValueLabel Optional

String 显示为最大值标签的字符串。默认值为 maxValue。

fontArray Optional

CalcArray 数组包含字体格式作为四种标签类型的字符串项 -showLabel选项必须为真。

minAngle Optional

Number circle类型的最小角度值。minAngle 应该小于 maxAngle。角度值对应于时钟中的时间。0 是 12 点,-90 是 9 点,90 是 3 点,-180/180 是 6 点。

默认值为 -90。

gaugeType 选项必须为 0( circle类型)。

maxAngle Optional

Number circle类型的最大角度值。maxAngle 应该大于 minAngle。角度值对应于时钟中的时间。0 是 12 点,-90 是 9 点,90 是 3 点,-180/180 是 6 点。

默认值为 90。

gaugeType 选项必须为 0(circle类型)。

radiusRatio Optional

Number 该值计算为内圆半径与外圆半径的比值。外圆半径值由单元格大小决定。值范围在 0 和 1 之间。默认值为 0。

gaugeType 选项必须为 0 (circle类型).

gaugeType Optional

Number KPI函数迷你图类型。0 - Circle1 - Vertical Bar2 - Horizontal Bar

默认类型为circle.

colorRange Optional

Repeatable

CalcArray 指定值之间的颜色范围。 {startValue, endValue, color_string}

其中startValue是范围内的起始值,endValue是范围内的结束值,color_string是两个指定值之间范围的颜色。

将在startValue必须小于endValue值和两个值应该是minValue(最小值)和包括maxValue之间。

从 minValue 到 maxValue 的默认颜色范围填充为浅灰色。

KPI函数迷你图公式具有以下语法:

=GAUGEKPISPARKLINE (targetValue, currentValue, minValue, maxValue,showLabel?,targetValueLabel?,currentValueLabel?,minValueLabel?,maxValueLabel?, fontArray?, minAngle?, maxAngle?, radiusRatio?, gaugeType?, colorRange?)

使用场景

KPI函数迷你图用于监控公司的年度销售业绩。收入、利润和销售指标根据其目标值和实际值进行衡量,这有助于轻松可视化数据并进行有意义的扣除。

// 初始化Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 2 });
// 获取工作表
var sheet1 = spread.sheets[0];
var sheet2 = spread.sheets[1];
//隐藏网格线
sheet1.options.gridline = { showVerticalGridline: false, showHorizontalGridline: false };
//隐藏行/列标题
sheet1.options.colHeaderVisible = false;
sheet1.options.rowHeaderVisible = false;
// 设置列宽
for (var i = 0; i < 3; i++)
    sheet1.setColumnWidth(i, 340);
for (var i = 0; i < 4; i++)
    sheet2.setColumnWidth(i, 70);
for (var i = 0; i < 5; i++)
    sheet2.setColumnWidth(i, 170);
//设置行高
sheet1.setRowHeight(0, 35, GC.Spread.Sheets.SheetArea.dataArea);
sheet1.setRowHeight(1, 280, GC.Spread.Sheets.SheetArea.dataArea);
          
// 设置数据
sheet1.setValue(0, 0, "Sales($'000) Dashboard FY 2019-20", GC.Spread.Sheets.SheetArea.dataArea);
var dataArray1 =
    [
        ["Parameters", "Target", "Current", "Min", "Max"],
        ["Revenue", 900, 1200, 0, 2000],
        ["Profit", 1200, 1100, 0, 2000],
        ["Sales", 1600, 1800, 0, 2000]
    ];
sheet2.setArray(0, 0, dataArray1);

// 设置 GaugeKPISparkline 公式
sheet1.setFormula(1, 0, '=GAUGEKPISPARKLINE(Sheet2!B2,Sheet2!C2,Sheet2!D2,Sheet2!E2,TRUE,TEXT(Sheet2!B3/1000,"$0.0K"),Sheet2!A2,TEXT(Sheet2!D2/1000,"$0.0K"),TEXT(Sheet2!E2/1000,"$0.0K"),{"normal normal 11pt Calibri","normal bold 16pt Calibri","normal normal 9pt Calibri","normal normal 9pt Calibri"},-90,90,0.4,0,{0,1200,"#FFB2BD"},{1200,1500,"#FFDFB0"},{1500,2000,"#BCEAB4"})');
sheet1.setFormula(1, 1, '=GAUGEKPISPARKLINE(Sheet2!B3,Sheet2!C3,Sheet2!D3,Sheet2!E3,TRUE,TEXT(Sheet2!B3/1000,"$0.0K"),Sheet2!A3,TEXT(Sheet2!D3/1000,"$0.0K"),TEXT(Sheet2!E3/1000,"$0.0K"),{"normal normal 11pt Calibri","normal bold 16pt Calibri","normal normal 9pt Calibri","normal normal 9pt Calibri"},-90,90,0.4,0,{0,1200,"#FFB2BD"},{1200,1500,"#FFDFB0"},{1500,2000,"#BCEAB4"})');
sheet1.setFormula(1, 2, '=GAUGEKPISPARKLINE(Sheet2!B4,Sheet2!C4,Sheet2!D4,Sheet2!E4,TRUE,TEXT(Sheet2!B4/1000,"$0.0K"),Sheet2!A4,TEXT(Sheet2!D4/1000,"$0.0K"),TEXT(Sheet2!E4/1000,"$0.0K"),{"normal normal 11pt Calibri","normal bold 16pt Calibri","normal normal 9pt Calibri","normal normal 9pt Calibri"},-90,90,0.6,0,{0,1200,"#FFB2BD"},{1200,1500,"#FFDFB0"},{1500,2000,"#BCEAB4"})');
           
sheet1.addSpan(0, 0, 1, 3, GC.Spread.Sheets.SheetArea.dataArea);

// 设置行0样式
style1 = new GC.Spread.Sheets.Style();
style1.font = "bold 28px Calibri";
style1.foreColor = "white";
style1.backColor = "#14928F";
style1.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
sheet1.setStyle(0, 0, style1, GC.Spread.Sheets.SheetArea.dataArea);

//设置行1样式
var style = new GC.Spread.Sheets.Style();
style.backColor = "#B4E7E2";
for (var i = 0; i < 3; i++) {
    sheet1.setStyle(1, i, style, GC.Spread.Sheets.SheetArea.dataArea);
}