Copy a range of cells or worksheet from using ExcelJS

Question

Is it possible / is there a convenience function to copy a range of cells and/or a whole worksheet from one Excel sheet to another using ExcelJS?

score 4 · Answer 1 · answered Jan 18 '19 at 04:38

4

copying worksheets with Merged cells

 var ws1 = workbook.getWorksheet('demo');
  let copySheet = workbook.addWorksheet('newdemo');

  copySheet.model = Object.assign(ws1.model, {
    mergeCells: ws1.model.merges
  });
  copySheet.name = 'new demo';

answered Jan 18 '19 at 04:38

Tính Ngô Quang

4,400
1
33
33

This is actually the easiest way to merge sheets from separate workbooks in exceljs. solved my issue in 3LOC – BoyePanthera May 17 '20 at 23:35
2

many thanks for posting an answer. I am trying to use your solution but i am facing issues `TypeError: Cannot read property 'merges' of undefined`. – opensource-developer Dec 16 '20 at 12:25

score 2 · Answer 2 · answered Mar 09 '20 at 07:23

var sourceWorkbook= new Excel.Workbook();
var sourceWorksheet;
sourceWorkbook.xlsx.readFile("template.xlsx").then(function(){
    sourceWorksheet= sourceWorkbook.getWorksheet(1);
    var targetWorkbook = new Excel.Workbook;
    var targetSheet = targetWorkbook.addWorksheet();
    sourceWorksheet.eachRow((row, rowNumber) => {
        var newRow = targetSheet.getRow(rowNumber);
        row.eachCell((cell, colNumber) => {
            var newCell = newRow.getCell(colNumber)
            for(var prop in cell)
            {
                newCell[prop] = cell[prop];
            }
        })
   })

This is what I done with my project. It works well.

many thanks for posting an answer. I am trying to use your solution but i am facing issues copying merged cells, the cells in the resulting workbook don't remain merged. Any help on how to fix this would be great, thanks. — opensource-developer, Dec 16 '20 at 12:14

score 0 · Answer 3 · answered Oct 09 '18 at 17:19

You can copy the whole worksheet from one Excel sheet to another using ExcelJS. Below code will work

const Excel = require('exceljs');

async function copyExcel() {
    let targetWorkbook = new Excel.Workbook();
    targetWorkbook = await targetWorkbook.xlsx.readFile('target.xlsx');
    const targetWorksheet = targetWorkbook.getWorksheet('target'); // you can add new sheet as well.

    let sourceWorkbook = new Excel.Workbook();
    sourceWorkbook = await sourceWorkbook.xlsx.readFile('source.xlsx');
    const sourceWorksheet = sourceWorkbook.getWorksheet('source');

    sourceWorksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
        var targetRow = targetWorksheet.getRow(rowNumber);
        row.eachCell({ includeEmpty: false }, (cell, cellNumber) => {
            targetRow.getCell(cellNumber).value = cell.value;
        });
        row.commit();
    });
    await targetWorkbook.xlsx.writeFile('target.xlsx');
}

copyExcel();

Copy a range of cells or worksheet from using ExcelJS

3 Answers3