node.js - What is the best way to send large data from node to angular?

Question

I have been working on a project that requires to download large data file (almost 500K-1M records)

I am using Angular for web app. Node as a backend. Azure SQL Server for the database.

This is how I am currently downloading data (But it does not work for 300K+ records)

I am sending data on response stream in JSON and converting it in Excel with [xlxs][1] package

For 3L+ data it takes forever to convert JSON to XLSX.

Previously I tried converting it to XLSX on node side and send it to client but this also took time.

Here is my current code on backend.

router.get("/", cors(), async (req, res) => {
  const start = Number(Date.now());
  sql.connect(config, () => {
    res.setHeader('Cache-Control', 'no-cache');

    const request = new sql.Request();
    request.stream = true;
    request.query(`query`);


    let rowCount = 0;
    const BATCH_SIZE = 50;
    let rows;

    request.on('recordset', () => {
              res.setHeader('Content-Type', 'application/json');
              res.write('[');
            });
        
            request.on('row', row => {
              if (rowCount > 0)
                res.write(',');
        
              if (rows % BATCH_SIZE === 0)
                res.flush();
        
              res.write(JSON.stringify(row));
              rowCount++;
            });
        
            request.on('done', ()=> {
              const end = Number(Date.now());
              console.log(req.path + 'Time: ' + (end - start) + 'ms');
              logger.info(('/api/file/cust-full' + 'Time: ' + (end - start) + 'ms'));
              res.write(']');
              sql.close();
              res.end();
            });
  });
});

My Conversion code on client:

const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const EXCEL_EXTENSION = '.xlsx';

@Injectable({
    providedIn: 'root'
  })
export class NewExcelService {
  constructor() { }

  public exportAsExcelFile(json: any[], excelFileName: string): void {
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
    console.log('worksheet', worksheet);
    const workbook: XLSX.WorkBook = { Sheets: { data: worksheet }, SheetNames: ['data'] };
    const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' });
    // const excelBuffer: any = XLSX.write(workbook, { bookType: 'xlsx', type: 'buffer' });
    this.saveAsExcelFile(excelBuffer, excelFileName);
  }

  private saveAsExcelFile(buffer: any, fileName: string): void {
    const data: Blob = new Blob([buffer], {
      type: EXCEL_TYPE
    });
    FileSaver.saveAs(data, fileName + '_export_' + new Date().getTime() + EXCEL_EXTENSION);
  }
}
```

Should I do it in CSV with header or should I save the data to blob storage on Azure.

If I save it on Azure blob storage how should I do it?

Please suggest the best way I can do it.

Any suggestion is appreciated.


  [1]: https://www.npmjs.com/package/xlsx

标签： node.jssql-serverangularexcelazure