Create and stream excel file with large data with couple of million rows in NodeJs. I tried search on internet, but I actually can not find any good guidance. Thank you so much for your reply.

CodePudding user response：

use the xlsx package, a simple way to create and manipulate Excel files in Node.js. The xlsx package supports streaming, which allows you to create large Excel files without running out of memory.

const XLSX = require('xlsx');
const fs = require('fs');

// Define the data for the Excel file
const data = [
  ['ID', 'Name', 'Email'],
  ['1', 'John Doe', '[email protected]'],
  ['2', 'Jane Doe', '[email protected]'],
  // Add more rows here...
];

// Create a new workbook and add worksheet
const workbook = XLSX.utils.book_new();
const worksheet = XLSX.utils.aoa_to_sheet(data);
XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

// Create a write stream for the Excel file
const stream = fs.createWriteStream('myfile.xlsx');

// Use the write stream to write the Excel file to disk
XLSX.write(workbook, {type: 'stream', bookType: 'xlsx'}, stream)
  .then(() => {
    // The file has been written successfully
    console.log('File written successfully');
  })
  .catch(err => {
    // There was an error writing the file
    console.error(err);
  });

xlsx package is imported and the fs module is used to create a write stream for the Excel file. The data for the Excel file is then defined as an array of arrays (AOA), and a new workbook and worksheet are created using this data.

XLSX.write method is then used to write the Excel file to the write stream, using the bookType: 'xlsx' option to specify that the file should be written in the XLSX format. The XLSX.write method returns a promise, so you can use the then and catch methods to handle the success and failure cases respectively. Change the file name and path and it will in your disk.