allstar/tools/mongo.js

const fs = require('fs');
const path = require('path');
const XLSX = require('xlsx');
const {MongoConf} = require('./config');

const MongoClient = require('mongodb').MongoClient;
const assert = require('assert');

const dbIns = MongoConf['R1'];
const connStr = `mongodb://${dbIns.host}:${dbIns.port}`
const connOpt = {
    auth : {
        user: dbIns.username,
        password: dbIns.password,
    },
    authSource: dbIns.dbname,
    authMechanism: "SCRAM-SHA-1",
    useNewUrlParser: true
}

const dataPath = "../assets/scripts/data";
const dataPrefix =  "allstar_";


/**
 * excel表转换为json对象
 * @param {String} filename 文件名
 */
const getSheet = function (filename) {
    let filePath = path.join(__dirname, "/excel", filename);
    // let buf = fs.readFile(filePath);
    let workbook = XLSX.readFileSync(filePath)
    let sheets = workbook.SheetNames;
    let sheetsObj = {};
    
    sheets.forEach(n => {
        let ws = workbook.Sheets[n];
        let def = XLSX.utils.sheet_to_json(ws, {range: 0, header: 1});
        let typeMap = {};
        let typeArr = def[0];
        let keyArr = def[2];
        keyArr.forEach((item, index) => {
            typeMap[item] = typeArr[index]
        })

        let res = XLSX.utils.sheet_to_json(ws, {range: 2})
        res.forEach(item => {
            for(let i in item) {
                item[i] = formatType(item[i], typeMap[i])
            }
        })
        sheetsObj[n] = res
    })
    return sheetsObj;
    
    // console.log(XLSX.utils.sheet_to_json(ws));
    // console.log(workbook.SheetNames);
}

/**
 * 写入json内容到客户端assets目录
 * @param {String} filename 文件名
 * @param {Object} content json内容
 */
const writeToAssets = function (filename, content) {
    let writeFileName = filename.replace(/\.xlsx|\.xls/, '.js').replace(dataPrefix, "");
    let writePath = path.join(__dirname, dataPath, writeFileName);
    fs.writeFile(writePath, 'module.exports=' + JSON.stringify(content))
    console.log(JSON.stringify(content, null, 2));
}

const formatType = function(target, type) {
    let _type = type.toLowerCase()
    switch (_type) {
        case 'integer':
            target = parseInt(target, 10);
            break;
        case 'double':
            target = parseFloat(target)
            break;
        case 'string':
            target = target.toString()
            break;
        case 'object':
            target = JSON.parse(target)
            break;
        default:
            break;
    }
    return target
}

const init = function () {
    fs.readdir("./excel", (err, file) => {
        file.forEach(n => {
            if(n.match(/\~\$/)) {
                return
            }
            let ws = getSheet(n);
            let sheetName = n.replace(/\.xlsx|\.xls/, "");

            let target;
            if(ws.length > 0) {
                //单个表
                // ws.forEach(n => {
                //     if(typeof n['id'] != undefined) {
                //         n['_id'] = n['id']
                //     }
                // })

                target = ws
            } else {
                //多个表合拼成一个Array
                let _array = []
                for(let i in ws) {
                    _array = _array.concat(ws[i])
                }
                target = _array                
            }

            MongoClient.connect(connStr, connOpt, (err, client) => {
                var db = client.db(dbIns.dbname);
                var collection = db.collection(sheetName)
                collection.drop(null, () => {
                    collection.insert(target, () => {
                        client.close()
                    })
                })
            });

            //把生成的js写入客户端assets目录
            let wsKeys = Object.keys(ws)
            if(wsKeys.length == 1) {
                writeToAssets(n, ws[wsKeys[0]]);
            } else {
                writeToAssets(n, ws);
            }
        })

        // process.exit()
    })
}

init()