handsontable使用总结

初始化时的各种配置说明

实战中使用过的代码

var settingForHandsontable = {
    rowHeaders: true, //导出时是否包含表头
    colHeaders: colHeaders, //设置表头的内容
    columns: columns, //设置列的属性
    height: "auto", //设置自动高度，如果多数据，编辑会卡，这时建议设置为固定值（500）
    allowEmpty: "false", //是否允许undefined/null/""为空值判断，为fasle时不包含""
    //autoColumnSize: true, //列根据内容自动变宽
    //autoRowSize: false, //行根据内容自动变化
    copyable: true, // 允许键盘复制
    licenseKey: 'non-commercial-and-evaluation', // for non-commercial use only
    afterChange: afterChange,
    //beforeChange: beforeChange,
    afterCreateRow: afterCreateRow,
    beforeRemoveRow: beforeRemoveRow,
    afterRemoveRow: afterRemoveRow,
    //afterValidate: afterValidate,
    beforeKeyDown: beforeKeyDown,
    afterRender: afterRender,
    contextMenu: {
        //右击菜单
        items: {
            row_above: {
                name: languageConversion("向上插入一行", "Insert one line up")
            },
            row_below: {
                name: languageConversion("向下插入一行", "Insert a line down")
            },
            remove_row: {
                name: languageConversion("删除行", "Delete rows")
            }
        }
    },
    data: deepCopy(dataInit)
}

function getObjectURL(file) {
    var url = null;
    if (window.createObjectURL != undefined) { // basic
        url = window.createObjectURL(file);
    } else if (window.URL != undefined) { // mozilla(firefox)
        url = window.URL.createObjectURL(file);
    } else if (window.webkitURL != undefined) { // webkit or chrome
        url = window.webkitURL.createObjectURL(file);
    }
    return url;
}


//检测 报关信息 必填项
function validatorHandsontable() {
    settingForHandsontable.colHeaders = deepCopy(colHeadersInit);

    //先去掉上一次必填的
    if (prev_required_element_cache.length > 0) {
        for (var index = 0; index < settingForHandsontable.columns.length; index++) {
            var colItem = settingForHandsontable.columns[index];
            if (prev_required_element_cache.indexOf(colItem.data) !== -1 && colItem.validator) {
                delete colItem.validator
            }
        }
    }

    if (required_element.length > 0) {
        for (var i = 0; i < required_element.length; i++) {
            var requireItem = required_element[i]
            for (var j = 0; j < settingForHandsontable.columns.length; j++) {
                var columnsItem = settingForHandsontable.columns[j]
                if (requireItem == columnsItem.data) {
                    columnsItem.validator = textValidator;

                    settingForHandsontable.colHeaders[j] = "<span class='must'>*</span>" + settingForHandsontable.colHeaders[j];
                    break;
                }
            }
        }

    }
}


function deepCopy(obj) {
    var newobj = null     // 接受拷贝的新对象
    if (typeof (obj) == 'object' && typeof (obj) !== null) {   // 判断是否是引用类型
        newobj = obj instanceof Array ? [] : {}               // 判断是数组还是对象
        for (var i in obj) {
            newobj[i] = deepCopy(obj[i])                        // 判断下一级是否还是引用类型
        }
    } else {
        newobj = obj
    }
    return newobj
}

function fileChange(e) {
    var e = e || window.event;
    //获取 文件 个数 取消的时候使用
    var files = e.target.files;
    if (files.length > 0) {
        var imgNode = $(imgDom)
        imgNode.attr("src", getObjectURL(files[0]))

        var reader = new FileReader();
        reader.onload = (function (file) {
            return function (e) {

                settingForHandsontable.data[imgDom["data-row"]][imgDom["data-type"]] = this.result;

                //更新该图片td的数据
                //setDataAtCell会触发afterChange事件
                hot.setDataAtCell(imgDom["data-row"], getColByKey(imgDom["data-type"]), this.result);

                document.querySelector('#uploadImgeForHandsontable').value = null
            };
        })(files[0]);
        reader.readAsDataURL(files[0]);

    }
}



var imgDom;
function imgRenderer(type) {
    return function (instance, td, row, col, prop, value, cellProperties) {

        var img = document.createElement('IMG')
        img.src = value ? value : ""
        img.classList.add("uploading-image-dom")
        img['data-row'] = row
        img['data-type'] = type

        img.addEventListener('click', function () {

            imgDom = img;

            $("#uploadImgeForHandsontable").click()
        })
        td.classList.add("uploading-image-cursor")
        td.innerText = ''
        td.appendChild(img)

    }
}


