nodejs+edatagrid读取本地excel表格

excel表格内容

点击  加载excel表格  后

1.npm install node-xlsx

2.在app.js中加入

 

var logger = require('morgan');var bodyParser = require('body-parser');var routes = require('./routes/index');var users = require('./routes/users');var getExcel=require('./routes/getExcel');//查excel文档var app = express();var template = require('art-template');template.config('base', '');template.config('extname', '.html');app.engine('.html', template.__express);app.set('view engine', 'html');

 

app.use('/', routes);app.use('/users', users);app.use('/getExcel', getExcel);// catch 404 and forward to error handlerapp.use(function(req, res, next) {  var err = new Error('Not Found');  err.status = 404;  next(err);});

3.在getExcel.js中

 

/** * Created by zhangfanying on 2015/12/18. */var express=require('express');var router=express.Router();var fs = require("fs");var xlsx = require("node-xlsx");var path = require('path');router.get("/excel", function (req, res) {    res.render("getExcel");});/** * 加载Excel表头 */router.post("/tmpHead", function (req, res) {    var url = path.join(__dirname + "/" + req.body.url);    var obj = xlsx.parse(url);    var data = obj[0].data[0];    var headData = [];    for (var i in data) {        headData.push(data[i]);    }    url = null;    obj = null;    data = null;    res.send({"data": headData});}) ;/** * 加载Excel模板内容 */router.post("/tmpShow", function (req, res) {    var url = path.join(__dirname + "/" + req.body.url);    var obj = xlsx.parse(url);    var data = obj[0].data;    data.splice(0, 1);          //删除Excel模板标题    var tempData = [];           //除标题外的模板数据    for (var i in data) {        var tmp = {};        tmp.nickname = data[i][0];        tmp.qq = data[i][1];        tmp.phone = data[i][2];        tmp.address = data[i][3];        tempData.push(tmp);        tmp = null;    }    url = null;    obj = null;    data = null;    res.send({"rows": tempData});});module.exports = router;

 

4.在getExcel.html中

 

<script type="text/javascript">
    var editFlag = undefined;//设置一个编辑标记
    var url = null;
    jQuery(function () {
        jQuery("#dg").edatagrid({});
        url = "../public/tmp/读取excel.xlsx";
        jQuery.post("/tmp/tmpHead",{
            url:url
        },function(result){
            jQuery("#headData").val(result.data);
        });
    });
    function loadTemplet(){
        var url = "../public/tmp/读取excel.xlsx";
        var headData = jQuery("#headData").val().split(",");
        jQuery("#dg").edatagrid( {
            url:"/tmp/tmpShow",
            nowrap: true,
            loadMsg : "正在努力为您加载数据",
            columns: [[
                {
                    field: 'nickname', title: headData[0], width: "15%", align: 'center', editor: {
                    type: 'validatebox',
                    options: {
                        required: true,
                        validType:{
                            length:[1,20]
                        }
                    }
                }
                },
                {
                    field: 'qq', title: headData[1], width: "15%", align: 'center', editor: {
                    type: 'validatebox',
                    options: {
                        required: true,
                        validType:{
                            length:[1,20]
                        }
                    }
                }
                },
                {
                    field: 'phone', title: headData[2], width: "15%", align: 'center', editor: {
                    type: 'validatebox',
                    options: {
                        required: true,
                        validType:{
                            length:[1,20]
                        }
                    }
                }
                },
                {
                    field: 'address', title: headData[3], width: "15%", align: 'center', editor: {
                    type: 'validatebox',
                    options: {
                        required: true,
                        validType:{
                            length:[1,20]
                        }
                    }
                }
                }
            ]],
            onBeforeLoad: function (param) {
                param.url = url;
            },
            onAfterEdit : function(rowIndex, rowData, changes) {
                editFlag = undefined;//重置
                jQuery("#dg").edatagrid("unselectAll");
            },
            onDblClickCell : function(rowIndex, field, value) {//双击该行修改内容
                if (editFlag != undefined) {
                    jQuery("#dg").edatagrid('endEdit', editFlag);//结束编辑，传入之前编辑的行
                    jQuery("#dg").edatagrid('beginEdit', rowIndex);//开启编辑并传入要编辑的行
                    editFlag = rowIndex;
                }
                if (editFlag == undefined) {
                    jQuery("#dg").edatagrid('beginEdit', rowIndex);//开启编辑并传入要编辑的行
                    editFlag = rowIndex;
                }
            },
            onClickCell : function(rowIndex, field, value) {//双击该行修改内容
                editFlag = rowIndex;
            }
        });
    }
</script>

 

<table id="dg" title="Excel查询信息" idField="id" style="width:100%;position: absolute;top: 50px;bottom: 0px;overflow-x: hidden"       toolbar="#toolbar" idField="id" rownumbers="true" fitColumns="true" singleSelect="true" ></table><div id="toolbar" style="position: fixed;top: 28px;left: 0px;right:0px; z-index: 9999;">    <input type="hidden" id="headData"/>    <a href="javascript:void(0)" class="easyui-linkbutton" data-options="iconCls:'icon-2012080412111',plain:true" onclick="javascript:loadTemplet()" >加载Excel表格</a></div>