首先安装mysql
npm i mysql -S
下面进行一个简单的查询

var mysql = require("mysql");

//数据库连接池
var pool = mysql.createPool({
  connectionLimit: 10,
  host: "localhost",
  port:"3307",
  user: "用户名",
  password: "密码",
  database: "数据库名"
});

pool.query("SELECT * from product", function(error, results, fields) {
  if (error) throw error;
  console.log(results);
  console.log(fields);
});

接下来我们把这个sql操作给封装起来，在需要使用的时候直接拿过来操作就好了：

var mysql = require("mysql");

//数据库连接池
var pool = mysql.createPool({
  connectionLimit: 10,
  host: "localhost",
  port: "3307",
  user: "用户名",
  password: "密码",
  database: "数据库名"
});

var db = {};

db.q = function(sql, params, callback) {
  pool.getConnection(function(err, connection) {
    if (err) {
      callback(err,null);
      return;
    }

    // 从连接池中取出连接
    connection.query(sql, params, function(error, results, fields) {
      console.log(`${sql}=>${params}`);
      //当使用完连接时，释放连接
      connection.release();
      callback(error,results);
    });
  });
};

module.exports=db;

使用：

const db=require('./db_test.js');

db.q(
  `insert into user(user_id,username,password,balance,tel) values(?,?,?,?,?)`,
  ["10029", "Alan", "123", "1256", "1586944452"],
  function(err, results) {
    if (err) throw err;
    console.log(results);
  }
);

下面介绍操作excel

安装node-xlsx
npm install node-xlsx -S

• 读取excel中的内容：

var xlsx = require('node-xlsx').default;
const fs=require('fs');

// Parse a buffer
const workSheetsFromBuffer = xlsx.parse(fs.readFileSync(`./test1.xlsx`));
// Parse a file
const workSheetsFromFile = xlsx.parse(`./test2.xlsx`);
console.log(workSheetsFromBuffer);
console.log(workSheetsFromFile);

console.log(workSheetsFromBuffer[0].data);
console.log(workSheetsFromFile[0].data);

读到的结果：

[ { name: 'Sheet1', data: [ [Array], [Array], [Array] ] },
  { name: 'Sheet2', data: [] },
  { name: 'Sheet3', data: [] } ]
[ { name: 'Sheet1', data: [ [Array], [Array], [Array] ] },
  { name: 'Sheet2', data: [] },
  { name: 'Sheet3', data: [] } ]
[ [ 12, 23, 'sasd', 'sdfsd' ],
  [ 123, 123, 12, 'sdf' ],
  [ 21321, 213, 213, 66 ] ]
[ [ 1, 2, 3, 4 ], [ 5, 6, 7, 8 ], [ 9 ] ]

image.png

• 写入excel文件：

const fs=require('fs');
var xlsx = require('node-xlsx').default;

const data = [
  [1, 2, 3],
  [true, false, null, "sheetjs"],
  ["foo", "bar", new Date("2014-02-19T14:30Z"), "0.3"],
  ["baz", null, "qux"]
];
var buffer = xlsx.build([{ name: "mySheetName", data: data }]); // Returns a buffer
fs.writeFile('./test3.xlsx',buffer,(err)=>{
  if(err) throw err;
  console.log('写入成功！');
});

最后写入成功

image.png

• 合并单元格：

var xlsx = require('node-xlsx').default;
const fs=require('fs');

const dataSheet1 = [
  [1, 2, 3],
  [true, false, null, "sheetjs"],
  ["foo", "bar", new Date("2014-02-19T14:30Z"), "0.3"],
  ["baz", null, "qux"]
];
const range = { s: { c: 0, r: 0 }, e: { c: 0, r: 3 } }; // A1:A4
const sheetOptions = { "!merges": [range] };

var buffer = xlsx.build([
  { name: "mySecondSheet", data: dataSheet1, options: sheetOptions }
]); // Returns a buffer
fs.writeFile("./test4.xlsx", buffer, err => {
  if (err) throw err;
  console.log("写入成功！");
});

image.png