使用nodejs将mysql数据库中的数据导出成excel格式
const express = require('express');
const nodeExcel = require('excel-export');
const app = express();
const dbUtils = require('./dbUtils');
const config = {
host: "127.0.0.1",
port: "3306",
user: "root",
password: "root",
database: "million",
}
const pool = dbUtils.create(config);
const dbClient = new dbUtils.dbClient(pool);
app.use('/', function(req, res){
//开启 promise 异步读取文件内容
let promise = new Promise((resolve, reject) => {
dbClient.listCols('test',['id', 'title', 'applicant_ch'], {'title': '0'}, function(err, data){
if(err)reject(err);
resolve(data);
})
})
promise.then(data => {
let conf = {};
if (data != '') {
conf.name = 'mysheet';// sheet 名称
//决定列名和类型
conf.cols = [
{
caption: 'id',
type: 'number'
}, {
caption: 'title',
type: 'string'
}, {
caption: 'applicant_ch',
type: 'string'
}
];
let tmpTotalArr = [];
for (let k = 0; k < data.length; k++) {
let el = data[k];
let tmpArr = new Array();
for(let i in el){
tmpArr.push(el[i]);
}
tmpTotalArr.push(tmpArr);
}
conf.rows = tmpTotalArr;//填充数据
}
let result = nodeExcel.execute(conf); // nodeExcel 生成的数据
res.setHeader('Content-Type', 'application/vnd.openxmlformats');
res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");
res.end(result, 'binary'); //返回给前台
}).catch(err => {
console.log(err);
})
})
app.listen('3000', function(){
console.log('server is running at http://127.0.0.1:3000');
})