demo-excel-export.ts

import { Controller, Get, Query, Res, Header, Response } from '@nestjs/common';
import { TrackingTransactionEntry } from '../entrities/tracking-transaction.entity';
import { ExportService } from '../services/export.service';
const excel = require('excel4node');
import * as moment from 'moment';

@Controller('exports')
export class ExportsController {

  constructor(private exportService: ExportService) { }

  @Header('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
  @Get('/job/dc2customer/tracking-history')
  async exportJobDC2CustomerTrackingHistory(@Res() res: Response, @Query('transactionId') transactionId: any) {
    const rs: any = await this.exportService.findByTransactionId(transactionId);
    const rss: any = await this.exportService.findSummaryTrackingHistory(transactionId);

    // return rs;

    const wb = new excel.Workbook();
    wb.creator = 'Satit Rianpit <[email protected]>';
    wb.created = new Date();
    wb.modified = new Date();

    const driverName: any = rss.driverName;
    const licensePlate: any = rss.licensePlate;
    const reportDate = `${moment(rss.reportDate).format('DD/MM')}/${moment(rss.reportDate).get('year') + 543}`;
    const warehouseName = rss.warehouseName;

    const ws = wb.addWorksheet(driverName);

    ws.cell(1, 1).string('รายงานสรุปการส่งสินค้ารายวัน');
    ws.cell(2, 1).string('ชื่อคนขับ');
    ws.cell(2, 2).string(driverName);

    ws.cell(3, 1).string('ทะเบียนรถ');
    ws.cell(3, 2).string(licensePlate);

    ws.cell(4, 1).string('ประจำวันที่');
    ws.cell(4, 2).string(reportDate);

    ws.cell(5, 1).string('ศูนย์ต้นทาง');
    ws.cell(5, 2).string(warehouseName);


    // header
    ws.cell(7, 1).string('ลำดับ');
    ws.cell(7, 2).string('เลขที่บิล');
    ws.cell(7, 3).string('ชื่อลูกค้า');
    ws.cell(7, 4).string('ที่อยู่');
    ws.cell(7, 5).string('อำเภอ');
    ws.cell(7, 6).string('จังหวัด');
    ws.cell(7, 7).string('สถานะ');
    ws.cell(7, 8).string('locations');
    ws.cell(7, 9).string('Time');
    ws.cell(7, 10).string('ระยะทาง');
    ws.cell(7, 11).string('ระยะทางรวม');

    let startCell = 8;

    const number = wb.createStyle({
      numberFormat: '#,##0.00; (#,##0.00); -',
    });

    let idx = 0;
    rs.forEach(v => {
      idx++;
      ws.cell(startCell, 1).number(idx);
      ws.cell(startCell, 2).string(v.receiveCode);
      ws.cell(startCell, 3).string(v.recipientName);
      ws.cell(startCell, 4).string(v.address);
      ws.cell(startCell, 5).string(v.ampurName);
      ws.cell(startCell, 6).string(v.provinceName);
      ws.cell(startCell, 7).string(v.statusName);
      ws.cell(startCell, 8).string(`${v.bLat},${v.bLng}`);

      const sendDate = moment(v.sendDate).isValid() ? moment(v.sendDate).format('HH:mm') : '00:00';

      ws.cell(startCell, 9).string(sendDate);
      const distance = v.distance / 1000;
      ws.cell(startCell, 10).number(distance).style(number);
      const sumdistance = v.sumDistance / 1000;
      ws.cell(startCell, 11).number(sumdistance).style(number);
      startCell++;
    });

    var exportFile = `${driverName}-${moment(rss.reportDate).format('DD-MM-YYYY')}.xlsx`;
    wb.write(exportFile, res);

  }
}