NodeJS对象Excel的操作
读取Excel插入到数据库
NodeJS通过node-xlsx
来操作我们的数据库,在操作数据库的过程当中,我们可以把一些常用的方法封装起来,来进行一些简便化的操作
在封装的过程当哪,一定要遵守相关的规范
-
遵守代表注释的规范
最常规的注释方法
使用JSDoc文档注释
-
高类聚,低耦合的方案
/**
* @author 张三
* @name Excel帮助类,提供Excel导入到数据库
* @version 1.0
* @requires DBHelper
*/
const path=require("path");
const xlsx=require("node-xlsx");
const DBHelper=require("./DBHelper")
/**
* ExcelHelper工具类,提供Excel导入到MySql的操作
*/
class ExcelHelper{
/**
* @name 读取指定路径的Excel文件
* @param {String} excelPath 要读取的Excel路径
* @returns {Object} excelObj读取的JS对象
*/
readExcel(excelPath){
let excelObj = xlsx.parse(excelPath);
return excelObj;
}
/**
* @name 根据Excel创建表
* @param {Object} excelObj 读取的Excel对象
*/
createTable(excelObj){
let newArr = excelObj[0].data[0].map(item=>{
return item+=" varchar(255)";
});
let ddlSql=`create table if not exists ${excelObj[0].name}(
${newArr.toString()}
)engine=innodb default charset=utf8`;
let conn=new DBHelper().getConn();
conn.query(ddlSql,[],(err,result)=>{
if(err){
console.log("数据表创建失败")
}
else{
//数据表创建成功
this.insertData(excelObj[0].data,excelObj[0].name);
}
conn.end();
})
}
/**
* @name 插入数据到Excel表
* @param {Object} excelData 要插入的数据
* @param {String} tableName 插入的表的名称
*/
insertData(excelData,tableName){
let insertSql=`insert into ${tableName} (${excelData[0].toString()}) values (${new Array(excelData[0].length).fill("?").toString()})`;
excelData.forEach((item,index,a)=>{
if(index!=0){
let conn=new DBHelper().getConn();
conn.query(insertSql,item,(err,result)=>{
if(err){
console.log("数据插入失败"+err);
}
else{
console.log(result);
}
})
conn.end();
}
});
}
/**
* @name 通过一个Excel文件 ,直接插入到数据库
* @param {String} excelPath 要插入的Excel文件
*/
readExcelIntoDB(excelPath){
let excelObj = this.readExcel(excelPath); //第一步的读取
this.createTable(excelObj); //第二步的创建表与插入
}
}
module.exports=ExcelHelper;
说明:
上面的代码是完整的,严格的按照开发标准来的,在开发过程当中,一定要遵守全名规范与注释规范,做到方法与方法这间低耦合,同时一个类型的方法做到高类聚
上面封装的ExcelHelper这个模块,它完成Excel读取到导入到数据库的操作,我们把它分成了三个部分
-
第一个部分:读取Excel文件
-
第二个部分:根据Excel文件创建表格
-
第三个部分:插入读取的Excel数据到MySql数据库
下面是调用过程
const ExcelHelper=require("./ExcelHelper");
const path=require("path");
//强制规定 Class后面的必须大写
//强制规定 变量标识符 驼锋命名 首字线小写
//强制规定: 约定俗成
//excelHelper 变量
//ExcelHelper 类名
let excelHelper=new ExcelHelper();
excelHelper.readExcelIntoDB(path.join(__dirname,"./Y102.xls"));
经过上面的代码,我们就把当前文件夹下面的Y102.xls这个Excel文件插入到了数据库
读取数据库数据生成Excel文件
如果要把数据库查询出来的结果生成一个Excel的文件 ,那么,也必须按照之前的Excel的格式数据来进行
result数据库查询的数据
let result = [
{
s_id: 'T20200121',
s_name: '张三',
s_sex: '男',
s_age: '18',
s_qq: '123456789',
s_school: '学校',
s_major: '挖掘机',
s_education: '小学'
},
{
s_id: 'T20200122',
s_name: '李四',
s_sex: '男',
s_age: '22',
s_qq: '58964785',
s_school: '湖北大学',
s_major: '计算机科学与技术',
s_education: '本科'
}
]
Excel里面的数据
excelObj=[
{
name:"Sheet1",
data:[
["s_id","s_name","s_sex"], //代表的是表头
["T20200124","王五","女"], //从这一行开始,代表的是数据
["T20200125","刘六","女"]
]
},
{
name:"Sheet2",
data:[
]
}
]
所以,我们必须要把result的数据转换成Excel里面的数据
/**
* @name 将Sql语句查询的结果导出为Excel
* @param {Object} result 通过Sql语句查询出来的结束
*/
resultToExcel(result,excelPath){
//第一步:生成Excel标题
let data=[];
data.push(Object.keys(result[0]));
//第二步:生成数据
result.forEach((item,index,a)=>{
data.push(Object.values(item));
});
//准备路径,以及生成的格式
let buff = xlsx.build([{name:"Sheet1",data:data}]);
fs.writeFileSync(excelPath,buff);
}
上面的方法就是把
result
的数据转换成了excel
的数据,然后调用了build
的方法,去生成一个二进制的数据,接下来就是把这个二进制的数据写入到文件里面,调用的是nodejs
内置的系统模块fs
根据一个像这样的方法,我们就可以把之前的ExcelHelper封装成一个完整的即有导入的功能又有导出的功能的对象了
👉 完整版
/**
* @author 张三
* @name Excel帮助类,提供Excel导入到数据库,以及结果生成为Excel文件
* @version 1.0
* @requires DBHelper
*/
const path=require("path");
const xlsx=require("node-xlsx");
const DBHelper=require("./DBHelper")
const fs=require("fs");
/**
* ExcelHelper工具类,提供Excel导入到MySql的操作
*/
class ExcelHelper{
/**
* @name 读取指定路径的Excel文件
* @param {String} excelPath 要读取的Excel路径
* @returns {Object} excelObj读取的JS对象
*/
readExcel(excelPath){
let excelObj = xlsx.parse(excelPath);
return excelObj;
}
/**
* @name 根据Excel创建表
* @param {Object} excelObj 读取的Excel对象
*/
createTable(excelObj){
let newArr = excelObj[0].data[0].map(item=>{
return item+=" varchar(255)";
});
let ddlSql=`create table if not exists ${excelObj[0].name}(
${newArr.toString()}
)engine=innodb default charset=utf8`;
let conn=new DBHelper().getConn();
conn.query(ddlSql,[],(err,result)=>{
if(err){
console.log("数据表创建失败")
}
else{
//数据表创建成功
this.insertData(excelObj[0].data,excelObj[0].name);
}
conn.end();
})
}
/**
* @name 插入数据到Excel表
* @param {Object} excelData 要插入的数据
* @param {String} tableName 插入的表的名称
*/
insertData(excelData,tableName){
let insertSql=`insert into ${tableName} (${excelData[0].toString()}) values (${new Array(excelData[0].length).fill("?").toString()})`;
excelData.forEach((item,index,a)=>{
if(index!=0){
let conn=new DBHelper().getConn();
conn.query(insertSql,item,(err,result)=>{
if(err){
console.log("数据插入失败"+err);
}
else{
console.log(result);
}
})
conn.end();
}
});
}
/**
* @name 通过一个Excel文件 ,直接插入到数据库
* @param {String} excelPath 要插入的Excel文件
*/
readExcelIntoDB(excelPath){
let excelObj = this.readExcel(excelPath); //第一步的读取
this.createTable(excelObj); //第二步的创建表与插入
}
/**
* @name 将Sql语句查询的结果导出为Excel
* @param {Object} result 通过Sql语句查询出来的结束
*/
resultToExcel(result,excelPath){
//第一步:生成Excel标题
let data=[];
data.push(Object.keys(result[0]));
//第二步:生成数据
result.forEach((item,index,a)=>{
data.push(Object.values(item));
});
//准备路径,以及生成的格式
let buff = xlsx.build([{name:"Sheet1",data:data}]);
fs.writeFileSync(excelPath,buff);
}
}
module.exports=ExcelHelper;