Question 244739 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – NodeJS large data excel

I have some problems on how to write large data to an excel file in a way that optimizes server memory. I read data from SQL database to the server about more than 1 million rows, this takes a lot of time and memory, then I write them to an excel file and consumes a lot of server memory, so what do I do? How can I optimize this? Is there a better way?

I tried the following code:

const express = require('express');
const ExcelJS = require('exceljs');
const fs = require('fs');
const app = express();
var db = require.main.require('./src/app/models/db_controller');

app.get('/download-excel', (req, res) => {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Sheet1');
  worksheet.columns = [
    { header: 'Header 1', key: 'date_time' },
    { header: 'Header 2', key: 'shaft_seal_pressure' },
    { header: 'Header 3', key: 'transfer_pressure' },
    { header: 'Header 4', key: 'cip_tem' },
    { header: 'Header 5', key: 'elect_usage' },];
  db.read_data_all('master_data', (err, result) => {
    if (err) {
      console.log(err);
    } else {
      worksheet.addRows(result);
      const tempFilePath = 'temp.xlsx';
      workbook.xlsx.writeFile(tempFilePath)
        .then(() => {
          res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
          res.setHeader('Content-Disposition', 'attachment; filename=excel_template.xlsx');
          const fileStream = fs.createReadStream(tempFilePath);
          fileStream.pipe(res);
          fileStream.on('end', () => {
            fs.unlink(tempFilePath, (err) => {
              if (err) {
                console.error('Lỗi khi xóa tệp tạm thời:', err);
              }
            });
          });
        })
        .catch((error) => {
          console.error('Lỗi:', error);
          res.status(500).send('Đã xảy ra lỗi trong quá trình tạo tệp Excel.');
        });
    }
  });
});

app.listen(5000, () => {
  console.log('Server is running on port 5000');
});

Result

    <--- Last few GCs --->
    
    [14120:000001B1C00BAA90]    75907 ms: Mark-sweep (reduce) 2046.9 (2082.7) -> 2046.5 (2083.5) MB, 2902.5 / 0.0 ms  (average mu = 0.172, current mu = 0.001) allocation failure; scavenge might not succeed
    
    
    <--- JS stacktrace --->
    
    FATAL ERROR: Reached heap limit Allocation failed - JavaScript heap out of memory
     1: 00007FF79DF307BF node_api_throw_syntax_error+175823
     2: 00007FF79DEB5796 DSA_meth_get_flags+59654
     3: 00007FF79DEB7480 DSA_meth_get_flags+67056
     4: 00007FF79E95DCC4 v8::Isolate::ReportExternalAllocationLimitReached+116
     5: 00007FF79E949052 v8::Isolate::Exit+674
     6: 00007FF79E7CAF0C v8::internal::EmbedderStackStateScope::ExplicitScopeForTesting+124
     7: 00007FF79E7C812B v8::internal::Heap::CollectGarbage+3963
     8: 00007FF79E7DE363 v8::internal::HeapAllocator::AllocateRawWithLightRetrySlowPath+2099
     9: 00007FF79E7DEC0D v8::internal::HeapAllocator::AllocateRawWithRetryOrFailSlowPath+93
    10: 00007FF79E7EE3D0 v8::internal::Factory::NewFillerObject+816
    11: 00007FF79E4DF315 v8::internal::DateCache::Weekday+1349
    12: 00007FF79E9FB1F1 v8::internal::SetupIsolateDelegate::SetupHeap+558193
    13: 00007FF79E980D02 v8::internal::SetupIsolateDelegate::SetupHeap+57218
    14: 00007FF71EC10EC4
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    const query = "SELECT * FROM master_data ORDER BY date_time DESC LIMIT 300000";
  const queryStream = pool.query(query).stream();
  var buffer = [];
  let count = 0;
  queryStream
    .on('data', (row) => {
      // Process each row and add it to the worksheet
      if (buffer.length >= 1000) {
        for (let i = 0; i < buffer.length; i++) {
          worksheet.addRow(buffer[i]);
        }
        buffer = [];
      } else {
        buffer.push(row);
      }
    })
    .on('end', () => {
      if (buffer.length >= 0) {
        for (let i = 0; i < buffer.length; i++) {
          worksheet.addRow(buffer[i]);
        }
        buffer = [];
      }
      const tempFilePath = 'temp.xlsx';
      workbook.xlsx.writeFile(tempFilePath)
        .then(() => {
          res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
          res.setHeader('Content-Disposition', 'attachment; filename=excel_template.xlsx');
          const fileStream = fs.createReadStream(tempFilePath);
          fileStream.pipe(res);
          fileStream.on('end', () => {
            fs.unlink(tempFilePath, (err) => {
              if (err) {
                console.error('Lỗi khi xóa tệp tạm thời:', err);
              }
            });
          });
          worksheet.spliceRows(0);
        })
        .catch((error) => {
          console.error('Lỗi:', error);
          res.status(500).send('Đã xảy ra lỗi trong quá trình tạo tệp Excel.');
        });
    });

    I have changed a part and also improved the heap memory a bit, but it's still not what I expected because after creating excel, the heap memory is not freed, it still causes overflow if the client accesses it many times, how to do that? Fix it, and is there any way to optimally transfer data to Excel?


  2. 0

    have you tried this Node.js heap out of memory ?
    Perhaps you can optimize the code by slightly changing the logic. for example, add mandatory filters to generate a document

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search