Excel Script实现推测成本计算遇阻，求代码排查方案

你的脚本无法运行的核心原因是两个关键函数未实现，同时缺少需求中明确要求的「&分隔数量/名称拆分」逻辑，还存在部分严谨性处理漏洞。以下是完整的修复方案：

修复后的完整脚本 #

function main(workbook: ExcelScript.Workbook) {
    // 获取指定工作表
    let damagedEquipmentSheet = workbook.getWorksheet("Damaged Equipment");
    // 注意：原需求中Speculated Cost(T列)默认在Damaged Equipment表，若实际在Cost表可改回
    let targetSheet = damagedEquipmentSheet;

    if (!damagedEquipmentSheet) {
        console.log("未找到'Damaged Equipment'工作表");
        return;
    }

    // 先处理原始数据拆分：将J/K列的&分隔内容拆分到AG/AH/AI/AJ列
    splitEquipmentData(damagedEquipmentSheet);

    // 计算并更新推测成本
    calculateSpeculatedCost(targetSheet, damagedEquipmentSheet, workbook);
}

// 拆分J列(数量)和K列(名称)中的&分隔内容到AG/AH/AI/AJ列
function splitEquipmentData(sheet: ExcelScript.Worksheet) {
    let lastRow = sheet.getRange("J:K").getLastCell().getRowIndex() + 1;
    if (lastRow < 2) return; // 无有效数据时直接返回

    let rawRange = sheet.getRange(`J2:K${lastRow}`);
    let rawValues = rawRange.getValues();

    for (let i = 0; i < rawValues.length; i++) {
        let rawQty = rawValues[i][0]?.toString() || "";
        let rawName = rawValues[i][1]?.toString() || "";

        // 拆分并过滤无效值
        let qtyParts = rawQty.split("&").map(item => item.trim()).filter(item => !isNaN(parseFloat(item)));
        let nameParts = rawName.split("&").map(item => item.trim()).filter(item => item);

        // 写入对应列
        let writeRow = i + 2;
        sheet.getRange(`AG${writeRow}`).setValue(qtyParts[0] || "");
        sheet.getRange(`AH${writeRow}`).setValue(nameParts[0] || "");
        sheet.getRange(`AI${writeRow}`).setValue(qtyParts[1] || "");
        sheet.getRange(`AJ${writeRow}`).setValue(nameParts[1] || "");
    }
}

// 计算推测成本并更新T列
function calculateSpeculatedCost(targetSheet: ExcelScript.Worksheet, dataSheet: ExcelScript.Worksheet, workbook: ExcelScript.Workbook) {
    let lastRow = dataSheet.getRange("AG:AH").getLastCell().getRowIndex() + 1;
    if (lastRow < 2) return;

    let splitRange = dataSheet.getRange(`AG2:AH${lastRow}`);
    let splitValues = splitRange.getValues();

    for (let i = 0; i < splitValues.length; i++) {
        let qtyStr = splitValues[i][0]?.toString() || "";
        let equipmentName = splitValues[i][1]?.toString() || "";

        if (!equipmentName) continue;

        // 解析数量，处理非数值情况
        let qty = parseFloat(qtyStr);
        if (isNaN(qty)) {
            console.log(`第${i+2}行数量无效: ${qtyStr}`);
            targetSheet.getRange(`T${i+2}`).setValue("");
            continue;
        }

        // 获取价格：先直接匹配，再尝试模糊映射
        let price = getPriceFromPriceTable(equipmentName, workbook);
        if (!price) {
            let mappedName = getMappedNameFromMappingTable(equipmentName, workbook);
            if (mappedName) {
                price = getPriceFromPriceTable(mappedName, workbook);
            }
        }

        // 更新推测成本
        if (price) {
            targetSheet.getRange(`T${i+2}`).setValue(qty * price);
        } else {
            targetSheet.getRange(`T${i+2}`).setValue("无匹配价格");
            console.log(`第${i+2}行设备${equipmentName}未找到价格`);
        }
    }
}

// 从PriceTable(AA/AB列)获取设备价格
function getPriceFromPriceTable(equipmentName: string, workbook: ExcelScript.Workbook): number | undefined {
    let costSheet = workbook.getWorksheet("Cost");
    if (!costSheet) return undefined;

    let lastRow = costSheet.getRange("AA:AB").getLastCell().getRowIndex() + 1;
    if (lastRow < 2) return undefined;

    let priceRange = costSheet.getRange(`AA2:AB${lastRow}`);
    let priceValues = priceRange.getValues();

    // 大小写不敏感匹配
    for (let row of priceValues) {
        let tableName = row[0]?.toString() || "";
        if (tableName.toLowerCase() === equipmentName.toLowerCase()) {
            let price = parseFloat(row[1]?.toString() || "");
            return isNaN(price) ? undefined : price;
        }
    }
    return undefined;
}

// 从MappingTable(AP/AQ列)模糊匹配通用设备名
function getMappedNameFromMappingTable(equipmentName: string, workbook: ExcelScript.Workbook): string | undefined {
    let damagedEquipmentSheet = workbook.getWorksheet("Damaged Equipment");
    if (!damagedEquipmentSheet) return undefined;

    let lastRow = damagedEquipmentSheet.getRange("AP:AQ").getLastCell().getRowIndex() + 1;
    if (lastRow < 2) return undefined;

    let mappingRange = damagedEquipmentSheet.getRange(`AP2:AQ${lastRow}`);
    let mappingValues = mappingRange.getValues();

    // 双向模糊匹配：变体名包含输入，或输入包含变体名
    for (let row of mappingValues) {
        let variantName = row[0]?.toString() || "";
        let commonName = row[1]?.toString() || "";
        if (variantName.toLowerCase().includes(equipmentName.toLowerCase()) || 
            equipmentName.toLowerCase().includes(variantName.toLowerCase())) {
            return commonName;
        }
    }
    return undefined;
}

关键修复说明 #

• 补全核心函数实现：
  • getPriceFromPriceTable：遍历Cost表的AA/AB列，通过大小写不敏感匹配获取设备价格
  • getMappedNameFromMappingTable：遍历Damaged Equipment表的AP/AQ列，实现双向模糊匹配，返回统一的通用设备名
• 新增&分隔数据拆分逻辑：
  新增splitEquipmentData函数，自动处理J列（数量）和K列（名称）中的&分隔内容，拆分后写入AG/AH/AI/AJ列，同时过滤无效值
• 完善数据严谨性：
  • 处理数量解析失败的情况，标记无效并输出日志
  • 强化空值判断，避免undefined引发的运行错误
  • 未匹配到价格时，明确标记并记录日志便于排查
• 修正工作表引用：
  默认将推测成本写入Damaged Equipment表的T列，若实际需写入Cost表，可修改targetSheet的赋值语句

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