node读取excel并转化存入mysql

npm install node-xlsx
const xlsx = require("node-xlsx")
复制代码

router.get("/commit", async (ctx, next) => {
  const m = xlsx.parse('./commit/2.xlsx')
  let table = new Array()
  m.forEach(item => {
    for(let i = 1; i < item['data'].length; i++) {
      let arr = new Array()
      for(let o = 9; o < item['data'][i].length; o++) {
        const t = item['data'][i][o] ? item['data'][i][o] + '--' : item['data'][i][o]
        arr.push(t)
      }
      table[i - 1] = {
        "dishes": item['data'][i][0],
        "introduce": item['data'][i][1],
        "ingredients": item['data'][i][2],
        "label": item['data'][i][3],
        "tips": item['data'][i][4],
        "hor": item['data'][i][5],
        "way": item['data'][i][6],
        "time": item['data'][i][7],
        "flavor": item['data'][i][8],
        "steps": arr
      }
    }
  })
  ctx.body = util.success(null, "新增成功")
})
复制代码

router.get("/commit", async (ctx, next) => {
  const m = xlsx.parse('./commit/2.xlsx')
  m.forEach(item => {
    for(let i = 1; i < item['data'].length; i++) {
      let arr = new Array()
      for(let o = 9; o < item['data'][i].length; o++) {
        const t = item['data'][i][o] ? item['data'][i][o] + '--' : item['data'][i][o]
        arr.push(t)
      }
      // 写入mysql
      const sql = `
        insert into daemon_food
          (dishes, introduce, ingredients, label, tips, hor, way, time, flavor, steps)
        values
          ("${ item['data'][i][0] ?? null }",
           "${ item['data'][i][1] ?? null }",
           "${ item['data'][i][2] ?? null }",
           "${ item['data'][i][3] ?? null }",
           "${ item['data'][i][4] ?? null }",
           "${ item['data'][i][5] ?? null }",
           "${ item['data'][i][6] ?? null }",
           "${ item['data'][i][7] ?? null }",
           "${ item['data'][i][8] ?? null }",
           "${ arr ?? null }")
      `
      query(sql)
    }
  })
  ctx.body = util.success("", "新增成功")
})
复制代码