How to merge worksheets of different workbooks in one workbook ?

[shubhamseth29]

Hi ,
I want to generate a combined excel workbook , where in I want to merge all different excel sheets into one.
Currently I am doing something of this sort

bookOld.worksheets.forEach((sheetLocal) => {
  const storedData = [];
  bookNew.addWorksheet(sheetLocal.name);
  sheetLocal.eachRow((row) => {
    storedData.push(row);
  });
  storedData.forEach((row) => {
    bookNew.getWorksheet(sheetLocal.name).addRow(row);
  });
});

But this doesn't work , as the data I get from eachRow method is not as it is need by addRow method.

Please help if you can :)

[liashchynskyi]

@shubhamseth29 Hello, I bumped into something similar today. I hope this is still relevant for you.

I needed to merge two CSV files with the same structure. Here's how I've done it.

1. Create new sheet

const workbook = new Excel.Workbook();
const sheet = workbook.addWorksheet(sheetName);

2. Map files, in my case files are streams - files: stream.PassThrough[]

for await (const [index, file] of files.entries()) {
     const fileWorkbook = new Excel.Workbook();
     const oldSheet = await fileWorkbook.csv.read(file);

     if (index === 0) {
      // Here we add header row just once. We also use `compact` from `lodash` to remove any empty rows
      // Row numbers start from 1
       sheet.addRow(compact(castArray(oldSheet.getRow(1).values as any[])));
     }

    // Here we map rows from `old` files
     const oldRows = oldSheet
       .getRows(2, oldSheet.rowCount)
       .map((row) => row.values) // That why it wasn't working, need to extract only `values`
       .map((row) => JSON.parse(JSON.stringify(row))); 

     oldRows.forEach((row) => {
       if (row.length === 0) return;

      // In my case it was adding empty value at start of each row, so I used slice
       const newRow = (row as any[]).slice(1).map((el) => {
         return el === null ? '' : el;
       });

       sheet.addRow(newRow);
     });
   }

.map((row) => JSON.parse(JSON.stringify(row)));

This is an interesting case. If we console log any row it will output <1 empty value> where any row value is empty. If we try to access that element from row by index it will output undefined as expected. But if we map row values to check for undefined it won't be there. Somehow exceljs does similar transformation as compact from lodash even if we set {ignoreEmpty: false} in parser options.

Without this row all empty rows will be missed in final file which will break the structure. So after parse and stringify those values will be replaced by null. So when mapping we can replace them by empty strings.

3. Export as stream

const pass = new stream.PassThrough();

workbook.csv.write(pass, {
      sheetName: sheetName,
      dateFormat: 'DD/MM/YYYY',
});

return pass;

So that's pretty much it. Tested it in my case and it's working.

[raizdev]

@shubhamseth29 Hello, I bumped into something similar today. I hope this is still relevant for you.

I needed to merge two CSV files with the same structure. Here's how I've done it.

1. Create new sheet

const workbook = new Excel.Workbook();
const sheet = workbook.addWorksheet(sheetName);

2. Map files, in my case files are streams - files: stream.PassThrough[]

for await (const [index, file] of files.entries()) {
     const fileWorkbook = new Excel.Workbook();
     const oldSheet = await fileWorkbook.csv.read(file);

     if (index === 0) {
      // Here we add header row just once. We also use `compact` from `lodash` to remove any empty rows
      // Row numbers start from 1
       sheet.addRow(compact(castArray(oldSheet.getRow(1).values as any[])));
     }

    // Here we map rows from `old` files
     const oldRows = oldSheet
       .getRows(2, oldSheet.rowCount)
       .map((row) => row.values) // That why it wasn't working, need to extract only `values`
       .map((row) => JSON.parse(JSON.stringify(row))); 

     oldRows.forEach((row) => {
       if (row.length === 0) return;

      // In my case it was adding empty value at start of each row, so I used slice
       const newRow = (row as any[]).slice(1).map((el) => {
         return el === null ? '' : el;
       });

       sheet.addRow(newRow);
     });
   }

.map((row) => JSON.parse(JSON.stringify(row)));

This is an interesting case. If we console log any row it will output <1 empty value> where any row value is empty. If we try to access that element from row by index it will output undefined as expected. But if we map row values to check for undefined it won't be there. Somehow exceljs does similar transformation as compact from lodash even if we set {ignoreEmpty: false} in parser options.

Without this row all empty rows will be missed in final file which will break the structure. So after parse and stringify those values will be replaced by null. So when mapping we can replace them by empty strings.

3. Export as stream

const pass = new stream.PassThrough();

workbook.csv.write(pass, {
      sheetName: sheetName,
      dateFormat: 'DD/MM/YYYY',
});

return pass;

So that's pretty much it. Tested it in my case and it's working.

does this also work for merged cells?