Export multiple array of object in excel as multiple sheets

user_15212955 0
Viewed 1560

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.

javascript
1 Answers

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");    
}
user_96952860
Related