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 = {
    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;
    },
    loadSheetHeader: function (setting, sheet) {
        sheet.setColumnCount(setting.cols.length);
        sheet.setRowCount(setting.headRows, GC.Spread.Sheets.SheetArea.colHeader);
        setting.headRowHeight.forEach(function (rowHeight, index) {
            sheet.setRowHeight(index, rowHeight, GC.Spread.Sheets.SheetArea.colHeader);
        })
        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]);
                }
                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);
        });
    },
    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);

        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));
            datas.forEach(function (data, iRow) {
                var cell = sheet.getCell(iRow, iCol, GC.Spread.Sheets.SheetArea.viewport);
                cell.value(data[colSetting.data.field]);
            });
        });

        sheet.resumeEvent();
        sheet.resumePaint();
    },
    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;
    }
};