使用ExcelJS跨表复制单元格区域或工作表是否有便捷函数？

Hey there! Great question. Unfortunately, ExcelJS doesn't come with a built-in "one-click" function to copy an entire worksheet or a specific cell range directly. But don't worry—we can easily build this functionality ourselves by iterating through cells and copying their values, styles, and even merged cell configurations.

1. 复制整个工作表 #

If you need to duplicate an entire worksheet (including values, styles, and merged cells), here's a reusable function to do that:

const ExcelJS = require('exceljs');

async function copyEntireWorksheet(sourceSheet, targetSheet) {
  // 可选：将源工作表名称复制到目标工作表
  targetSheet.name = `${sourceSheet.name} - Copy`;

  // 遍历源工作表的每一行
  for (const row of sourceSheet.rows) {
    if (!row) continue; // 跳过空行
    const targetRow = targetSheet.getRow(row.number);

    // 复制每个单元格的值和样式
    for (const cell of row.values) {
      if (!cell || cell === null) continue;
      const targetCell = targetRow.getCell(cell.address.column);
      targetCell.value = cell.value;
      // 深度复制样式（如果不需要完整样式可调整）
      targetCell.style = { ...cell.style };
    }

    targetRow.commit(); // 保存目标行的更改
  }

  // 复制合并单元格区域
  sourceSheet.eachMerge(merge => {
    targetSheet.mergeCells(merge.top, merge.left, merge.bottom, merge.right);
  });
}

// 使用示例
async function runCopy() {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile('your-source-file.xlsx');
  
  const sourceSheet = workbook.getWorksheet('Sheet1');
  const copiedSheet = workbook.addWorksheet(); // 创建新的空白工作表

  await copyEntireWorksheet(sourceSheet, copiedSheet);
  await workbook.xlsx.writeFile('your-output-file.xlsx');
}

runCopy();

2. 复制指定单元格区域 #

If you only need to copy a specific range (like A1 to C5), use this function instead. It lets you define the exact range and pastes it to the target sheet (starting from row 1, column 1 by default—you can adjust this):

async function copyCellRange(sourceSheet, targetSheet, range) {
  // 范围格式：{ top: 1, left: 1, bottom: 5, right: 3 } → 对应A1到C5
  const startRowTarget = 1; // 调整为你想粘贴到目标工作表的起始行
  const startColTarget = 1;

  for (let rowNum = range.top; rowNum <= range.bottom; rowNum++) {
    const sourceRow = sourceSheet.getRow(rowNum);
    const targetRow = targetSheet.getRow(startRowTarget + (rowNum - range.top));

    for (let colNum = range.left; colNum <= range.right; colNum++) {
      const sourceCell = sourceRow.getCell(colNum);
      const targetCell = targetRow.getCell(startColTarget + (colNum - range.left));

      targetCell.value = sourceCell.value;
      targetCell.style = { ...sourceCell.style }; // 如果不需要样式可删除此行
    }

    targetRow.commit();
  }

  // 复制指定范围内的合并单元格
  sourceSheet.eachMerge(merge => {
    // 检查合并单元格是否在目标范围内
    const isInRange = 
      merge.top >= range.top && 
      merge.bottom <= range.bottom && 
      merge.left >= range.left && 
      merge.right <= range.right;

    if (isInRange) {
      // 将源合并单元格坐标转换为目标工作表坐标
      const targetTop = startRowTarget + (merge.top - range.top);
      const targetLeft = startColTarget + (merge.left - range.left);
      const targetBottom = startRowTarget + (merge.bottom - range.top);
      const targetRight = startColTarget + (merge.right - range.left);

      targetSheet.mergeCells(targetTop, targetLeft, targetBottom, targetRight);
    }
  });
}

// 使用示例
async function runRangeCopy() {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile('your-source-file.xlsx');
  
  const sourceSheet = workbook.getWorksheet('Sheet1');
  const targetSheet = workbook.addWorksheet('Range Copy');

  // 复制A1到C5的单元格区域（top:1, left:1, bottom:5, right:3）
  await copyCellRange(sourceSheet, targetSheet, {
    top: 1,
    left: 1,
    bottom: 5,
    right: 3
  });

  await workbook.xlsx.writeFile('your-output-file.xlsx');
}

runRangeCopy();

小提示 #

• 如果你只需要复制值不需要样式，直接删掉targetCell.style = { ...cell.style }这一行即可，能提升运行速度。
• 对于更复杂的场景（比如条件格式或数据验证），需要额外添加逻辑来复制这些属性，因为ExcelJS对这类属性的封装不如基础值和样式直观。

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