基于Office.js开发财务模型评审用公式格式刷的技术问询

需求与着色逻辑 #

脚本在活动工作表运行，实现公式格式一致性校验，着色规则如下：

• 文本/非公式单元格：填充为灰色
• 公式单元格：
  • 同一行左侧相邻单元格的R1C1公式与当前单元格完全相同 → 填充为绿色
  • 左侧无公式（或左侧为文本/空），或左侧公式与当前不同 → 填充为黄色

现有代码优化与实现方案 #

你不需要将formulasR1C1序列化为JSON再解析，它本身就是二维数组（行×列），直接遍历即可。核心思路是逐行逐列遍历单元格，根据公式内容判断后设置填充色，同时遵循Office.js的异步操作规范批量提交格式更改。

完整实现代码 #

$("#run").on("click", () => tryCatch(getFormulas));

async function getFormulas() {
  try {
    await Excel.run(async (context) => {
      const sheet = context.workbook.worksheets.getActiveWorksheet();
      const range = sheet.getUsedRange();
      // 加载公式数组属性
      range.load("formulasR1C1");
      await context.sync();

      const formulas = range.formulasR1C1; // 直接使用原生二维数组，无需JSON转换
      const rowCount = formulas.length;
      const colCount = formulas[0]?.length || 0;

      // 逐行遍历
      for (let rowIndex = 0; rowIndex < rowCount; rowIndex++) {
        const currentRow = formulas[rowIndex];
        // 逐列遍历
        for (let colIndex = 0; colIndex < colCount; colIndex++) {
          const currentFormula = currentRow[colIndex];
          const cell = range.getCell(rowIndex, colIndex);
          const fill = cell.format.fill;

          // 判断是否为公式（formulasR1C1中公式以"="开头）
          if (!currentFormula || !currentFormula.startsWith("=")) {
            // 非公式单元格：灰色填充
            fill.color = "#D9D9D9";
          } else {
            // 公式单元格：判断左侧单元格情况
            if (colIndex === 0) {
              // 第一列无左侧单元格，标记黄色
              fill.color = "#FFFF00";
            } else {
              const leftFormula = currentRow[colIndex - 1];
              if (leftFormula && leftFormula.startsWith("=") && leftFormula === currentFormula) {
                // 左侧有相同公式，标记绿色
                fill.color = "#92D050";
              } else {
                // 左侧无公式或公式不同，标记黄色
                fill.color = "#FFFF00";
              }
            }
          }
        }
      }

      // 同步所有格式更改到Excel
      await context.sync();
      console.log("格式刷执行完成");
    });
  } catch (error) {
    console.error("执行出错：", error);
  }
}

/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
  try {
    await callback();
  } catch (error) {
    console.error(error);
  }
}

关键说明 #

• 公式判断：通过formulasR1C1内容是否以=开头，区分公式与文本单元格
• 性能优化：直接遍历原生二维数组，避免不必要的JSON序列化；批量设置单元格格式后统一同步，减少异步交互次数
• 边界处理：单独处理第一列单元格（无左侧单元格）的情况，直接标记黄色

内容的提问来源于stack exchange，提问作者kfk103