ConstructionCost/public/web/sheet/sheet_common.js

/**
 * Created by Tony on 2017/4/28.
 */
var sheetCommonObj = {
    // CSL.2017.06.05
    // createSpread、initSheet 在一个Spread多个Sheet分别调用时的情况下使用。
    createSpread: function(container, SheetCount){
        var me = this;
        var spreadBook = new GC.Spread.Sheets.Workbook(container, { sheetCount: SheetCount });
        spreadBook.options.tabStripVisible = false;
        spreadBook.options.showHorizontalScrollbar = true;
        spreadBook.options.showVerticalScrollbar = true;
        spreadBook.options.allowCopyPasteExcelStyle = false;
        spreadBook.options.allowUserDragDrop = true;
        spreadBook.options.allowContextMenu = false;
        spreadBook.options.allowUserEditFormula = false;
        spreadBook.options.showDragFillSmartTag = false;
        return spreadBook;
    },

    initSheet: function(sheet, setting, rowCount) {

        var me = this;
        var spreadNS = GC.Spread.Sheets;
        if(setting.headRows == 2){
            return me.buildSpanHeader(sheet, setting, rowCount);//初始化合并表格列头
        }
        sheet.suspendPaint();
        sheet.suspendEvent();
        if(setting.frozenCols)  sheet.frozenColumnCount(setting.frozenCols);//冻结列s
        sheet.setRowCount(setting.headRows ? setting.headRows : 1, spreadNS.SheetArea.colHeader);
        sheet.setColumnCount(setting.header.length, spreadNS.SheetArea.viewport);

        if (setting && setting.view && setting.view.colHeaderHeight) {
            sheet.setRowHeight(0, setting.view.colHeaderHeight, spreadNS.SheetArea.colHeader);
        };
        if (setting && setting.view && setting.view.rowHeaderWidth) {
            sheet.setColumnWidth(0, setting.view.rowHeaderWidth, spreadNS.SheetArea.rowHeader);
        };
        if (setting.emptyRowHeader) {
            sheet.setColumnWidth(0, 1, GC.Spread.Sheets.SheetArea.rowHeader);
        }
        sheet.options.colHeaderAutoTextIndex = 1;
        sheet.options.colHeaderAutoText = spreadNS.HeaderAutoText.numbers;
        sheet.options.clipBoardOptions = GC.Spread.Sheets.ClipboardPasteOptions.values;
        sheet.options.protectionOptions = {
            allowResizeColumns: true
        };

        sheet.showRowOutline(false);
        sheet.options.allowCellOverflow = false;
        me.buildHeader(sheet, setting);
        sheet.resumeEvent();
        sheet.resumePaint();
    },

    // buildSheet 在一个Spread、一个Sheet的情况下使用。
    buildSheet: function(container, setting, rowCount) {
        var me = this;
        var spreadBook = me.createSpread(container, { sheetCount: 1 });
        var sheet = spreadBook.getSheet(0);
        me.initSheet(sheet, setting, rowCount);
        return spreadBook;
    },

    buildSpanHeader:function (sheet, setting, rowCount) {
        let temSetting = {
            "emptyRows":0,
            "headRows":2,
            "headRowHeight":[21],
            "defaultRowHeight": 21,
            "treeCol": 0,
            "cols":[]
        };
        let spanSetting =   sheetCommonObj.transferToTreeSetting(setting,temSetting);
        TREE_SHEET_HELPER.loadSheetHeader(spanSetting,sheet,rowCount);

    },


    buildHeader: function(sheet, setting){
        var me = this, ch = GC.Spread.Sheets.SheetArea.colHeader;
        for (var i = 0; i < setting.header.length; i++) {
            sheet.setValue(0, i, setting.header[i].headerName, ch);
            sheet.getCell(0, i, ch).wordWrap(true);
            sheet.setColumnWidth(i, setting.header[i].headerWidth?setting.header[i].headerWidth:100);
            sheet.setColumnVisible(i,setting.header[i].visible === false ? false:true);
        }
    },
    getHeadersFromTreeSetting: function (treeSetting) {
        const vAlignMap = ['top', 'center', 'bottom'];
        const hAlignMap = ['left', 'center', 'right'];
        return treeSetting.cols.map(item => {
            const vAlign = vAlignMap[item.data.vAlign];
            const hAlign = hAlignMap[item.data.hAlign];
            return {
                name: item.head.titleNames[0],
                dataCode: item.data.field,
                width: item.width,
                vAlign,
                hAlign,
            };
        });

    },
    setHeader: function(sheet, headers) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        sheet.setColumnCount(headers.length);
        sheet.setRowHeight(0, 30, GC.Spread.Sheets.SheetArea.colHeader);
        headers.forEach((header, index) => {
            sheet.setValue(0, index, header.name, GC.Spread.Sheets.SheetArea.colHeader);
            sheet.setColumnWidth(index, header.width, GC.Spread.Sheets.SheetArea.colHeader);
            if (header.formatter) {
                sheet.setFormatter(-1, index, header.formatter);
            }
            sheet.getRange(-1, index, -1, 1).hAlign(GC.Spread.Sheets.HorizontalAlign[header.hAlign]);
            sheet.getRange(-1, index, -1, 1).vAlign(GC.Spread.Sheets.VerticalAlign[header.vAlign]);
        });
        sheet.resumeEvent();
        sheet.resumePaint();
    },
    cleanSheet: function(sheet, setting, rowCount) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        sheet.clear(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
        if (rowCount > 0) sheet.setRowCount(rowCount);
        sheet.clearSelection();
        sheet.resumeEvent();
        sheet.resumePaint();
    },
    cleanData: function (sheet, setting, rowCount) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        sheet.clear(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
        if (rowCount > 0) sheet.setRowCount(rowCount);
        sheet.resumeEvent();
        sheet.resumePaint();
    },
    setAreaAlign: function(area, hAlign, vAlign){
        if (!(hAlign) || hAlign === "left") {
            area.hAlign(GC.Spread.Sheets.HorizontalAlign.left);
        } else if (hAlign === "right") {
            area.hAlign(GC.Spread.Sheets.HorizontalAlign.right);
        } else if (hAlign === "center") {
            area.hAlign(GC.Spread.Sheets.HorizontalAlign.center);
        } else {
            area.hAlign(GC.Spread.Sheets.HorizontalAlign.left);
        }
        if (!(vAlign) || vAlign === "center") {
            area.vAlign(GC.Spread.Sheets.VerticalAlign.center);
        } else if (vAlign === "top") {
            area.vAlign(GC.Spread.Sheets.VerticalAlign.top);
        } else if (vAlign === "bottom") {
            area.vAlign(GC.Spread.Sheets.VerticalAlign.bottom);
        } else {
            area.vAlign(GC.Spread.Sheets.VerticalAlign.center);
        }
    },
    showTreeData:function (sheet,setting,data) {
        let ch = GC.Spread.Sheets.SheetArea.viewport;
        let parentMap=_.groupBy(data, 'ParentID');
        let visibleMap = {};
        let styleRow=[];
        sheet.suspendPaint();
        sheet.suspendEvent();
        for (let col = 0; col < setting.header.length; col++) {
            let hAlign = "left", vAlign = "center";
            if (setting.header[col].hAlign) {
                hAlign = setting.header[col].hAlign;
            } else if (setting.header[col].dataType !== "String"){
                hAlign = "right";
            }
            vAlign = setting.header[col].vAlign?setting.header[col].vAlign:vAlign;
            sheetCommonObj.setAreaAlign(sheet.getRange(-1, col, -1, 1), hAlign, vAlign);
            if (setting.header[col].formatter) {
                sheet.setFormatter(-1, col, setting.header[col].formatter, GC.Spread.Sheets.SheetArea.viewport);
            }
            if(setting.header[col].cellType === "comboBox"){
                this.setComboBox(-1,col,sheet,setting.header[col].options,setting.header[col].editorValueType,setting.header[col].editable,setting.header[col].maxDropDownItems);
            }
            for (let row = 0; row < data.length; row++) {
                if(data[row].cellType === 'comboBox'){
                    let options = data[row].options ? data[row].options.split("@") : [];
                    this.setComboBox(row,col,sheet,options);
                }else if(data[row].cellType === 'String'){//默认设置字符输入，避免出现输入10：01变成日期的情况
                    sheet.setFormatter(row, col,"@", GC.Spread.Sheets.SheetArea.viewport);
                }
                let val = data[row][setting.header[col].dataCode];
                if(val&&setting.header[col].dataType === "Number"){
                    if(setting.header[col].hasOwnProperty('decimalField')){
                        let decimal = getDecimal(setting.header[col].decimalField);
                        val =scMathUtil.roundToString(val,decimal);
                        sheet.setFormatter(-1, col,getFormatter(decimal), GC.Spread.Sheets.SheetArea.viewport);
                    }else {
                        val =scMathUtil.roundToString(val,2);
                    }
                }
                if(val!=null && setting.header[col].cellType == "checkBox"){
                    this.setCheckBoxCell(row,col,sheet,val);
                }
                sheet.setValue(row, col, val, ch);
                if(col==0){
                    let treeType = sheetCommonObj.getTreeNodeCellType(data,row,parentMap);
                    sheet.getCell(row, 0).cellType(treeType);
                    visibleMap[data[row].ID] = treeType.collapsed;
                    this.setRowVisible(data,row,visibleMap,sheet);
                }
                if(data[row].bgColour) styleRow.push(row)
            }
        }
        for(let r of styleRow){
            this.setRowStyle(r,sheet,data[r].bgColour);
        }
        this.lockCells(sheet,setting);
        sheet.resumeEvent();
        sheet.resumePaint();
    },
    setRowVisible:function (data,row,visibleMap,sheet) {
        sheet.getRange(row , -1, 1, -1).visible(getVisible(data[row].ParentID));//显示或隐藏
        function getVisible(ParentID) {
            if(visibleMap[ParentID]) return false //如果父节点是缩起的，那就隐藏本身。
            if(visibleMap[ParentID] == false){//如果父节点不是缩起的，要再往父节点找看
                let pnode = _.find(data,{'ID':ParentID});
                if(pnode) return getVisible(pnode.ParentID);//如果有父节点，递归调用
                return true;//没有，返回显示
            }
        }
    },
    setSheetBySetting: function (sheet, setting) {
        function setCellType(range, cellType) {
            let type;
            if (cellType === 'checkBox') {
                type = new GC.Spread.Sheets.CellTypes.CheckBox();
            }
            range.cellType(type);
        }
        setting.header.forEach((item, index) => {
            const range = sheet.getRange(-1, index, -1, 1);
            this.setAreaAlign(range, item.hAlign, item.vAlign);
            if (item.cellType) {
                console.log(item);
                setCellType(range, item.cellType);
            }
            if (item.formatter) {
                range.formatter(item.formatter)
            }
        });
    },
    appendData: function (sheet, index, rowIndex, setting, data) {
        /*const viewport = GC.Spread.Sheets.SheetArea.viewport;
        const rowHeader = GC.Spread.Sheets.SheetArea.rowHeader;*/
        if (!index) {
            sheet.setRowCount(0);
        }
        sheet.suspendPaint();
        sheet.suspendEvent();
        // 追加空行
        sheet.addRows(index, data.length);
        // 显示数据
        data.forEach((item, rIdx) => {
            const row = index + rIdx;
            setting.header.forEach((hItem, cIdx) => {
                const dataCode = hItem.dataCode;
                const val = gljUtil.isDef(item[dataCode]) ? item[dataCode] : '';
                sheet.setValue(row, cIdx, val);
            });
        });
        sheet.resumeEvent();
        sheet.resumePaint();
    },
    showData: function(sheet, setting, data,distTypeTree,callback) {
        var me = this, ch = GC.Spread.Sheets.SheetArea.viewport;
        sheet.suspendPaint();
        sheet.suspendEvent();
        //sheet.addRows(row, 1);
        sheet.clear(0, 0, sheet.getRowCount(), sheet.getColumnCount(), GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
        if(sheet.getRowCount()<data.length){
            data.length<30? sheet.setRowCount(30):sheet.setRowCount(data.length);
        }else if(sheet.getRowCount()==0){
            sheet.setRowCount(30);
        }
        for (var col = 0; col < setting.header.length; col++) {
            var hAlign = "left", vAlign = "center";
            if (setting.header[col].hAlign) {
                hAlign = setting.header[col].hAlign;
            } else if (setting.header[col].dataType !== "String"){
                hAlign = "right";
            }
            vAlign = setting.header[col].vAlign?setting.header[col].vAlign:vAlign;
            me.setAreaAlign(sheet.getRange(-1, col, -1, 1), hAlign, vAlign);
            if (setting.header[col].formatter) {
                sheet.setFormatter(-1, col, setting.header[col].formatter, GC.Spread.Sheets.SheetArea.viewport);
            }
            if(setting.headRows != 2 &&  setting.header[col].cellType === "checkBox"||setting.header[col].cellType === "button"){//clear and reset   2019 - 11- 11 加入了多行列头的判断情况，排除多行列头时清空操作，先试运行看是否有问题
                var me = this, header = GC.Spread.Sheets.SheetArea.colHeader;
                sheet.deleteColumns(col,1);
                sheet.addColumns(col, 1);
                sheet.setValue(0, col, setting.header[col].headerName, header);
                sheet.setColumnWidth(col, setting.header[col].headerWidth?setting.header[col].headerWidth:100);
            }
            if(setting.header[col].visible!==null&&setting.header[col].visible!==undefined){
                sheet.setColumnVisible(col,setting.header[col].visible);
            }
            sheet.getCell(0, col, GC.Spread.Sheets.SheetArea.colHeader).wordWrap(true);
        }
        for (var row = 0; row < data.length; row++) {
            //var cell = sheet.getCell(row, col, GC.Spread.Sheets.SheetArea.viewport);
            this.showRowData(sheet,setting,row,data,distTypeTree);
        }
        if(setting.emptyRowHeader){
            let rowCount = sheet.getRowCount();
            for (let row = 0; row < rowCount; row++) {
                sheet.setValue(row, 0, '', GC.Spread.Sheets.SheetArea.rowHeader);
            }
        }
        this.lockCells(sheet,setting);
        if(callback) callback();
        sheet.resumeEvent();
        sheet.resumePaint();
        //me.shieldAllCells(sheet);
    },
    showRowData:function (sheet,setting,row,data,distTypeTree=null) {
        let ch = GC.Spread.Sheets.SheetArea.viewport;
        if(data[row].cellType == "date"){//日期格式比较特殊，要判断数据里的属性，为data类型,同时读取列
            this.setDatePickerCellType(row,data[row].dateCol,sheet)
        }
        if(data[row].cellType === 'comboBox'){//对于选项在数据里下拉框类型,同时读取列
            let options = data[row].options ? data[row].options.split("@") : [];
            this.setComboBox(row,data[row].dateCol,sheet,options);
        }

        for (var col = 0; col < setting.header.length; col++) {
            //var cell = sheet.getCell(row, col, GC.Spread.Sheets.SheetArea.viewport);
            var val = data[row][setting.header[col].dataCode];
            let style = null;
            if(data[row].foreColor && data[row].styleCol == col){
                style = {foreColor: data[row].foreColor};
                sheet.setStyle(row, col, {foreColor: data[row].foreColor});
            }
            if(val&&setting.header[col].dataType === "Number"){
                if(setting.header[col].hasOwnProperty('tofix')){
                    val =scMathUtil.roundToString(val,setting.header[col].tofix);
                }
                else if(setting.header[col].hasOwnProperty('decimalField')){
                    var decimal = getDecimal(setting.header[col].decimalField);
                    val =scMathUtil.roundToString(val,decimal);
                    sheet.setFormatter(-1, col,getFormatter(decimal), GC.Spread.Sheets.SheetArea.viewport);
                }else {
                    val =val+'';
                }
            }
            if(val!=null&&setting.header[col].cellType === "checkBox"){
                this.setCheckBoxCell(row,col,sheet,val)
            }
            if(setting.header[col].cellType === "comboBox"){
                this.setComboBox(row,col,sheet,setting.header[col].options,setting.header[col].editorValueType,setting.header[col].editable,setting.header[col].maxDropDownItems);
            }
            if(setting.header[col].cellType === "selectButton"){
                this.setSelectButton(row,col,sheet,setting.header[col]);
            }
            if(setting.header[col].cellType === "replaceButton"){
                this.setReplaceButton(row,col,sheet,setting.header[col]);
            }
            if(setting.header[col].cellType === "cusButton"){
                this.setCusButton(row,col,sheet,setting,style);
            }
            if(setting.header[col].cellType === "tipsCell"){
                this.setTipsCell(row,col,sheet,setting.header[col]);
            }
            if(setting.owner==='gljTree'){
                if(setting.header[col].cellType === "checkBox"){
                    val==1?val:0;
                    this.setCheckBoxCell(row,col,sheet,val);
                }
                if(setting.header[col].dataCode === 'gljType' && data[row].gljType){
                    let distTypeVal =  distTypeTree.distTypes[distTypeTree.prefix + data[row].gljType].data.fullName;
                    val=distTypeVal;
                }
            }
            if(setting.header[col].getText){
                val = setting.getText[setting.header[col].getText](data[row],val)
            }
            sheet.setValue(row, col, val, ch);
        }
        this.setRowStyle(row,sheet,data[row].bgColour);
        if(setting.autoFit==true){//设置自动行高
            if(setting.fitRow && setting.fitRow.length > 0){//如果有设置特定的某些列才需要自动行高就按设置的来，没有设置就默认所有列
                for(let dataCode of setting.fitRow){
                  let col =  _.findIndex(setting.header,{dataCode:dataCode})
                  sheet.getCell(row,col).wordWrap(true);
                }
            }else {
                sheet.getRange(row, -1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).wordWrap(true);
            }

            sheet.autoFitRow(row);
        }
        if(setting.getStyle && setting.getStyle(data[row],row,sheet.getActiveRowIndex())){
          sheet.setStyle(row, -1, setting.getStyle(data[row]));
        }
    },
    checkData : function(col,setting, value) {
        let result = true;
        let validator = setting.header[col].validator !== undefined ? setting.header[col].validator : null;
        if (validator === null) {
            return result;
        }
        switch (validator) {
            case 'number':
                let regular = /^\d+(\.\d+)?$/;
                result = regular.test(value);
                break;
            case 'boolean':
                let booleanValue = [true, false];
                result = booleanValue.indexOf(value) >= 0;
                break;
        }
        return result;
    },
    //todo
    analyzePasteData: function(setting, pastedInfo) {
        var rst = [], propId = pastedInfo.cellRange.col, preStrIdx = 0, itemObj = {};//propId = 0 to proId = pastedInfo.cellRange.col, update by zhong
        for (var i = 0; i < pastedInfo.pasteData.text.length; i++) {
            if (pastedInfo.pasteData.text[i] === "\n") {
                propId = pastedInfo.cellRange.col;//propId = 0 to proId = pastedInfo.cellRange.col, update by zhong
                preStrIdx = i + 1;
                rst.push(itemObj);
                if (i < pastedInfo.pasteData.text.length - 1) {
                    itemObj = {};
                }
            } else if (pastedInfo.pasteData.text[i] === "\t" || pastedInfo.pasteData.text[i] === "\r") {
                if (setting.header[propId]) {
                    itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx, i);
                }
                propId++;
                preStrIdx = i + 1;
                //if the last copied-cell were empty, should check whether the end of text
                if (i == pastedInfo.pasteData.text.length - 1 && setting.header[propId]) {
                    itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx);
                    rst.push(itemObj);
                }
            } else if (i == pastedInfo.pasteData.text.length - 1 && setting.header[propId]) {
                itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx);
                rst.push(itemObj);
            }
        }
        return rst;
    },
    combineRowData: function(sheet, setting, row) {
        var rst = {};
        for (var col = 0; col < setting.header.length; col++) {
            rst[setting.header[col].dataCode] = sheet.getValue(row, col);
        }
        return rst;
    },
    shieldAllCells: function(sheet) {
        sheet.options.isProtected = true;
    },
    unShieldAllCells: function(sheet) {
        sheet.options.isProtected = false;
    },
    lockCells: function(sheet, setting){
        if (setting && setting.view.lockColumns && setting.view.lockColumns.length > 0) {
            sheet.options.isProtected = true;
            sheet.getRange(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport).locked(false);
            for (var i = 0; i < setting.view.lockColumns.length; i++) {
                let col = setting.view.lockColumns[i];
                if(_.isString(col)){//如果是dataCode 进行转换
                    col = _.findIndex(setting.header,{dataCode:col})
                }
                sheet.getRange(-1,col, -1, 1, GC.Spread.Sheets.SheetArea.viewport).locked(true);
            }
        }
        if (setting && Array.isArray(setting.view.lockRows) && setting.view.lockRows.length) {
            if (!setting.view.lockColumns || !setting.view.lockColumns.length) {
                sheet.options.isProtected = true;
                sheet.getRange(-1, 0, -1, setting.header.length, GC.Spread.Sheets.SheetArea.viewport).locked(false);
            }
            setting.view.lockRows.forEach(row => {
                sheet.getRange(row, -1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).locked(true);
            });
        }
    },
    setCheckBoxCell(row,col,sheet,val){
        var c = this.getCheckBox();
        sheet.setCellType(row, col,c,GC.Spread.Sheets.SheetArea.viewport);
        sheet.getCell(row, col).value(val);
        sheet.getCell(row, col).hAlign(GC.Spread.Sheets.HorizontalAlign.center);

    },
    getCheckBox(threeState = false){
        var c = new GC.Spread.Sheets.CellTypes.CheckBox();
        c.isThreeState(threeState);
        return c
    },
    // 无法勾选的复选框
    getReadOnlyCheckBox () {
        function ReadOnlyCheckBox() {}
        ReadOnlyCheckBox.prototype = new GC.Spread.Sheets.CellTypes.CheckBox();
        ReadOnlyCheckBox.prototype.processMouseUp = function () {
            return;
        };
        return new ReadOnlyCheckBox();
    },
    setComboBox(row,col,sheet,options,editorValueType,editable,maxDropDownItems){
        //let combo = new GC.Spread.Sheets.CellTypes.ComboBox();
        let dynamicCombo = sheetCommonObj.getDynamicCombo(true);
        if(options){
            dynamicCombo.items(options);
            if(maxDropDownItems) dynamicCombo.maxDropDownItems(maxDropDownItems);
            if(editable == true)  dynamicCombo.editable(true);//可编辑
            if(editorValueType==true){
                dynamicCombo.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value);
            }
        }
        sheet.setCellType(row, col,dynamicCombo,GC.Spread.Sheets.SheetArea.viewport);
    },
    setRowStyle(row,sheet,bgColour) {
        if(bgColour){
            let style = new GC.Spread.Sheets.Style();
            style.backColor = bgColour;
            style.borderLeft = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin);
            style.borderTop = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin);
            style.borderRight = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin);
            style.borderBottom = new GC.Spread.Sheets.LineBorder("#D4D4D4", GC.Spread.Sheets.LineStyle.thin);
            sheet.setStyle(row, -1, style);
        }
    },
    getCustomerCoeCellType: function (htmlGenerator,setEditorValue,updateCallback) {
        let me = this;
        function CustomerCoeCellType() {
            this.isEscKey=false;
            this.displayText='';
        }
        CustomerCoeCellType.prototype = new GC.Spread.Sheets.CellTypes.Base();

        CustomerCoeCellType.prototype.createEditorElement = function (context) {
            console.log("create editor")
            let element = document.createElement("div");//这里创建的，会自动销毁
            return element
        };
        CustomerCoeCellType.prototype.activateEditor = function (editorContext, cellStyle, cellRect, context) {
            if (editorContext) {
                $editor = $(editorContext);
                $editor.css("position", "fixed");
                $editor.css("background", "white");
                //$editor.css("width", cellRect.width); 2018-11-15 改成固定列宽
                $editor.css("width", 160);
                $editor.attr("gcUIElement", "gcEditingInput");
                if(htmlGenerator) htmlGenerator(context,cellRect,$editor);
            }
        }
        CustomerCoeCellType.prototype.deactivateEditor = function (editorContext, context) {

        };
        CustomerCoeCellType.prototype.setEditorValue = function (editor, value, context) {
            console.log("set editor value");
            this.displayText = value;
        };
        CustomerCoeCellType.prototype.getEditorValue = function (editor, context) {
            console.log("get value");
            if(this.isEscKey !=true&& updateCallback){
                updateCallback();
            }
            this.isEscKey = false;
            return this.displayText;
        };
        CustomerCoeCellType.prototype.updateEditor = function (editorContext, cellStyle, cellRect, context) {
            console.log(" update editor");
            if( setEditorValue){//不是esc时才更新
                setEditorValue(context);
            }
        };
        CustomerCoeCellType.prototype.isReservedKey = function (e, context) {
            //cell type handle tab key by itself
            this.isEscKey = e.keyCode === GC.Spread.Commands.Key.esc;
            return false;
        };
        return new CustomerCoeCellType();
    },
    scrollSheetForOption:function (sheet,cxt,cellRect,row,options){
        let topRow = sheet.getViewportTopRow(1);
        if(row == topRow) return;//已经是最顶行了
        let length = options&&options.length>0?options.length:1;
        let height = cxt.canvas.height;
        let startY = cellRect.y+cellRect.height;//下拉框的起始显示位置
        let endY =  startY + length * cellRect.height;//下拉框的结束显示位置
        if(endY <= height) return;  //如果没有超出显示位置，直接返回
        let overRow =  Math.ceil((endY - height)/cellRect.height);//超出的行数
        let showRow = topRow + overRow > row?row:topRow + overRow;
        sheet.showRow(showRow, GC.Spread.Sheets.VerticalPosition.top);

    },
    setSelectButton(row,col,sheet,header){
      /*  let getSelectButton = function (cellWidth=100) {
            function moreButton() {

            }
            moreButton.prototype = new GC.Spread.Sheets.CellTypes.Button();
            moreButton.prototype.paint = function (ctx, value, x, y, w, h, style, options){
                GC.Spread.Sheets.CellTypes.Button.prototype.paint.call(this, ctx, value, x, y, w, h, style, options);
                let buttonW = cellWidth/5;
                let endX = x+w-2;
                if(value){
                    let textWidth = ctx.measureText(value).width;
                    let spaceWidth = cellWidth - buttonW;
                    let textEndX = x+2+textWidth;
                    if(spaceWidth<textWidth){
                        for(let i = value.length-1;i>1;i--){
                            let newValue = value.substr(0,i);
                            let newTestWidth =  ctx.measureText(newValue).width;
                            if(spaceWidth>newTestWidth){
                                value = newValue;
                                textEndX = x+2+newTestWidth;
                                break;
                            }
                        }
                    }
                    ctx.fillText(value,textEndX,y+h-5);
                }

                //画三个点
                ctx.save();
                ctx.beginPath();
                ctx.arc(endX-buttonW/2,y+h/2,1,0,360,false);
                ctx.arc(endX-buttonW/2-4,y+h/2,1,0,360,false);
                ctx.arc(endX-buttonW/2+4,y+h/2,1,0,360,false);
                ctx.fillStyle="black";//填充颜色,默认是黑色
                ctx.fill();//画实心圆
                ctx.closePath();
                ctx.restore();
            };

            moreButton.prototype.processMouseLeave= function (hitinfo) {
                let newCell = new selectButton();
                hitinfo.sheet.setCellType(hitinfo.row, hitinfo.col, newCell, GC.Spread.Sheets.SheetArea.viewport);
                hitinfo.sheet.getCell(hitinfo.row, hitinfo.col).locked(false);
            };

            function selectButton() {
            }

            selectButton.prototype = new GC.Spread.Sheets.CellTypes.Text();

            selectButton.prototype.paint = function (ctx, value, x, y, w, h, style, options){
                GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this,arguments);
            };
            selectButton.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
                return {
                    x: x,
                    y: y,
                    row: context.row,
                    col: context.col,
                    cellStyle: cellStyle,
                    cellRect: cellRect,
                    sheetArea: context.sheetArea
                };
            };
            selectButton.prototype.processMouseDown = function (hitinfo){
                if(hitinfo.sheet.getCell(hitinfo.row,hitinfo.col).locked()!=true){
                    let b1 = new moreButton();
                    b1.marginLeft(cellWidth*4/5);
                    hitinfo.sheet.setCellType(hitinfo.row, hitinfo.col, b1, GC.Spread.Sheets.SheetArea.viewport);
                    hitinfo.sheet.getCell(hitinfo.row, hitinfo.col).locked(true);
                }
            };
            return new selectButton();
        };*/
        sheet.setCellType(row, col,this.getSelectButton(header.headerWidth),GC.Spread.Sheets.SheetArea.viewport);
    },

    setCusButton:function (row,col,sheet,setting,style) {
        let functionName = setting.header[col].callback;
        let readOnly = setting.disable?setting.disable[setting.header[col].disable]:false;
        if(typeof(readOnly) == 'function' ){
            readOnly = readOnly(row,col);
        }
        if(functionName){
            sheet.setCellType(row, col,this.getCusButtonCellType(setting.callback[functionName],readOnly,style));
        }
        //sheet.setCellType(row, col,this.getSelectButton(header.headerWidth),GC.Spread.Sheets.SheetArea.viewport);
    },

    getCusButtonCellType: function (callback, readOnly = false, ostyle) {
        var ns = GC.Spread.Sheets;

        function CusButtonCellType() {
        }

        CusButtonCellType.prototype = new ns.CellTypes.Text();
        CusButtonCellType.prototype.paint = function (ctx, value, x, y, w, h, style, options) {
            if (!readOnly) {
                if (options.sheet.getActiveRowIndex() == options.row && options.sheet.getActiveColumnIndex() == options.col) {
                    var image = document.getElementById('f_btn'), imageMagin = 3;
                    var imageHeight = 15;
                    var imageWidth = 25;
                    var imageX = x + w - imageWidth - imageMagin, imageY = y + h / 2 - imageHeight / 2;
                    ctx.save();
                    if (style.backColor) {
                        ctx.fillStyle = style.backColor;
                        ctx.fillRect(x, y, w, h);
                    }
                    ctx.drawImage(image, imageX, imageY, imageWidth, imageHeight);
                    ctx.beginPath();
                    ctx.arc(imageX + imageWidth / 2, imageY + imageHeight / 2, 1, 0, 360, false);
                    ctx.arc(imageX + imageWidth / 2 - 4, imageY + imageHeight / 2, 1, 0, 360, false);
                    ctx.arc(imageX + imageWidth / 2 + 4, imageY + imageHeight / 2, 1, 0, 360, false);
                    ctx.fillStyle = "black";//填充颜色,默认是黑色
                    ctx.fill();//画实心圆
                    ctx.closePath();
                    ctx.restore();
                    w = w - imageWidth - imageMagin;
                    //这里的左对齐的，当显示的字长度超过空白地方时，要改成右对齐
                    if (style.hAlign == 0) {
                        if (value) {
                            let textWidth = ctx.measureText(value).width;
                            let spaceWidth = w;
                            if (spaceWidth < textWidth) {
                                style.hAlign = 2;
                            }
                        }
                    }
                }
            }
            if (ostyle) gljUtil.setProperty(style, ostyle);
            GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this, arguments);
        };
        CusButtonCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
            return {
                x: x,
                y: y,
                row: context.row,
                col: context.col,
                cellStyle: cellStyle,
                cellRect: cellRect,
                sheetArea: context.sheetArea
            };
        };

        CusButtonCellType.prototype.processMouseDown = function (hitinfo) {
            if (hitinfo.sheet.getActiveRowIndex() == hitinfo.row && hitinfo.sheet.getActiveColumnIndex() == hitinfo.col) {
                var offset = hitinfo.cellRect.x + hitinfo.cellRect.width - 6;
                var imageWidth = 25;
                if (hitinfo.x < offset && hitinfo.x > offset - imageWidth) {
                    if (!readOnly) {
                        if (callback) callback(hitinfo)
                    }
                }
            }
        };
        return new CusButtonCellType();
    },

    getSelectButton(cellWidth=100){

        function moreButton() {

        }
        moreButton.prototype = new GC.Spread.Sheets.CellTypes.Button();
        moreButton.prototype.paint = function (ctx, value, x, y, w, h, style, options){
            GC.Spread.Sheets.CellTypes.Button.prototype.paint.call(this, ctx, value, x, y, w, h, style, options);
            ctx.font = '14px Calibri';
            let buttonW = cellWidth/5;
            let endX = x+w-2;
            if(value){
                let textWidth = ctx.measureText(value).width;
                let spaceWidth = cellWidth - buttonW;
                let textEndX = x+textWidth+2;
                if(spaceWidth<textWidth){
                    for(let i = value.length-1;i>1;i--){
                        let newValue = value.substr(0,i);
                        let newTestWidth =  ctx.measureText(newValue).width;
                        if(spaceWidth>newTestWidth){
                            value = newValue;
                            textEndX = x+newTestWidth+2;
                            break;
                        }
                    }
                }
                ctx.fillText(value,textEndX,y+h-6);
            }

            //画三个点
            ctx.save();
            ctx.beginPath();
            ctx.arc(endX-buttonW/2,y+h/2,1,0,360,false);
            ctx.arc(endX-buttonW/2-4,y+h/2,1,0,360,false);
            ctx.arc(endX-buttonW/2+4,y+h/2,1,0,360,false);
            ctx.fillStyle="black";//填充颜色,默认是黑色
            ctx.fill();//画实心圆
            ctx.closePath();
            ctx.restore();
        };

        moreButton.prototype.processMouseLeave= function (hitinfo) {
            let newCell = new selectButton();
            hitinfo.sheet.setCellType(hitinfo.row, hitinfo.col, newCell, GC.Spread.Sheets.SheetArea.viewport);
            hitinfo.sheet.getCell(hitinfo.row, hitinfo.col).locked(false);
        };

        function selectButton() {
        }

        selectButton.prototype = new GC.Spread.Sheets.CellTypes.Text();

        selectButton.prototype.paint = function (ctx, value, x, y, w, h, style, options){
            GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this,arguments);
        };
        selectButton.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
            return {
                x: x,
                y: y,
                row: context.row,
                col: context.col,
                cellStyle: cellStyle,
                cellRect: cellRect,
                sheetArea: context.sheetArea
            };
        };
        selectButton.prototype.processMouseDown = function (hitinfo){
            if(hitinfo.sheet.getCell(hitinfo.row,hitinfo.col).locked()!=true){
                let b1 = new moreButton();
                b1.marginLeft(cellWidth*4/5);
                hitinfo.sheet.setCellType(hitinfo.row, hitinfo.col, b1, GC.Spread.Sheets.SheetArea.viewport);
                hitinfo.sheet.getCell(hitinfo.row, hitinfo.col).locked(true);
            }
        };
        return new selectButton();
    },
    setReplaceButton(row,col,sheet){
        let replaceButton = function(){

        };
        replaceButton.prototype = new GC.Spread.Sheets.CellTypes.Button();
        replaceButton.prototype.paint = function (ctx, value, x, y, w, h, style, options){
            GC.Spread.Sheets.CellTypes.Button.prototype.paint.apply(this,arguments);
            if(value){
                ctx.save();
                ctx.fillStyle = "white";
                let fh = options.fontInfo&&options.fontInfo.fontSize?options.fontInfo.fontSize:h-6;
                ctx.fillText(value,x+(w+ctx.measureText(value).width)/2,y+(h+fh)/2-2);
                ctx.restore();
            }
        };
        let cellType = new replaceButton();
        cellType.buttonBackColor("#07A0FF");
        sheet.setCellType(row, col,cellType,GC.Spread.Sheets.SheetArea.viewport);
    },
    setDatePickerCellType(row,col,sheet){
        let ns = GC.Spread.Sheets;

        function DatePickerCellType() {
        }
        DatePickerCellType.prototype = new GC.Spread.Sheets.CellTypes.Base();

        DatePickerCellType.prototype.createEditorElement = function (context) {
            //Create input presenter.
            return document.createElement("input");
        };
        DatePickerCellType.prototype.activateEditor = function (editorContext, cellStyle, cellRect, context) {
            //Initialize input editor.
            if (editorContext) {
                $editor = $(editorContext);
                //DatePickerCellType.prototype.activateEditor.apply(this, arguments);
                $editor.datepicker({dateFormat: 'yy-mm-dd'});
                $editor.css("position", "absolute");
                $editor.attr("gcUIElement", "gcEditingInput");
                $(".ui-datepicker").attr("gcUIElement", "gcEditingInput");
            }
        }
        DatePickerCellType.prototype.deactivateEditor = function (editorContext, context) {
            //Remove input editor when end editor status.
            if (editorContext) {
                var element = editorContext;
                $(element).datepicker("hide");
                $(element).datepicker("destroy");
            }
            // DatePickerCellType.prototype.deactivateEditor.apply(this, arguments)
        };
        DatePickerCellType.prototype.setEditorValue = function (editor, value, context) {
            //Sync value from Cell value to editor value.
            $(editor).datepicker("setDate", value);
        };
        DatePickerCellType.prototype.getEditorValue = function (editor, context) {
            //Sync value from editor value to cell value.
            return $(editor).datepicker("getDate");
        };
        DatePickerCellType.prototype.updateEditor = function (editorContext, cellStyle, cellRect, context) {
            if (editorContext) {
                $editor = $(editorContext);
                $editor.css("width", cellRect.width - 1);
                $editor.css("height", cellRect.height - 3);
            }
        };
        let picker = new DatePickerCellType();
        sheet.getCell(row, col).cellType(picker).width(100).formatter('yyyy-mm-dd');
    },

    setTipsCell(row,col,sheet,header){
        let TipCellType = function () {};
        TipCellType.prototype = new GC.Spread.Sheets.CellTypes.Text();
        TipCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
            return {
                x: x,
                y: y,
                row: context.row,
                col: context.col,
                cellStyle: cellStyle,
                cellRect: cellRect,
                sheet: context.sheet,
                sheetArea: context.sheetArea
            };
        };
        TipCellType.prototype.processMouseEnter = function (hitinfo) {
            let text = hitinfo.sheet.getText(hitinfo.row, hitinfo.col);
            let value = hitinfo.sheet.getValue(hitinfo.row, hitinfo.col);
            let tag = hitinfo.sheet.getTag(hitinfo.row, hitinfo.col);
            let acStyle = hitinfo.sheet.getActualStyle(hitinfo.row, hitinfo.col),
                zoom = hitinfo.sheet.zoom();
            let textLength = this.getAutoFitWidth(value, text, acStyle, zoom, {sheet: hitinfo.sheet, row: hitinfo.row, col: hitinfo.col, sheetArea: GC.Spread.Sheets.SheetArea.viewport});
            let cellWidth = hitinfo.sheet.getCell(-1, hitinfo.col).width();
            let setting = {};
            if(textLength <= cellWidth){
                return;
            }
            if(sheet && sheet.getParent().qo){
                setting.pos = SheetDataHelper.getObjPos(sheet.getParent().qo);
            }

            TREE_SHEET_HELPER.showTipsDiv(text,setting,hitinfo);
        };
        TipCellType.prototype.processMouseLeave = function (hitinfo) {
            TREE_SHEET_HELPER.tipDiv = 'hide';
            if (TREE_SHEET_HELPER._toolTipElement) {
                $(TREE_SHEET_HELPER._toolTipElement).hide();
                TREE_SHEET_HELPER._toolTipElement = null;
            };
            TREE_SHEET_HELPER.tipDivCheck();//延时检查：当tips正在show的时候，就调用了hide方法，会导致tips一直存在，所以设置一个超时处理
        };

        sheet.setCellType(row, col,new TipCellType(),GC.Spread.Sheets.SheetArea.viewport);

    },
    chkIfEmpty: function(rObj, setting) {
        var rst = true;
        if (rObj) {
            for (var i = 0; i < setting.header.length; i++) {
                if (rObj[setting.header[i].dataCode]) {
                    rst = false;
                    break;
                }
            }
        }
        return rst;
    },
    //add by zhong 2017-10-10
    //动态下拉框,配合EnterCell, args.sheet.repaint();
    getDynamicCombo: function (forLocked) {
        let ComboCellForActiveCell = function () { };
        ComboCellForActiveCell.prototype = new GC.Spread.Sheets.CellTypes.ComboBox();
        ComboCellForActiveCell.prototype.paintValue = function (ctx, value, x, y, w, h, style, options) {
            let sheet = options.sheet;
            if (options.row === sheet.getActiveRowIndex() && options.col === sheet.getActiveColumnIndex() && (!forLocked || forLocked && !sheet.getCell(options.row, options.col).locked())) {
                GC.Spread.Sheets.CellTypes.ComboBox.prototype.paintValue.apply(this, arguments);

            } else {
                GC.Spread.Sheets.CellTypes.Base.prototype.paintValue.apply(this, arguments);
            }
        };
        ComboCellForActiveCell.prototype.getHitInfo = function (x, y, cellStyle, cellRect, options) {
            let sheet = options.sheet;
            if (options.row === sheet.getActiveRowIndex() && options.col === sheet.getActiveColumnIndex() && (!forLocked || forLocked && !sheet.getCell(options.row, options.col).locked())) {
                return GC.Spread.Sheets.CellTypes.ComboBox.prototype.getHitInfo.apply(this, arguments);
            } else {
                return  {
                    x: x,
                    y: y,
                    row: options.row,
                    col: options.col,
                    cellStyle: cellStyle,
                    cellRect: cellRect,
                    sheetArea: options.sheetArea
                };//GC.Spread.Sheets.CellTypes.Text.prototype.getHitInfo.apply(this, arguments);
            }
        };
        return new ComboCellForActiveCell();
    },
    getTipsCombo:function (forLocked,tips,setting,node) {
        let getTipsCombo = function () {
            this.clickCom=false;
        };
        getTipsCombo.prototype = sheetCommonObj.getDynamicCombo(forLocked);
        if(tips && tips !=""){
            getTipsCombo.prototype.processMouseEnter = function(hitinfo){
                if(this.clickCom == true){ //点击了下拉框的三角形，则不用再显示悬浮框了
                    this.clickCom = false;
                    return;
                }
                let text =  typeof tips == 'function'?tips(node):tips;
                TREE_SHEET_HELPER.delayShowTips(hitinfo,setting,text);
            };
            getTipsCombo.prototype.processMouseLeave = function (hitinfo) {
                TREE_SHEET_HELPER.hideTipsDiv();
            };
            getTipsCombo.prototype.processMouseDown = function (hitinfo){
                if(hitinfo.isReservedLocation == true){//这里是点击了下拉框的三角形才会有这个属性
                    TREE_SHEET_HELPER.hideTipsDiv();
                    this.clickCom = true;
                }
                GC.Spread.Sheets.CellTypes.ComboBox.prototype.processMouseDown.apply(this, arguments);
            };

            getTipsCombo.prototype.updateEditor = function (editorContext, cellStyle, cellRect, context){
                TREE_SHEET_HELPER.hideTipsDiv();
                GC.Spread.Sheets.CellTypes.ComboBox.prototype.updateEditor.apply(this, arguments);
            };
        }
        return new getTipsCombo();
    },
    // paintFunc，需要追加到paint方法中的自定义paint方法
    getTreeNodeCellType:function (datas,row,parentMap,paintFunc) {// 2018-09-26  不用spreadjs默认的树结构，自定义控件
        var ns = GC.Spread.Sheets;
        let rectW = 10;
        let rectH = 10;
        let margin = 3;

        function TreeNodeCellType() {
            this.collapsed = gljUtil.isDef(datas[row].collapsed)?datas[row].collapsed: true; //默认是折叠的
            this.treeNodeType = true;
            this.rectInfo = {};
        }
        TreeNodeCellType.prototype = new ns.CellTypes.Text();
        TreeNodeCellType.prototype.paint = function (ctx, value, x, y, w, h, style, options) {
            if (paintFunc) {
                paintFunc(ctx, value, x, y, w, h, style, { rectW, rectH, margin }, datas[row]);
            }
            let offset = 0;
            let step = 7;
            let level = getTreeLevel(datas[row],datas);//从0开始，取当前节点是第几级的
            let tem = offset+margin+ rectW/2+step;//两条线之间的间隔
            let t_offset = offset;
            let temParentID = datas[row].ParentID;
            if(this.treeNodeType == true){
                for(let i = level;i>0;i--){//这里是画子节点前面的竖线，从第二级开始
                    let temParent = getParent(temParentID,datas);
                    if(temParent){//父节点有下一个兄弟节点才需要画
                        if(hasNextBrother(parentMap,temParent)) sheetCommonObj.drawLine(ctx,x+t_offset+tem*i,y,x+t_offset+tem*i,y+h);
                        temParentID = temParent.ParentID;
                    }
                    offset +=tem;
                }
            }
            offset+=step; //这个没法移动,所以要两个判断
            if(this.treeNodeType == true){
                if(hasChildern(datas[row].ID,datas)){//如果是有子节点
                    //第一条 或者没有父节点(如费率子表综合里程项)不用画方框头上那条竖线其它都要
                    if(row !=0 && gljUtil.isDef(datas[row].ParentID)) sheetCommonObj.drawLine(ctx,x+offset+ rectW/2+margin,y,x+offset+ rectW/2+margin,y + Math.round(h / 2) - rectH / 2);
                    //画方框下面的那条竖线，如果没有下一个兄弟节点，则不用画
                    if(hasNextBrother(parentMap,datas[row])) sheetCommonObj.drawLine(ctx,x+offset+ rectW/2+margin,y + Math.round(h / 2) + rectH / 2,x+offset+ rectW/2+margin,y + h);
                    sheetCommonObj.drowRect(ctx, x+offset, y, w, h,rectW,rectH,margin);
                    sheetCommonObj.drowSymbol(ctx, x+offset, y, w, h,rectW,rectH,margin, this.collapsed);
                    this.rectInfo = {x:x+offset+margin,rectW:rectW}//计录一下可点击位置
                }else {
                    let hasNext = datas[row+1]?datas[row+1].ParentID == datas[row].ParentID:false;
                    sheetCommonObj.drowSubItem(ctx, x, y, w, h, offset,hasNext,margin+ rectW/2);
                }
            }
            offset += step;
            offset += rectW;
            x = x + offset;//设置偏移
            w = w - offset;
            if(datas[row].foreColor) style.foreColor = datas[row].foreColor;
            GC.Spread.Sheets.CellTypes.Text.prototype.paint.apply(this, arguments);
        };
        // override getHitInfo to allow cell type get mouse messages
        TreeNodeCellType.prototype.getHitInfo = function (x, y, cellStyle, cellRect, context) {
            return {
                x: x,
                y: y,
                row: context.row,
                col: context.col,
                cellStyle: cellStyle,
                cellRect: cellRect,
                sheetArea: context.sheetArea
            };
        }
        TreeNodeCellType.prototype.processMouseDown = function (hitinfo) {
            ////方框外1像素内都有效
            if (!_.isEmpty(this.rectInfo)&&Math.floor(hitinfo.x) <= this.rectInfo.x+this.rectInfo.rectW+2 && Math.floor(hitinfo.x) >= this.rectInfo.x-2) {
                this.collapsed = !this.collapsed;
                datas[row].collapsed = this.collapsed;
                this.refreshChildrenVisible(hitinfo.sheet);
                hitinfo.sheet.invalidateLayout();
                hitinfo.sheet.repaint();
            }
        };
        TreeNodeCellType.prototype.refreshChildrenVisible = function (sheet) {
            sheet.suspendPaint();
            sheet.suspendEvent();
            refreshVisible(datas[row]);
            sheet.resumeEvent();
            sheet.resumePaint();
            function refreshVisible(item){
                if(parentMap[item.ID]){
                    for(let sub of parentMap[item.ID]){
                        refreshVisible(sub)
                    }
                }
                let visible = getVisible(item);
                let trow = datas.indexOf(item);
                sheet.getRange(trow , -1, 1, -1).visible(visible);
            }

            function getVisible(item) {
                if(item.ParentID){
                    let parent = getParent(item.ParentID,datas);
                    if(!parent) return true;
                    let p_row= datas.indexOf(parent);
                    let visible = !sheet.getCellType(p_row,0).collapsed;
                    if(visible == true){ //如果是显示的，则要再往父节点的父节点检查，只要有一个节点是隐藏的，则都是隐藏
                        return getVisible(parent);
                    }else {
                        return visible
                    }
                }else {//如果parentID 为空则是最根节点
                    return true;
                }
            }


        };
        return new TreeNodeCellType()

        function getTreeLevel(item,data) {
            if(item.ParentID && item.ParentID!=-1){
                let pitem =  _.find(data,{'ID':item.ParentID});
                return  getTreeLevel(pitem,data) + 1;
            }else {
                return 0
            }
        }
        function hasChildern(ID,data) {//返回是否有子项
            let p = _.find(data,{'ParentID':ID});
            if(p) return true;
            return false
        }
        function getParent(ParentID,data) {
            let p = _.find(data,{'ID':ParentID});
            return p;
        }
        function hasNextBrother(parentMap,item){
            let children =parentMap[item.ParentID];
            if(!gljUtil.isDef(children)|| children.indexOf(item) == children.length -1) return false;
            return true
        }

    },
    setDynamicCombo: function (sheet, beginRow, col, rowCount, items, itemsHeight, itemsType) {
        let me = this;
        sheet.suspendPaint();
        let combo = me.getDynamicCombo();
        for(let i = 0, len = rowCount; i < len; i++){
            if(itemsHeight) {
                combo.itemHeight(itemsHeight);
                combo._maxDropDownItems = itemsHeight + 5;
            }
            if(itemsType === 'value') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value);
            else if(itemsType === 'text') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.text);
            else combo.items(items);
            sheet.getCell(beginRow + i, col).cellType(combo);
        }
        sheet.resumePaint();
    },
    setStaticCombo: function (sheet, beginRow, col, rowCount, items, itemsHeight, itemsType) {
        sheet.suspendPaint();
        let combo = new GC.Spread.Sheets.CellTypes.ComboBox();
        for(let i = 0, len = rowCount; i < len; i++){
            if(itemsHeight) combo.itemHeight(itemsHeight);
            if(itemsType === 'value') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value);
            else if(itemsType === 'text') combo.items(items).editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.text);
            else combo.items(items);
            sheet.getCell(beginRow + i, col).cellType(combo);
        }
        sheet.resumePaint();
    },
    //设置系统粘贴板数据,需要用户触发事件，直接调用会失败
    copyTextToClipboard: function(text) {
        let textArea = document.createElement("textarea");
        textArea.style.position = 'fixed';
        textArea.style.top = 0;
        textArea.style.left = 0;
        textArea.style.width = '2em';
        textArea.style.height = '2em';
        textArea.style.padding = 0;
        textArea.style.border = 'none';
        textArea.style.outline = 'none';
        textArea.style.boxShadow = 'none';
        textArea.style.background = 'transparent';
        textArea.value = text;
        document.body.appendChild(textArea);
        textArea.select();
        try {
            let successful = document.execCommand('copy');
            let msg = successful ? 'successful' : 'unsuccessful';
            console.log('Copying text command was ' + msg);
        } catch (err) {
            console.log('Oops, unable to copy');
        }
        document.body.removeChild(textArea);
    },
    //获取选中区域的表格类型数据（可粘贴到excel）
    getTableData: function (sheet, colSettings = null) {
        let rst = '';
        let sel = sheet.getSelections()[0];
        let pasteText = [];
        for(let row = sel.row; row < sel.row + sel.rowCount; row++){
            if(!sheet.getCell(row, -1).visible())
                continue;
            let rowText = [];
            for(let j = 0; j < sel.colCount; j++){
                let col = sel.col + j;
                if(!sheet.getCell(-1, col).visible())
                    continue;
                if(colSettings && (colSettings[col]['data']['field'] === 'itemCharacterText' || colSettings[col]['data']['field'] === 'jobContentText'))
                    rowText.push(sheet.getText(row, col) ? `"${sheet.getText(row, col)}"` : '');
                else
                    rowText.push(sheet.getText(row, col) ? sheet.getText(row, col): '');
            }
            pasteText.push(rowText.join('\t'));
        }
        return pasteText.join('\n');
    },
    transferToTreeSetting:function(setting,treeSetting,treeCol){
        for(let h of setting.header){
            treeSetting.cols.push(getSettingCol(h))
        }
        for(let l of setting.view.lockColumns){
            if(_.isString(l)){//如果是dataCode 进行转换
                l = _.findIndex(setting.header,{dataCode:l})
            }
            if(treeSetting.cols[l]) treeSetting.cols[l].readOnly = true;
        }
        return treeSetting;
        function getSettingCol(header) {
            let aMap ={left:0,center:1,right:2};
            let hAlign = header.hAlign?aMap[header.hAlign]:0;
            let col = {
                "width":header.headerWidth?header.headerWidth:100,
                "head":{
                    "titleNames":Array.isArray(header.headerName)?header.headerName:[header.headerName],
                    "spanCols":header.spanCols?header.spanCols:[1],
                    "spanRows":header.spanRows?header.spanRows:[1],
                    "vAlign":[1],
                    "hAlign":[1],
                    "font":["Arial"]
                },
                "data": {
                    "field": header.dataCode,
                    "vAlign": 1,
                    "hAlign": hAlign,
                    "font": "Arial"
                }
            };
            if(header.showHint == true){
                col.showHint = true;
            }

            if(header.cellType){
                col.data.cellType = getCellType(header);
            }
            if(header.decimalField){//设置formatter
                let decimal = getDecimal(header.decimalField);
                col.formatter = getFormatter(decimal);
            }
            if(header.getText && treeCol){
                col.data.getText = treeCol.getEvent(header.getText);
            }



            /*col.readOnly = function (node) {
                if(node.data.ParentID == -1 || node.data.id == 'GJ'){//三材类别项不能编辑)
                    return true;
                }
                return false;
            };*/
            return col;
        }
        function getCellType(header) {
            return function () {
                if(header.cellType === "checkBox"){
                    return new GC.Spread.Sheets.CellTypes.CheckBox();
                }
                if(header.cellType === "comboBox"){
                    let dynamicCombo = sheetCommonObj.getDynamicCombo(true);
                    if(header.options){
                        dynamicCombo.itemHeight(header.options.length).items(header.options);
                        if(header.editorValueType==true){
                            dynamicCombo.editorValueType(GC.Spread.Sheets.CellTypes.EditorValueType.value);
                        }
                    }
                    return dynamicCombo
                }
            }
        }
    },
    //注册自定义回车键事件
    bindEnterKey: function (workBook, operation) {
        workBook.commandManager().register('myEnter', operation);
        workBook.commandManager().setShortcutKey(null, GC.Spread.Commands.Key.enter, false, false, false, false);
        workBook.commandManager().setShortcutKey('myEnter', GC.Spread.Commands.Key.enter, false, false, false, false);
    },
    //解决esc后触发了编辑结束的保存事件，显示与实际数据不同问题
    bindEscKey: function (workBook, sheets) {
        function isDef(v){
            return typeof v !== 'undefined' && v !== null;
        }
        workBook.commandManager().register('myEsc', function () {
            let activeSheet = workBook.getActiveSheet();
            let hasTheSheet = false;
            for(let sheetObj of sheets){
                let sheet = sheetObj.sheet;
                if(sheet === activeSheet){
                    hasTheSheet = true;
                    let editStarting = sheetObj.editStarting;
                    let editEnded = sheetObj.editEnded;
                    if(editStarting){
                        sheet.unbind(GC.Spread.Sheets.Events.EditStarting);
                    }
                    if(editEnded){
                        sheet.unbind(GC.Spread.Sheets.Events.EditEnded);
                    }
                    let row = sheet.getActiveRowIndex();
                    let col = sheet.getActiveColumnIndex();
                    let orgV = sheet.getValue(row, col);
                    let orgText = sheet.getText(row, col);
                    if(!isDef(orgV)){
                        orgV = '';
                    }
                    if(sheet.isEditing()){
                        sheet.endEdit();
                        sheet.setValue(row, col, orgV);
                    }
                    if(editStarting){
                        sheet.bind(GC.Spread.Sheets.Events.EditStarting, editStarting);
                    }
                    if(editEnded){
                        sheet.bind(GC.Spread.Sheets.Events.EditEnded, editEnded);
                    }
                }
            }
            //容错处理，以防没把所有工作簿的表格信息传入参数
            if(!hasTheSheet){
                if(activeSheet.isEditing()){
                    activeSheet.endEdit();
                }
            }
        });
        workBook.commandManager().setShortcutKey(null, GC.Spread.Commands.Key.esc, false, false, false, false);
        workBook.commandManager().setShortcutKey('myEsc', GC.Spread.Commands.Key.esc, false, false, false, false);
    },
    //生成列字段与列号映射
    initColMapping: function (obj, headers) {
        //colToField 列下标与列字段映射
        //fieldToCol 列字段与列下标映射
        let colMapping = {colToField: {}, fieldToCol: {}};
        for(let header of headers){
            colMapping['colToField'][headers.indexOf(header)] = header.dataCode;
            colMapping['fieldToCol'][header.dataCode] = headers.indexOf(header);
        }
        console.log(colMapping);
        obj.colMapping = colMapping
    },
    //设置默认样式
    spreadDefaultStyle: function (workBook) {
        let defaultStyle = new GC.Spread.Sheets.Style();
        defaultStyle.font = '14px Calibri';let sheetCount = workBook.getSheetCount();
        for(let i = 0; i < sheetCount; i++){
            let sheet = workBook.getSheet(i);
            sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
            sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.colHeader);
            sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.rowHeader);
        }
    },
    //动态根据工作簿宽度和各列宽度比例设置宽度
    setColumnWidthByRate: function (workBookWidth, workBook, headers){
        if(workBook){
            const sheet = workBook.getActiveSheet();
            sheet.suspendEvent();
            sheet.suspendPaint();
            for(let col = 0; col < headers.length; col++){
                if(headers[col]['rateWidth'] !== undefined && headers[col]['rateWidth'] !== null && headers[col]['rateWidth'] !== ''){
                    sheet.setColumnWidth(col, workBookWidth * headers[col]['rateWidth'], GC.Spread.Sheets.SheetArea.colHeader)
                }
                else {
                    if(headers[col]['headerWidth'] !== undefined && headers[col]['headerWidth'] !== null && headers[col]['headerWidth'] !== ''){
                        sheet.setColumnWidth(col, headers[col]['headerWidth'], GC.Spread.Sheets.SheetArea.colHeader)
                    }
                }
            }
            sheet.resumeEvent();
            sheet.resumePaint();
        }
    },
    drowRect:function (ctx, x, y, w, h,rectW,rectH,margin) {
        ctx.save();
        ctx.strokeStyle = "gray";
        ctx.translate(0.5, 0.5);
        ctx.beginPath();
        let rectX = x + margin;
        let rectY = y + Math.round(h / 2) - rectH / 2;
        ctx.moveTo(rectX, rectY);
        ctx.lineTo(rectX, rectY + rectH);
        ctx.lineTo(rectX + rectW, rectY + rectH);
        ctx.lineTo(rectX + rectW, rectY);
        ctx.lineTo(rectX, rectY);
        ctx.moveTo(rectX + rectW, y + Math.round(h / 2));
        ctx.lineTo(rectX + rectW + 5, y + Math.round(h / 2));
        ctx.stroke();
        ctx.restore();
    },
    drawLine : function (ctx, x1, y1, x2, y2, color) {
        let l_color = color?color:"gray";
        ctx.save();
        ctx.translate(0.5, 0.5);
        ctx.beginPath();
        ctx.moveTo(x1, y1);
        ctx.lineTo(x2, y2);
        ctx.strokeStyle = l_color;
        ctx.stroke();
        ctx.restore();
    },
    drowSymbol :function (ctx, x, y, w, h,rectW,rectH,margin,collapsed) {
        ctx.save();
        ctx.strokeStyle = "#000000";
        ctx.translate(0.5, 0.5);
        ctx.beginPath();
        ctx.moveTo(x + margin + 2, y + Math.round(h / 2));
        ctx.lineTo(x + margin + 8, y + Math.round(h / 2));
        let rectY = y + Math.round(h / 2) - rectH / 2;
        if (collapsed) {
            ctx.moveTo(x + margin + rectW / 2, rectY + 2);
            ctx.lineTo(x + margin + rectW / 2, rectY + 2 + 6);
        }
        ctx.stroke();
        ctx.restore();
    },
    drowTriangle:function(ctx,x,y){//画向下三角形
        ctx.save();
        ctx.fillStyle = "black";
        ctx.beginPath();
        ctx.moveTo(x, y);
        ctx.lineTo(x-3, y -6);
        ctx.lineTo(x+3, y -6);
        ctx.fill();
        ctx.restore();
    },
    drowSubItem: function (ctx, x, y, w, h, offset, hasNext,step) {
        let t_step = step?step:6;
        offset += t_step;
        ctx.save();
        ctx.strokeStyle = "gray";
        ctx.translate(0.5, 0.5);
        ctx.beginPath();
        ctx.moveTo(x + offset, y);
        ctx.lineTo(x + offset, y + Math.round(h / 2));
        offset += 9;
        ctx.lineTo(x + offset, y + Math.round(h / 2));
        if (hasNext) {
            ctx.moveTo(x + offset - 9, y + Math.round(h / 2));
            ctx.lineTo(x + offset - 9, y + h);
        }
        ctx.stroke();
        ctx.restore();
        return offset;
    },
    checkData:function(col,setting, value) {
        let result = true;
        let validator = setting.header[col].validator !== undefined ? setting.header[col].validator : null;
        if (validator === null) {
            return result;
        }
        switch (validator) {
            case 'number':
                let regular = /^\d+(\.\d+)?$/;
                result = regular.test(value);
                break;
            case 'boolean':
                let booleanValue = [true, false];
                result = booleanValue.indexOf(value) >= 0;
                break;
        }
        return result;
    },
    // 延迟一段时间刷新表格，因为有的弹窗里面有表格，马上刷新可能会造成，弹窗界面还未完全显示完就完成了表格刷新，导致表格显示不完整
    refreshWorkbookDelDefer(workbook, time) {
        if (workbook) {
            setTimeout(() => workbook.refresh(), time);
        }
    },
    setRowsAutoFit(sheet, rows, col, wordWrap) {
        rows.forEach(row => {
            sheet.getCell(row, col).wordWrap(wordWrap);
            sheet.autoFitRow(row);
        });
    }
}