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

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

// 定义画点线的方法
const proto = window.CanvasRenderingContext2D && CanvasRenderingContext2D.prototype;
proto.dottedLine = function (x1, y1, x2, y2, interval = 4) {
    const isHorizontal = x1 == x2 ? false : true;
    const dotLen = 1;
    let len = isHorizontal ? x2 - x1 : y2 - y1;
    this.moveTo(x1, y1);
    let progress = 0;
    while (len > progress) {
        if (progress > len) { progress = len; }
        if (isHorizontal) {
            this.moveTo(x1 + progress, y1);
            this.lineTo(x1 + progress + dotLen, y1);
        } else {
            this.moveTo(x1, y1 + progress);
            this.lineTo(x1, y1 + progress + dotLen);
        }
        progress += interval;
    }
};

// 简写Spread常量
const spreadNS = GC.Spread.Sheets;
// 授权码
spreadNS.LicenseKey = "559432293813965#A0y3iTOzEDOzkjMyMDN9UTNiojIklkI1pjIEJCLi4TPB9mM5AFNTd4cvZ7SaJUVy3CWKtWYXx4VVhjMpp7dYNGdx2ia9sEVlZGOTh7NRlTUwkWR9wEV4gmbjBDZ4ElR8N7cGdHVvEWVBtCOwIGW0ZmeYVWVr3mI0IyUiwCMzETN8kzNzYTM0IicfJye&Qf35VfiEzRwEkI0IyQiwiIwEjL6ByUKBCZhVmcwNlI0IiTis7W0ICZyBlIsIyNyMzM5ADI5ADNwcTMwIjI0ICdyNkIsIibj9SbvNmL4N7bjRnch56ciojIz5GRiwiI8+Y9sWY9QmZ0Jyp96uL9v6L0wap9biY9qiq95q197Wr9g+89iojIh94Wiqi";

