How to save a worksheet from excel as a new excel file using javascript

Question

An excel file with two worksheets in it(TAB1, TAB2).

How to save 'TAB1' from the below excel as a new Excel file say tab1.xlsx?

I am trying to work it out with exceljs but not sure how to save it as new file. If it cannot be done with help of exceljs please suggest any other way to get this done.

const ExcelJS = require('exceljs');

const wb = new ExcelJS.Workbook();
//const newwork =  new ExcelJS.Workbook();

filename = "abc.xlsx";
const fl = async () => {
    wb.xlsx.readFile(filename)
        .then(async function () {
            wb.eachSheet(async function (worksheet, sheetId) {
                console.log("worksheet", worksheet, "sheetid", sheetId);
                if(worksheet.name.toLocaleLowerCase() ==='tab1'){
                    var data = wb.getWorksheet(worksheet.name);
                  
                    // how to save worksheet as tab1.xlsx
                    // let dt =await newwork.xlsx.writeFile('tab1.xlsx',data);
                   
                }
            });
        });
}

fl();

I think there is no simple solution, can you try the following steps? 1. create workbook `var newWorkBook = new ExcelJS.Workbook();` 2. create a new sheet `var newWorkSheet = newWorkBook.addWorksheet(worksheet.name);` 3. for each row set values from the original sheet to the new one: `worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) { newWorkSheet.getRow(rowNumber).values = row.values; });` 4. now you would be able to save the workbook `await newWorkBook.xlsx.writeFile(worksheet.name + '.xlsx');` — Artúr Manó Marschal, Jun 24 '21 at 09:07
How about setting the worksheet directly: `newWorkbook.worksheets[0] = worksheet;` — Artúr Manó Marschal, Jun 24 '21 at 17:44

score 1 · Answer 1 · answered Jun 25 '21 at 07:59

I have created a not so nice solution, although it worked for me. Basically you read the xlsx multiple times, and remove the not necessary worksheets. This way formatting is preserved for me.

async function excelTest() {
    await workbook.xlsx.readFile('test.xlsx').then(async function(){
        workbook.eachSheet(async function(worksheet, sheetId) {
            let newWorkbook = new exceljs.Workbook();
            await newWorkbook.xlsx.readFile('test.xlsx').then(async function(){
                newWorkbook.eachSheet(function(newSheet, newSheetId){
                    if (sheetId != newSheetId) {
                        newWorkbook.removeWorksheet(newSheetId);
                    }
                });
                await newWorkbook.xlsx.writeFile(sheetId + '.xlsx');
            });
        });
    });
}

no, it didn't work for me. Although it saved when opened excel threw an error -Excel was able to open the file by repairing or removing content — saicharan, Jun 26 '21 at 06:23
I couldn't find any better solution in the API, probably you could submit a feature request on ExcelJS GitHub. Other than that using an alternative library might have some solutions for this requirement. — Artúr Manó Marschal, Jun 26 '21 at 19:56

score 0 · Answer 2 · answered Jul 01 '21 at 05:26

const ExcelJS = require('exceljs');

const sourceWb = new ExcelJS.Workbook();

filename = "abc.xlsx";
const fl = async () => {
    sourceWb.xlsx.readFile(filename)
        .then(async function() {
            sourceWorksheet = sourceWb.getWorksheet('tab1');
            var targetWorkbook = new Excel.Workbook;
            var targetSheet = targetWorkbook.addWorksheet('newtab');
            targetSheet.model = Object.assign(sourceWorksheet.model, {
                mergeCells: sourceWorksheet.model.merges
            });
            targetSheet.name = 'newtab';
            targetWorkbook.xlsx.writeFile('target.xlsx');


        });
}

you can try model.merges

How to save a worksheet from excel as a new excel file using javascript

2 Answers2