handsontable结合js-xlsx实现可编辑xlsx导入导出功能(参考)

详细说明查看官网文档:

handsontable结合js-xlsx实现可编辑xlsx导入导出功能(参考)
demo

示例页面

<!DOCTYPE html>
<html>

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
    <!--引入相关文件-->
    <link rel="stylesheet" type="text/css" href="https://cdn.bootcss.com/handsontable/0.32.0/handsontable.full.css" />
    <script src="https://cdn.bootcss.com/handsontable/0.32.0/handsontable.full.min.js"></script>
    <script src="http://oss.sheetjs.com/js-xlsx/xlsx.full.min.js"></script>
    <script>
        //----工具类 开始----
        var utl = {};
        utl.Binary = {
            fixdata(data) { //文件流转BinaryString
                var o = "",
                    l = 0,
                    w = 10240;
                for (; l < data.byteLength / w; ++l) o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)));
                o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
                return o;
            },
            s2ab(s) { //字符串转字符流
                var buf = new ArrayBuffer(s.length);
                var view = new Uint8Array(buf);
                for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
                return buf;
            }
        }
        utl.XLSX = {
            wb: null,
            rABS: false,
            import(f, c) {//导入根据文件
                this.wb = null;
                var reader = new FileReader();
                reader.onload = function (e) {
                    var data = e.target.result;
                    if (utl.XLSX.rABS) {
                        utl.XLSX.wb = XLSX.read(btoa(utl.Binary.fixdata(data)), {//手动转化
                            type: 'base64'
                        });
                    } else {
                        utl.XLSX.wb = XLSX.read(data, {
                            type: 'binary'
                        });
                    }
                    if (c && typeof (c)) {
                        c();
                    }
                };
                if (utl.XLSX.rABS) {
                    reader.readAsArrayBuffer(f);
                } else {
                    reader.readAsBinaryString(f);
                }
            },
            onImport(obj, c) {//导入根据 input file标签
                if (!obj.files) {
                    return;
                }
                this.import(obj.files[0], c);
            },
            getSheetsByIndex(index = 0) {//获取数据根据Sheets索引
                return XLSX.utils.sheet_to_json(this.wb.Sheets[this.wb.SheetNames[index]]);
            },
            getCharCol(n) {
                let temCol = '',
                    s = '',
                    m = 0
                while (n > 0) {
                    m = n % 26 + 1
                    s = String.fromCharCode(m + 64) + s
                    n = (n - m) / 26
                }
                return s
            },
            export(json, title, type) {//导出
                var keyMap = [];//获取keys
                for (k in json[0]) {
                    keyMap.push(k);
                }
                var tmpdata = [];//用来保存转换好的json 
                json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
                    v: v[k],
                    position: (j > 25 ? utl.XLSX.getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
                }))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
                    v: v.v
                });
                var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10
                var tmpWB = new Object();
                title = title ? title : "mySheet";
                tmpWB.SheetNames = [title];
                tmpWB.Sheets = {};
                tmpWB.Sheets[title] = Object.assign({}, tmpdata, //内容
                    {
                        '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] //设置填充区域
                    });
                return new Blob([utl.Binary.s2ab(XLSX.write(tmpWB,
                    { bookType: (type == undefined ? 'xlsx' : type), bookSST: false, type: 'binary' }//这里的数据是用来定义导出的格式类型
                ))], { type: "" }); //创建二进制对象写入转换好的字节流
            },
            onExport(json, title, type) {//直接下载
                utl.Download.byObj(this.export(json, title, type), "下载.xlsx");
            }
        };
        utl.Download = {
            byURL(url, fileName) {//动态下载
                var tmpa = document.createElement("a");
                tmpa.download = fileName || "下载";
                tmpa.href = url; //绑定a标签
                tmpa.click(); //模拟点击实现下载
            },
            byObj(obj, fileName) {//内存二进制数据下载
                this.byURL(URL.createObjectURL(obj), fileName);
                setTimeout(function () { //延时释放
                    URL.revokeObjectURL(obj); //用URL.revokeObjectURL()来释放这个object URL
                }, 100);
            }
        }
        utl.Object = {
            reverse(obj) {//对象键值倒转
                var o = new Object();
                for (var k in obj) {
                    o[obj[k]] = k;
                }
                return o;
            },
            deepCopy() {//深拷贝
                let temp = obj.constructor === Array ? [] : {}
                for (let val in obj) {
                    temp[val] = typeof obj[val] == 'object' ? deepCopy(obj[val]) : obj[val];
                }
                return temp;
            },
            copyJson(o) { return JSON.parse(JSON.stringify(o)); }//拷贝JSON格式
        }
        //----工具类 结束----
    </script>
    <style>
        * {
            margin: 0;
            padding: 0;
        }

        body {
            font-family: Helvetica Neue, Helvetica, PingFang SC, Hiragino Sans GB, Microsoft YaHei, SimSun, sans-serif;
            overflow: auto;
            font-weight: 400;
            -webkit-font-smoothing: antialiased;
        }
    </style>
    <script>
        //---导入头信息 开始--
        const xHred = {
            "ID": "id",
            "账号": "uname",
            "用户名": "rname",
            "密码": "pwd",
            "性别": "sex",
            "出生日期": "birth",
            "电话": "mobPhone"
        };

        const filterVal = {//<=====值转化 
            "pwd":function(value){
                return "假装 被加密了"
            },
            "sex": function (key) {
                key = { "男": 0, "女": 1, "": 0, undefined: 0 }[key];
                return key ? key : 0;
            }
        };
        //---导入头信息 结束--
    </script>
