nodejs 实现从excel批量导入数据,并取关键词首字母

• node-xlsx
• js-pinyin
• mysql
• phpcms 关键词按首字母批量导入

const xlsx = require('node-xlsx');
const pinyin = require('js-pinyin');
const mysql = require('mysql');

pinyin.setOptions({checkPolyphone: false, charCase: 0});

const workbook = xlsx.parse(`${__dirname}/keyword.xlsx`);
const excelData = workbook[0].data.splice(1); // 去掉标题

let resultData = [];
for (i = 0; i < excelData.length; i++) {
  const keyword = excelData[i][0];
  const price = excelData[i][1];
  const py = pinyin.getFullChars(keyword).toUpperCase().slice(0, 1);

  resultData.push({
    keyword,
    price,
    py
  })
};

// 连接数据库
connection = mysql.createConnection({
  host: 'IP',
  user: '用户名',
  password: '密码',
  database: '数据库名'
});

connection.connect(function(err) {
  if (err) {
    console.error('连接失败！');
  } else {  // 操作数据库
    for (i = 0; i < resultData.length; i++) {
      const post = {
        keyword: resultData[i].keyword,
        classify: resultData[i].py,
        searchnums: resultData[i].price
      }

      connection.query("INSERT INTO v9_search_keyword SET ?", post, (error, results, fields) => {
        if (error) {
          console.log('--重复数据--', error);
        };
      });
    }
    console.log('--插入完成--，总计：' + resultData.length + ' 条');
  }
  connection.end();
});