Calculation/app/public/js/spreadjs_rela/spreadjs_zh.js

/**
 * Spreadjs 通用方法集
 *
 * @author Mai
 * @date 2018/02/06
 * @version
 */

const SpreadJsObj = {
    /**
     * 创建Spread(默认1张表，3行数据)
     * @param obj 用于创建spreadjs的Dom元素
     * @returns {GC.Spread.Sheets.Workbook}
     */
    createNewSpread: function (obj) {
        const 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;
    },
    /**
     * 保护sheet(需设置保护后, 单元格的locked等属性方可生效)
     * @param {GC.Spread.Sheets.Worksheet} sheet
     */
    protectedSheet: function (sheet) {
        const option = {
            allowSelectLockedCells: true,
            allowSelectUnlockedCells: true,
            allowResizeRows: true,
            allowResizeColumns: true
        };

        sheet.options.protectionOptions = option;
        sheet.options.isProtected = true;
    },
    /**
     * sheet批量操作优化(sheet操作大批量数据时, 屏蔽数据刷新, 可优化大量时间)
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @param {function} operation
     */
    massOperationSheet: function (sheet, operation) {
        sheet.suspendPaint();
        sheet.suspendEvent();
        operation();
        sheet.resumeEvent();
        sheet.resumePaint();
    },
    /**
     * 获取Obj左顶点位置(部分功能需通过spreadjs左顶点位置计算)
     * @param obj
     * @returns {{x: number, y: number}}
     */
    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;
    },
    /**
     * 以下四个方法来自Spread示例, 参见官网或文档
     */
    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) {
        const count = selections.length;
        let 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;
    },
    /**
     * 获取 spread 在鼠标右键时, spread的选中区域
     * viewport, 选中鼠标点击单元格X, X不在原选中区域内, 则选中X
     * colHeader, 选中整列
     * rowHeader, 选中整行
     * corner, 全选
     * 该方法返回不符合需求时，可通过getHitTest/getTargetSelection/getCellInSelections/checkTargetInSelection来确定鼠标右键点击时，spread当前Sheet应选中的单元格
     * @param obj: 创建Spread的Dom元素(jquery-contextmenu.build方法的第一个变量可读取)
     * @param e: jquery-contextmenu.build方法的第二个变量
     * @param {GC.Spread.Sheets.Workbook} spread
     * @returns {*}
     */
    safeRightClickSelection: function (obj, e, spread) {
        const sheet = spread.getActiveSheet();
        const 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 {GC.Spreads.Sheets.Workbook} spread
     * @param {function} fun
     */
    addDeleteBind: function (spread, fun) {
        spread.commandManager().register('deleteEvent', function () {
            fun(spread.getActiveSheet());
        });
        spread.commandManager().setShortcutKey('null', GC.Spread.Commands.Key.del, false, false, false, false);
        spread.commandManager().setShortcutKey('deleteEvent', GC.Spread.Commands.Key.del, false, false, false, false);
    },
    /**
     * 根据sheet.zh_setting初始化sheet表头
     * @param {GC.Spread.Sheets.Worksheet} sheet
     */
    initSheetHeader: function (sheet) {
        if (!sheet.zh_setting) { return; }

        this.massOperationSheet(sheet, function () {
            sheet.setColumnCount(sheet.zh_setting.cols.length);
            for (const i in sheet.zh_setting.cols) {
                const col = sheet.zh_setting.cols[i];
                const cell = sheet.getCell(0, i, GC.Spread.Sheets.SheetArea.colHeader);
                cell.text(col.title);
                sheet.setColumnWidth(i, col.width);
            }
            sheet.rowOutlines.direction(GC.Spread.Sheets.Outlines.OutlineDirection.backward);
            sheet.showRowOutline(false);
            if (sheet.zh_setting.defaultRowHeight) {
                sheet.defaults.rowHeight = sheet.zh_setting.defaultRowHeight;
            }
        });
    },
    /**
     * 初始化sheet, 设置sheet.zh_setting, 并初始化表头
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @param setting
     */
    initSheet: function (sheet, setting) {
        sheet.zh_setting = setting;
        this.initSheetHeader(sheet);
        sheet.setRowCount(sheet.zh_setting.emptyRows);
        sheet.extendCellType = {};
    },
    /**
     * 整个sheet重新加载数据
     * @param sheet
     */
    reLoadSheetData: function (sheet) {
        this.protectedSheet(sheet);
        this.massOperationSheet(sheet, function () {
            // 设置总行数
            const sortData = sheet.zh_dataType === 'tree' ? sheet.zh_tree.nodes : sheet.zh_data;
            const totalRow = sortData.length + sheet.zh_setting.emptyRows;
            sheet.setRowCount(totalRow, GC.Spread.Sheets.SheetArea.viewport);
            // 控制空白行
            const emptyRows = sheet.getRange(sortData.length, -1, sheet.zh_setting.emptyRows, -1);
            emptyRows.locked(sheet.zh_dataType === 'tree');
            // 单元格写入数据
            sortData.forEach(function (data, i) {
                sheet.zh_setting.cols.forEach(function (col, j) {
                    const cell = sheet.getCell(i, j);
                    if (col.field !== '' && data[col.field]) {
                        cell.value(data[col.field]).locked(col.readOnly || false);
                    } else {
                        cell.locked(col.readOnly || false);
                    }
                });
            });
            // 设置列单元格格式
            sheet.zh_setting.cols.forEach(function (col, j) {
                if (!col.cellType) { return; }

                if (col.cellType === 'tree') {
                    if (!sheet.extendCellType.tree) {
                        sheet.extendCellType.tree = SpreadJsExtendCellType.getTreeNodeCellType();
                    }
                    sheet.getRange(-1, j, -1, 1).cellType(sheet.extendCellType.tree);
                } else if (col.cellType === 'tip') {
                    if (!sheet.extendCellType.tip) {
                        sheet.extendCellType.tip = SpreadJsExtendCellType.getTipCellType();
                    }
                    sheet.getRange(-1, j, -1, 1).cellType(sheet.extendCellType.tip);
                }
            });
        });
    },
    /**
     * 重新加载部分数据行
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @param {Number} row
     * @param {Number} count
     */
    reLoadRowData: function (sheet, row, count) {
        this.massOperationSheet(sheet, function () {
            const sortData = sheet.zh_dataType === 'tree' ? sheet.zh_tree.nodes : sheet.zh_data;
            // 清空原单元格数据
            sheet.clear(row, -1, count, -1, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
            // 单元格重新写入数据
            for (let i = row; i < row + count; i++) {
                const data = sortData[i];
                if (!data) { continue; }
                sheet.zh_setting.cols.forEach(function (col, j) {
                    const cell = sheet.getCell(i, j);
                    if (col.field !== '' && data[col.field]) {
                        cell.value(data[col.field]).locked(col.readOnly || false);
                    } else {
                        cell.locked(col.readOnly || false);
                    }
                });
            };
            // 设置列单元格格式
            sheet.zh_setting.cols.forEach(function (col, j) {
                if (!col.cellType) { return; }

                if (col.cellType === 'tree') {
                    if (!sheet.extendCellType.tree) {
                        sheet.extendCellType.tree = SpreadJsExtendCellType.getTreeNodeCellType();
                    }
                    sheet.getRange(row, j, count, 1).cellType(sheet.extendCellType.tree);
                } else if (col.cellType === 'tip') {
                    if (!sheet.extendCellType.tip) {
                        sheet.extendCellType.tip = SpreadJsExtendCellType.getTipCellType();
                    }
                    sheet.getRange(row, j, count, 1).cellType(sheet.extendCellType.tip);
                }
            });
        });
    },
    /**
     * 重新加载部分行数据
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @param {Array} rows
     */
    reLoadRowsData: function (sheet, rows) {
        this.massOperationSheet(sheet, function () {
            const sortData = sheet.zh_dataType === 'tree' ? sheet.zh_tree.nodes : sheet.zh_data;
            for (const row of rows) {
                // 清空原单元格数据
                sheet.clear(row, -1, 1, -1, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
                const data = sortData[row];
                // 单元格重新写入数据
                sheet.zh_setting.cols.forEach(function (col, j) {
                    // 设置值
                    const cell = sheet.getCell(row, j);
                    if (col.field !== '' && data[col.field]) {
                        cell.value(data[col.field]).locked(col.readOnly || false);
                    } else {
                        cell.locked(col.readOnly || false);
                    }
                    // 设置单元格格式
                    if (col.cellType) {
                        if (col.cellType === 'tree') {
                            if (!sheet.extendCellType.tree) {
                                sheet.extendCellType.tree = SpreadJsExtendCellType.getTreeNodeCellType();
                            }
                            sheet.getRange(row, j, 1, 1).cellType(sheet.extendCellType.tree);
                        } else if (col.cellType === 'tip') {
                            if (!sheet.extendCellType.tip) {
                                sheet.extendCellType.tip = SpreadJsExtendCellType.getTipCellType();
                            }
                            sheet.getRange(row, j, 1, 1).cellType(sheet.extendCellType.tip);
                        }
                    }
                });
            };
        });
    },
    /**
     * 根据data加载sheet数据，合并了一般数据和树结构数据的加载
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @param {String} dataType - 1.'zh_data' 2.'zh_tree'
     * @param {Array|PathTree} data - 对dataType对应
     */
    loadSheetData: function (sheet, dataType, data){
        sheet.zh_dataType = dataType;
        if (dataType === 'tree') {
            sheet.zh_tree = data;
        } else {
            sheet.zh_data = data;
        }
        this.reLoadSheetData(sheet);
    },
    /**
     * 获取复制数据HTML格式（过滤不可见单元格）
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @returns {string}
     */
    getFilterCopyHTML: function (sheet) {
        const sel = sheet.getSelections()[0];
        const html = [];
        html.push('<table>');
        for (let i = sel.row, iLen = sel.row + sel.rowCount; i < iLen; i++) {
            // 跳过隐藏行
            if (!sheet.getCell(i, -1).visible()) { continue; }

            const rowHtml = [];
            rowHtml.push('<tr>');
            for (let j = sel.col, jLen = sel.col + sel.colCount; j < jLen; j++) {
                const data = sheet.getText(i, j);
                rowHtml.push('<td>' + data + '</td>');
            }
            rowHtml.push('</tr>');
            html.push(rowHtml.join(''));
        }
        html.push('</table>');
        return html.join('');
    },
    /**
     * 获取复制数据Text格式（过滤不可见单元格）
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @returns {string}
     */
    getFilterCopyText: function (sheet) {
        const copyData = [];
        const sel = sheet.getSelections()[0];
        for(let i = sel.row, iLen = sel.row + sel.rowCount; i < iLen; i++) {
            // 跳过隐藏行
            if (!sheet.getCell(i, -1).visible()) { continue; }

            const rowText = [];
            for (let j = sel.col, jLen = sel.col + sel.colCount; j < jLen; j++) {
                const data = sheet.getText(i, j);
                rowText.push(data);
            }
            copyData.push(rowText.join('\t'));
        }
        return copyData.join('\n');
    }
};