使用node把mysql表导出为excel表格

安装
npm i mysql
npm i node-xlsx
npm i fs

const mysql = require('mysql');
const xlsx = require('node-xlsx');
const fs = require('fs');
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '数据库密码',
  database: '数据库名',
  port: 3306
});
let data = [];
//excel表头部分
data.push([
  'id',
  'user',
  'passworld'
])
db.connect();
db.query('SELECT * from user', (err, result) =>{
  if (rows) {
    for (var i = 0; i < result.length; i++) {
      var arr = [];
      var value = result[i];
      for (var j in value) {
        arr.push(value[j]);
      }
      data.push(arr);
    }
  }
  var buffer = xlsx.build([
    {
      name: 'sheet1',
      data: data
    }
  ]);
  fs.writeFileSync('C:/Users/86152/Desktop/data.xlsx', buffer, { 'flag': 'w' });
                    这里的路径是桌面，导出的excel会在桌面上，路径根据需要修改
});
db.end();

 如果用接口调用打印特别注意，断开接口，否则重复调用接口会报错 “Cannot enqueue Handshake after already enqueuing a Handshake.”

const express = require('express')
const mysql = require('mysql');
const xlsx = require('node-xlsx');
const fs = require('fs');
const app = express()
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '数据库密码',
  database: '数据库名',
  port: 3306
});
app.get("/", (req, res) => {

  let data = [];
  //excel表头部分
  data.push([
    'id',
    'user',
    'passworld'
  ])
  db.query('SELECT * from user', (err, result) => {
    if (result) {
      for (var i = 0; i < result.length; i++) {
        var arr = [];
        var value = result[i];
        for (var j in value) {
          arr.push(value[j]);
        }
        data.push(arr);
      }
    }

    var buffer = xlsx.build([
      {
        name: 'sheet1',
        data: data
      }
    ]);
    fs.writeFileSync('C:/Users/86152/Desktop/data.xlsx', buffer, { 'flag': 'w' });
    res.send(result)
  });

})
app.listen(3300, () => {
  console.log("启动成功");
  db.connect()
  此位置断开，如果不断开重复调用接口会报错
})