Excel自定义公式单元格取消链接时上下文同步耗时过长的优化咨询

问题核心 #

原代码在处理工作表和单元格时，循环内频繁调用ctx.sync()，每次同步都会触发与Excel服务端的通信，大量重复的同步请求导致整体耗时长达5-10分钟。虽然移除同步会导致功能异常，但可以通过批量操作+减少同步次数来解决性能问题。

优化措施 #

1. 批量加载所有资源：一次性加载所有工作表的使用范围、单元格地址，以及所有溢出单元格的值，避免分次加载触发多次同步。
2. 集中处理修改操作：先收集所有需要替换公式的单元格操作，最后统一提交同步，减少服务端交互次数。
3. 移除循环内的同步调用：只保留3次关键同步：加载工作表列表、加载所有数据资源、提交最终修改。

优化后的代码 #

async unlinkWorkbook() {
    try {
        await Excel.run(async (ctx) => {
            const worksheets = ctx.workbook.worksheets;
            worksheets.load('name');
            await ctx.sync();

            const usedRanges: Excel.Range[] = [];
            const cellPropertiesList: any[] = [];

            // 批量加载所有工作表的usedRange和cellProperties
            for (let i = 0; i < worksheets.items.length; i++) {
                const ws = worksheets.items[i];
                const usedRange = ws.getUsedRange();
                usedRange.load(['address', 'values', 'formulas']);
                usedRanges.push(usedRange);

                const cellProperties = usedRange.getCellProperties({ address: true });
                cellPropertiesList.push(cellProperties);
            }
            await ctx.sync();

            // 批量收集所有需要检查的溢出范围
            const spillRanges: any[] = [];
            for (let i = 0; i < worksheets.items.length; i++) {
                const ws = worksheets.items[i];
                const usedRange = usedRanges[i];
                const cellProperties = cellPropertiesList[i].value;
                const sheetSpillRanges: any[] = [];

                usedRange.formulas.forEach((row, xIndex) => {
                    row.forEach((formula, yIndex) => {
                        const location = cellProperties[xIndex][yIndex].address.split('!')[1];
                        const cell = ws.getRange(location);
                        const spillRange = cell.getSpillingToRangeOrNullObject().load(['values']);
                        sheetSpillRanges.push(spillRange);
                    });
                });
                spillRanges.push(sheetSpillRanges);
            }
            await ctx.sync();

            // 批量执行公式替换操作
            let count = 0;
            for (let i = 0; i < worksheets.items.length; i++) {
                const ws = worksheets.items[i];
                const usedRange = usedRanges[i];
                const cellProperties = cellPropertiesList[i].value;
                const sheetSpillRanges = spillRanges[i];
                let k = 0;

                usedRange.formulas.forEach((row, xIndex) => {
                    row.forEach((formula, yIndex) => {
                        if (formula && isNaN(formula) && 
                            (formula.toLowerCase().includes('q.get') || formula.toLowerCase().includes('q.getlist'))) {
                            const location = cellProperties[xIndex][yIndex].address;
                            let cell = ws.getRange(location);
                            const spillRange = sheetSpillRanges[k];

                            if (spillRange.values) {
                                // 处理溢出单元格
                                cell = cell.getResizedRange(spillRange.values.length - 1, spillRange.values[0].length - 1);
                                cell.formulas = spillRange.values;
                            } else {
                                // 处理普通单元格
                                const value = usedRange.values[xIndex][yIndex];
                                cell.values = [[value]];
                                if (!isNaN(value) && value.toString().includes('.')) {
                                    cell.numberFormat = [['0.00']];
                                }
                            }
                            count++;
                        }
                        k++;
                    });
                });
            }

            // 最后一次同步提交所有修改
            if (count > 0) {
                await ctx.sync();
                this.apiDataService.showUnlinkModalFn({
                    severity: 'success',
                    summary: '成功',
                    detail: '公式已成功取消链接。',
                });
            } else {
                this.apiDataService.showBannerUp({
                    severity: 'info',
                    summary: '取消链接',
                    detail: '未找到需要取消链接的公式！',
                });
            }
        });
    } catch {
        this.apiDataService.showUnlinkModalFn({
            severity: 'error',
            summary: '取消链接失败',
            detail: '出现错误，请重试！',
            type: 'workbook',
        });
    }
}

关键改动说明 #

• 将原代码中循环内的多次ctx.sync()合并为3次：加载工作表、加载所有数据资源、提交修改，大幅减少服务端通信次数。
• 批量收集所有溢出范围的加载请求，一次性同步获取数据，避免逐个单元格触发加载。
• 所有修改操作先在本地上下文完成，最后一次同步提交到Excel，避免中间多次提交的开销。

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