使用 ONLYOFFICE 宏创建电子表格数据热力图

2025年04月18日作者：Krystal

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

Create a spreadsheet data heat map using an ONLYOFFICE macro

构建宏

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

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

“未选择数据”错误处理

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

//We check if no data is selected, and show message if that is the case
  if (!data) {
    console.log("未选择数据");
    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 数组，并用选定的数据填充它。

var values = []; //We will store number from selected data here


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //We are checking if the value is a number
      //If it is, we store it to values array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }

初始化 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。

完整的宏代码

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


  //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;


  console.log(firstColIndex + " " + lastColIndex); //Testing if we got the right column indexes, first should be on spot, last should be higher by 1
  console.log(firstRowIndex + " " + lastRowIndex); //Testing if we got the right row indexes, first should be on spot, last should be higher by 1


  var values = []; //We will store number from selected data here


  for (var i = firstColIndex; i < lastColIndex; i++) {
    for (var j = firstRowIndex; j < lastRowIndex; j++) {
      //We are checking if the value is a number
      //If it is, we store it to values array
      if (!isNaN(parseFloat(sheet.GetCells(j, i).GetValue()))) {
        var value = parseFloat(sheet.GetCells(j, i).GetValue());
        values.push(sheet.GetCells(j, i).GetValue());
      }
    }
  }


  //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
      }
    }
  }
})();