ExcelScript.PivotDateFilter接口失效：数据透视表日期筛选脚本问题

问题描述 #

尝试编写Office脚本从数据透视表筛选器中选择特定日期，数据透视表显示格式为短日期（yyyy-MM-dd），但数据源实际存储格式为dd/MM/yyyy。执行脚本后无法选中手动可选择的2023-06-01，所有筛选条件均无效，怀疑是日期格式问题。

脚本代码 #

function main(workbook: ExcelScript.Workbook) {
    let patchMonthSummary = workbook.getPivotTable("patchMonthSummary");

    // Get filters
    const patchMonth = patchMonthSummary.getHierarchy("Patch Month").getPivotField("Patch Month");

    // Clear filters
    patchMonth.clearAllFilters();

    // Define date
    let chosenDate: ExcelScript.FilterDatetime = {
        date: "2023-06-01",
        specificity: ExcelScript.FilterDatetimeSpecificity.day
    };

    // Apply filter
    patchMonth.applyFilter({
        dateFilter: {
            condition: ExcelScript.DateFilterCondition.equals,
            comparator: chosenDate
        }
    });
}

问题原因 #

Office脚本处理日期筛选时，不会自动识别数据源的显示格式，而是直接基于单元格存储的原始日期值（本质是代表日期的数字）进行匹配。用字符串"2023-06-01"定义日期时，脚本会按yyyy-MM-dd解析为6月1日，但数据源中如果是dd/MM/yyyy格式存储的"01/06/2023"，实际对应的日期是1月6日，自然无法匹配到目标日期。

解决方法 #

1. 使用Date对象（推荐） #

明确指定年、月、日创建Date对象，避免格式解析歧义（注意：JavaScript的Date对象月份是0-based，即0代表1月，5代表6月）：

let chosenDate: ExcelScript.FilterDatetime = {
    date: new Date(2023, 5, 1),
    specificity: ExcelScript.FilterDatetimeSpecificity.day
};

2. 使用Excel日期数值 #

Excel中日期以1900年1月1日为起始点按天计数，2023-06-01对应的数值是45088，可直接使用该数值匹配：

let chosenDate: ExcelScript.FilterDatetime = {
    date: 45088,
    specificity: ExcelScript.FilterDatetimeSpecificity.day
};

3. 验证数据源实际日期值 #

可在脚本中读取数据源单元格的日期值，确认实际存储的日期是否符合预期：

// 示例：读取数据源工作表中A1单元格的日期
const dataSheet = workbook.getWorksheet("数据源");
const cellDate = dataSheet.getRange("A1").getValue() as Date;
console.log(cellDate.toISOString()); // 输出实际日期的ISO格式，确认是否为2023-06-01

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