How to combine 2 XLSX files into one using NodeJS

Question

I am currently working with generating XLSX spreadsheets using NodeJS. I am using the module xlsx-populate to create single-sheet XLSX files on an Express server.

I was wondering if anyone knew a way to combine multiple XLSX files into one file with multiple sheets using Node.

Thanks!

Example:

const xlsx = require('xlsx-populate');

xlsx.fromFileAsync('template1.xlsx')
  .then((workbook) => {
    // populate the workbook with stuff

    return workbook.toFileAsync('spreadsheet1.xlsx');
  })
  .then(() => xlsx.fromFileAsync('template2.xlsx'))
  .then((workbook) => {
    // populate the other workbook with stuff

    return workbook.toFileAsync('spreadsheet2.xlsx');
  });

This Promise chain saves two separate XLSX files (spreadsheet1.xlsx, spreadsheet2.xlsx), each being built from a corresponding template file. xlsx-populate does not let you create multiple sheets from different XLSX files on the same workbook, so I am wondering if it is possible to combine the two workbooks into one with multiple sheets?

EDIT

I ended up switching modules to excel4node which I found to be a more flexible, albeit more complex, module. My problem was that I had two template files, each containing an image, I wanted to merge into one file using xlsx-populate.

Since I failed to failed to find a successful way to merge the two templates to one file using xlsx-populate, I used excel4node to rebuild the template files from scratch, inserting the images (which xlsx-populate does not support).

Answer 1

This snippet can merge two excel files into a new one.

 const XlsxPopulate = require('xlsx-populate');

 Promise.all([
    XlsxPopulate.fromFileAsync('./src/data/template.xlsx'),
    XlsxPopulate.fromFileAsync('./src/data/template2.xlsx')
 ])
  .then(workbooks => {
    const workbook = workbooks[0];
    const workbook2 = workbooks[1];
    const sheets2 = workbook2.sheets();

    sheets2.forEach(sheet => {
      const newSheet = workbook.addSheet(sheet.name());
      const usedRange = sheet.usedRange();
      const oldValues = usedRange.value();

      newSheet.range(usedRange.address()).value(oldValues);
    });

    return workbook.toFileAsync('./src/data/xlsx-populate/spreadsheet2.xlsx');
 });

Known Issue:

It will lose the second one's style, that's because the copy style feature was under development, please refer here.