function imgRendererForDom(instance, td, row, col, prop, value, cellProperties) {
    var img = document.createElement('IMG')
    img.src = value ? value : "null"
    img['data-row'] = row
    td.classList.add("url-img")
    td.innerText = ''
    td.appendChild(img)
    return td
}

var colHeaders = [
    "SKU",
    "<span class='must'>*</span>" + languageConversion("英文品名", "English name"),
    languageConversion("中文品名", "Chinese Name"),
    "<span class='must'>*</span>" + languageConversion("数量", "Quantity"),
    "<span class='must'>*</span>" + languageConversion("单价(USD)", "USD"),
    languageConversion("单位", "Unit"),
    languageConversion("总价值", "Total Price"),
    languageConversion("商品单重", "Net Weight"),
    languageConversion("海关编号", "HS Code"),
    languageConversion("备货备注", "Ready for the remarks"),
    languageConversion("商品图片", "Commodity images"),
    languageConversion("销售网址", "Sale Website"),
    languageConversion("材质", "Material"),
    languageConversion("规格型号", "Specification"),
    languageConversion("用途", "Scope of use"),
    languageConversion("品牌", "Brand"),
    languageConversion("行邮税号", "Posttax Num"),
]

var colHeadersInit = deepCopy(colHeaders)

function textValidator(query, callback) {

    if (query == undefined || query == null || $.trim(query) == '') {
        callback(false)
    } else {
        callback(true)
    }

}

function cnnameValidator(query, callback) {
    if (query != null && query != undefined && $.trim(query) != '' && !(/[\u4e00-\u9fa5]+/).test($.trim(query))) {
        layer.msg($.t("crjs.cnnamechange"));
        callback(false)
    } else {
        callback(true)
    }
}

function getUnitCode(unitCode) {
    var current = "";
    var key = getColByKey("unit_code")
    for (var i = 0; i < columns[key].selectOptions.length; i++) {
        var item = columns[key].selectOptions[i];
        if (item.indexOf(unitCode) != -1) {
            current = item
            break
        }
    }
    return current
}

var columns = [
    {
        data: "sku",
        width: 90,
    },
    {
        data: "invoice_enname",
        validator: textValidator,
        width: 90,
    },
    {
        data: "invoice_cnname",
        validator: cnnameValidator,
        width: 90,
    },
    {
        data: "invoice_quantity",
        type: "numeric",
        allowEmpty: false,
    },
    {
        data: "invoice_unitcharge",
        type: "numeric",
        numericFormat: {
            pattern: '0.0000',
        },
        allowEmpty: false,
    },
    {
        data: "unit_code",
        editor: 'select',
        selectOptions: ['(PCE)件', '(SET)套', '(MTR)米', '(ABC)张', '(BOX)盒', '(DZ)打', '(PAI)双', '(BUN)串', '(TET)咑'],
        width: 90,
    },
    {
        data: "invoice_totalprice",
        type: "numeric",
        numericFormat: {
            pattern: '0.0000',
        },
        readOnly: true,
    },
    {
        data: "net_weight",
        type: 'numeric',
        numericFormat: {
            pattern: '0.000',
        },
        width: 90,
    },
    {
        data: "hs_code",
        type: 'text',
        width: 90,
    },
    {
        data: "invoice_note",
        type: 'text',
        width: 90,
    },
    {
        data: "invoice_info",
        type: 'text',
        width: 90,
    },
    {
        data: "invoice_url",
        type: 'text',
        width: 90,
    },
    {
        data: "invoice_material",
        type: 'text',
        width: 90,
    },
    {
        data: "invoice_spec",
        type: 'text',
        width: 90,
    },
    {
        data: "invoice_use",
        type: 'text',
        width: 90,
    },
    {
        data: "invoice_brand",
        type: 'text',
        width: 90,
    },
    {
        data: "posttax_num",
        type: 'text',
        width: 90,
    },
]


function afterChange(changeData, operationType) {
    if (operationType == "loadData") return
    var sourceData = hot.getSourceData()

    invoiceTotalpriceOp(sourceData, changeData)
    //可以使用 hot.getSourceData()来获取数据，不要通过settingForHandsontable配置里的data
    mappingHotDataForSettingForHandsontableData()
}

