{ NodeJs } Elasticsearch + Excel + node-cron 응용편 #2

1편에서 elasticsearch 담겨있는 값을 excel파일로 출력해보았습니다.

 

[ 다시 보기 ] 

2020/11/19 - [01.Computer/Nodejs] - { NodeJs } Elasticsearch + Excel 연동 #1

 

2편에서는 스케쥴을 통해서 excel출력까지 해보도록 하겠습니다.

 

config 파일에 schedule 시간을 등록합니다.

module.exports = {
    elasticsearch: {
        hosts: [ 'elastic:123456@localhost:9200' ],
        timeout: 10 * 1000
    },
    settings: {
        schedule: '*/1 * * * *'
    } 
    // url : {
    //    confederation_url : "https://www.yna.co.kr/sports/football"
    // }
};

 

node-cron을 이용하여 스케쥴링을 통해서 start()를 호출하도록 해줍니다.

routes\schedule.js 

const proc = require("./process.js");
const config = require('../config/index');
const cron = require("node-cron");
cron.schedule(JSON.stringify(config.settings),  () => {
    console.log("running process");
    proc.start();
});

 

 start, stop 모듈을 선언하여 처리해줍니다.

start => 데이터를 읽고, excel에 출력

stop => 데이터가 없거나 exception이 일어났을 경우 닫기.

routes\process.js

const esclient = require('../esclient/index');
const excel = require('excel4node');

const start = () => {
    let params = {
        index: "test*",
        body: {
            query: {
                match_all: {}
            }
        }
    };

    esclient.search(params, function (err, resp) {
        if (err) {
            console.error('getDataError : ' + err);
            setTimeout(stop, 1);
            return;
        } else {
            let total = resp.hits.total.value;
            if (total > 0) {
                // Create a new instance of a Workbook class
                const workbook = new excel.Workbook();
                setTimeout(setSheet, 1, workbook, resp.hits.hits);
            }
        }
    });
};


const setSheet = (wb, jsonData) => {
    // Add Worksheets to the workbook
    let worksheet = wb.addWorksheet('Sheet 1');
    // let worksheet2 = wb.addWorksheet('Sheet 2');
    let titleStyle = setStyle(wb, 'title');
    let contentStyle = setStyle(wb, 'contents');
    let col = 1;
    let rowKeys = Object.keys(jsonData[0]._source);
    for (let idx in rowKeys) {
        var width = 10;
        switch (rowKeys[idx]) {
            case 'dept_name' :
                width = 17;
                break;
            case 'dept_no' :
                width = 40;
                break;
        }
        worksheet.column(col).setWidth(width);
        worksheet.cell(1, col++).string(rowKeys[idx]).style(titleStyle);
    }

    let row = 2;

    for (let idx in jsonData) {
        let source = jsonData[idx]._source;
        let col = 1;
        for (let key in rowKeys) {

            setInputText(worksheet, row, col, source[rowKeys[key]], contentStyle);
            col++;
        }
        row++;
    }
    wb.write('Excel.xlsx');
    stop();
};

const setStyle = (wb, type) => {
    let border = {top: {style: 'thin'}, bottom: {style: 'thin'}, left: {style: 'thin'}, right: {style: 'thin'}};
    let alignment = {horizontal: 'center'};
    let family = 'decorative';
    let fillType = 'none';
    let font = {};
    let fill = {};

    if (type == 'dept_name') {
        font = {
            bold: true,
            size: 10

        };
        alignment = {horizontal: 'center'};
        fill = {
            patternType: 'solid',
            fgColor: '#F8F5EE'
        };
        fillType = 'pattern';
    }

    font.family = family;
    fill.type = fillType;
    return wb.createStyle({
        border: border,
        font: font,
        alignment: alignment,
        fill: fill
    });
}

const setInputText = (sheet, row, col, text, style) => {
    sheet.cell(row, col).style(style);
    if (!text || text === '' || typeof text == 'undefined' || text === undefined || text === 'undefined') {
        return sheet.cell(row, col).string(' ');
    } else {
        text = text.toString().replace(/[\x00-\x09\x0B-\x1F]/gi, '');
        sheet.cell(row, col).string(text);
    }
};

const stop = () => {
    console.log("stop");
    esclient.close();
};


module.exports = {
    start: start,
    stop: stop
};