node.js实现上传excel/读取excel/批量导入mysql

基于 Egg.js 框架的 Node.js实现上传excel/读取excel/批量导入mysql等功能

// controller层代码
  async uploadSimCardFile() {
        const {ctx} = this;
        const file = ctx.request.files[0]; //获取上传文件
        if (!file) return ctx.throw(404);
        const source = fs.createReadStream(file.filepath); //创建可读流
        const filename = encodeURIComponent(ctx.request.body.name) + path.extname(file.filename)
            .toLowerCase();
        const distPath = path.join(this.config.baseDir, 'app/temp');
        const stat = fs.statSync(distPath);
        if (!stat.isDirectory()) {
            fs.mkdirSync(distPath);
        }
        const targetPath = path.join(this.config.baseDir, 'app/temp', filename);
        const target = fs.createWriteStream(targetPath);
        try {
            await pump(source, target);
            ctx.logger.warn('SimCard will be save %s', targetPath);
        } finally {
            // delete those request tmp files
            await ctx.cleanupRequestFiles();
        }
        // 读取内容
        const workbook = xlsx.readFile(targetPath);
        const sheetNames = workbook.SheetNames; //获取表名
        const sheet = workbook.Sheets[sheetNames[0]]; //通过表名得到表对象
        const thead = [sheet.A1.v, sheet.B1.v, sheet.C1.v, sheet.D1.v, sheet.E1.v, sheet.F1.v, sheet.G1.v, sheet.H1.v, sheet.I1.v];
        const data = xlsx.utils.sheet_to_json(sheet); //通过工具将表对象的数据读出来并转成json
        const theadRule = ['SIM卡CCID', '手机号', '号码归属地', '品牌', '数据套餐', '办卡日期', '备注1', '所属公司', '设备ID'];
        const isValid = thead.every((value, index) => value === theadRule[index]);
        if(!isValid) {
            ctx.failure('上传的excel格式错误');
            return false;
        }
        const result = [];
        for (let i = 0; i < data.length; i++) {
            result.push({
                iccid: data[i][thead[0]],
                sn: data[i][thead[8]] || '',
                phone_number: data[i][thead[1]],
                location: data[i][thead[2]],
                brand: data[i][thead[3]],
                remark: data[i][thead[6]],
                owner: data[i][thead[7]],
                price: data[i][thead[4]],
                available: (data[i][thead[6]] && data[i][thead[6]].indexOf('销') > -1) ? 0 : 1,
                create_time: new Date(),
                transact_time: data[i][thead[5]] || '',
                capacity: (data[i][thead[4]] && data[i][thead[4]].indexOf('M') > -1) ? data[i][thead[4]].split('元')[1].split('M')[0] * 1024 : 0
            });
        }

        await ctx.service.device.basic.addSimCard(result);
        ctx.success();
    }

// service层
async addSimCard(result) {
       // 将获取到结果包装成数组格式类似  [[],[],[],[]]
        const values = [];
        result.forEach(function(n) {
            const _arr = [];
            for(const m in n) {
                _arr.push(n[m]);
            }
            values.push(_arr);
        });
        // 重点sql语句
        const addSql = 'INSERT INTO sys_simcard (iccid,sn,phone_number,location,brand,capacity,price, owner, remark,available, create_time,transact_time) VALUES ?';
        const _result = await this.app.mysql.query(addSql, [values]);
        return _result.affectedRows === 1;
 }