allstar/tools/local.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()