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;
        sheet.suspendPaint();
        sheet.suspendEvent();

        sheet.setRowCount(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);
        };

        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);
        me.buildHeader(sheet, setting);
        if (rowCount > 0)
            sheet.setRowCount(rowCount)
        else
            sheet.setRowCount(1);
        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;
    },

    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.setColumnWidth(i, setting.header[i].headerWidth?setting.header[i].headerWidth:100);
            sheet.setColumnVisible(i,setting.header[i].visible === false ? false:true);
        }
    },
    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);
        }
    },
    showData: function(sheet, setting, data,distTypeTree) {
        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) {
                //var style = new GC.Spread.Sheets.Style();
                //style.formatter = setting.header[col].formatter;
                //sheet.setStyle(row,col,style,GC.Spread.Sheets.SheetArea.viewport);
                sheet.setFormatter(-1, col, setting.header[col].formatter, GC.Spread.Sheets.SheetArea.viewport);
            }
            if(setting.header[col].cellType === "checkBox"||setting.header[col].cellType === "button"){//clear and reset
                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);
            }
        }
        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);
        }
        this.lockCells(sheet,setting);
        sheet.resumeEvent();
        sheet.resumePaint();
        //me.shieldAllCells(sheet);
    },
    showRowData:function (sheet,setting,row,data,distTypeTree=null) {
        let ch = GC.Spread.Sheets.SheetArea.viewport;
        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];
            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);
            }
            if(setting.header[col].cellType === "selectButton"){
                this.setSelectButton(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){
            sheet.getRange(row, -1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).wordWrap(true);
            sheet.autoFitRow(row);
        }
    },
    //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") {
                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) {
                    itemObj[setting.header[propId].dataCode] = pastedInfo.pasteData.text.slice(preStrIdx);
                    rst.push(itemObj);
                }
            } else if (i == pastedInfo.pasteData.text.length - 1) {
                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++) {
                sheet.getRange(-1,setting.view.lockColumns[i], -1, 1, GC.Spread.Sheets.SheetArea.viewport).locked(true);
            }
        }
    },
    setCheckBoxCell(row,col,sheet,val){
        var c = new GC.Spread.Sheets.CellTypes.CheckBox();
        c.isThreeState(false);
        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);

    },
    setComboBox(row,col,sheet,options,editorValueType){
        //let combo = new GC.Spread.Sheets.CellTypes.ComboBox();
        let dynamicCombo = sheetCommonObj.getDynamicCombo(true);
        if(options){
            dynamicCombo.itemHeight(options.length).items(options);
            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);
        }
    },

    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,getSelectButton(header.headerWidth),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 GC.Spread.Sheets.CellTypes.Base.prototype.getHitInfo.apply(this, arguments);
            }
        };
        return new ComboCellForActiveCell();
    },
    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);
            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){
            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
                }
            }
        }
    }

}