Office Script拆分Excel表格Product列多值的异常问题求助

我需要在Excel中基于逗号、分号或竖线作为分隔符拆分Product列的多值。目前的Office Script代码能执行拆分并复制对应列的其他值，但存在两个问题：

• 生成大量与原表格行数相关的空白行
• 将值拆分为单个字符而非完整取值，不符合预期

表格与期望输出 #

现有错误代码 #

async function main(workbook: ExcelScript.Workbook) {
    // Set the name of the sheet and the column to split
    let sheetName = "Sheet1";
    let columnNameToSplit = "Product";
    let delimiters = [",", ";", "|"];

    // Get the sheet and table
    let sheet = workbook.getWorksheet(sheetName);
    let table = sheet.getTables()[0];

    // Get the index of the column to split
    let columnIndexToSplit = table.getHeaderRowRange().getTexts()[0].indexOf(columnNameToSplit);

    // Get the data from the table
    let data = table.getRangeBetweenHeaderAndTotal().getValues();

    // Create an array to hold the new data
    let newData: (string | number)[][] = [];

    // Loop through each row of data
    for (let i = 0; i < data.length; i++) {
        let row = data[i];
        let cellValue = row[columnIndexToSplit];

        // Check if the cell value is a string and contains one of the delimiters
        if (typeof cellValue === "string" && delimiters.some(delimiter => cellValue.includes(delimiter))) {
            // Split the cell value by the delimiters
            let splitValues = cellValue.split(new RegExp(delimiters.join("|"), "g"));

            // Add a new row to the new data array for each split value
            for (let j = 0; j < splitValues.length; j++) {
                let newRow = [...row];
                newRow[columnIndexToSplit] = splitValues[j];
                newData.push(newRow);
            }
        } else {
            // Add the original row to the new data array
            newData.push(row);
        }
    }

    // Clear the old data from the table and add the new data
    table.getRangeBetweenHeaderAndTotal().clear();
    table.addRows(-1, newData);
}

实际错误输出 #

问题分析与修复方案 #

错误原因 #

1. 正则表达式未转义特殊字符：|在正则中是逻辑或的特殊符号，直接用delimiters.join("|")会生成,;|，正则会将其解析为匹配逗号、分号或空字符，导致拆分出单个字符。
2. 空白行生成：仅用clear()清除旧数据内容但未删除表格行，后续添加新数据时旧行保留，同时拆分可能产生空字符串值，最终生成空白行。
3. 未过滤无效拆分值：拆分后可能出现空字符串（如分隔符前后的空格或连续分隔符），直接添加会生成空行。

修正后的代码 #

async function main(workbook: ExcelScript.Workbook) {
    const sheetName = "Sheet1";
    const columnNameToSplit = "Product";
    const delimiters = [",", ";", "|"];

    // 获取工作表和表格
    const sheet = workbook.getWorksheet(sheetName);
    const table = sheet.getTables()[0];
    if (!table) {
        console.error("未找到表格");
        return;
    }

    // 获取目标列索引
    const headerTexts = table.getHeaderRowRange().getTexts()[0];
    const columnIndexToSplit = headerTexts.indexOf(columnNameToSplit);
    if (columnIndexToSplit === -1) {
        console.error("未找到目标列");
        return;
    }

    // 获取表格数据
    const data = table.getRangeBetweenHeaderAndTotal().getValues();
    const newData: (string | number)[][] = [];

    // 遍历处理每一行
    for (const row of data) {
        const cellValue = row[columnIndexToSplit];
        if (typeof cellValue === "string") {
            // 转义分隔符中的正则特殊字符，构建正确的正则表达式
            const escapedDelimiters = delimiters.map(delimiter => RegExp.escape(delimiter));
            const splitRegex = new RegExp(escapedDelimiters.join("|"), "g");
            // 拆分后过滤掉空值和纯空格值
            const splitValues = cellValue.split(splitRegex).filter(val => val.trim() !== "");
            
            if (splitValues.length > 0) {
                // 为每个有效拆分值生成新行
                splitValues.forEach(val => {
                    const newRow = [...row];
                    newRow[columnIndexToSplit] = val.trim();
                    newData.push(newRow);
                });
            } else {
                // 无有效拆分值时保留原行
                newData.push(row);
            }
        } else {
            // 非字符串值直接保留原行
            newData.push(row);
        }
    }

    // 先删除所有旧数据行，再添加新数据
    const rowCount = table.getRowCount();
    if (rowCount > 0) {
        table.deleteRowsAt(0, rowCount);
    }
    if (newData.length > 0) {
        table.addRows(newData);
    }
}

关键修改点 #

• 正则转义：使用RegExp.escape()转义分隔符中的特殊字符（如|变为\|），确保拆分逻辑正确。
• 过滤无效值：拆分后用filter(val => val.trim() !== "")去掉空字符串和纯空格值，避免生成空行。
• 正确删除旧行：用table.deleteRowsAt(0, rowCount)删除所有现有数据行，而非仅清除内容，解决空白行问题。
• 健壮性处理：增加表格和目标列的存在性检查，避免报错。

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