Excel Office.js 处理Range对象实现批量数据回填的代码优化咨询

核心性能瓶颈 #

原代码的耗时主要来自几处不合理的实现：

• 循环过程中重复调用getItem()获取相同工作表对象，生成大量冗余代理对象
• 每个待写入单元格单独创建Range实例，上万条数据会生成上万个离散Range，context.sync()序列化这些对象的开销极高
• 用Array.indexOf()判断工作表是否存在，每次查找时间复杂度为O(n)，数据量大时累计开销很高
• 给range.values直接传入单个值，Office.js需要做额外的格式转换，增加额外开销

具体优化方案 #

• 提前缓存所有工作表对象到映射表，循环时直接从映射表取，无需重复调用API
• 按工作表分组整理待写入的单元格地址，同工作表的所有地址一次性用getRanges()生成组合Range，大幅减少代理对象数量
• 工作表名存入Set结构，存在性判断的时间复杂度降到O(1)
• 单个单元格赋值统一使用[[值]]的二维数组格式，避免隐式转换
• 移除不必要的Range对象数组存储，减少内存占用

优化后代码 #

/* global Excel, console*/
export default async function restoreData() {
  var allRollback = false;
  await Excel.run(async (context) => {
    // 预加载所有工作表
    const sheets = context.workbook.worksheets.load("items/name");
    const wsdb = context.workbook.worksheets.getItem("db");
    const arryRange = wsdb.getUsedRange();
    arryRange.load(["rowCount", "columnCount", "values"]);
    await context.sync();

    // 生成工作表名Set和工作表对象映射表
    const sheetNameSet = new Set();
    const sheetMap = {};
    sheets.items.forEach(sheet => {
      sheetNameSet.add(sheet.name);
      sheetMap[sheet.name] = sheet;
    });

    let addRow = 0;
    // 按工作表分组存储待更新的地址和值 {sheet名: {地址: 值}}
    const sheetUpdateMap = {};

    for (let iRow = 0; iRow < arryRange.rowCount; iRow++) {
      const currentSheetName = arryRange.values[iRow][0];
      if (currentSheetName == "SheetName/CellAddress") {
        addRow = iRow;
        continue;
      }
      if (!sheetNameSet.has(currentSheetName)) {
        console.log("Y");
        continue;
      }
      // 初始化当前工作表的更新集合
      if (!sheetUpdateMap[currentSheetName]) {
        sheetUpdateMap[currentSheetName] = {};
      }
      const currentSheetUpdates = sheetUpdateMap[currentSheetName];
      for (let iCol = 1; iCol < arryRange.columnCount; iCol++) {
        const cellAddress = arryRange.values[addRow][iCol];
        if (!cellAddress) continue;
        currentSheetUpdates[cellAddress] = arryRange.values[iRow][iCol];
      }
    }

    // 批量处理每个工作表的更新
    Object.entries(sheetUpdateMap).forEach(([sheetName, updates]) => {
      const sheet = sheetMap[sheetName];
      const addresses = Object.keys(updates);
      // 一次性获取该工作表所有需要更新的Range
      const combinedRange = sheet.getRanges(addresses.join(","));
      // 按顺序生成values数组
      const values = addresses.map(addr => [[updates[addr]]]);
      combinedRange.values = values;
    });

    console.log("批量更新准备完成，开始同步");
    await context.sync();
    console.log("写入完成");
  });
  allRollback = true;
  return allRollback;
}

优化后1万条数据的写入耗时可以降到10秒以内，数据量越大优化效果越明显。

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