// SpreadJs常用方法
const SpreadJsObj = {
    initSpreadSettingEvents: function (setting, events) {
        const getEvent = function (eventName) {
            const names = eventName.split('.');
            let event = events;
            for (let name of names) {
                if (event[name]) {
                    event = event[name];
                } else {
                    return null;
                }
            }
            if (event && Object.prototype.toString.apply(event) !== "[object Function]") {
                return null;
            } else {
                return event;
            }
        };

        for (const col of setting.cols) {
            if (col.readOnly && Object.prototype.toString.apply(col.readOnly) === "[object String]") {
                col.readOnly = getEvent(col.readOnly);
            }
            if (col.getValue && Object.prototype.toString.apply(col.getValue) === "[object String]") {
                col.getValue = getEvent(col.getValue);
            }
        }
    },

    DataType: {
        Data: 'data',
        Tree: 'tree',
    },
    /**
     * 创建Spread(默认1张表，3行数据)
     * @param obj 用于创建spreadjs的Dom元素
     * @returns {GC.Spread.Sheets.Workbook}
     */
    createNewSpread: function (obj) {
        const spread = new spreadNS.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.options.allowUserDragFill = false;
        spread.options.showDragFillSmartTag = false;
        spread.options.allowUserEditFormula = false;
        spread.options.allowExtendPasteRange = true;
        spread.options.copyPasteHeaderOptions = spreadNS.CopyPasteHeaderOptions.noHeaders;
        spread.getActiveSheet().options.clipBoardOptions = spreadNS.ClipboardPasteOptions.values;//设置粘贴时只粘贴值
        spread.getActiveSheet().setRowCount(3);
        return spread;
    },
    addCutEvents: function (spread, fun) {
        const cut = spreadNS.Commands.cut.execute;
        spreadNS.Commands.cut.execute = function (context, options, isUndo) {
            const self = this, sheet = context.getActiveSheet();
            const sels = sheet.getSelections();
            const sel = sels ? sels[0] : null;
            if (sel) {
                return fun(sheet,  sel, function () {
                    cut(context, options, isUndo);
                });
            }
        }
    },
    /**
     * 保护sheet(需设置保护后, 单元格的locked等属性方可生效)
     * @param {GC.Spread.Sheets.Worksheet} sheet
     */
    protectedSheet: function (sheet) {
        const option = {
            allowSelectLockedCells: true,
            allowSelectUnlockedCells: true,
            allowResizeRows: true,
            allowResizeColumns: true
        };

        const defaultStyle = sheet.getDefaultStyle();
        defaultStyle.locked = false;
        sheet.setDefaultStyle(defaultStyle);

        sheet.options.protectionOptions = option;
        sheet.options.isProtected = true;
        sheet.options.allowCellOverflow = false;
    },
    /**
     * sheet批量操作优化(sheet操作大批量数据时, 屏蔽数据刷新, 可优化大量时间)
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @param {function} operation
     */
    beginMassOperation: function (sheet) {
        sheet.suspendPaint();
        sheet.suspendEvent();
    },
    endMassOperation: function (sheet) {
        sheet.resumeEvent();
        sheet.resumePaint();
    },
    massOperationSheet: function (sheet, operation) {
        this.beginMassOperation(sheet);
        operation();
        this.endMassOperation(sheet);
    },
    /**
     * 获取Obj左顶点位置(部分功能需通过spreadjs左顶点位置计算)
     * @param obj
     * @returns {{x: number, y: number}}
     */
    getObjPos: function (obj) {
        if (!obj) return null;
        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 === spreadNS.SheetArea.colHeader) {
            return sheet.getRange(-1, target.col, sheet.getRowCount(), 1);
        } else if (target.hitTestType === spreadNS.SheetArea.rowHeader) {
            return sheet.getRange(target.row, -1, 1, sheet.getColumnCount());
        } else if (target.hitTestType === spreadNS.SheetArea.viewport) {
            return sheet.getRange(target.row, target.col, 1, 1);
        } else if (target.hitTestType === spreadNS.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;
    },
    /**
     * 禁用右键菜单
     * @param selector
     * @param spread
     */
    forbiddenSpreadContextMenu: function (selector, spread) {
        $.contextMenu({
            selector: selector,
            build: function ($trigger, e) {
                const target = SpreadJsObj.safeRightClickSelection($trigger, e, spread);
                return target.hitTestType === spreadNS.SheetArea.viewport || target.hitTestType === spreadNS.SheetArea.rowHeader;
            }
        });
    },
    /**
     * 获取写入sheet的数据序列
     * data：sheet.zh_data, tree: sheet.zh_tree.nodes
     * @param sheet
     * @returns {*}
     */
    getSortData: function (sheet) {
        if (sheet.zh_dataType) {
            if (sheet.zh_dataType === this.DataType.Data) {
                return sheet.zh_data;
            } else if (sheet.zh_dataType === this.DataType.Tree) {
                return sheet.zh_tree.nodes;
            } else {
                return null;
            }
        } else {
            return null;
        }
    },
    /**
     * 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);
    },
    _initSheetDeafult: function (sheet) {
        if (sheet.zh_setting.headerFont) {
            const vStyle = new spreadNS.Style();
            vStyle.font = sheet.zh_setting.headerFont;
            vStyle.locked = false;
            sheet.setDefaultStyle(vStyle, spreadNS.SheetArea.colHeader);
        }
        if (sheet.zh_setting.font) {
            const vStyle = new spreadNS.Style();
            vStyle.font = sheet.zh_setting.font;
            vStyle.locked = false;
            sheet.setDefaultStyle(vStyle, spreadNS.SheetArea.viewport);
        }
    },
    /**
     * 根据sheet.zh_setting初始化sheet表头
     * @param {GC.Spread.Sheets.Worksheet} sheet
     */
    _initSheetHeader: function (sheet) {
        if (!sheet.zh_setting) { return; }

        sheet.setColumnCount(sheet.zh_setting.cols.length);
        sheet.setRowCount(sheet.zh_setting.headRows, spreadNS.SheetArea.colHeader);
        for (let iRow = 0; iRow < sheet.zh_setting.headRowHeight.length; iRow ++) {
            sheet.setRowHeight(iRow, sheet.zh_setting.headRowHeight[iRow], spreadNS.SheetArea.colHeader);
        }
        for (let iCol = 0; iCol < sheet.zh_setting.cols.length; iCol++) {
            const col = sheet.zh_setting.cols[iCol];
            const title = col.title.split('|');
            const colSpan = col.colSpan ? col.colSpan.split('|'): ['1'], rowSpan = col.rowSpan ? col.rowSpan.split('|'): ['1'];
            for (let i = 0; i < title.length; i++) {
                const cell = sheet.getCell(i, iCol, spreadNS.SheetArea.colHeader);
                cell.text(title[i]).wordWrap(true);
                if ((colSpan[i] !== '' && colSpan[i] !== '1') || (rowSpan[i] !== '' && rowSpan[i] !== '1')) {
                    sheet.addSpan(i, iCol, parseInt(rowSpan[i]), parseInt(colSpan[i]), spreadNS.SheetArea.colHeader);
                }
            }
            sheet.setColumnWidth(iCol, col.width);
            if (col.visible !== undefined && col.visible !== null) {
                sheet.setColumnVisible(iCol, col.visible);
            }
        }
        sheet.rowOutlines.direction(spreadNS.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) {
        this.beginMassOperation(sheet);
        setting.pos = sheet.getParent().pos;
        sheet.zh_setting = setting;
        this._initSheetDeafult(sheet);
        this._initSheetHeader(sheet);
        sheet.setRowCount(sheet.zh_setting.emptyRows);
        sheet.extendCellType = {};
        sheet.getRange(0, 0, sheet.getRowCount(), sheet.getColumnCount()).locked(setting.readOnly);
        this.endMassOperation(sheet);
    },
    _loadRowData: function (sheet, data, row) {
        // 单元格重新写入数据
        if (!data) { return }
        sheet.zh_setting.cols.forEach(function (col, j) {
            const cell = sheet.getCell(row, j);
            if (col.getValue && Object.prototype.toString.apply(col.getValue) === "[object Function]") {
                cell.value(col.getValue(data));
            } else if (col.field !== '' && data[col.field]) {
                cell.value(data[col.field]);
            }
            if (col.font) {
                cell.font(col.font);
            }
            if (col.foreColor) {
                if (Object.prototype.toString.apply(col.foreColor) === "[object Function]") {
                    cell.foreColor(col.foreColor(data, sheet.getDefaultStyle().foreColor));
                } else {
                    cell.foreColor(col.foreColor);
                }
            }
            if (col.readOnly && Object.prototype.toString.apply(col.readOnly) === "[object Function]") {
                cell.locked(col.readOnly(data) || sheet.zh_setting.readOnly || false).vAlign(1).hAlign(col.hAlign);
            } else {
                cell.locked(col.readOnly || sheet.zh_setting.readOnly || false).vAlign(1).hAlign(col.hAlign);
            }
            if (col.formatter) {
                cell.formatter(col.formatter);
            } else if (col.type === 'Number') {
                cell.formatter('0.######');
            }
            if (sheet.zh_setting.getColor && Object.prototype.toString.apply(sheet.zh_setting.getColor) === "[object Function]") {
                cell.backColor(sheet.zh_setting.getColor(sheet, data, col, sheet.getDefaultStyle().backColor));
            }
        });

    },
    _defineColCellType: function (sheet, col, colSetting) {
        sheet.AcitveComboRefresh = false;
        if(colSetting.cellType === 'ellipsis') {
            if (!sheet.extendCellType.ellipsis) {
                sheet.extendCellType.ellipsis = this.CellType.getEllipsisTextCellType();
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.ellipsis);
        }
        if(colSetting.cellType === 'ellipsisAutoTip') {
            if (!sheet.extendCellType.ellipsisAutoTip) {
                sheet.extendCellType.ellipsisAutoTip = this.CellType.getEllipsisTextAutoTipCellType();
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.ellipsisAutoTip);
        }
        if(colSetting.cellType === 'html') {
            if (!sheet.extendCellType.html) {
                sheet.extendCellType.html = this.CellType.getHtmlCellType();
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.html);
        }
        if (colSetting.cellType === 'image') {
            if (!sheet.extendCellType.image) {
                sheet.extendCellType.image = this.CellType.getImageCellType();
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.image);
        }
        if (colSetting.cellType === 'imageBtn') {
            if (!sheet.extendCellType.image) {
                sheet.extendCellType.imageBtn = this.CellType.getImageButtonCellType();
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.imageBtn);
        }
        if (colSetting.cellType === 'tree') {
            if (!sheet.extendCellType.tree) {
                sheet.extendCellType.tree = this.CellType.getTreeNodeCellType();
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.tree);
        }
        if (colSetting.cellType === 'tip') {
            if (!sheet.extendCellType.tip) {
                sheet.extendCellType.tip = this.CellType.getTipCellType();
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.tip);
        }
        if (colSetting.cellType === 'autoTip') {
            if (!sheet.extendCellType.autoTip) {
                sheet.extendCellType.autoTip = this.CellType.getAutoTipCellType();
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.autoTip);
        }
        if (colSetting.cellType === 'checkbox') {
            if (!sheet.extendCellType.checkbox) {
                sheet.extendCellType.checkbox = new spreadNS.CellTypes.CheckBox();
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.checkbox);
        }
        if (colSetting.cellType === 'unit') {
            if (!sheet.extendCellType.unit) {
                sheet.extendCellType.unit = this.CellType.getUnitCellType();
                if (!sheet.AcitveComboRefresh) {
                    sheet.bind(spreadNS.Events.LeaveCell, function (e, info) {
                        const cellType = info.sheet.getCell(info.row, info.col).cellType();
                        if (cellType === sheet.extendCellType.unit) {
                            info.sheet.leaveCell = {row: info.row, col: info.col};
                        } else {
                            delete info.sheet.leaveCell;
                        }
                    });
                    sheet.bind(spreadNS.Events.EnterCell, function (e, info) {
                        if (info.sheet.leaveCell) {
                            info.sheet.repaint(info.sheet.getCellRect(info.sheet.leaveCell.row, info.sheet.leaveCell.col));
                        }
                    });
                    sheet.AcitveComboRefresh = true;
                }
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.unit);
        }
        if (colSetting.cellType === 'customizeCombo') {
            if (!sheet.extendCellType.unit) {
                sheet.extendCellType.unit = this.CellType.getCustomizeComboCellType(colSetting.comboItems);
                if (!sheet.AcitveComboRefresh) {
                    sheet.bind(spreadNS.Events.LeaveCell, function (e, info) {
                        const cellType = info.sheet.getCell(info.row, info.col).cellType();
                        if (cellType === sheet.extendCellType.unit) {
                            info.sheet.leaveCell = {row: info.row, col: info.col};
                        } else {
                            delete info.sheet.leaveCell;
                        }
                    });
                    sheet.bind(spreadNS.Events.EnterCell, function (e, info) {
                        if (info.sheet.leaveCell) {
                            info.sheet.repaint(info.sheet.getCellRect(info.sheet.leaveCell.row, info.sheet.leaveCell.col));
                        }
                    });
                    sheet.AcitveComboRefresh = true;
                }
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.unit);
        }
        if (colSetting.formatter) {
            sheet.getRange(-1, col, -1, 1).formatter(colSetting.formatter);
        }
    },
    /**
     * 整个sheet重新加载数据
     * @param {GC.Spread.Sheets.Worksheet} sheet
     */
    reLoadSheetData: function (sheet) {
        const self = this;
        const sortData = sheet.zh_dataType === 'tree' ? sheet.zh_tree.nodes : sheet.zh_data;
        this.beginMassOperation(sheet);
        try {
            sheet.clear(0, 0, sheet.getRowCount(), sheet.getColumnCount(), spreadNS.SheetArea.viewport, spreadNS.StorageType.data);
            sheet.getRange(0, 0, sheet.getRowCount(), sheet.getColumnCount()).backColor(sheet.getDefaultStyle().backColor);
            // 设置总行数
            const totalRow = sortData.length + sheet.zh_setting.emptyRows;
            sheet.setRowCount(totalRow, spreadNS.SheetArea.viewport);
            // 控制空白行
            const emptyRows = sheet.getRange(sortData.length, -1, sheet.zh_setting.emptyRows, -1);
            emptyRows.locked(sheet.zh_dataType === 'tree');
            if (sortData) {
                // 单元格写入数据
                sortData.forEach(function (data, i) {
                    self._loadRowData(sheet, data, i);
                    sheet.setRowVisible(i, data.visible);
                });
            }
            // 设置列单元格格式
            sheet.zh_setting.cols.forEach(function (col, j) {
                //if (!col.cellType) { return; }
                self._defineColCellType(sheet, j, col);
            });
            this.endMassOperation(sheet);
        } catch (err) {
            this.endMassOperation(sheet);
        }
    },
    /**
     * 重新加载部分数据行
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @param {Number} row
     * @param {Number} count
     */
    reLoadRowData: function (sheet, row, count = 1) {
        if (row < 0) { return; }
        const self = this;
        const sortData = sheet.zh_dataType === 'tree' ? sheet.zh_tree.nodes : sheet.zh_data;
        this.beginMassOperation(sheet);
        try {
            // 清空原单元格数据
            sheet.clear(row, -1, count, -1, spreadNS.SheetArea.viewport, spreadNS.StorageType.data);
            sheet.getRange(row, -1, count, -1).backColor(sheet.getDefaultStyle().backColor);
            // 单元格重新写入数据
            for (let i = row; i < row + count; i++) {
                const data = sortData[i];
                if (!data) { continue; }
                this._loadRowData(sheet, data, i);
                sheet.setRowVisible(i, data.visible);
            }
            this.endMassOperation(sheet);
        } catch (err) {
            this.endMassOperation(sheet);
        }
    },
    /**
     * 重新加载部分行数据
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @param {Array} rows
     */
    reLoadRowsData: function (sheet, rows) {
        const self = this;
        const sortData = sheet.zh_dataType === 'tree' ? sheet.zh_tree.nodes : sheet.zh_data;

        this.beginMassOperation(sheet);
        try {
            for (const row of rows) {
                if (row < 0) { continue; }
                // 清空原单元格数据
                sheet.clear(row, -1, 1, -1, spreadNS.SheetArea.viewport, spreadNS.StorageType.data);
                sheet.getRange(row, -1, 1, -1).backColor(sheet.getDefaultStyle().backColor);
                const data = sortData[row];
                // 单元格重新写入数据
                this._loadRowData(sheet, data, row);
                sheet.setRowVisible(row, data.visible);
            };
            this.endMassOperation(sheet);
        } catch (err) {
            this.endMassOperation(sheet);
        }
    },
    /**
     * 重新加载部分列数据
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @param {Array} cols
     */
    reLoadColsData: function (sheet, cols) {
        const self = this;
        const sortData = sheet.zh_dataType === 'tree' ? sheet.zh_tree.nodes : sheet.zh_data;

        this.beginMassOperation(sheet);
        try {
            for (const iCol of cols) {
                // 清空原单元格数据
                sheet.clear(-1, iCol, -1, 1, spreadNS.SheetArea.viewport, spreadNS.StorageType.data);
                const col = sheet.zh_setting.cols[iCol];

                sortData.forEach(function (data, i) {
                    // 设置值
                    const cell = sheet.getCell(i, iCol);
                    if (col.field !== '' && data[col.field]) {
                        cell.value(data[col.field]).locked(col.readOnly || sheet.zh_setting.readOnly || false).vAlign(1).hAlign(col.hAlign);
                    } else {
                        cell.locked(col.readOnly || sheet.zh_setting.readOnly || false).vAlign(1).hAlign(col.hAlign);
                    }
                    // 设置单元格格式
                    if (col.formatter) {
                        cell.formatter(col.formatter);
                    }
                });
            }
            this.endMassOperation(sheet);
        } catch (err) {
            this.endMassOperation(sheet);
        }
    },
    reLoadNodesData: function (sheet, nodes) {
        this.beginMassOperation(sheet);
        nodes = nodes instanceof Array ? nodes : [nodes];
        for (const node of nodes) {
            const sortData = sheet.zh_dataType === 'tree' ? sheet.zh_tree.nodes : sheet.zh_data;
            this._loadRowData(sheet, node, sortData.indexOf(node));
        }
        this.endMassOperation(sheet);
    },
    /**
     * 根据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.protectedSheet(sheet);
        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');
    },
    analysisPasteText: function (text) {
        const result = [];
        if (text === '') return result;

        const rows = text.split('\n');
        for (const r of rows) {
            const cols = r.split('\t');
            result.push(cols);
        }
        return result;
    },
    analysisPasteHtml: function (html) {
        const result = [];
        if (!html || html === '') return result;
    },
    /**
     * 树表结构，定位至指定的节点
     * @param {GC.Spread.Sheets.Worksheet} sheet - 需要定位的sheet
     * @param {Number} id - 定位节点的id
     */
    locateTreeNode: function (sheet, id, autoExpand = false) {
        const tree = sheet.zh_tree;
        if (!tree) { return }
        const node = tree.getItems(id);
        if (!node) { return }
        if (autoExpand && !node.visible) {
            const parents = tree.autoExpandNode(node);
            if (parents && parents.length > 0) {
                SpreadJsObj.reLoadNodesData(sheet, parents);
                SpreadJsObj.refreshTreeRowVisible(sheet);
            }
        }
        const index = tree.nodes.indexOf(node);
        const sels = sheet.getSelections();
        sheet.setSelection(index, sels[0].col, 1, 1);
        sheet.showRow(index, spreadNS.VerticalPosition.center);
    },
    saveTopAndSelect: function (sheet, cacheKey) {
        const sel = sheet.getSelections()[0];
        const top = sheet.getViewportTopRow(1);
        setLocalCache(cacheKey, JSON.stringify({top: top, sel: sel}));
    },
    loadTopAndSelect: function (sheet, cacheKey) {
        let ts = getLocalCache(cacheKey);
        if (ts !== '') {
            ts = JSON.parse(ts);
            if (ts === undefined || ts === null) return;
            if (ts.sel) {
                sheet.setSelection(ts.sel.row, ts.sel.col, ts.sel.rowCount, ts.sel.colCount);
            }
            if (ts.top) {
                sheet.showRow(ts.top, spreadNS.VerticalPosition.top);
            }
        }
    },
    resetTopAndSelect: function (sheet) {
        sheet.setSelection(0, 0, 1, 1);
        sheet.showRow(0, spreadNS.VerticalPosition.top);
    },
    /**
     * 获取当前选行的数据对象
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @returns {Object}
     */
    getSelectObject: function (sheet) {
        if (!sheet) {
            return null;
        } else if (sheet.zh_dataType) {
            const sel = sheet.getSelections()[0];
            if (!sel) {
                return null;
            }
            if (sheet.zh_dataType === this.DataType.Tree) {
                return sheet.zh_tree.nodes[sel.row];
            } else if (sheet.zh_dataType === this.DataType.Data) {
                return sheet.zh_data[sel.row];
            } else {
                return null;
            }
        }
    },
    /**
     * 刷新列显示
     * @param sheet
     */
    refreshColumnVisible: function (sheet) {
        if(sheet.zh_setting) {
            sheet.zh_setting.cols.forEach(function (col, index) {
                if (col.visible !== undefined && col.visible !== null) {
                    sheet.setColumnVisible(index, col.visible);
                }
            });
        }
    },
    /**
     * 刷新行显示
     * @param sheet
     */
    refreshTreeRowVisible: function (sheet) {
        this.beginMassOperation(sheet);
        const sortData = sheet.zh_dataType === this.DataType.Data ? sheet.zh_data : sheet.zh_tree.nodes;
        for (const iRow in sortData) {
            const node = sortData[iRow];
            if (node.visible !== undefined && node.visible !== null) {
                sheet.setRowVisible(iRow, node.visible);
            } else {
                sheet.setRowVisible(iRow, true);
            }
        }
        // if (sheet.zh_tree) {
        //     for (const iRow in sheet.zh_tree.nodes) {
        //         const node = sheet.zh_tree.nodes[iRow];
        //         if (node.visible !== undefined && node.visible !== null) {
        //             sheet.setRowVisible(iRow, node.visible);
        //         } else {
        //             sheet.setRowVisible(iRow, true);
        //         }
        //     }
        // }
        this.endMassOperation(sheet);
    },
    refreshColumnAlign: function (sheet) {
        if (sheet.zh_setting) {
            for (const iCol in sheet.zh_setting.cols) {
                const col = sheet.zh_setting.cols[iCol];
                sheet.getRange(-1, iCol, -1, 1).hAlign(col.hAlign);
            }
        }
    },
    /**
     * 刷新列是否只读
     * @param sheet
     * @param field
     * @param readonly
     */
    resetFieldReadOnly: function (sheet, field, readonly) {
        const fields = field instanceof Array ? field : [field];
        if (sheet.zh_setting) {
            sheet.zh_setting.cols.forEach(function (col, i) {
                if (fields.indexOf(col.field) !== -1) {
                    col.readOnly = readonly;
                    for (let iRow = 0; iRow < sheet.getRowCount(); iRow++) {
                        sheet.getCell(iRow, i).locked(col.readOnly || sheet.zh_setting.readOnly || false);
                    }
                    //sheet.getRange(-1, i, -1, 1, GC.Spread.Sheets.SheetArea.viewport).locked(col.readOnly || sheet.zh_setting.readOnly || false);
                }
            });
        }
    },

    CellType: {
        /**
         * 获取树结构CellType
         * 通过SpreadJsObj.loadSheetData(sheet, 'tree', tree)加载树结构数据
         * 要求tree类型为PathTree, 节点必须含有{id, pid, level, order, is_leaf}数据
         * @returns {TreeNodeCellType}
         */
        getTreeNodeCellType: function () {
            const indent = 20;
            const levelIndent = -5;
            const halfBoxLength = 5;
            const halfExpandLength = 3;

            /**
             * 画一条点线段
             * @param canvas - 画布
             * @param x1 - 线段起点 x
             * @param y1 - 线段起点 y
             * @param x2 - 线段终点 x
             * @param y2 - 线段终点 y
             * @param color - 线段颜色
             */
            const drawDotLine = function (canvas, x1, y1, x2, y2, color) {
                canvas.save();
                // 设置偏移量
                canvas.translate(0.5, 0.5);
                canvas.beginPath();
                canvas.strokeStyle = color;
                canvas.dottedLine(x1, y1, x2, y2);
                canvas.stroke();
                canvas.restore();
            };
            /**
             * 画一条线段
             * @param canvas - 画布
             * @param x1 - 线段起点 x
             * @param y1 - 线段起点 y
             * @param x2 - 线段终点 x
             * @param y2 - 线段终点 y
             * @param color - 线段颜色
             */
            const drawLine = function (canvas, x1, y1, x2, y2, color) {
                canvas.save();
                // 设置偏移量
                canvas.translate(0.5, 0.5);
                canvas.beginPath();
                canvas.moveTo(x1, y1);
                canvas.lineTo(x2, y2);
                canvas.strokeStyle = color;
                canvas.stroke();
                canvas.restore();
            };
            /**
             * 画一个方框
             * @param {Object} canvas - 画布
             * @param {Object} rect - 方框区域
             * @param {String} lineColor - 画线颜色
             * @param {String} fillColor - 填充颜色
             */
            const drawBox = function (canvas, rect, lineColor, fillColor) {
                canvas.save();
                // 设置偏移量
                canvas.translate(0.5, 0.5);
                canvas.strokeStyle = lineColor;
                canvas.beginPath();
                canvas.moveTo(rect.left, rect.top);
                canvas.lineTo(rect.left, rect.bottom);
                canvas.lineTo(rect.right, rect.bottom);
                canvas.lineTo(rect.right, rect.top);
                canvas.lineTo(rect.left, rect.top);
                canvas.stroke();
                canvas.fillStyle = fillColor;
                canvas.fill();
                canvas.restore();
            };
            /**
             * 画树结构-展开收起按钮
             * @param {Object} canvas - 画布
             * @param {Number} x - 单元格左顶点坐标 x
             * @param {Number} y - 单元格左顶点坐标 y
             * @param {Number} w - 单元格宽度
             * @param {Number} h - 单元格高度
             * @param {Number} centerX - 按钮中央坐标
             * @param {Number} centerY - 按钮中央坐标
             * @param {Boolean} expanded - 当前节点展开收起状态
             */
            const drawExpandBox = function (canvas, x, y, w, h, centerX, centerY, expanded) {
                let rect = {
                    top: centerY - halfBoxLength,
                    bottom: centerY + halfBoxLength,
                    left: centerX - halfBoxLength,
                    right: centerX + halfBoxLength
                };
                let h1, h2, offset = 1;

                if (rect.left < x + w) {
                    // 方框超出单元格宽度时，超出部分不画。
                    rect.right = Math.min(rect.right, x + w);
                    drawBox(canvas, rect, '#808080', 'white');

                    // 画中心十字
                    // 画十字横线
                    h1 = centerX - halfExpandLength;
                    h2 = Math.min(centerX + halfExpandLength, x + w);
                    if (h2 > h1) {
                        drawLine(canvas, h1, centerY, h2, centerY, '#808080');
                    }
                    // 画十字竖线
                    if (!expanded && (centerX < x + w)) {
                        drawLine(canvas, centerX, centerY - halfExpandLength, centerX, centerY + halfExpandLength, '#808080');
                    }
                }
            };

            let TreeNodeCellType = function (){};
            TreeNodeCellType.prototype = new spreadNS.CellTypes.Text();
            const proto = TreeNodeCellType.prototype;

            /**
             * 绘制方法
             * @param {Object} canvas - 画布
             * @param value - cell.value
             * @param {Number} x - 单元格左顶点坐标 x
             * @param {Number} y - 单元格左顶点坐标 y
             * @param {Number} w - 单元格宽度
             * @param {Number} h - 单元格高度
             * @param {Object} style - cell.style
             * @param {Object} options
             */
            proto.paint = function (canvas, value, x, y, w, h, style, options) {
                // 清理 画布--单元格范围 旧数据
                if (style.backColor) {
                    canvas.save();
                    canvas.fillStyle = style.backColor;
                    canvas.fillRect(x, y, w, h);
                    canvas.restore();
                } else {
                    canvas.clearRect(x, y, w, h);
                }

                const tree = options.sheet.zh_tree;
                // 使用TreeCellType前，需定义sheet.tree
                if (tree) {
                    const node = options.row < tree.nodes.length ? tree.nodes[options.row] : null;
                    if (node) {
                        const showTreeLine = true;
                        const centerX = Math.floor(x) + (node.level) * indent + (node.level) * levelIndent + indent / 2;
                        const centerY = Math.floor((y + (y + h)) / 2);
                        // Draw Sibling Line
                        if (showTreeLine) {
                            // Draw Horizontal Line
                            if (centerX < x + w) {
                                const x1 = centerX + indent / 2;
                                //drawLine(canvas, centerX, centerY, Math.min(x1, x + w), centerY, 'gray');
                                drawDotLine(canvas, centerX, centerY, Math.min(x1, x + w), centerY, '#b8b8b8');
                            }
                            // Draw Vertical Line
                            if (centerX < x + w) {
                                const y1 = tree.isLastSibling(node) ? centerY : y + h;
                                const parent = tree.getParent(node);
                                const y2 = y1 - centerY;
                                if (node.order === 1 && !parent) {
                                    //drawLine(canvas, centerX, centerY, centerX, y1, 'gray');
                                    drawDotLine(canvas, centerX, centerY, centerX, y1, '#b8b8b8');
                                } else {
                                    //drawLine(canvas, centerX, y, centerX, y1, 'gray');
                                    drawDotLine(canvas, centerX, y, centerX, y1, '#b8b8b8');
                                }
                            }
                        }
                        // Draw Expand Box
                        if (!node.is_leaf) {
                            drawExpandBox(canvas, x, y, w, h, centerX, centerY, node.expanded);
                        }
                        // Draw Parent Line
                        if (showTreeLine) {
                            let parent = tree.getParent(node), parentCenterX = centerX - indent - levelIndent;
                            while (parent) {
                                if (!tree.isLastSibling(parent)) {
                                    if (parentCenterX < x + w) {
                                        //drawLine(canvas, parentCenterX, y, parentCenterX, y + h, 'gray');
                                        drawDotLine(canvas, parentCenterX, y, parentCenterX, y + h, '#b8b8b8');
                                    }
                                }
                                parent = tree.getParent(parent);
                                parentCenterX -= (indent + levelIndent);
                            }
                        };
                        // 重定位x
                        const move = (node.level + 1) * indent + (node.level) * levelIndent;
                        x = x + move;
                        w = w - move;
                    }
                }
                // Drawing Text
                spreadNS.CellTypes.Text.prototype.paint.apply(this, [canvas, value, x, y, w, h, style, options]);
            };
            /**
             * 获取点击信息
             * @param {Number} x
             * @param {Number} y
             * @param {Object} cellStyle
             * @param {Object} cellRect
             * @param {Object} context
             * @returns {{x: *, y: *, row: *, col: *|boolean|*[]|number|{}|UE.dom.dtd.col, cellStyle: *, cellRect: *, sheet: *|StyleSheet, sheetArea: *}}
             */
            proto.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
                };
            };
            /**
             * 鼠标点击 树结构按钮 响应展开收起（未加载子节点时，先加载子节点）
             * @param {Object} hitinfo - 见getHitInfo
             */
            proto.processMouseDown = function (hitinfo) {
                const offset = -1;

                const tree = hitinfo.sheet.zh_tree;
                if (!tree) { return; }

                const node = tree.nodes[hitinfo.row];
                if (!node) { return; }

                let centerX = hitinfo.cellRect.x + offset + (node.level) * indent + (node.level) * levelIndent + indent / 2;
                let centerY = (hitinfo.cellRect.y + offset + (hitinfo.cellRect.y + offset + hitinfo.cellRect.height)) / 2;

                // 点击展开节点时，如果已加载子项，则展开，反之这加载子项，展开
                if (Math.abs(hitinfo.x - centerX) < halfBoxLength && Math.abs(hitinfo.y - centerY) < halfBoxLength) {
                    const children = tree.getChildren(node);
                    if (!node.expanded && !node.is_leaf && children.length === 0 && tree.loadChildren) {
                        tree.loadChildren(node, function () {
                            node.expanded = true;
                            const children = tree.getChildren(node);
                            hitinfo.sheet.addRows(hitinfo.row + 1, children.length);
                            SpreadJsObj.reLoadRowData(hitinfo.sheet, hitinfo.row + 1, children.length);
                        });
                    } else {
                        tree.setExpanded(node, !node.expanded);

                        SpreadJsObj.massOperationSheet(hitinfo.sheet, function () {
                            const posterity = tree.getPosterity(node);
                            for (const child of posterity) {
                                hitinfo.sheet.setRowVisible(tree.nodes.indexOf(child), child.visible, hitinfo.sheetArea);
                            }
                        });
                        hitinfo.sheet.repaint();
                    }
                }
            };

            return new TreeNodeCellType();
        },
        /**
         * 获取 带悬浮提示的CellType
         * @returns {TipCellType}
         */
        getTipCellType: function () {
            const maxHintWidth = 200, indent = 15, borderIndent = 10;
            const TipCellType = function () {};
            // 继承 SpreadJs定义的 普通的TextCellType
            TipCellType.prototype = new spreadNS.CellTypes.Text();
            const proto = TipCellType.prototype;
            proto.getTextDisplayWidth = function(hitinfo, str, font) {
                const xs = hitinfo.sheet.getParent().xs;
                const ctx = xs.childNodes[0].getContext("2d");
                if (font && font !== '') {
                    ctx.font = font;
                } else {
                    ctx.font = hitinfo.cellStyle.font;
                }
                return ctx.measureText(str).width;
            };
            proto.showTip = function (hitinfo, text) {
                return text && text !== '';
            };
            /**
             * 获取点击信息
             * @param {Number} x
             * @param {Number} y
             * @param {Object} cellStyle
             * @param {Object} cellRect
             * @param {Object} context
             * @returns {{x: *, y: *, row: *, col: *|boolean|*[]|number|{}|UE.dom.dtd.col, cellStyle: *, cellRect: *, sheet: *|StyleSheet, sheetArea: *}}
             */
            proto.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,
                    ctx: context.sheet.getParent().xs,
                };
            };
            /**
             * 鼠标进入单元格事件 - 显示悬浮提示
             * @param {Object} hitinfo - 见getHitInfo返回值
             */
            proto.processMouseEnter = function (hitinfo) {
                let text = hitinfo.sheet.getText(hitinfo.row, hitinfo.col);
                const col = hitinfo.sheet.zh_setting.cols[hitinfo.col];
                if (col.getTip && Object.prototype.toString.apply(col.getTip) === "[object Function]") {
                    const sortData = SpreadJsObj.getSortData(hitinfo.sheet);
                    text = col.getTip(sortData[hitinfo.row]);
                }
                const pos = SpreadJsObj.getObjPos(hitinfo.sheet.getParent().qo);
                if (pos && this.showTip(hitinfo, 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)
                                .css("z-index", 999)
                                .css("max-width", maxHintWidth)
                                .css("word-wrap", "break-word")
                                .attr("id", 'autoTip');
                            document.body.insertBefore(div, null);
                        }
                        const validWidth = $(window).width() - (pos.x + hitinfo.x + indent) - borderIndent;
                        const textWidth = this.getTextDisplayWidth(hitinfo, text, "9pt Arial");
                        if (validWidth >= maxHintWidth || textWidth <= validWidth) {
                            $(div).text(text).css("top", pos.y + hitinfo.y + indent).css("left", pos.x + hitinfo.x + indent);
                        } else if (textWidth > maxHintWidth) {
                            $(div).text(text).css("top", pos.y + hitinfo.y + indent).css("left", pos.x + hitinfo.x - indent - maxHintWidth);
                        } else {
                            $(div).text(text).css("top", pos.y + hitinfo.y + indent).css("left", pos.x + hitinfo.x - indent - textWidth);
                        }
                        this._toolTipElement = div;
                        $(div).show("fast");
                    }
                }
            };
            /**
             * 鼠标移出单元格事件 - 隐藏悬浮提示
             * @param {Object} hitinfo - 见getHitInfo返回值
             */
            proto.processMouseLeave = function (hitinfo) {
                if (this._toolTipElement) {
                    $(this._toolTipElement).hide();
                    this._toolTipElement = null;
                }
            };

            return new TipCellType();
        },
        getAutoTipCellType: function () {
            const AutoTipCellType = function () {};
            // 继承 TipCellType
            AutoTipCellType.prototype = SpreadJsObj.CellType.getTipCellType();
            const proto = AutoTipCellType.prototype
            proto.showTip = function (hitinfo, text) {
                return text && text !== '' && this.getTextDisplayWidth(hitinfo, text) > hitinfo.cellRect.width;
            };

            return new AutoTipCellType();
        },
        /**
         * 获取 带图片的cellType(图片需在document中定义好img，并写入col的img属性)
         *
         * img：
         * 1. 整列固定，则传入img的select
         *    e.g. {title: '附件', field: 'attachment', cellType: 'image', img = '#attachment-img'}
         *
         * 2. 各单元格自定义，则
         *    e.g. {title: '附件', field: 'attachment', cellType: 'image', img = getAttachmentImage}
         *    function getAttachmentImage (data) {
         *      $('#attachment-img').url = data.attachmentImageUrl;
         *      return $('#attachment-img')[0];
         *    }
         *
         * @returns {ImageCellType}
         */
        getImageCellType: function () {
            const ImageCellType = function (){};
            ImageCellType.prototype = new spreadNS.CellTypes.Text();
            const proto = ImageCellType.prototype;
            proto.getImage = function (sheet, iRow, iCol) {
                const col = sheet.zh_setting.cols[iCol];
                let imgSource = col.img;
                if (imgSource && Object.prototype.toString.apply(imgSource) === "[object Function]") {
                    const sortData = SpreadJsObj.getSortData(sheet);
                    const data = sortData ? sortData[iRow] : null;
                    return data ? imgSource(data) : null;
                } else {
                    return $(imgSource)[0] ? $(imgSource)[0] : null;
                }

            };
            proto.paint = function (canvas, value, x, y, w, h, style, options) {
                const col = options.sheet.zh_setting.cols[options.col];
                const img = this.getImage(options.sheet, options.row, options.col);
                const indent = col.indent ? col.indent : 10;
                if (img) {
                    if (style.backColor) {
                        canvas.save();
                        canvas.fillStyle = style.backColor;
                        canvas.fillRect(x, y, indent + img.width, h);
                        canvas.restore();
                    }
                    canvas.drawImage(img, x + indent, y + (h - img.height) / 2);
                    if (style.hAlign !== spreadNS.HorizontalAlign.left) {
                        style.hAlign = spreadNS.HorizontalAlign.left;
                    }
                    x = x + indent + img.width;
                    w = w - indent - img.width;
                }
                // Drawing Text
                spreadNS.CellTypes.Text.prototype.paint.apply(this, [canvas, value, x, y, w, h, style, options]);
            };
            /**
             * 获取点击信息
             * @param {Number} x
             * @param {Number} y
             * @param {Object} cellStyle
             * @param {Object} cellRect
             * @param {Object} context
             * @returns {{x: *, y: *, row: *, col: *|boolean|*[]|number|{}|UE.dom.dtd.col, cellStyle: *, cellRect: *, sheet: *|StyleSheet, sheetArea: *}}
             */
            proto.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
                };
            };
            /**
             * 鼠标点击
             * @param {Object} hitinfo - 见getHitInfo
             */
            proto.processMouseDown = function (hitinfo) {
                const img = this.getImage(hitinfo.sheet, hitinfo.row, hitinfo.col);
                if (img) {
                    const halfX = img.width / 2, halfY = img.height / 2;
                    const centerX = hitinfo.cellRect.x + indent + halfX;
                    const centerY = hitinfo.cellRect.y + hitinfo.cellRect.height / 2;

                    // 点击展开节点时，如果已加载子项，则展开，反之这加载子项，展开
                    if (Math.abs(hitinfo.x - centerX) < halfX && Math.abs(hitinfo.y - centerY) < halfY) {
                        const imageClick = hitinfo.sheet.zh_setting ? hitinfo.sheet.zh_setting.imageClick : null;
                        if (imageClick && Object.prototype.toString.apply(imageClick) === "[object Function]") {
                            const sortData = SpreadJsObj.getSortData(hitinfo.sheet);
                            const data = sortData ? sortData[hitinfo.row] : null;
                            imageClick(data);
                        }
                    }
                }
            };
            return new ImageCellType();
        },
        /**
         *
         * 获取 带normal-hover-active按钮的cellType(需定义三张图片，须在document中定义好img，并写入col的normalImg, hoverImg, activeImg属性)
         * 其中：normalImg必需，向下套用（不存在activeImg则使用hoverImg，不存在hoverImg则使用normalImg）
         * 三个img均可像getImageCellType一样动态获取，参见getImageCellType注释
         *
         * @returns {ImageCellType}
         */
        getImageButtonCellType: function () {
            let hover = 1, active = 2;
            const ImageCellType = function (){};
            ImageCellType.prototype = new spreadNS.CellTypes.Text();
            const proto = ImageCellType.prototype;
            proto.getImage = function (sheet, iRow, iCol) {
                const col = sheet.zh_setting.cols[iCol];
                let imgSource = col.normalImg;
                const cell = sheet.getCell(iRow, iCol), tag = cell.tag();
                if (tag === active) {
                    imgSource = col.activeImg ? col.activeImg : (col.hoverImg ? col.hoverImg : col.normalImg);
                } else if (tag === hover) {
                    imgSource = col.hoverImg ? col.hoverImg : col.normalImg;
                }
                if (imgSource && Object.prototype.toString.apply(imgSource) === "[object Function]") {
                    const sortData = SpreadJsObj.getSortData(sheet);
                    const data = sortData ? sortData[iRow] : null;
                    return data ? imgSource(data) : null;
                } else {
                    return $(imgSource)[0] ? $(imgSource)[0] : null;
                }

            };
            proto.paint = function (canvas, value, x, y, w, h, style, options) {
                const col = options.sheet.zh_setting.cols[options.col];

                const sortData = SpreadJsObj.getSortData(options.sheet);
                const data = sortData ? sortData[options.row] : null;
                let showImage = true;
                if (col.showImage && Object.prototype.toString.apply(col.showImage) === "[object Function]") {
                    showImage = col.showImage(data);
                }
                const img = showImage ? this.getImage(options.sheet, options.row, options.col) : null;

                const indent = col.indent ? col.indent : 10;
                if (style.hAlign === spreadNS.HorizontalAlign.right) {
                    if (img) {
                        if (style.backColor) {
                            canvas.save();
                            canvas.fillStyle = style.backColor;
                            canvas.fillRect(x + w - indent - img.width, y, img.width, h);
                            canvas.restore();
                        }
                        canvas.drawImage(img, x + w - indent - img.width, y + (h - img.height) / 2);
                        w = w - indent - img.width;
                    }
                    // Drawing Text
                    spreadNS.CellTypes.Text.prototype.paint.apply(this, [canvas, value, x, y, w, h, style, options]);
                } else {
                    if (img) {
                        if (style.backColor) {
                            canvas.save();
                            canvas.fillStyle = style.backColor;
                            canvas.fillRect(x, y, indent + img.width, h);
                            canvas.restore();
                        }
                        canvas.drawImage(img, x + 10, y + (h - img.height) / 2);
                        if (style.hAlign !== spreadNS.HorizontalAlign.left) {
                            style.hAlign = spreadNS.HorizontalAlign.left;
                        }
                        x = x + indent + img.width;
                        w = w - indent - img.width;
                    }
                    // Drawing Text
                    spreadNS.CellTypes.Text.prototype.paint.apply(this, [canvas, value, x, y, w, h, style, options]);
                }

            };
            /**
             * 获取点击信息
             * @param {Number} x
             * @param {Number} y
             * @param {Object} cellStyle
             * @param {Object} cellRect
             * @param {Object} context
             * @returns {{x: *, y: *, row: *, col: *|boolean|*[]|number|{}|UE.dom.dtd.col, cellStyle: *, cellRect: *, sheet: *|StyleSheet, sheetArea: *}}
             */
            proto.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
                };
            };
            /**
             * 鼠标点击
             * @param {Object} hitinfo - 见getHitInfo
             */
            proto.processMouseEnter = function (hitinfo) {
                const col = hitinfo.sheet.zh_setting.cols[hitinfo.col];
                // Drawing Image
                if (col.hoverImg) {
                    const cell = hitinfo.sheet.getCell(hitinfo.row, hitinfo.col);
                    cell.tag(hover);
                    hitinfo.sheet.repaint(hitinfo.cellRect);
                }
            };
            proto.processMouseLeave = function (hitinfo) {
                const col = hitinfo.sheet.zh_setting.cols[hitinfo.col];
                // Drawing Image
                if (col.hoverImg) {
                    const cell = hitinfo.sheet.getCell(hitinfo.row, hitinfo.col);
                    cell.tag(null);
                    hitinfo.sheet.repaint(hitinfo.cellRect);
                }
            };
            proto.processMouseDown = function (hitinfo) {
                const col = hitinfo.sheet.zh_setting.cols[hitinfo.col];
                if (col.activeImg) {
                    const cell = hitinfo.sheet.getCell(hitinfo.row, hitinfo.col);
                    cell.tag(active);
                    hitinfo.sheet.repaint(hitinfo.cellRect);
                }
            };
            proto.processMouseUp = function (hitinfo) {
                const col = hitinfo.sheet.zh_setting.cols[hitinfo.col];
                const sortData = SpreadJsObj.getSortData(hitinfo.sheet);
                const data = sortData ? sortData[hitinfo.row] : null;
                if (col.showImage && Object.prototype.toString.apply(col.showImage) === "[object Function]") {
                    if (!col.showImage(data)) {
                        return;
                    }
                }
                const imageClick = hitinfo.sheet.zh_setting ? hitinfo.sheet.zh_setting.imageClick : null;
                if (imageClick && Object.prototype.toString.apply(imageClick) === "[object Function]") {
                    imageClick(data);
                    const cell = hitinfo.sheet.getCell(hitinfo.row, hitinfo.col);
                    cell.tag(null);
                    hitinfo.sheet.repaint(hitinfo.cellRect);
                }
            };
            /*
             注释部分以进入鼠标进入图片，点击图片为基准更新图片，鼠标快速移动时，可能失效
              */
            // proto.processMouseDown = function (hitinfo) {
            //     const img = this.getImage(hitinfo.sheet, hitinfo.row, hitinfo.col);
            //     const halfX = img.width / 2, halfY = img.height / 2;
            //     const centerX = hitinfo.cellRect.x + indent + halfX;
            //     const centerY = hitinfo.cellRect.y + hitinfo.cellRect.height / 2;
            //
            //     if (Math.abs(hitinfo.x - centerX) < halfX && Math.abs(hitinfo.y - centerY) < halfY) {
            //         const cell = hitinfo.sheet.getCell(hitinfo.row, hitinfo.col);
            //         cell.tag(down);
            //         hitinfo.sheet.repaint(hitinfo.cellRect);
            //     }
            // };
            // proto.processMouseUp = function (hitinfo) {
            //     const img = this.getImage(hitinfo.sheet, hitinfo.row, hitinfo.col);
            //     const halfX = img.width / 2, halfY = img.height / 2;
            //     const centerX = hitinfo.cellRect.x + indent + halfX;
            //     const centerY = hitinfo.cellRect.y + hitinfo.cellRect.height / 2;
            //
            //     // 点击展开节点时，如果已加载子项，则展开，反之这加载子项，展开
            //     if (Math.abs(hitinfo.x - centerX) < halfX && Math.abs(hitinfo.y - centerY) < halfY) {
            //         const imageClick = hitinfo.sheet.zh_setting ? hitinfo.sheet.zh_setting.imageClick : null;
            //         if (imageClick && Object.prototype.toString.apply(imageClick) === "[object Function]") {
            //             const sortData = SpreadJsObj.getSortData(hitinfo.sheet);
            //             const data = sortData ? sortData[hitinfo.row] : null;
            //             imageClick(data);
            //             const cell = hitinfo.sheet.getCell(hitinfo.row, hitinfo.col);
            //             cell.tag(null);
            //             hitinfo.sheet.repaint(hitinfo.cellRect);
            //         }
            //     }
            // };
            // proto.processMouseMove = function (hitinfo) {
            //     const img = this.getImage(hitinfo.sheet, hitinfo.row, hitinfo.col);
            //     const halfX = img.width / 2, halfY = img.height / 2;
            //     const centerX = hitinfo.cellRect.x + indent + halfX;
            //     const centerY = hitinfo.cellRect.y + hitinfo.cellRect.height / 2;
            //     const cell = hitinfo.sheet.getCell(hitinfo.row, hitinfo.col);
            //     if (Math.abs(hitinfo.x - centerX) < halfX && Math.abs(hitinfo.y - centerY) < halfY) {
            //         if (cell.tag() !== hover) {
            //             cell.tag(hover);
            //             hitinfo.sheet.repaint(hitinfo.cellRect);
            //         }
            //     } else {
            //         if (cell.tag() === hover) {
            //             cell.tag(null);
            //             hitinfo.sheet.repaint(hitinfo.cellRect);
            //         }
            //     }
            // };
            return new ImageCellType();
        },
        /**
         * 获取 嵌入Html的cellType
         * @returns {HTMLCellType}
         */
        getHtmlCellType: function () {
            const HTMLCellType = function (){};
            HTMLCellType.prototype = new spreadNS.CellTypes.Text;
            const proto = ImageCellType.prototype;
            proto.paint = function (ctx, value, x, y, w, h, style, context) {
                let DOMURL = window.URL || window.webkitURL || window;
                let cell = context.sheet.getCell(context.row, context.col);
                let img = cell.tag();
                if (img) {
                    try {
                        ctx.save();
                        ctx.rect(x, y, w, h);
                        ctx.clip();
                        ctx.drawImage(img, x + 2, y + 2)
                        ctx.restore();
                        cell.tag(null);
                        return;
                    }
                    catch (err) {
                        GC.Spread.Sheets.CustomCellType.prototype.paint.apply(this, [ctx, "#HTMLError", x, y, w, h, style, context])
                        cell.tag(null);
                        return;
                    }
                }
                let svgPattern = '<svg xmlns="http://www.w3.org/2000/svg" width="{0}" height="{1}">' +
                    '<foreignObject width="100%" height="100%"><div xmlns="http://www.w3.org/1999/xhtml" style="font:{2}">{3}</div></foreignObject></svg>';

                let data = svgPattern.replace("{0}", w).replace("{1}", h).replace("{2}", style.font).replace("{3}", value);
                let doc = document.implementation.createHTMLDocument("");
                doc.write(data);
                // Get well-formed markup
                data = (new XMLSerializer()).serializeToString(doc.body.children[0]);

                img = new Image();
                //var svg = new Blob([data], {type: 'image/svg+xml;charset=utf-8'});
                //var url = DOMURL.createObjectURL(svg);
                //img.src = url;
                img.src = 'data:image/svg+xml;base64,' + window.btoa(data);
                cell.tag(img);
                img.onload = function () {
                    context.sheet.repaint(new GC.Spread.Sheets.Rect(x, y, w, h));
                }
            };
            return new HTMLCellType();
        },
        /**
         * 获取 字符超长缩略的cellType
         * @returns {EllipsisTextCellType}
         */
        getEllipsisTextCellType: function () {
            const EllipsisTextCellType = function (){};
            EllipsisTextCellType.prototype = new spreadNS.CellTypes.Text;
            const proto = EllipsisTextCellType.prototype;
            proto.getEllipsisText = function(c, str, maxWidth) {
                var width = c.measureText(str).width;
                var ellipsis = '…';
                var ellipsisWidth = c.measureText(ellipsis).width;
                if (width <= maxWidth || width <= ellipsisWidth) {
                    return str;
                } else {
                    var len = str.length;
                    while (width >= maxWidth - ellipsisWidth && len-- > 0) {
                        str = str.substring(0, len);
                        width = c.measureText(str).width;
                    }
                    return str + ellipsis;
                }
            };
            proto.paint = function (ctx, value, x, y, w, h, style, context) {
                ctx.font = style.font;
                value = this.getEllipsisText(ctx, value, w - 2);
                spreadNS.CellTypes.Text.prototype.paint.apply(this, [ctx, value, x, y, w, h, style, context]);
            };
            return new EllipsisTextCellType();
        },
        getEllipsisTextAutoTipCellType: function () {
            const CellType = function () {};
            // 继承 TipCellType
            CellType.prototype = SpreadJsObj.CellType.getAutoTipCellType();
            const proto = CellType.prototype;
            const ellipsisTextCellType = SpreadJsObj.CellType.getEllipsisTextCellType();
            proto.getEllipsisText = ellipsisTextCellType.getEllipsisText;
            proto.paint = ellipsisTextCellType.paint;

            return new CellType();
        },
        /**
         * 获取 动态显示ComboBox的cellType
         * @returns {ActiveComboCellType}
         */
        getActiveComboCellType: function () {
            const ActiveComboCellType = function () {};
            ActiveComboCellType.prototype = new spreadNS.CellTypes.ComboBox();
            const proto = ActiveComboCellType.prototype;
            proto.paintValue = function (ctx, value, x, y, w, h, style, options) {
                const sheet = options.sheet;
                if (options.row === sheet.getActiveRowIndex() && options.col === sheet.getActiveColumnIndex()
                    && !sheet.getCell(options.row, options.col).locked()) {
                    spreadNS.CellTypes.ComboBox.prototype.paintValue.apply(this, arguments);
                } else {
                    spreadNS.CellTypes.Base.prototype.paintValue.apply(this, arguments);
                }
            };
            proto.getHitInfo = function (x, y, cellStyle, cellRect, options) {
                const sheet = options.sheet;
                if (options.row === sheet.getActiveRowIndex() && options.col === sheet.getActiveColumnIndex()
                    && !sheet.getCell(options.row, options.col).locked()) {
                    return spreadNS.CellTypes.ComboBox.prototype.getHitInfo.apply(this, [x, y, cellStyle, cellRect, options]);
                } else {
                    return  {
                        x: x,
                        y: y,
                        row: options.row,
                        col: options.col,
                        cellStyle: cellStyle,
                        cellRect: cellRect,
                        sheetArea: options.sheetArea
                    };
                }
            };
            return new ActiveComboCellType();
        },
        /**
         * 获取 单位的CellType
         * @returns {GC.Spread.Sheets.CellTypes.ComboBox}
         */
        getUnitCellType: function () {
            let combo = this.getActiveComboCellType();
            combo.itemHeight(10).items(['m', 'km', 'm2', 'm3', 'dm3', 'kg', 't', 'm3·km',
                '总额', '月' ,'项', '处' ,'个', '根', '棵', '块', '台', '系统', '每一试桩',
                '桥长米', '公路公里', '株', '组', '座', '元', '工日', '套', '台班', '艘班', '亩',
                'm/处', 'm/道', 'm/座', 'm2/m', 'm3/m', 'm3/处', '根/米', 'm3/m2']);
            return combo;
        },
        /**
         * 获取 自定义的CellType
         * @returns {GC.Spread.Sheets.CellTypes.ComboBox}
         */
        getCustomizeComboCellType: function (items) {
            let combo = this.getActiveComboCellType();
            combo.itemHeight(10).editorValueType(spreadNS.CellTypes.EditorValueType.value).items(items);
            return combo;
        },
    },

    Clipboard: (function () {
        let cText = '', cHtml;
        const setCopyData = function (text, html) {
            cText = text;
            cHtml = html;
        };
        const getPasteData = function () {
            return {text: cText, html: cHtml};
        };
        const clearData = function () {
            cText = '';
            cHtml = undefined;
        };
        const setSheetFilterCopyData = function (sheet) {
            cText = SpreadJsObj.getFilterCopyText(sheet);
            cHtml = SpreadJsObj.getFilterCopyHTML(sheet);
        };
        const getAnalysisPasteText = function () {
            return SpreadJsObj.analysisPasteText(cText);
        };
        const getAnalysisPasteHtml = function () {
            return SpreadJsObj.analysisPasteHtml(cHtml);
        }
        return {
            setCopyData, getPasteData, clearData,
            setSheetFilterCopyData,
            getAnalysisPasteText, getAnalysisPasteHtml,
        }
    })(),
};