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
– Artúr Manó Marschal, Commented Jun 24, 2021 at 9:07
How about setting the worksheet directly: newWorkbook.worksheets[0] = worksheet; — Artúr Manó Marschal
– Artúr Manó Marschal, Commented Jun 24, 2021 at 17:44

Artúr Manó Marschal · Accepted Answer · 2021-06-25 07:59:44Z

1

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');
            });
        });
    });
}

answered Jun 25, 2021 at 7:59

Artúr Manó Marschal

863 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

saicharan Over a year ago

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

Artúr Manó Marschal Over a year ago

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.

Tushar Nikam · Accepted Answer · 2021-07-01 05:26:02Z

0

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

answered Jul 1, 2021 at 5:26

Tushar Nikam

6245 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related