</head>

<body>
    <script>
        //------导入导出 -----
        function impt(emt) {//<===导入
            utl.XLSX.onImport(emt, function () {
                var rt = utl.XLSX.getSheetsByIndex();//<===默认获取Sheet1
                var tmp = [];
                rt.forEach(function (value, index, array) {
                    var t = new Object();
                    for (var k in value) {
                        t[xHred[k]] = value[k];
                    }
                    tmp.push(t);
                });
                hot.loadData(tmp);//<====将读取完成数据显示到handsontable
            });
        }
        function down() {//<===下载导出数据
            var d = utl.Object.copyJson(hot.getSourceData());//<=====读取handsontable的数据
            d.unshift(utl.Object.reverse(xHred));//<====追加到列头
            utl.XLSX.onExport(d);//<====导出
        }
        function showData() {//<====获取真实数据
            var tmp = [];
            hot.getSourceData().forEach(function (value, index, array) {
                var t = new Object();
                for (var k in value) {
                    t[k] = filterVal[k] ? filterVal[k](value[k]) : value[k];//<====将xlsx数据转为实际需要的值
                }
                tmp.push(t);
            });
            /*
            *更多规则自己定义吧
            */
            alert(JSON.stringify(tmp));
            window.open("data:text/html;charset=utf-8,"+JSON.stringify(tmp));
            console.log(tmp);
            return tmp;
        }
        //------导入导出 -----
    </script>
    数据量大于1万条时,文件解析可能需要10秒以上读取,当然这也跟你当前使用电脑性能有关系
    <input type="file" onchange="impt(this)" />
    <input type="button" value="下载" onclick="down()" />
    <input type="button" value="查看结果数据数据" onclick="showData()" />
    <div id="hot"></div>
    <script>
        //----handsontable 初始化 开始------
        var hotElement = document.querySelector('#hot');//<====绑定handsontable初始化div 
        var colHeaders = [];//<===handsontable列头显示值
        var cols = [];//<====handsontable列头显示值对应的实际字段与其他配置
        for (var key in xHred) {
            cols.push({ data: xHred[key] });
            colHeaders.push(key);
        }
        var hotSettings = {//<====handsontable的配置
            colHeaders: colHeaders,//当值为true时显示列头,当值为数组时,列头为数组的值
            data: [],//数据源
            columns: cols,//列具体配置 
            // width: 906,
            autoWrapRow: true,
            // height: 641,
            // maxRows: 22,
            minRows: 1,
            // currentRowClassName:当前行样式的名称,
            // currentColClassName:当前列样式的名称,
            manualColumnResize: true,//当值为true时,允许拖动,当为false时禁止拖动
            manualRowResize: true,//当值为true时,允许拖动,当为false时禁止拖动
            stretchH: "all",     //last:延伸最后一列,all:延伸所有列,none默认不延伸。
            manualColumnMove: true,// 当值为true时,列可拖拽移动到指定列
            manualRowMove: true,// 当值为true时,行可拖拽至指定行
            rowHeaders: true, //当值为true时显示行头,当值为数组时,行头为数组的值
            columnSorting: true,//允许排序
            sortIndicator: true,
            contextMenu: true,//显示右键菜单
            autoColumnSize: true //当值为true且列宽未设置时,自适应列大小
        };
        var hot = new Handsontable(hotElement, hotSettings);
    //----handsontable 初始化 完成------
    </script>
</body>

</html>
上一篇:如何将UI5应用部署到Fiori On-Premise和On-Cloud的Launchpad上去


下一篇:关于单页应用(SPA)的经验之谈