function beforeRemoveRow(changes, source) {

    if (settingForHandsontable.data.length == 1) {
        layer.alert("至少保留一条申报信息", {
            icon: 0,
            title: $.t("diaebay.deleteoperation"),
            skin: 'layui-layer-lan',
            btn: [$.t("crjs.ok")],
        });

        return false;
    }
}

function afterRemoveRow() {
    mappingHotDataForSettingForHandsontableData()
}

function afterCreateRow(index, amount, source) {
    //setDataAtCell会触发afterChange事件
    hot.setDataAtCell(index, getColByKey("unit_code"), dataInit[0].unit_code);
    hot.setDataAtCell(index, getColByKey("invoice_quantity"), dataInit[0].invoice_quantity);
    mappingHotDataForSettingForHandsontableData();

    //mappingHotDataForSettingForHandsontableData()
    //settingForHandsontable.data[index].unit_code = dataInit[0].unit_code
    //settingForHandsontable.data[index].invoice_quantity = dataInit[0].invoice_quantity
    //hot.loadData(settingForHandsontable.data)
}

function mappingHotDataForSettingForHandsontableData() {
    settingForHandsontable.data = hot.getSourceData()
}

function computedInvoiceTotalprice(invoice_quantity, invoice_unitcharge) {
    var invoice_totalprice = "";

    if (invoice_quantity != null && invoice_quantity != undefined && invoice_quantity != '' && !Number.isNaN(Number(invoice_quantity)) &&
        invoice_unitcharge != null && invoice_unitcharge != undefined && invoice_unitcharge != '' && !Number.isNaN(Number(invoice_unitcharge))) {
        invoice_totalprice = (parseFloat(invoice_quantity) * parseFloat(invoice_unitcharge)).toFixed(4);
    }

    return invoice_totalprice
}

function invoiceTotalpriceOp(sourceData, changeData) {
    var typeArr = ['invoice_quantity', 'invoice_unitcharge']

    for (var i = 0; i < changeData.length; i++) {
        var arr = changeData[i];
        if (typeArr.indexOf(arr[1]) != -1 && arr[2] !== arr[3]) {
            var item = sourceData[arr[0]];

            //将invoice_quantity小数变整数
            //if (arr[1] == "invoice_quantity" && String(item.invoice_quantity).indexOf(".") >= 0) {
            //	item.invoice_quantity = parseInt(String(item.invoice_quantity).split(".")[0])
            //}

            item.invoice_totalprice = computedInvoiceTotalprice(item.invoice_quantity, item.invoice_unitcharge);

            var rowIndex = parseInt(arr[0])
            var colIndex;
            //获取invoice_totalprice在第几列
            var colIndex = getColByKey("invoice_totalprice")
            if (colIndex != null && colIndex != undefined) {
                //更新 invoice_totalprice列内容
                //setDataAtCell会触发afterChange事件
                hot.setDataAtCell(rowIndex, colIndex, item.invoice_totalprice);
            }
            //settingForHandsontable.data[arr[0]].invoice_totalprice = item.invoice_totalprice
        }
    }
}

function getColByKey(key) {
    var column;
    for (var y = 0; y < columns.length; y++) {
        var col = columns[y];
        if (col.data == key) {
            column = y;
            break;
        }
    }
    return column
}

function getKeyByCol(col) {
    var key;
    if (col < columns.length) {
        key = columns[col].data
    }
    return key;
}

function updateRow(addRows) {

    if (addRows <= 0) {
        layer.alert("行数不能小于1", {
            icon: 0,
            title: $.t("crjs.alert"),
            skin: 'layui-layer-lan',
            btn: [$.t("crjs.ok")],
        });
        return
    }

    var data = settingForHandsontable.data;
    if (addRows > data.length) {
        var spaceRow = addRows - data.length
        while (spaceRow > 0) {
            data.push(deepCopy(dataInit[0]));
            spaceRow--;
        }
    } else if (addRows < data.length) {
        data.splice(addRows, data.length - addRows)
    }

    if (addRows >= 200) {
        settingForHandsontable.height = 700
    } else {
        //能优化性能
        settingForHandsontable.height = "auto"
    }
    hot.updateSettings(settingForHandsontable)

    hot.loadData(data)
}

