ONLYOFFICE深度解锁系列.12-如何用 ONLYOFFICE宏创建表格热力图

在数据分析中，迅速识别数值变化对决策至关重要。然而，手动设置单元格格式既耗时又容易出错。本文介绍的 ONLYOFFICE 宏通过动态调整单元格颜色，简化了这一过程，使得解读复杂数据更加高效、直观。

构建宏

var sheet = Api.GetActiveSheet();var range = sheet.GetSelection();var data = range.GetValue();

首先，我们在 Sheet、Range 和 Data 变量中分别初始化活动工作表、选定的内容及范围。

“未选择数据”错误处理

随后，我们将检查是否选择了数据。若未选择任何数据，则终止宏并显示消息："No data selected"

//We check if no data is selected, and show message if that is the case if (!data) { console.log("No data selected"); return; }

从选定内容中提取行和列索引

如果存在数据，我们将继续获取其参数，包括列起始索引、列结束索引、行起始索引和行结束索引。

//Indexes indicating where rows and columns start and end var firstRowIndex = range.GetCells().Row; var firstColIndex = range.GetCells().Col; var lastRowIndex = data.length + firstRowIndex; var lastColIndex = data[0].length + firstColIndex;

获取索引的方法有多种，上面的代码片段展示了一种实现方式：

range.GetCells() – 访问选定的单元格，并通过 .Row() 和 .Col() 获取第一行和第一列的索引。
获取索引后，通过 data.length 获取数据的行数，将此值添加到 firstRowIndex 变量中获取最后一行的索引。同理，使用 data[0].length 获取列数，然后将其添加到 firstColIndex 变量中

为什么是 data[0]？

由于我们无法预知数据的行数，且 data[0]、 data[1] 等都代表一行，因此我们唯一可以确定的是，数据至少包含一行。这意味着只要 data 变量中有值，data[0] 就总是有效的。

从选定的电子表格单元格中收集数据

接下来，我们创建一个 values 数组，并用选定的数据填充它。

初始化 values 数组后，我们遍历选定区域的每个单元格。

默认情况下，单元格中的值都被视为字符串，因此在检查该值是否为数字时，需先使用 parseFloat() 方法将其转换为数字。

如果单元格内的字符串代表数字，parseFloat 会将其转换为数字。否则，返回 NaN（非数字）。

isNaN(…) 函数用于检查括号内的值是否不是数字。如果在 isNaN(…) 前添加“!”，则表示检查该值是否为数字。

如果该值是数字，我们将初始化 value 变量并存储该数字，然后将此值添加到 values 数组中。

迭代之后，values 数组将包含选定单元格中的所有数字。

我们需要这个数组的原因是，可以利用 JavaScript 的 Math.min() 和 Math.max() 方法，从选定单元格中找出最小值和最大值。

查找最小值和最大值

//Storing minimum and maximum values from the values array var minValue = Math.min(...values); var maxValue = Math.max(...values);

应用颜色

现在我们已经获取了所有必要信息，可以对包含数字的单元格应用自定义颜色了。

我们已确定选定单元格中的最小值和最大值，接下来可以再次遍历这些单元格。

for (var i = firstColIndex; i < lastColIndex; i++) { for (var j = firstRowIndex; j < lastRowIndex; j++) { //Again we have to check if the value is a number //If it is, we create the color depending on that value //As well as minimum and maximum value from the array if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) { var value = parseFloat(sheet.GetCells(j, i).GetValue()); var ratio = (value - minValue) / (maxValue - minValue); var red = Math.round(255 * ratio); var green = Math.round(255 * (1 - ratio)); sheet .GetCells(j, i) .SetFillColor(Api.CreateColorFromRGB(red, green, 0)); //We want colors to go from green to red } } }

使用 RGB 系统创建颜色时，需设定红色、绿色和蓝色参数。为实现从绿色到红色的颜色范围，蓝色参数固定为0，而红色和绿色参数则根据所需色调调整。

如果数字较小（接近最小值），颜色会偏向绿色（最小值对应纯绿色）；若数字较大，则颜色偏向红色（最大值对应纯红色）。

我们采用最小-最大归一化方法，通过 minValue 和 maxValue 计算出一个0到1之间的比率，以确定数字的相对大小。较大的数字会使比率接近1，而较小的数字则使比率接近0。

我们可以使用该比率来确定红色和绿色的参数值。由于比率的范围是0到1，而红色和绿色的值范围是从0到255，我们需要将比率乘以255以得到相应的颜色参数值。这样就能根据比率调整红色和绿色的强度。

为了使红色在较大数字中更占主导，我们通过将比率乘以225并四舍五入来计算红色值。

对于绿色值，由于其应在较小数字中更突出，我们使用(1 - 比率)乘以225来计算，确保随着比率接近1（即数字较大时），绿色值减少。

得到红色和绿色的参数后，我们利用 Api.CreateColorFromRGB(r, g, b) 创建颜色，并通过 .SetFillColor(color) 方法将其应用到单元格上。这样可以根据数值大小动态调整单元格颜色，使其从绿色平滑过渡到红色。注意这里蓝色值b固定为0。

完整的宏代码

接下来，让我们看看宏的运行效果！

运行宏之前：

图片

运行宏后：

图片

福利推荐-软件获取方法

豆容器市场专注提供优质Docker应用服务，集成一键式容器安装功能，助力用户快速部署OnlyOffice、协作空间、Nextcloud、可道云等办公应用。平台新增IPv6内网直连技术，搭配自动化SSL证书配置及智能域名解析功能，为家庭云服务提供完整技术方案，简化私有云搭建与运维流程，轻松实现高效云端协作管理。

onlyoffice已经支持ARM和x86双模式安装,其他软件同步支持中。

地址: https://ds.sendtokindle.net.cn/