node 读取excel表格中数据,批量插入数据库

引入依赖 

   const xlsx=require('xlsx')

async function readExcel(filepath){
    //读取内容
    const workbook = xlsx.readFile(filepath);
    const sheetNames = workbook.SheetNames; //获取表名
    const sheet = workbook.Sheets[sheetNames[0]]; //通过表名得到表对象
    const thead = [sheet.A1.v, sheet.B1.v];
    const data = xlsx.utils.sheet_to_json(sheet); //通过工具将表对象的数据读出来并转成json
    const theadRule = ['学号', '姓名'];
    const isValid = thead.every((value, index) => value === theadRule[index]); //检验表字段
    if(!isValid) {
        return false
    }

    let params = [];
    for (let i = 0; i < data.length; i++) {
        let  edu_student_number=data[i][thead[0]];
        let  name= data[i][thead[1]];    
        params.push({
                edu_student_number:data[i][thead[0]],           
                name: data[i][thead[1]]       
            });
    }
    //数据批量插入数据库
    let sql='INSERT INTO student_table (edu_student_number,name) VALUES ?';
    const values = [];
    params.forEach(function(item) {
        const arr = [];
        for(const field in item) {
            arr.push(item[field]);
        }
        values.push(arr);
    });
    try{
          await sql_execute(sql,[values])
             return true
     }catch(e){
          return false
        }   
    }

  //sql执行  

   const pool = mysql.createPool({})

function sql_execute(sql,params) {
    return new Promise(function (resolve, reject) {
        pool.query(sql,params, function (error, results,fields) {
            if (error) {
                return reject(error);
            } else {
                resolve({results,fields});
            }
        })
    })
}