Excel UDF（JavaScript API）中批量处理Web请求的可行性问询

当然可以实现这类批量优化逻辑，以下是对应三个需求的具体落地方案：

1. 检测数据区域的起止位置 #

要自动识别连续调用同一自定义函数的单元格区域，核心是利用自定义函数的Invocation对象获取当前调用单元格的地址，再通过Excel上下文遍历相邻单元格，判断其是否使用了目标自定义函数，最终确定区域的起止边界。

示例代码：

// 辅助函数：解析单元格地址为列号、行号（如A1转为[1,1]）
function parseCellAddress(address) {
  const colMatch = address.match(/[A-Z]+/)[0];
  const row = parseInt(address.match(/\d+/)[0]);
  // 列字母转数字（A=1, B=2...）
  let col = 0;
  for (let char of colMatch) {
    col = col * 26 + (char.charCodeAt(0) - 64);
  }
  return [col, row];
}

// 辅助函数：数字转列字母（1=A, 2=B...）
function getColLetter(colNum) {
  let letter = '';
  while (colNum > 0) {
    const remainder = (colNum - 1) % 26;
    letter = String.fromCharCode(65 + remainder) + letter;
    colNum = Math.floor((colNum - 1) / 26);
  }
  return letter;
}

// 检测连续的同函数调用区域
async function detectBatchRange(invocation) {
  const currentAddr = invocation.address;
  const [currentCol, currentRow] = parseCellAddress(currentAddr);
  let startRow = currentRow, endRow = currentRow;
  let startCol = currentCol, endCol = currentCol;

  // 向上遍历检测连续同函数单元格
  while (true) {
    const checkRow = startRow - 1;
    if (checkRow < 1) break;
    const checkAddr = `${getColLetter(startCol)}${checkRow}`;
    const formula = await Excel.run(async context => {
      const cell = context.workbook.worksheets.getActiveWorksheet().getCell(checkAddr);
      cell.load("formula");
      await context.sync();
      return cell.formula;
    });
    if (formula && formula.includes('BATCH_FETCH_FUNC')) { // 替换为你的自定义函数名
      startRow = checkRow;
    } else {
      break;
    }
  }

  // 向下遍历（逻辑同上，略）
  // 向左遍历（逻辑同上，略）
  // 向右遍历（逻辑同上，略）

  return `${getColLetter(startCol)}${startRow}:${getColLetter(endCol)}${endRow}`;
}

2. 单次请求获取所需数据 #

拿到批量区域后，先提取区域内所有单元格的请求参数，再将参数打包为单次请求的Payload，调用后端接口获取批量数据。

示例代码：

// 从批量区域收集所有请求参数
async function collectBatchParams(rangeAddress) {
  return await Excel.run(async context => {
    const range = context.workbook.worksheets.getActiveWorksheet().getRange(rangeAddress);
    range.load("values");
    await context.sync();
    // 扁平化参数数组（假设每个单元格传入一个参数）
    return range.values.flat().filter(param => param !== null && param !== '');
  });
}

// 发起批量请求
async function fetchBatchData(params) {
  const response = await fetch('https://your-api-endpoint.com/batch', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ requestIds: params })
  });
  if (!response.ok) throw new Error('批量请求失败');
  return await response.json();
}

3. 异步更新Excel区域避免阻塞UI #

利用Excel JavaScript API的批量操作能力，在获取到批量数据后，通过Excel.run上下文一次性将数据写入目标区域，全程使用异步逻辑避免阻塞UI。同时要确保当前调用单元格能返回对应的数据，符合自定义函数的执行规范。

示例完整自定义函数：

/**
 * 批量获取数据的自定义函数
 * @customfunction
 * @param {string} param 当前单元格的请求参数
 * @param {CustomFunctions.Invocation} invocation 调用上下文
 * @returns {Promise<string>} 当前单元格对应的数据
 */
async function BATCH_FETCH_FUNC(param, invocation) {
  try {
    // 1. 检测批量区域
    const batchRange = await detectBatchRange(invocation);
    // 2. 收集所有参数并发起单次请求
    const allParams = await collectBatchParams(batchRange);
    const batchData = await fetchBatchData(allParams);
    // 3. 批量更新区域数据
    await Excel.run(async context => {
      const range = context.workbook.worksheets.getActiveWorksheet().getRange(batchRange);
      // 将数据转换为二维数组匹配单元格结构
      const values = batchData.map(item => [item.result]);
      range.values = values;
      await context.sync();
    });
    // 返回当前单元格对应的数据
    const currentIndex = allParams.indexOf(param);
    return currentIndex !== -1 ? batchData[currentIndex].result : '无匹配数据';
  } catch (err) {
    return `错误: ${err.message}`;
  }
}

注意事项 #

• 权限配置：确保自定义函数具备读取单元格公式和写入数据的权限，在清单文件中配置对应的Permissions节点。
• 重复请求拦截：可以通过缓存已检测的区域和请求结果，避免同一区域多次触发批量请求。
• 错误处理：批量请求失败时，要批量返回错误信息，避免部分单元格无响应。

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