[{"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"}]}]
直方图用于表示数据集的频率分布。SpreadJS 允许您使用直方函数迷你图函数创建直方函数迷你图。
上图显示了直方函数迷你图及其元素:
直方函数迷你图可以以离散或连续方式显示,如下所示:
离散直方图通过采用现有值以离散方式(没有连续间隔)表示数据。例如:20、34、38。这些值按升序绘制。
连续直方图通过采用一系列连续值来表示数据。第一个 bin 的区间为左闭右闭,而其余 bin 的区间为左开右闭。
可以使用HistogramSparkline 公式中的连续选项指定直方图的类型。直方函数迷你图公式具有以下选项,其中只有 'dataRange' 是必需的参数:
选项 | 描述 |
---|---|
dataRange |
指定数据源的范围。它支持一个范围。它支持计算数组和计算引用。 |
continuous |
如果设置为 true,迷你图是一个连续的直方图。如果设置为 false,则迷你图是离散直方图。 默认值是true。 |
paintLabel |
如果设置为 true,迷你图绘制数据标签。默认值为false. |
scale |
指定箱宽度。当直方图类型为连续时,此值很有用。默认值为false. |
barWidth |
指定bar的宽,默认值为 1。Bar width = auto calc width * barWidth 值范围为 0 < 值 ≤ 1。 |
barColor |
指定条的颜色。默认值为“5B9BD5”。 支持 CSS 颜色属性。 |
labelFontStyle |
指定数据标签字体的字体样式。默认值为 9pt Calibri。 支持 CSS 字体属性。它支持font-style, font-weight, font-size, font-family. |
labelColor | 指定数据标签字体的字体颜色。默认为黑色 (#00000)。 支持 CSS 颜色字符串。 |
edgeColor | 指定边缘的颜色。默认值为silver (#C0C0C0). 支持 CSS 颜色属性。 |
直方函数迷你图公式具有以下语法:
=HISTOGRAMSPARKLINE(dataRange,continuous?,paintLabel?,scale?,barWidth?,barColor?,labelFontStyle?,labelColor,edgeColor?)
注:
考虑一个组织每周对用户的社交媒体消费进行调查的场景。直方函数迷你图可以描述这些统计数据并分析用户在不同社交媒体平台上花费的时间的趋势。
// 初始化Spread
var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
spread.suspendPaint();
// 获取工作表
var sheet1 = spread.sheets[0];
// 设置 sheetAreaOffset 选项
sheet1.options.sheetAreaOffset = { left: 1, top: 1 };
// 隐藏网格线
sheet1.options.gridline = { showVerticalGridline: false, showHorizontalGridline: false };
// 隐藏行/列标题
sheet1.options.colHeaderVisible = false;
sheet1.options.rowHeaderVisible = false;
// 设置列宽
for (var i = 1; i < 5; i++)
sheet1.setColumnWidth(i, 265);
sheet1.setColumnWidth(0, 140);
// 设置行高
sheet1.setRowHeight(0, 35, GC.Spread.Sheets.SheetArea.dataArea)
sheet1.setRowHeight(1, 35, GC.Spread.Sheets.SheetArea.dataArea)
sheet1.setRowHeight(2, 200, GC.Spread.Sheets.SheetArea.dataArea)
var dataArray =
[
["Name", "Facebook", "Instagram", "LinkedIn", "Twitter"]
];
var dataArray1 =
[
["John Mathew", 71, 74, 97, 85],
["Jim Parker", 76, 58, 88, 84],
["Sophia Ran", 97, 80, 74, 80],
["Wendi Blake", 55, 97, 84, 60],
["Stephan Lai", 81, 83, 94, 90],
["Fay Van Damme", 71, 78, 72, 82],
["Brevin Dice", 84, 80, 83, 93],
["Regina Oleveria", 86, 76, 56, 92],
["Georgi Facello", 78, 61, 91, 68],
["Bezalel Simmel", 85, 58, 92, 68],
["Sachin Tsukuda", 76, 70, 55, 81],
["Patricia Breugel", 64, 77, 83, 85],
["Zito Baaz", 82, 97, 66, 90],
["Navin Argence", 59, 87, 83, 85],
["Siamak Salverda", 77, 85, 97, 65],
["Khaled Kohling", 87, 67, 77, 97],
["Bangqing Kleiser", 49, 97, 78, 75],
["Rimli Dusink", 25, 61, 91, 91],
["Nathan Ranta", 65, 60, 96, 92],
["Gila Lukaszewicz", 27, 91, 62, 93],
["Kaijung Rodham", 31, 81, 67, 87],
["Mohammed Pleszkun", 20, 98, 67, 82],
["Shuichi Piazza", 67, 56, 81, 82],
["Katsuo Leuchs", 83, 74, 51, 42],
["Masanao Ducloy", 67, 64, 58, 73],
["Mihalis Crabtree", 80, 91, 57, 84],
["Danny Lenart", 92, 38, 99, 86],
["Yongqiao Dalton", 85, 61, 73, 81],
["Gaetan Veldwijk", 64, 52, 76, 72],
["Leszek Pulkowski", 95, 75, 64, 99],
["Weidon Gente", 77, 88, 77, 96],
["Krister Stranks", 72, 38, 89, 55],
["Ziyad Baaz", 89, 81, 83, 48],
["Ymte Perelgut", 67, 70, 97, 94],
["Tonia Butner", 99, 71, 87, 76],
["Shigeaki Narlikar", 78, 80, 80, 97],
["Ayakannu Beerel", 86, 61, 85, 81],
["Moni Bale", 60, 72, 71, 86],
["Manohar Heemskerk", 97, 60, 75, 78],
["Angus Swan", 33, 97, 99, 99],
["Christ Murtagh", 91, 76, 73, 80],
["Hilary Rodiger", 75, 64, 84, 54],
["Maren Baez", 45, 77, 86, 88],
["Greger Jahnichen", 98, 89, 81, 63],
["Ymte Duclos", 95, 83, 72, 72],
["Chenyi Hainaut", 81, 97, 99, 87],
["Kasidit Picel", 87, 80, 88, 80],
["Elrique Walstra", 96, 54, 38, 54],
["Adel Reghbati", 56, 88, 81, 88]
];
//设置数据
sheet1.setArray(1, 0, dataArray);
sheet1.setArray(3, 0, dataArray1);
sheet1.setValue(0, 0, "Social media usage of users (in minutes)", GC.Spread.Sheets.SheetArea.dataArea);
// 设置对齐
sheet1.getRange(-1, 0, -1, 10).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
sheet1.addSpan(0, 0, 1, 5, GC.Spread.Sheets.SheetArea.dataArea);
//设置 HistogramSparkline 公式
sheet1.setFormula(2, 1, 'HISTOGRAMSPARKLINE(Sheet1!B4: B52, true, true, 20, 1, "#DC4463", "bold normal 10pt Calibri", "black")');
sheet1.setFormula(2, 2, 'HISTOGRAMSPARKLINE(Sheet1!C2: C50, true, true, 20, 1, "#FFAE49", "bold normal 10pt Calibri", "black")');
sheet1.setFormula(2, 3, 'HISTOGRAMSPARKLINE(Sheet1!D2: D50, true, true, 20, 1, "#5AB4BD", "bold normal 10pt Calibri", "black")');
sheet1.setFormula(2, 4, 'HISTOGRAMSPARKLINE(Sheet1!E2: E50, true, true, 20, 1, "#C2EDB2", "bold normal 10pt Calibri", "black")');
//设置第 0 行的样式
var style1 = new GC.Spread.Sheets.Style();
style1.font = "bold 24px Calibri";
style1.foreColor = "black";
style1.backColor = "#9FD5B7";
style1.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style1.vAlign = GC.Spread.Sheets.VerticalAlign.center;
sheet1.setStyle(0, 0, style1, GC.Spread.Sheets.SheetArea.dataArea);
// 设置第 1 行的样式
var style = new GC.Spread.Sheets.Style();
style.font = "bold italic 20px Calibri";
style.foreColor = "black";
style.backColor = "#D3F0E0";
style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
for (var i = 0; i < 5; i++) {
sheet1.setStyle(1, i, style, GC.Spread.Sheets.SheetArea.dataArea);
}
// 设置第 2 行的样式
var style2 = new GC.Spread.Sheets.Style();
style2.backColor = "#EDFDF4";
for (var i = 0; i < 5; i++)
sheet1.setStyle(2, i, style2, GC.Spread.Sheets.SheetArea.dataArea);
// 为 dataArea 单元格设置边框
sheet1.getRange("A4:E52").setBorder(
new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thick),
{ top: true, bottom: true, left: true, right: true }, GC.Spread.Sheets.SheetArea.dataArea);
spread.resumePaint();