如何在Excel Office Script中为共享表格添加多条件格式规则

安全阿岳

2026-7-9

批量添加Excel条件格式规则的Office Script实现

针对共享Excel表格（可在SharePoint/Teams/OneDrive访问）中协作时条件格式易被覆盖的问题，可通过Office Script批量重新应用8条单元格值匹配规则，目标区域为工作表Attendance中的命名区域AttendanceData（$D$6:$NF$37）。

解决方案代码

function main(workbook: ExcelScript.Workbook) {
  // 获取目标工作表和区域
  const shAttendance = workbook.getWorksheet("Attendance");
  const rngAttendanceData = shAttendance.getRange("AttendanceData");

  // 定义所有8条条件格式规则的配置：匹配值 + 对应填充色
  const formatRules = [
    { value: "LA", color: "#AEAAAA" },
    { value: "AB", color: "#FFC0CB" },
    { value: "CD", color: "#90EE90" },
    { value: "EF", color: "#ADD8E6" },
    { value: "GH", color: "#F0E68C" },
    { value: "IJ", color: "#D8BFD8" },
    { value: "KL", color: "#FFA07A" },
    { value: "MN", color: "#98FB98" }
  ];

  // 循环遍历规则配置，逐个添加条件格式
  formatRules.forEach(ruleConfig => {
    const conditionalFormat = rngAttendanceData.addConditionalFormat(
      ExcelScript.ConditionalFormatType.cellValue
    ).getCellValue();

    // 设置单元格值匹配规则
    const cellValueRule: ExcelScript.ConditionalCellValueRule = {
      formula1: `="${ruleConfig.value}"`,
      operator: ExcelScript.ConditionalCellValueOperator.equalTo
    };
    conditionalFormat.setRule(cellValueRule);

    // 设置填充格式
    const format = conditionalFormat.getFormat();
    format.getFill().setColor(ruleConfig.color);
  });
}