如何在Office Script中实现Excel左连接并迁移VBA宏？

核心逻辑拆解 #

• 先清理Output表：
  • 删除ID在Source表中不存在的行
  • 删除ID存在但Error1/Error2/Error3任意一列与Source对应值不一致的行
• 再同步新增行：将Source表中Output表未包含的ID对应的整行数据，添加到Output表末尾

完整Office Script代码 #

function main(workbook: ExcelScript.Workbook) {
    // 获取指定工作表
    const sourceSheet = workbook.getWorksheet("Source");
    const outputSheet = workbook.getWorksheet("Output");
    if (!sourceSheet || !outputSheet) {
        console.error("找不到Source或Output工作表");
        return;
    }

    // 获取两表已用数据范围（默认表头在第一行）
    const sourceRange = sourceSheet.getUsedRange();
    const outputRange = outputSheet.getUsedRange();
    if (!sourceRange || !outputRange) {
        console.error("Source或Output工作表无有效数据");
        return;
    }

    // 获取表头数组，用于匹配关键列位置
    const sourceHeaders = sourceRange.getValues()[0] as string[];
    const outputHeaders = outputRange.getValues()[0] as string[];

    // 定位关键列的索引
    const idColSource = sourceHeaders.indexOf("ID");
    const error1ColSource = sourceHeaders.indexOf("Error1");
    const error2ColSource = sourceHeaders.indexOf("Error2");
    const error3ColSource = sourceHeaders.indexOf("Error3");

    const idColOutput = outputHeaders.indexOf("ID");
    const error1ColOutput = outputHeaders.indexOf("Error1");
    const error2ColOutput = outputHeaders.indexOf("Error2");
    const error3ColOutput = outputHeaders.indexOf("Error3");

    // 检查关键列是否存在
    if (idColSource === -1 || idColOutput === -1 ||
        error1ColSource === -1 || error1ColOutput === -1 ||
        error2ColSource === -1 || error2ColOutput === -1 ||
        error3ColSource === -1 || error3ColOutput === -1) {
        console.error("关键列（ID/Error1/Error2/Error3）缺失，请检查表头");
        return;
    }

    // 把Source的ID和对应错误值存入Map，方便快速查询
    const sourceDataMap = new Map<string, [string | number, string | number, string | number]>();
    const sourceValues = sourceRange.getValues();
    for (let i = 1; i < sourceValues.length; i++) { // 跳过表头行
        const id = sourceValues[i][idColSource].toString();
        const error1 = sourceValues[i][error1ColSource];
        const error2 = sourceValues[i][error2ColSource];
        const error3 = sourceValues[i][error3ColSource];
        sourceDataMap.set(id, [error1, error2, error3]);
    }

    // 标记Output中需要删除的行
    const outputValues = outputRange.getValues();
    const rowsToDelete: number[] = [];
    for (let i = 1; i < outputValues.length; i++) { // 跳过表头行
        const id = outputValues[i][idColOutput].toString();
        if (!sourceDataMap.has(id)) {
            // ID不在Source中，标记删除
            rowsToDelete.push(i + 1); // Excel行号从1开始，数组索引i对应行号i+1
        } else {
            // 对比错误列值
            const [sourceErr1, sourceErr2, sourceErr3] = sourceDataMap.get(id)!;
            const outputErr1 = outputValues[i][error1ColOutput];
            const outputErr2 = outputValues[i][error2ColOutput];
            const outputErr3 = outputValues[i][error3ColOutput];
            if (outputErr1 !== sourceErr1 || outputErr2 !== sourceErr2 || outputErr3 !== sourceErr3) {
                rowsToDelete.push(i + 1);
            }
        }
    }

    // 倒序删除行（避免删除行后索引错乱）
    rowsToDelete.sort((a, b) => b - a);
    rowsToDelete.forEach(rowNum => {
        const lastCol = outputRange.getLastColumn().getAddress()[0];
        outputSheet.getRange(`A${rowNum}:${lastCol}${rowNum}`).delete(ExcelScript.DeleteShiftDirection.up);
    });

    // 收集Source中Output没有的行，准备新增
    const updatedOutputRange = outputSheet.getUsedRange();
    const updatedOutputValues = updatedOutputRange ? updatedOutputRange.getValues() : [];
    const outputIdSet = new Set<string>();
    for (let i = 1; i < updatedOutputValues.length; i++) {
        const id = updatedOutputValues[i][idColOutput].toString();
        outputIdSet.add(id);
    }

    const rowsToAdd: (string | number)[][] = [];
    for (let i = 1; i < sourceValues.length; i++) {
        const id = sourceValues[i][idColSource].toString();
        if (!outputIdSet.has(id)) {
            rowsToAdd.push(sourceValues[i]);
        }
    }

    // 批量写入新增行
    if (rowsToAdd.length > 0) {
        const startRow = updatedOutputRange ? updatedOutputRange.getRowCount() + 1 : 2;
        const targetRange = outputSheet.getRangeByIndexes(startRow - 1, 0, rowsToAdd.length, rowsToAdd[0].length);
        targetRange.setValues(rowsToAdd);
    }

    console.log("同步完成");
}

代码关键细节 #

• 用Map存储Source的ID和错误值，比逐行遍历查询效率高很多
• 删除行时采用倒序操作，避免删除前一行后，后续行的索引偏移导致删错行
• 通过表头名称定位关键列，不用硬编码列位置，适配列顺序调整的场景
• 新增行前先获取清理后的Output ID集合，确保只添加真正缺失的数据

注意事项 #

• 确保两个工作表的表头完全一致，否则会导致列匹配失败
• 若数据量极大（超10万行），可改用Excel内置Table对象优化性能
• 运行前建议备份工作簿，避免数据意外丢失

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