上传excel文件并存入数据库

node express环境下，进行上传excel文件并解析，拼接sql语句存入数据库：

1、应用时主要分为前台(页面，js)、后端(路由，数据库操作) 2个部分。

2、view:

id="appForm" name="appForm"

method="post"

enctype="multipart/form-data">

value="保存" class="btn btn-primary bzwd"

onclick="saveOrUpdate()">

function saveOrUpadate() {

$.ajax({

type: "POST",

url:"./uploadexcel",

data:$('#appForm').serialize(),

// 序列化表单值

async: false,

error: function(request) {

alert("Connection error");

},

success: function(data) {

alert(data);

}

});

}

3.routes

var express = require('express');

var router = express.Router();

var mysql = require('mysql');

var async = require("async");

var node_xlsx = require('node-xlsx');

var multiparty = require('multiparty');

var fs = require('fs');

var pool = mysql.createPool(mysql_config);

function exe_mysql(sql, callback) {

pool.getConnection(

function (err, connection) {

// Use the connection

if (err) {

console.log(err);

connection.destroy();

}

connection.

query(sql, function (err, rows) {

callback(err, rows)

// And done with the connection.

connection.destroy();

});

});

} //数据库连接

router.post('/uploadexcel',

function (req, res, next) {

try{

var form =

new multiparty.Form();

//上传完成后处理

form.parse(req,

function(err, fields, files) {

var filesTmp =

JSON.stringify(files, null, 2);

// console.log(files);

if (err) {

console.log('parse error: ' + err);

} else {

var inputFile = files.file[0];

var uploadedPath = inputFile.path;

var dstPath = './public/files/' +

inputFile.originalFilename;

//重命名为真实文件名

fs.rename(uploadedPath, dstPath,

function (err) {

if (err) {

console.log('rename error: ' + err);

} else {

ExcelParse(dstPath,function(err,data){

if (err==200) {

res.json(data);

} else {

res.json(data);

}

});

}

});

}

})

}catch(ex){

res.json("服务器错误！");

}

});

function ExcelParse(newPath,cb){

var obj = node_xlsx.parse(newPath);

var excelObj =

obj[0].data;//取得第一个excel表的数据

//统计上传多少个学生信息

var num = 1;

//循环遍历表每一行的数据

for(var i=1;i

var rdata = excelObj[i];

if(rdata.length>0){

if(rdata[0]==""){

cb(500,

"第"+num+"行，【...】为空！导入失败！");

return;

}

}

if(rdata.length>1){

if(rdata[1]==""){

cb(500,

"第"+num+"行，【...】为空！导入失败！");

return;

}

}

if(rdata.length>2){

if(rdata[2]==""){

cb(500,

"第"+num+"行，【...】为空！导入失败！");

return;

}

}

.......

num++;

}

//1.删除所有该校数据

if (num === excelObj.length) {

addchilds(excelObj,cb);

}

}

function addchilds(excelObj,cb){

var insert_sql

= "insert into 表名(一串参数名) values ";

var sqlarray=[];

var sql="";

var num=10;//分页，每页10条

var page=1;

var tempi = 1;

for(var i=1;i

if((i==(num*page))||(i==excelObj.length-1))

{

sql+="('"+excelObj[i][0]+"','"+

excelObj[i][1]+"','"+

excelObj[i][2]+"','"+

((excelObj[i][3])?excelObj[i][3]:"")+

"','"+excelObj[i][4]+"','"+

excelObj[i][5]+"','"+excelObj[i][6]+"');";

sqlarray.push(insert_sql+sql);

sql="";

page++;

}else{

sql+="('"+excelObj[i][0]+"','"+

excelObj[i][1]+"','"+

excelObj[i][2]+"','"+

((excelObj[i][3])?excelObj[i][3]:"")+

"','"+excelObj[i][4]+"','"+

excelObj[i][5]+"','"+excelObj[i][6]+"'),";

}

tempi++;

}

if (tempi === excelObj.length) {

async.eachSeries(sqlarray,

function (item, callback) {

console.log(item);

exe_mysql(item, function (err, result) {

callback(err,"1");

});

}, function (err) {

if(!err){

cb(200,'插入成功!');

}else{

cb(500,'插入失败!');

}

});

}

});

}