Export multiple array of object in excel as multiple sheets

Question

Suppose I have multiple array of object as,

const books = [{"book":"harry","part":1},{"book":"harry","part":2},{"book":"harry","part":3}, 
               {"book":"lotr","part":1},{"book":"lotr","part":2}]

const personDetails = [{"name":"ram","age":21},{"name":"jack","age":22},{"name":"ryan","age":45}];

I can download for one array of object, by method as,

import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';

function downloadExcel() {
    const fileType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
    const bookDetails = XLSX.utils.json_to_sheet(books);
    const wb = { Sheets: { 'BookDet': bookDetails }, SheetNames: ['BookDet'] };
    const excelBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
    const data1 = new Blob([excelBuffer], { type: fileType });
    FileSaver.saveAs(data1, "BookDetail Summary.xlsx");
}

But how can I export multiple array of object in different sheets in excel. If anyone needs any further information please let me know.

Can you please provide information regarding which libraries you are using? Like FileSaer.js & any else? — Karan
– Karan, Commented Apr 19, 2021 at 5:03

Karan · Accepted Answer · 2021-04-19 05:33:26Z

3

Try creating workbook in below manner using var wb = XLSX.utils.book_new();.

Create worksheets with XLSX.utils.json_to_sheet & append both worksheeet objects to workbook with XLSX.utils.book_append_sheet.

Reference : https://www.npmjs.com/package/xlsx#working-with-the-workbook

import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';

function downloadExcel() {
    
    /* create a new blank workbook */
    var wb = XLSX.utils.book_new();

    /* create a worksheet for books */
    var wsBooks = XLSX.utils.json_to_sheet(books);

    /* Add the worksheet to the workbook */
    XLSX.utils.book_append_sheet(wb, wsBooks, "Books");

    /* create a worksheet for person details */
    var wsPersonDetails = XLSX.utils.json_to_sheet(personDetails);

    /* Add the worksheet to the workbook */
    XLSX.utils.book_append_sheet(wb, wsPersonDetails, "PersonDetails");
    
    
    const fileType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
    const excelBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
    const data1 = new Blob([excelBuffer], { type: fileType });
    FileSaver.saveAs(data1, "BookDetail Summary.xlsx");    
}

edited Apr 19, 2021 at 5:33

answered Apr 19, 2021 at 5:22

Karan

12.7k3 gold badges31 silver badges45 bronze badges

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

4 Comments

Siva Pradhan Over a year ago

I had one more query related to this question. Can I ask over here only or should I create new question?

Siva Pradhan Over a year ago

Question is: I want to add a text for each sheet say in sheet1 contains text:" Sheet1" and sheet2 contains text :"sheet2" at top and after that there is table.

Karan Over a year ago

I briefly reviewed documentation from reference but do not found any function which could do so. You can try finding some workaround if possible.

Siva Pradhan Over a year ago

No Problem. Thank you so much for your time and input. If I am able to find something I will post here :)

Collectives™ on Stack Overflow

Export multiple array of object in excel as multiple sheets

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related