使用Office JS从单元格地址列表生成Excel范围

需求说明 #

给定一组Excel单元格地址列表（数量、排列方式任意），需按照Excel规则将其转换为最小的连续范围列表：

• 连续的横向单元格合并为行范围（如["A1","B1","C1"] → "A1:C1"）
• 多行且列范围完全一致的连续行范围，合并为跨行列范围（如["F2","G2","F3","G3","F4","G4"] → "F2:G4"）
• 单个单元格需格式化为"X5:X5"形式

示例输入输出：

const exampleCells1 = ["A1","B1","C1","F3","F4","G3","G4","X1"];
// 预期输出：["A1:C1","F3:G4","X1:X1"]

const exampleCells2 = ["AN299","AN300"];
// 预期输出：["AN299:AN300"]

const exampleCells3 = ["P44"];
// 预期输出：["P44:P44"]

完整实现代码 #

// 将Excel列名（如A、AN）转换为数字
const colNameToNum = (col) => {
  let num = 0;
  for (const char of col.toUpperCase()) {
    num = num * 26 + (char.charCodeAt(0) - 64);
  }
  return num;
};

// 将数字转换回Excel列名
const numToColName = (num) => {
  let name = '';
  while (num > 0) {
    const remainder = (num - 1) % 26;
    name = String.fromCharCode(65 + remainder) + name;
    num = Math.floor((num - 1) / 26);
  }
  return name;
};

// 解析单元格地址为列号、行号和原始字符串
const parseCell = (cell) => {
  const match = cell.match(/^([A-Za-z]+)(\d+)$/);
  if (!match) throw new Error(`Invalid cell address: ${cell}`);
  const colName = match[1].toUpperCase();
  return {
    colNum: colNameToNum(colName),
    rowNum: parseInt(match[2], 10),
    original: cell,
    colName
  };
};

const getRanges = (cells) => {
  if (cells.length === 0) return [];

  // 解析并排序单元格：先按行号升序，同按列号升序
  const parsedCells = cells.map(parseCell).sort((a, b) => {
    if (a.rowNum !== b.rowNum) return a.rowNum - b.rowNum;
    return a.colNum - b.colNum;
  });

  // 第一步：合并同一行内的连续列，得到行级范围列表
  const rowRanges = [];
  let currentRowRange = {
    startRow: parsedCells[0].rowNum,
    endRow: parsedCells[0].rowNum,
    startCol: parsedCells[0].colNum,
    endCol: parsedCells[0].colNum
  };

  for (let i = 1; i < parsedCells.length; i++) {
    const cell = parsedCells[i];
    // 如果是同一行，且列号连续
    if (cell.rowNum === currentRowRange.startRow && cell.colNum === currentRowRange.endCol + 1) {
      currentRowRange.endCol = cell.colNum;
    } else {
      // 保存当前行范围，开始新的
      rowRanges.push(currentRowRange);
      currentRowRange = {
        startRow: cell.rowNum,
        endRow: cell.rowNum,
        startCol: cell.colNum,
        endCol: cell.colNum
      };
    }
  }
  rowRanges.push(currentRowRange);

  // 第二步：合并连续行且列范围一致的行级范围
  const mergedRanges = [];
  let currentMergedRange = {...rowRanges[0]};

  for (let i = 1; i < rowRanges.length; i++) {
    const range = rowRanges[i];
    // 如果行连续，且列范围完全相同
    if (range.startRow === currentMergedRange.endRow + 1 && 
        range.startCol === currentMergedRange.startCol && 
        range.endCol === currentMergedRange.endCol) {
      currentMergedRange.endRow = range.endRow;
    } else {
      mergedRanges.push(currentMergedRange);
      currentMergedRange = {...range};
    }
  }
  mergedRanges.push(currentMergedRange);

  // 转换为Excel范围字符串格式
  return mergedRanges.map(range => {
    const start = `${numToColName(range.startCol)}${range.startRow}`;
    const end = `${numToColName(range.endCol)}${range.endRow}`;
    return `${start}:${end}`;
  });
};

// 测试示例
console.log(getRanges(exampleCells1)); // ["A1:C1","F3:G4","X1:X1"]
console.log(getRanges(exampleCells2)); // ["AN299:AN300"]
console.log(getRanges(exampleCells3)); // ["P44:P44"]

关键逻辑说明 #

1. 单元格解析与排序：将每个单元格地址拆分为列名、行号，转换列名为数字方便计算，再按行号、列号排序，确保连续单元格相邻。
2. 行内连续列合并：遍历同一行的单元格，将连续列的单元格合并为单个行范围。
3. 跨行列范围合并：检查相邻行的范围是否列完全一致且行连续，合并为跨多行的矩形范围。
4. 格式转换：将数字形式的列号转换回Excel列名，拼接成标准的范围字符串。

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