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

拼搏现实的明天。 2021-11-29 04:54 320阅读 0赞

「说明」：此文档为下面大神所写，此处留个记录。

详细说明查看官网文档:

*  handsontable 官网:[https://handsontable.com/][https_handsontable.com]
 *  handsontable github:[https://github.com/handsontable/handsontable][https_github.com_handsontable_handsontable]
 *  js-XLSX github:[https://github.com/SheetJS/js-xlsx][https_github.com_SheetJS_js-xlsx]

作者：关爱单身狗成长协会  
链接：https://www.jianshu.com/p/6cea596fb08f  
来源：简书  
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。

<!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>

[https_handsontable.com]: https://link.jianshu.com?t=https://handsontable.com/
[https_github.com_handsontable_handsontable]: https://link.jianshu.com?t=https://github.com/handsontable/handsontable
[https_github.com_SheetJS_js-xlsx]: https://link.jianshu.com?t=https://github.com/SheetJS/js-xlsx