function fillRow() {
    var d = top.dialog({
        title: "报关信息填充",
        url: '/usercenter/dialog/dialog_fab_fill_information.aspx',
        width: '60em',
        height: '400px',
        cancel: false,
        onclose: function () {
            if (this.returnValue) {

                var fillInfoObj = JSON.parse(this.returnValue)

                if (fillInfoObj && fillInfoObj.rows != settingForHandsontable.data.length) {
                    updateRow(fillInfoObj.rows)
                }

                if (fillInfoObj.infoArr && fillInfoObj.infoArr.length > 0) {
                    fillInfoObj.infoArr.forEach(function (fillInfo) {
                        if (fillInfo.startRow != null && fillInfo.startRow != undefined && fillInfo.endRow != null && fillInfo.endRow != undefined) {
                            var keys = Object.keys(fillInfo)
                            for (var i = parseInt(fillInfo.startRow) - 1; i < parseInt(fillInfo.endRow); i++) {
                                var item = settingForHandsontable.data[i];

                                keys.forEach(function (key) {
                                    if (key != "startRow" || key != "endRow") {
                                        item[key] = fillInfo[key]


                                    }
                                })
                            }
                        }
                    });
                    hot.loadData(settingForHandsontable.data)
                }
            }
        },
    }).showModal();

    d.rows = settingForHandsontable.data.length;
}

function addRows() {
    settingForHandsontable.data.push(deepCopy(dataInit[0]))
    hot.loadData(settingForHandsontable.data)
}

function beforeKeyDown(e) {
    if (e.keyCode === 13) {
        var selection = hot.getSelected()[0]; //[row, col]

        var row = selection[0]
        var col = selection[1]

        // 2 英文品名 3 中文品名 8 海关编号
        var typeArr = ["invoice_enname", "invoice_cnname", "hs_code"]
        var key = getKeyByCol(col)
        if (key != null && key != undefined && typeArr.indexOf(key) != -1) {
            //设置为选中，预防还是编辑中
            hot.selectCell(row, col)
            setTimeout(function () {
                columnsData = hot.getDataAtCell(row, col)
                if (columnsData != null && columnsData != undefined && columnsData != '') {
                    selectcommoninvoice(columnsData, row)
                }
            }, 100)
            e.stopImmediatePropagation();
        }
    }
}

function afterRender() {
    //if (isSubmitValidator) {
    //    imgUploadingMustAddCss()
    //}
}

var dataInit = [{"invoice_quantity": 1, "unit_code": "(PCE)件" }]

var container = document.getElementById('handsontable');
var hot;
//初始化
hot = new Handsontable(container, settingForHandsontable);

//滚动到指定错误位置（只能横向滚动，竖向不行）
function scrollToErrorCol() {
    var htInvalidDom = $(".htInvalid")
    if (htInvalidDom && htInvalidDom.length > 0) {
        var firstHtInvalidDom = $(htInvalidDom[0])
        var errorRow = firstHtInvalidDom.parent().index();
        var errorCol = firstHtInvalidDom.index() - 1;
        hot.selectCell(errorRow, errorCol)
    }
}

handsontable的常用方法

hot.selectCell(row, col)

选中指定的单元格（如果是编辑状态也会取消该状态），如果该单元格不在视图范围内，该单元格会自动滚动到视图，但只能是配置项设置高度为固定值才触发，如果是高度为atuo，数据多时，只会横向滚动到该单元格，竖向不生效

hot.getDataAtCell(row, col)

获取指定单元格的数据（只会获取不是编辑状态时的，该单元格处于编辑中，获取的不是编辑中的数据，而是之前的）

hot.validateCells()

校验handsontable是否符合数据规范

hot.updateSettings()

更新handsontable的配置

hot.loadData()

重新加载数据，能触发afterChange方法

hot.setDataAtCell(row, col, value)

设置指定单元格的值

hot.getSourceData()

返回hondsontable已经存在的数据，已下面这种格式返回

[{"invoice_quantity": 1, "unit_code": "(PCE)件" }]

性能优化

当配置中的height为auto时，存在大量数据时，整个hondsontable编辑会卡顿，这时设置height为固定高度，能解决

if (rows >= 200) {
     settingForHandsontable.height = 700
  } else {
      //能优化性能
      settingForHandsontable.height = "auto"
  }
  hot.updateSettings(settingForHandsontable)