ConstructionCost/public/web/sheet/sheet_data_helper.js

/**
 * Created by Mai on 2017/3/13.
 */
// setting示例
var __settingTemp = {
    cols: [
        {
            id: 'code',
            head: {
                titleNames: ['编号'],
                spanCols:[1],
                spanRows:[1],
                vAlign: [1],
                hAlign: [1],
                font: '4px Arial'
            },
            data:{
                field: 'code',
                vAlign: 1,
                hAlign: 0,
                font: '4px Arial'
            },
            width: 60,
            readOnly: false
        }
    ],
    headRows: 1,
    headRowHeight: [25],
    emptyRows: 3
};

var SheetDataHelper = {
    getObjPos: function (obj) {
        let target = obj;
        let pos = {x: obj.offsetLeft, y: obj.offsetTop};

        target = obj.offsetParent;
        while (target) {
            pos.x += target.offsetLeft;
            pos.y += target.offsetTop;
            target = target.offsetParent;
        }
        return pos;
    },
    initSetting: function (obj, setting) {
        setting.pos = this.getObjPos(obj);
    },
    createNewSpread: function (obj) {
        var spread = new GC.Spread.Sheets.Workbook(obj, {sheetCount: 1});
        spread.options.tabStripVisible = false;
        spread.options.scrollbarMaxAlign = true;
        spread.options.cutCopyIndicatorVisible = false;
        spread.options.allowCopyPasteExcelStyle = false;
        spread.options.allowUserDragDrop = false;
        spread.getActiveSheet().setRowCount(3);
        return spread;
    },
    massOperationSheet: function (sheet, Operation) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        Operation();
        sheet.resumeEvent();
        sheet.resumePaint();
    },
    loadSheetHeader: function (setting, sheet) {
        this.massOperationSheet(sheet, function () {
            if (setting.frozenCols) {
                sheet.frozenColumnCount(setting.frozenCols);
            }
            sheet.setColumnCount(setting.cols.length);
            sheet.setRowCount(setting.headRows, GC.Spread.Sheets.SheetArea.colHeader);
            if (setting.headRowHeight) {
                setting.headRowHeight.forEach(function (rowHeight, index) {
                    sheet.setRowHeight(index, rowHeight, GC.Spread.Sheets.SheetArea.colHeader);
                });
            }
            if (setting.cols) {
                sheet.setColumnCount(setting.cols.length);
                setting.cols.forEach(function (col, index) {
                    var i, iRow = 0, cell;
                    for (i = 0; i < col.head.spanCols.length; i++) {
                        if (col.head.spanCols[i] !== 0) {
                            cell = sheet.getCell(iRow, index, GC.Spread.Sheets.SheetArea.colHeader);
                            cell.value(col.head.titleNames[i]).font(col.head.font).hAlign(col.head.hAlign[i]).vAlign(col.head.vAlign[i]).wordWrap(true);
                        }
                        if (col.head.spanCols[i] > 1 || col.head.spanRows[i] > 1) {
                            sheet.addSpan(iRow, index, col.head.spanRows[i], col.head.spanCols[i], GC.Spread.Sheets.SheetArea.colHeader);
                        }
                        iRow += col.head.spanRows[i];
                    };
                    sheet.setColumnWidth(index, col.width);
                    sheet.setColumnVisible(index, col.visible);
                });
            }
        });
    },
    protectdSheet: function (sheet) {
        var option = {
            allowSelectLockedCells: true,
            allowSelectUnlockedCells: true,
            allowResizeRows: true,
            allowResizeColumns: true
        };

        sheet.options.protectionOptions = option;
        sheet.options.isProtected = true;        
    },
    getSheetCellStyle: function (setting) {
        var style = new GC.Spread.Sheets.Style();
        style.locked = setting.readOnly;
        style.name = setting.id;
        style.font = setting.data.font;
        style.hAlign = setting.data.hAlign;
        style.vAlign = setting.data.vAlign;
        style.wordWrap = setting.data.wordWrap;
        return style;
    },
    loadSheetData: function (setting, sheet, datas) {
        SheetDataHelper.protectdSheet(sheet);

        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 tag = hitinfo.sheet.getTag(hitinfo.row, hitinfo.col);
            if(tag !== undefined && tag){
                text = tag;
            }
            if (setting.pos && text && text !== '') {
                if (!this._toolTipElement) {
                    let div = $('#autoTip')[0];
                    if (!div) {
                        div = document.createElement("div");
                        $(div).css("position", "absolute")
                            .css("border", "1px #C0C0C0 solid")
                            .css("box-shadow", "1px 2px 5px rgba(0,0,0,0.4)")
                            .css("font", "9pt Arial")
                            .css("background", "white")
                            .css("padding", 5)
                            .attr("id", 'autoTip');
                        $(div).hide();
                        document.body.insertBefore(div, null);
                    }
                    this._toolTipElement = div;
                    $(this._toolTipElement).html(text).css("top", setting.pos.y + hitinfo.y + 15).css("left", setting.pos.x + hitinfo.x + 15);
                    $(this._toolTipElement).show("fast");
                }
            }
        };
        TipCellType.prototype.processMouseLeave = function (hininfo) {
            if (this._toolTipElement) {
                $(this._toolTipElement).hide();
                this._toolTipElement = null;
            }
        }

        sheet.suspendPaint();
        sheet.suspendEvent();

        sheet.clear(0, 0, sheet.getRowCount(), sheet.getColumnCount(), GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
        if (setting.defaultRowHeight) {
            sheet.defaults.rowHeight = setting.defaultRowHeight;
        }
        sheet.setRowCount(datas.length + setting.emptyRows, GC.Spread.Sheets.SheetArea.viewport);
        setting.cols.forEach(function (colSetting, iCol) {       
            sheet.setStyle(-1, iCol, SheetDataHelper.getSheetCellStyle(colSetting));
            if (colSetting.showHint) {
                sheet.getRange(-1, iCol, -1, 1).cellType(new TipCellType());
            }
            datas.forEach(function (data, iRow) {
                var cell = sheet.getCell(iRow, iCol, GC.Spread.Sheets.SheetArea.viewport);
                var getFieldText2 = function () {
                    var fields = colSetting.data.field.split('.'), iField, value = data;
                    for (iField = 0; iField < fields.length; iField++) {
                        if (value[fields[iField]]) {
                            value = value[fields[iField]];
                        } else {
                            return '';
                        }
                    }
                    return value;
                };
                cell.value(data[colSetting.data.field]);
                if (colSetting.data.getText) {
                    cell.value(colSetting.data.getText(data));
                } else {
                    cell.value(getFieldText2());
                }
                if (colSetting.readOnly) {
                    if (Object.prototype.toString.apply(colSetting.readOnly) === "[object Function]") {
                        cell.locked(colSetting.readOnly(node));
                    } else {
                        cell.locked(true);
                    }
                } else {
                    cell.locked(false);
                }
            });
        });

        sheet.resumeEvent();
        sheet.resumePaint();
    },
    refreshColumnVisible: function (setting, sheet) {
        setting.cols.forEach(function (col, index) {
            sheet.setColumnVisible(index, col.visible);
        });
    },
    bindSheetData: function (setting, sheet, datas) {     
        var getBindColInfo = function (setting) {
            var colInfo = {};
            colInfo.name = setting.data.field;
            colInfo.size = setting.width;
            return colInfo;
        }

        SheetDataHelper.protectdSheet(sheet); 
        sheet.autoGenerateColumns = false;
        sheet.setDataSource(datas);
        setting.cols.forEach(function (colSetting, iCol) {
            sheet.setStyle(-1, iCol, SheetDataHelper.getSheetCellStyle(colSetting));
            sheet.bindColumn(iCol, getBindColInfo(colSetting));
        });
    },
    getHitTest: function (obj, e, sheet) {
        var offset = obj.offset(),
            x = e.pageX - offset.left,
            y = e.pageY - offset.top;
        return sheet.hitTest(x, y);
    },
    getTargetSelection: function (sheet, target) {
        if (target.hitTestType === GC.Spread.Sheets.SheetArea.colHeader) {
            return sheet.getRange(-1, target.col, sheet.getRowCount(), 1);
        } else if (target.hitTestType === GC.Spread.Sheets.SheetArea.rowHeader) {
            return sheet.getRange(target.row, -1, 1, sheet.getColumnCount());
        } else if (target.hitTestType === GC.Spread.Sheets.SheetArea.viewport) {
            return sheet.getRange(target.row, target.col, 1, 1);
        } else if (target.hitTestType === GC.Spread.Sheets.SheetArea.corner) {
            return sheet.getRange(-1, -1, sheet.getRowCount(), sheet.getColumnCount());
        };
    },
    getCellInSelections: function (selections, row, col) {
        var count = selections.length, range;
        for (var i = 0; i < count; i++) {
            range = selections[i];
            if (range.contains(row, col)) {
                return range;
            }
        }
        return null;
    },
    checkTargetInSelection: function (selections, range) {
        var count = selections.length, sel;
        for (var i = 0; i < count; i++) {
            sel = selections[i];
            if (sel.containsRange(range)) {
                return true;
            }
        }
        return false;
    },
    /**
     * @param obj: Dom Element of create spread(first parameter of jquery-contextmenu.build)
     * @param e: secord parameter of jquery-contextmenu.build
     * @param spread
     * @returns {*}
     */
    safeRightClickSelection: function (obj, e, spread) {
        var sheet = spread.getActiveSheet();
        var selections = sheet.getSelections(), target = this.getHitTest(obj, e, sheet), range = this.getTargetSelection(sheet, target);
        if (!this.checkTargetInSelection(selections, range)) {
            sheet.setSelection(range.row, range.col, range.rowCount, range.colCount);
        }
        return target;
    },
    /**
     * 在sheet中使用delete键，触发EndEdited事件
     * @param sheet
     */
    deleteBind: function (sheet, fun) {
        sheet.addKeyMap(46, false, false, false, false, function () {
            let selections = sheet.getSelections();
            
        });
    }
};