ConstructionOperation/public/web/sheet/sheet_common.js

/**
 * Created by Tony on 2017/4/28.
 */
var sheetCommonObj = {
    // createSpread、initSheet 在一个Spread多个Sheet分别调用时的情况下使用。
    // buildSheet 在一个Spread、一个Sheet的情况下使用。
    createSpread: function (container, SheetCount) {
        var me = this;
        var spreadBook = new GC.Spread.Sheets.Workbook(container, { sheetCount: SheetCount });
        spreadBook.options.allowCopyPasteExcelStyle = false;
        spreadBook.options.allowExtendPasteRange = true;
        spreadBook.options.tabStripVisible = false;
        //spreadBook.options.showHorizontalScrollbar = false;
        spreadBook.options.allowUserDragDrop = false;
        spreadBook.options.allowUserDragFill = false;
        spreadBook.options.scrollbarMaxAlign = true;
        spreadBook.options.allowContextMenu = false;
        spreadBook.options.allowUndo = false;
        spreadBook.options.cutCopyIndicatorVisible = 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);
        sheet.options.colHeaderAutoTextIndex = 1;
        sheet.options.colHeaderAutoText = spreadNS.HeaderAutoText.numbers;
        sheet.options.protectionOptions = {
            allowResizeRows: true,
            allowResizeColumns: true
        };
        sheet.showRowOutline(false);
        sheet.options.allowCellOverflow = false;
        me.buildHeader(sheet, setting);
        if (rowCount > 0) sheet.setRowCount(rowCount);
        sheet.resumeEvent();
        sheet.resumePaint();
    },

    buildSheet: function (container, setting, rowCount, spreadOptions) {
        var me = this;
        var spreadBook = new GC.Spread.Sheets.Workbook(container, { sheetCount: 1 });
        spreadBook.options.tabStripVisible = false;
        //spreadBook.options.showHorizontalScrollbar = false;
        spreadBook.options.scrollbarMaxAlign = true;
        spreadBook.options.allowCopyPasteExcelStyle = false;
        spreadBook.options.cutCopyIndicatorVisible = false;
        spreadBook.options.allowExtendPasteRange = true;
        spreadBook.options.allowUserDragDrop = false;
        spreadBook.options.allowUserDragFill = false;
        spreadBook.options.allowUndo = false;
        spreadBook.options.allowContextMenu = false;
        if (spreadOptions) {
            Object.assign(spreadBook.options, spreadOptions)
        }
        var spreadNS = GC.Spread.Sheets;
        var sheet = spreadBook.getSheet(0);
        sheet.suspendPaint();
        sheet.suspendEvent();
        //Set rowHeader count and columnHeader count.
        sheet.setRowCount(1, spreadNS.SheetArea.colHeader);
        sheet.setColumnCount(setting.header.length, spreadNS.SheetArea.viewport);
        sheet.options.colHeaderAutoTextIndex = 1;
        sheet.options.colHeaderAutoText = spreadNS.HeaderAutoText.numbers;
        sheet.options.clipBoardOptions = GC.Spread.Sheets.ClipboardPasteOptions.values;
        sheet.options.protectionOptions = {
            allowResizeRows: true,
            allowResizeColumns: true
        };
        sheet.showRowOutline(false);
        //setup column header
        me.buildHeader(sheet, setting);
        //setup cells
        if (rowCount > 0) sheet.setRowCount(rowCount);
        sheet.resumeEvent();
        sheet.resumePaint();
        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);
        }
        if (setting.headerHeight) sheet.setRowHeight(0, setting.headerHeight, GC.Spread.Sheets.SheetArea.colHeader);
    },
    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();
    },
    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();
    },
    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) {
                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);
            }
            if (setting.header[col].visible === false) {
                sheet.setColumnVisible(col, false);
            }
            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.getStyle && setting.getStyle(data[row])) {
                sheet.setStyle(row, -1, setting.getStyle(data[row]));
            }
        }
        this.lockCells(sheet, setting);
        sheet.resumeEvent();
        sheet.resumePaint();
        //me.shieldAllCells(sheet);
    },
    getCheckBox(threeState = false) {
        var c = new GC.Spread.Sheets.CellTypes.CheckBox();
        c.isThreeState(threeState);
        return c
    },
    // 无法勾选的复选框
    getReadOnlyCheckBox(threeState = false) {
        function ReadOnlyCheckBox() { }
        ReadOnlyCheckBox.prototype = this.getCheckBox(threeState);
        ReadOnlyCheckBox.prototype.processMouseUp = function () {
            return;
        };
        return new ReadOnlyCheckBox();
    },
    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 {
                    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].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);
        }
    },
    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);
        }
    },
    analyzePasteData: function (setting, pastedInfo) {
        var rst = [], propId = pastedInfo.cellRange.col, preStrIdx = 0, itemObj = {};
        for (var i = 0; i < pastedInfo.pasteData.text.length; i++) {
            if (pastedInfo.pasteData.text[i] === "\n") {
                propId = pastedInfo.cellRange.col;
                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;
    },
    unLockAllCells: function (sheet) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        let defaultStyle = new GC.Spread.Sheets.Style();
        defaultStyle.locked = false;
        sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
        sheet.setStyle(-1, 0, defaultStyle);
        sheet.options.isProtected = false;
        sheet.resumePaint();
        sheet.resumeEvent();
    },
    lockAllCells: function (sheet) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        let defaultStyle = new GC.Spread.Sheets.Style();
        defaultStyle.locked = true;
        sheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
        for (let i = 0; i < sheet.getRowCount(); i++) {
            sheet.setStyle(i, 0, defaultStyle);
        }
        sheet.options.isProtected = true;
        sheet.resumePaint();
        sheet.resumeEvent();
    },
    lockCells: function (sheet, setting) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        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);
            }
        }
        sheet.resumePaint();
        sheet.resumeEvent();
    },
    setLockCol: function (sheet, col, isLocked) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        for (let row = 0, len = sheet.getRowCount(); row < len; row++) {
            sheet.getCell(row, col).locked(isLocked);
        }
        sheet.resumePaint();
        sheet.resumeEvent();
    },
    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
    //动态下拉框
    getDynamicCombo: function () {
        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()) {
                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()) {
                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();
        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);
        for (let i = 0, len = rowCount; i < len; i++) {
            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();
    },
    //注册自定义回车键事件
    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);
                    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
    },
    //动态根据工作簿宽度和各列宽度比例设置宽度
    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();
        }
    },
    renderSheetFunc: function (sheet, func) {
        sheet.suspendEvent();
        sheet.suspendPaint();
        if (func) {
            func();
        }
        sheet.resumeEvent();
        sheet.resumePaint();
    }
}