Office Script遍历表格列正则匹配标红及日期值转换问题

问题概述 #

需要编写Office Script实现以下功能：遍历表格「Last Update Time」列，检查单元格值是否匹配指定正则表达式，匹配则将单元格填充为红色。同时解决测试中遇到的日期值被转为Excel序列化数字的问题。

初始代码：

function main(workbook: ExcelScript.Workbook) {
    let worksheet = workbook.getWorksheet("Workstreams");
    let usedColumn = worksheet.getTables()[0].getColumn("Last Update Time");
    let regexPattern = "^(0[1-9]|1[0-2])\/(0[1-9]|1\d|2\d|3[01])\/(23) ([1-9]|0[1-9]|1[0-2]):[0-5][0-9] ([AaPp][Mm])$"
}

测试中遇到的问题：获取单元格日期值时，输出为Excel序列化数字（如44641.25）而非显示的日期字符串。

解决方案 #

1. 遍历表格列的方法 #

通过表格列对象的getRangeBetweenHeaderAndTotal()方法获取表头和总计行之间的数据区域，避免遍历无关行。推荐先获取区域的二维值数组再遍历索引，这种方式能减少Excel对象调用次数，性能更优。

2. 匹配正则后标记单元格为红色 #

使用单元格区域的getFormat().getFill().setColor()方法设置填充色，参数可以是颜色名称（如"red"）或十六进制颜色码（如"#FF0000"）。

3. 解决日期值转为序列化数字的问题 #

Excel中的日期存储为序列化数字，直接用getValue()会返回数字而非显示的字符串。解决方法有两种：

• 方法一：获取单元格显示文本：使用getText()方法直接获取单元格显示的字符串，适合匹配单元格可见格式的场景
• 方法二：将数字转为日期对象并格式化：通过new Date((rowValue as number) * 86400000 + Date.UTC(1899, 11, 30))将序列化数字转为Date对象，再用toLocaleString()或自定义格式转为目标字符串

若正则匹配的是单元格显示的日期格式，优先用getText()更直接。

完整实现代码 #

function main(workbook: ExcelScript.Workbook) {
    // 获取工作表和目标表格列
    let worksheet = workbook.getWorksheet("Workstreams");
    let targetTable = worksheet.getTables()[0];
    let targetColumn = targetTable.getColumn("Last Update Time");
    let dataRange = targetColumn.getRangeBetweenHeaderAndTotal();
    let cellValues = dataRange.getValues();
    // 定义正则表达式
    let dateRegex = /^(0[1-9]|1[0-2])\/(0[1-9]|1\d|2\d|3[01])\/23 ([1-9]|0[1-9]|1[0-2]):[0-5][0-9] ([AaPp][Mm])$/;

    // 遍历每一行数据
    for (let rowIndex = 0; rowIndex < cellValues.length; rowIndex++) {
        // 获取当前单元格
        let currentCell = dataRange.getCell(rowIndex, 0);
        // 获取单元格显示文本（解决日期转数字问题）
        let cellText = currentCell.getText();
        
        // 检查是否匹配正则
        if (dateRegex.test(cellText)) {
            // 设置单元格填充为红色
            currentCell.getFormat().getFill().setColor("red");
        }
    }
}

代码说明 #

• getRangeBetweenHeaderAndTotal()：仅获取表格数据区域，排除表头和总计行
• getText()：确保获取单元格显示的字符串，避免日期被转为序列化数字
• regex.test(cellText)：检查文本是否匹配正则表达式
• getFormat().getFill().setColor("red")：将匹配的单元格填充为红色

内容的提问来源于stack exchange，提问作者Michał Mańkowski