demo-excel-export.ts

demo-excel-export.ts · GitHub

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);

} }