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;
// 授权码
// V10版本
// spreadNS.LicenseKey = "559432293813965#A0y3iTOzEDOzkjMyMDN9UTNiojIklkI1pjIEJCLi4TPB9mM5AFNTd4cvZ7SaJUVy3CWKtWYXx4VVhjMpp7dYNGdx2ia9sEVlZGOTh7NRlTUwkWR9wEV4gmbjBDZ4ElR8N7cGdHVvEWVBtCOwIGW0ZmeYVWVr3mI0IyUiwCMzETN8kzNzYTM0IicfJye&Qf35VfiEzRwEkI0IyQiwiIwEjL6ByUKBCZhVmcwNlI0IiTis7W0ICZyBlIsIyNyMzM5ADI5ADNwcTMwIjI0ICdyNkIsIibj9SbvNmL4N7bjRnch56ciojIz5GRiwiI8+Y9sWY9QmZ0Jyp96uL9v6L0wap9biY9qiq95q197Wr9g+89iojIh94Wiqi";
// V11版本
spreadNS.LicenseKey = "yhqa.smartcost.com.cn|yhqm.smartcost.com.cn|yhuat.smartcost.com.cn|yhum.smartcost.com.cn|yhyun.smartcost.com.cn|yhm.smartcost.com.cn|ym.smartcost.com.cn|qm.smartcost.com.cn|um.smartcost.com.cn|civil.smartcost.com.cn|yun.smartcost.com.cn|cloud.smartcost.com.cn|qa.smartcost.com.cn|uat.smartcost.com.cn|zbqa.smartcost.com.cn|zbuat.smartcost.com.cn|jlqa.smartcost.com.cn|jluat.smartcost.com.cn|zb.smartcost.com.cn|jl.smartcost.com.cn|jladmin.smartcost.com.cn|jlqaadmin.smartcost.com.cn|jluatadmin.smartcost.com.cn,834986144349993#A0B5njRnch56cu4Wds86Yu46bj9Cdz36Y4JXYtNnLtFHLuNmLt36YuQ7cvNGdyFWbz9Sb9xibj9SbvNmL4N7bjRnch56cu4Ga9xibj9SbvNmL4N7bjRnch56cu8Wd9hWes86Yu46bj9Cdz36Y4JXYtNnLtVHa9xibj9SbvNmL4N7bjRnch56cuQXY5hWes86Yu46bj9Cdz36Y4JXYtNnLtFHa9xibj9SbvNmL4N7bjRnch56cuEWcolnI0IyctRkIsICuPWOrFWOkZmeicaut1S+r3iOsWa+mIWuqoaet0e+t5aOoPeuI0ISYONkIsIyM9kTO4MDN4EjN8kDNzgjI0ICZJJCL3V6csFmZ0IiczRmI1pjIs9WQisnOiQkIsISP3c6LJh4Sm3iWrx6QulkNzhVSMB7ZCBFdGdVMyw4bztiRalHMrg4SzclTGV5LqFUTQ9ETpdTbVp7KwgFcw8mRUhDWOV5QMlWcKBTMRhVQ7IHWEFHcoh5SiojITJCLyMTOwMDNxMzN0IicfJye&Qf35VfiMjY6cmI0IyQiwiIxEjL6ByUKBCZhVmcwNlI0IiTis7W0ICZyBlIsICOykzM6ADIwMzMwAjMwIjI0ICdyNkIsIibj9SbvNmL4N7bjRnch56cu8WatRWY4FWdspGLuNmLt36YuQ7cvNGdyFWbz9ibp5GZhFWcspGLuNmLt36YuQ7cvNGdyFWbz9ibp5GZhxmas86Yu46bj9Cdz36Y4JXYtNnLspGLuNmLt36YuQ7cvNGdyFWbz9iY0xibj9SbvNmL4N7bjRnch56cuQXY5xmas86Yu46bj9Cdz36Y4JXYtNnLhFHbqxibj9SbvNmL4N7bjRnch56cuQXY5Jmes86Yu46bj9Cdz36Y4JXYtNnLhFnY0xibj9SbvNmL4N7bjRnch56cuQXY5xibj9SbvNmL4N7bjRnch56cuEWcs86Yu46bj9Cdz36Y4JXYtNnLkV7bsNGLuNmLt36YuQ7cvNGdyFWbz9ib5lHLuNmLt36YuQ7cvNGdyFWbz9CbpZXajxibj9Sbvlmn0";

// 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.allowContextMenu = false;
        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.copy.execute;
        spreadNS.Commands.cut.execute = function (context, options, isUndo) {
            const 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();
        if (sheet.isEditing()) sheet.endEdit(true);
        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);
            SpreadJsObj.reloadRowsBackColor(sheet, [selections[0].row, range.row]);
        }
        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;
            }
        });
    },
    selChangedRefreshBackColor: function (sheet) {
        sheet.bind(spreadNS.Events.SelectionChanged, function (e, info) {
            const rows = [];
            if (info.oldSelections && info.oldSelections[0]) rows.push(info.oldSelections[0].row);
            if (info.newSelections && info.newSelections[0]) rows.push(info.newSelections[0].row);
            if (rows.length > 0) SpreadJsObj.reloadRowsBackColor(info.sheet, rows);
        });
    },
    /**
     * 获取写入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.hAlign = 1;
            vStyle.locked = false;
            sheet.setDefaultStyle(vStyle, spreadNS.SheetArea.rowHeader);
        }
        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 || !sheet.zh_setting.cols) { 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;
        }
    },
    reinitSheetHeader: function (sheet) {
        this.beginMassOperation(sheet);
        if (sheet.zh_setting.headColWidth) {
            for (const [i, w] of sheet.zh_setting.headColWidth.entries()) {
                sheet.setColumnWidth(i, w, spreadNS.SheetArea.rowHeader);
            }
        }
        this._initSheetHeader(sheet);
        this.endMassOperation(sheet);
    },
    _rememberColWidth: function (sheet) {
        sheet.bind(spreadNS.Events.ColumnWidthChanged, function (e, info) {
            if (!info.sheet.zh_setting) return;
            const setting = info.sheet.zh_setting;
            if (!setting.localCache || !setting.localCache.key) return;
            if (!setting.localCache.colWidthCache) return;
            const cache = setting.localCache.colWidthCache;

            for (const col of info.colList) {
                const colSetting = setting.cols[col];
                if (colSetting && colSetting.field)
                    cache[colSetting.field] = info.sheet.getColumnWidth(col);
            }
            setLocalCache(setting.localCache.key + '-colWidth', JSON.stringify(cache));
        });
    },
    _loadCacheSetting: function (sheet, setting) {
        if (!setting.localCache || !setting.localCache.key) return;
        if (setting.localCache.colWidth) {
            const cache = getLocalCache(setting.localCache.key + '-colWidth');
            setting.localCache.colWidthCache = cache ? JSON.parse(cache) : {};
            for (const prop in setting.localCache.colWidthCache) {
                const col = _.find(setting.cols, {field: prop});
                if (col) col.width = setting.localCache.colWidthCache[prop];
            }
            this._rememberColWidth(sheet);
        }
    },
    /**
     * 初始化sheet, 设置sheet.zh_setting, 并初始化表头
     * @param {GC.Spread.Sheets.Worksheet} sheet
     * @param setting
     */
    initSheet: function (sheet, setting) {
        this.beginMassOperation(sheet);
        this._loadCacheSetting(sheet, setting);
        setting.pos = sheet.getParent().pos;
        if (!setting.tree) setting.tree = {};
        sheet.zh_setting = setting;
        if (sheet.zh_setting.headColWidth) {
            for (const [i, w] of sheet.zh_setting.headColWidth.entries()) {
                sheet.setColumnWidth(i, w, spreadNS.SheetArea.rowHeader);
            }
        }
        this._initSheetDeafult(sheet);
        this._initSheetHeader(sheet);
        sheet.setRowCount(sheet.zh_setting.emptyRows);
        sheet.extendCellType = {};
        sheet.borderLine = new spreadNS.LineBorder('#cccccc', spreadNS.LineStyle.thin);
        sheet.getRange(0, 0, sheet.getRowCount(), sheet.getColumnCount()).locked(setting.readOnly).setBorder(sheet.borderLine, {all: true});
        if (setting.selectedBackColor) {
            SpreadJsObj.selChangedRefreshBackColor(sheet);
        }
        this.endMassOperation(sheet);
    },
    reLoadSheetHeader: function (sheet) {
        if (sheet.zh_setting) {
            this.beginMassOperation(sheet);
            this._initSheetHeader(sheet);
            this.endMassOperation(sheet);
        }
    },
    _loadCellData: function (sheet, data, iRow, iCol) {
        if (!data || !sheet.zh_setting) return;

        const col = sheet.zh_setting.cols[iCol];
        const cell = sheet.getCell(iRow, iCol);

        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]);
        }

        let font = sheet.getDefaultStyle().font;
        if (col.font) {
            font = col.font;
        }
        if (sheet.zh_setting.tree.getFont && Object.prototype.toString.apply(sheet.zh_setting.tree.getFont) === "[object Function]") {
            font = sheet.zh_setting.tree.getFont(sheet, data, iRow, col, font);
        }
        cell.font(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.type === 'Number') {
            if (col.formatter) {
                cell.formatter(SpreadJsObj.Formatter.getNumberFormatter(col.formatter))
            } else {
                cell.formatter(SpreadJsObj.Formatter.getNumberFormatter('0.######'));
            }
        } else if (col.formatter) {
            cell.formatter(col.formatter);
        }

        cell.backColor(SpreadJsObj._getBackColor(sheet, data, iRow, col));

        cell.setBorder(sheet.borderLine, {all: true});
    },
    _getBackColor: function (sheet, data, row, col) {
        let backColor = sheet.getDefaultStyle().backColor;
        let sels = sheet.getSelections();
        if (sheet.zh_setting.selectedBackColor && sels && sels[0].row === row ) {
            return sheet.zh_setting.selectedBackColor;
        } else {
            if (sheet.zh_setting.tree.getColor && Object.prototype.toString.apply(sheet.zh_setting.tree.getColor) === "[object Function]") {
                backColor = sheet.zh_setting.tree.getColor(sheet, data, row, col, backColor);
            }
            if (sheet.zh_setting.getColor && Object.prototype.toString.apply(sheet.zh_setting.getColor) === "[object Function]") {
                backColor = sheet.zh_setting.getColor(sheet, data, row, col, backColor);
            }
            return backColor;
        }
    },
    _loadRowStyle: function (sheet, row) {
        sheet.zh_setting.cols.forEach(function (col, j) {
            const cell = sheet.getCell(row, j);

            if (col.font) {
                cell.font(col.font);
            }

            if (col.foreColor && Object.prototype.toString.apply(col.foreColor) !== "[object Function]") {
                cell.foreColor(col.foreColor);
            }

            if (col.readOnly && Object.prototype.toString.apply(col.readOnly) !== "[object Function]") {
                cell.locked(col.readOnly || sheet.zh_setting.readOnly || false);
            }
            cell.vAlign(1).hAlign(col.hAlign);

            if(col.type === 'Number') {
                if (col.formatter) {
                    cell.formatter(SpreadJsObj.Formatter.getNumberFormatter(col.formatter))
                } else {
                    cell.formatter(SpreadJsObj.Formatter.getNumberFormatter('0.######'));
                }
            } else if (col.formatter) {
                cell.formatter(col.formatter);
            }

            cell.setBorder(sheet.borderLine, {all: true});
        });
    },
    _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]);
            }

            let font = sheet.getDefaultStyle().font;
            if (col.font) {
                font = col.font;
            }
            if (sheet.zh_setting.tree.getFont && Object.prototype.toString.apply(sheet.zh_setting.tree.getFont) === "[object Function]") {
                font = sheet.zh_setting.tree.getFont(sheet, data, row, col, font);
            }
            cell.font(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.type === 'Number') {
                if (col.formatter) {
                    cell.formatter(SpreadJsObj.Formatter.getNumberFormatter(col.formatter))
                } else {
                    cell.formatter(SpreadJsObj.Formatter.getNumberFormatter('0.######'));
                }
            } else if (col.formatter) {
                cell.formatter(col.formatter);
            }

            cell.backColor(SpreadJsObj._getBackColor(sheet, data, row, col));

            cell.setBorder(sheet.borderLine, {all: true});
        });
    },
    _addActivePaintEvents: function (sheet, cellType) {
        if (!sheet.ActiveType) {
            sheet.ActiveType = [];
        }
        sheet.ActiveType.push(cellType);

        if (!sheet.AcitveRefresh) {
            sheet.bind(spreadNS.Events.LeaveCell, function (e, info) {
                const cellType = info.sheet.getCell(info.row, info.col).cellType();
                if (sheet.ActiveType.indexOf(cellType) >= 0) {
                    info.sheet.leaveCell = {row: info.row, col: info.col};
                } else {
                    delete info.sheet.leaveCell;
                }
            });
            sheet.bind(spreadNS.Events.EnterCell, function (e, info) {
                const cellType = info.sheet.getCell(info.row, info.col).cellType();
                if (sheet.ActiveType.indexOf(cellType) >= 0) {
                    info.sheet.repaint(info.sheet.getCellRect(info.row, info.col));
                }
                if (info.sheet.leaveCell) {
                    info.sheet.repaint(info.sheet.getCellRect(info.sheet.leaveCell.row, info.sheet.leaveCell.col));
                }
            });
            sheet.AcitveRefresh = true;
        }
    },
    _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.imageBtn) {
                sheet.extendCellType.imageBtn = this.CellType.getImageButtonCellType();
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.imageBtn);
        }
        if (colSetting.cellType === 'activeImageBtn') {
            if (!sheet.extendCellType.activeImageBtn) {
                sheet.extendCellType.activeImageBtn = this.CellType.getActiveImageButtonCellType();
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.activeImageBtn);
        }
        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();
                SpreadJsObj._addActivePaintEvents(sheet, sheet.extendCellType.unit);
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.unit);
        }
        if (colSetting.cellType === 'customizeCombo') {
            const cellKey = colSetting.cellTypeKey ? 'customizeCombo-' + colSetting.cellTypeKey : 'customizeCombo';
            if (!sheet.extendCellType[cellKey]) {
                sheet.extendCellType[cellKey] = this.CellType.getCustomizeComboCellType(colSetting.comboItems);
                SpreadJsObj._addActivePaintEvents(sheet, sheet.extendCellType[cellKey]);
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType[cellKey]);
        }
        if (colSetting.cellType === 'datepicker') {
            if (!sheet.extendCellType.datepicker) {
                sheet.extendCellType.datepicker = this.CellType.getDatePickerCellType();
                SpreadJsObj._addActivePaintEvents(sheet, sheet.extendCellType.datepicker);
            }
            sheet.getRange(-1, col, -1, 1).cellType(sheet.extendCellType.datepicker);
        }
        if (colSetting.formatter) {
            sheet.getRange(-1, col, -1, 1).formatter(colSetting.formatter);
            if(colSetting.type === 'Number') {
                if (col.formatter) {
                    sheet.getRange(-1, col, -1, 1).formatter(SpreadJsObj.Formatter.getNumberFormatter(colSetting.formatter));
                } else {
                    sheet.getRange(-1, col, -1, 1).formatter(SpreadJsObj.Formatter.getNumberFormatter('0.######'));
                }
            } else if (col.formatter) {
                cell.formatter(col.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' || sheet.zh_setting.readOnly);
            if (sortData) {
                // 单元格写入数据
                sortData.forEach(function (data, i) {
                    self._loadRowData(sheet, data, i);
                    sheet.setRowVisible(i, data.visible);
                });
                for (let iRow = sortData.length - 1; iRow < totalRow; iRow++) {
                    self._loadRowStyle(sheet, iRow);
                }
            }
            // 设置列单元格格式
            sheet.zh_setting.cols.forEach(function (col, j) {
                //if (!col.cellType) { return; }
                self._defineColCellType(sheet, j, col);
            });
            this.endMassOperation(sheet);
        } catch (err) {
            console.log(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);
        }
    },
    reloadRowsBackColor: function (sheet, rows) {
        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; }
                const data = sortData[row];
                for (const [iCol, col] of sheet.zh_setting.cols.entries()) {
                    sheet.getCell(row, iCol).backColor(SpreadJsObj._getBackColor(sheet, data, row, col));
                }
            };
            this.endMassOperation(sheet);
        } catch (err) {
            this.endMassOperation(sheet);
        }
    },
    reloadColData: function (sheet, col, count = 1) {
        const cols = [];
        for (let i = 0; i < count; i++) {
            cols.push(col+i);
        }
        this.reLoadColsData(sheet, cols);
    },
    /**
     * 重新加载部分列数据
     * @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 col of cols) {
                const iCol = sheet.zh_setting.cols.indexOf(col);
                // 清空原单元格数据
                sheet.clear(-1, iCol, -1, 1, spreadNS.SheetArea.viewport, spreadNS.StorageType.data);

                for (const [iRow, data] of sortData.entries()) {
                    this._loadCellData(sheet, data, iRow, iCol);
                }
            }
            this.endMassOperation(sheet);
        } catch (err) {
            console.log(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);
        SpreadJsObj.reloadRowsBackColor(sheet, [index, sels[0].row]);
        sheet.getParent().focus();
        sheet.showRow(index, spreadNS.VerticalPosition.center);
    },
    locateData: function (sheet, data) {
        if (!sheet.zh_data) { return }
        const index = sheet.zh_data.indexOf(data);
        const sels = sheet.getSelections();
        sheet.setSelection(index, sels[0].col, 1, 1);
        SpreadJsObj.reloadRowsBackColor(sheet, [index, sels[0].row]);
        sheet.getParent().focus();
        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);
                SpreadJsObj.reloadRowsBackColor(sheet, [0, ts.sel.row]);
            }
            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);
            }
        }
    },
    refreshSheetReadOnly: function (sheet) {
        this.beginMassOperation(sheet);
        if (sheet.zh_setting) {
            sheet.zh_setting.cols.forEach(function (col, i) {
                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);
            });
        }
        this.endMassOperation(sheet);
    },
    /**
     * 刷新列是否只读
     * @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 xOffset = 2 + 5;
            const indent = 20;
            const levelIndent = -5;
            const halfBoxLength = 5;
            const halfExpandLength = 3;
            const lineColor = '#515151';//'#b8b8b8';
            const expandBoxColor = '#434343';//'#808080';
            const dotLine = true;

            /**
             * 画一条点线段
             * @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 ? fillColor : 'white';
                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, style) {
                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, expandBoxColor, style.backColor);

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

            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 - 1) * indent + (node.level) * levelIndent + indent / 2 + xOffset;
                        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;
                                if (dotLine) {
                                    drawDotLine(canvas, centerX, centerY, Math.min(x1, x + w), centerY, lineColor);
                                } else {
                                    drawLine(canvas, centerX, centerY, Math.min(x1, x + w), centerY, lineColor);
                                }
                            }
                            // 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) {
                                    if (dotLine) {
                                        drawDotLine(canvas, centerX, centerY, centerX, y1, lineColor);
                                    } else {
                                        drawLine(canvas, centerX, centerY, centerX, y1, lineColor);
                                    }
                                } else {
                                    if (dotLine) {
                                        drawDotLine(canvas, centerX, y, centerX, y1, lineColor);
                                    } else {
                                        drawLine(canvas, centerX, y, centerX, y1, lineColor);
                                    }
                                }
                            }
                        }
                        // Draw Expand Box
                        if (!node.is_leaf) {
                            drawExpandBox(canvas, x, y, w, h, centerX, centerY, node.expanded, style);
                        }
                        // Draw Parent Line
                        if (showTreeLine) {
                            let parent = tree.getParent(node), parentCenterX = centerX - indent - levelIndent;
                            while (parent) {
                                if (!tree.isLastSibling(parent)) {
                                    if (parentCenterX < x + w) {
                                        if (dotLine) {
                                            drawDotLine(canvas, parentCenterX, y, parentCenterX, y + h, lineColor);
                                        } else {
                                            drawLine(canvas, parentCenterX, y, parentCenterX, y + h, lineColor);
                                        }
                                    }
                                }
                                parent = tree.getParent(parent);
                                parentCenterX -= (indent + levelIndent);
                            }
                        };
                        // 重定位x
                        const move = (node.level) * indent + (node.level) * levelIndent + xOffset;
                        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 - 1) * indent + (node.level) * levelIndent + indent / 2 + xOffset;
                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).html(text).css("top", pos.y + hitinfo.y + indent).css("left", pos.x + hitinfo.x + indent);
                        } else if (textWidth > maxHintWidth) {
                            $(div).html(text).css("top", pos.y + hitinfo.y + indent).css("left", pos.x + hitinfo.x - indent - maxHintWidth);
                        } else {
                            $(div).html(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, hitinfo);
                        }
                    }
                }
            };
            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
                };
            };
            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
                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 col = hitinfo.sheet.zh_setting.cols[hitinfo.col];
                const img = this.getImage(hitinfo.sheet, hitinfo.row, hitinfo.col);
                const halfX = img.width / 2, halfY = img.height / 2;
                const indent = col.indent ? col.indent : 10;
                const centerX = hitinfo.cellStyle.hAlign === spreadNS.HorizontalAlign.right
                    ? hitinfo.cellRect.x + hitinfo.cellRect.width - indent -halfX
                    : 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(active);
                    hitinfo.sheet.repaint(hitinfo.cellRect);
                }
            };
            proto.processMouseUp = function (hitinfo) {
                const col = hitinfo.sheet.zh_setting.cols[hitinfo.col];
                const img = this.getImage(hitinfo.sheet, hitinfo.row, hitinfo.col);
                const halfX = img.width / 2, halfY = img.height / 2;
                const indent = col.indent ? col.indent : 10;
                const centerX = hitinfo.cellStyle.hAlign === spreadNS.HorizontalAlign.right
                    ? hitinfo.cellRect.x + hitinfo.cellRect.width - indent -halfX
                    : 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, hitinfo);
                        const cell = hitinfo.sheet.getCell(hitinfo.row, hitinfo.col);
                        cell.tag(null);
                        hitinfo.sheet.repaint(hitinfo.cellRect);
                    }
                }
            };
            proto.processMouseMove = function (hitinfo) {
                const col = hitinfo.sheet.zh_setting.cols[hitinfo.col];
                const img = this.getImage(hitinfo.sheet, hitinfo.row, hitinfo.col);
                const halfX = img.width / 2, halfY = img.height / 2;
                const indent = col.indent ? col.indent : 10;
                const centerX = hitinfo.cellStyle.hAlign === spreadNS.HorizontalAlign.right
                    ? hitinfo.cellRect.x + hitinfo.cellRect.width - indent -halfX
                    : 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();
        },
        getActiveImageButtonCellType: function () {
            let show = 1, hover = 2, 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 = null;
                const cell = sheet.getCell(iRow, iCol), tag = cell.tag();
                if (tag === show) {
                    imgSource = col.normalImg;
                } else 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.getActiveImage = function(sheet, iRow, iCol) {
                const col = sheet.zh_setting.cols[iCol];

                const sortData = SpreadJsObj.getSortData(sheet);
                const data = sortData ? sortData[iRow] : null;
                let showImage = true;
                if (col.showImage && Object.prototype.toString.apply(col.showImage) === "[object Function]") {
                    showImage = col.showImage(data);
                }
                const img = showImage ? this.getImage(sheet, iRow, iCol) : null;
                return [col, img];
            };
            proto.paint = function (canvas, value, x, y, w, h, style, options) {
                const [col, img] = this.getActiveImage(options.sheet, options.row, options.col);

                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 + indent, 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
                };
            };
            proto.processMouseEnter = function (hitinfo) {
                const col = hitinfo.sheet.zh_setting.cols[hitinfo.col];
                // Drawing Image
                if (col.normalImg) {
                    const cell = hitinfo.sheet.getCell(hitinfo.row, hitinfo.col);
                    cell.tag(show);
                    hitinfo.sheet.repaint(hitinfo.cellRect);
                }
            };
            proto.processMouseLeave = function (hitinfo) {
                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 [col, img] = this.getActiveImage(hitinfo.sheet, hitinfo.row, hitinfo.col);
                if (!img) return;

                const halfX = img.width / 2, halfY = img.height / 2;
                const indent = col.indent ? col.indent : 10;
                const centerX = hitinfo.cellStyle.hAlign === spreadNS.HorizontalAlign.right
                    ? hitinfo.cellRect.x + hitinfo.cellRect.width - indent -halfX
                    : 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(active);
                    hitinfo.sheet.repaint(hitinfo.cellRect);
                }
            };
            proto.processMouseUp = function (hitinfo) {
                const [col, img] = this.getActiveImage(hitinfo.sheet, hitinfo.row, hitinfo.col);
                if (!img) return;

                const halfX = img.width / 2, halfY = img.height / 2;
                const indent = col.indent ? col.indent : 10;
                const centerX = hitinfo.cellStyle.hAlign === spreadNS.HorizontalAlign.right
                    ? hitinfo.cellRect.x + hitinfo.cellRect.width - indent -halfX
                    : 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, hitinfo);
                        const cell = hitinfo.sheet.getCell(hitinfo.row, hitinfo.col);
                        cell.tag(hover);
                        hitinfo.sheet.repaint(hitinfo.cellRect);
                    }
                }
            };
            proto.processMouseMove = function (hitinfo) {
                const [col, img] = this.getActiveImage(hitinfo.sheet, hitinfo.row, hitinfo.col);
                if (!img) return;

                const halfX = img.width / 2, halfY = img.height / 2;
                const indent = col.indent ? col.indent : 10;
                const centerX = hitinfo.cellStyle.hAlign === spreadNS.HorizontalAlign.right
                    ? hitinfo.cellRect.x + hitinfo.cellRect.width - indent -halfX
                    : 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(show);
                        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.processMouseEnter = function (hitinfo) {
                hitinfo.sheet.repaint(hitinfo.cellRect);
            };
            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;
        },
        /**
         * 获取 日期选择 CellType
         * @returns {DatePickerCellType}
         */
        getDatePickerCellType: function () {
            let datepicker;

            const DatePickerCellType = function () {};
            DatePickerCellType.prototype = new spreadNS.CellTypes.Text();
            const proto = DatePickerCellType.prototype;
            proto.createEditorElement = function (context) {
                return document.createElement("input");
            };
            proto.activateEditor = function (editorContext, cellStyle, cellRect, context) {
                const self = this;
                if (editorContext) {
                    const $editor = $(editorContext);
                    //spreadNS.CellTypes.Text.prototype.activateEditor.apply(this, arguments);
                    $editor.css("position", "absolute");
                    datepicker = $editor.datepicker({
                        language: 'zh',
                        dateFormat: 'yyyy-MM-DD'
                    }).data('datepicker');
                    datepicker.show();
                }
            };
            proto.deactivateEditor = function (editorContext, context) {
                datepicker.hide();
                datepicker.destroy();
                spreadNS.CellTypes.Base.prototype.deactivateEditor.apply(this, arguments);
            };
            proto.setEditorValue = function (editor, value, context) {
                if (datepicker) {
                    datepicker.selectDate(value);
                }
            };
            proto.getEditorValue = function (editor, context) {
                return datepicker ? datepicker.focused : null;
            };
            proto.updateEditor = function (editorContext, cellStyle, cellRect, context) {
                if (editorContext) {
                    const $editor = $(editorContext);
                    $editor.css("width", cellRect.width + 1);
                    $editor.css("height", cellRect.height + 3);
                }
            };
            return new DatePickerCellType();
        }
    },

    Formatter: {
        baseNumberFormatter: function () {
            const formatter = function () {};
            formatter.prototype = new GC.Spread.Formatter.FormatterBase();
            const proto = formatter.prototype;
            /**
             * 格式化数字显示方式
             * 用法
             * formatNumber(12345.999,'#,##0.00');
             * formatNumber(12345.999,'#,##0.##');
             * formatNumber(123,'000000');
             * @param num
             * @param pattern
             */
            proto.formatNum = function (num, pattern) {
                var strarr = num?num.toString().split('.'):['0'];
                var fmtarr = pattern?pattern.split('.'):[''];
                var retstr='';

                // 整数部分
                var str = strarr[0];
                var fmt = fmtarr[0];
                var i = str.length-1;
                var comma = false;
                for(var f=fmt.length-1;f>=0;f--){
                    switch(fmt.substr(f,1)){
                        case '#':
                            if(i>=0 ) retstr = str.substr(i--,1) + retstr;
                            break;
                        case '0':
                            if(i>=0) retstr = str.substr(i--,1) + retstr;
                            else retstr = '0' + retstr;
                            break;
                        case ',':
                            comma = true;
                            retstr=','+retstr;
                            break;
                    }
                }
                if(i>=0){
                    if(comma){
                        var l = str.length;
                        for(;i>=0;i--){
                            retstr = str.substr(i,1) + retstr;
                            if(i>0 && ((l-i)%3)==0) retstr = ',' + retstr;
                        }
                    }
                    else retstr = str.substr(0,i+1) + retstr;
                }

                retstr = retstr+'.';
                // 处理小数部分
                str=strarr.length>1?strarr[1]:'';
                fmt=fmtarr.length>1?fmtarr[1]:'';
                i=0;
                for(var f=0;f<fmt.length;f++){
                    switch(fmt.substr(f,1)){
                        case '#':
                            if(i<str.length) retstr+=str.substr(i++,1);
                            break;
                        case '0':
                            if(i<str.length) retstr+= str.substr(i++,1);
                            else retstr+='0';
                            break;
                    }
                }
                return retstr.replace(/^,+/,'').replace(/\.$/,'');
            };
            proto.format = function (obj, formattedData) {
                if (this.pattern) {
                    return this.formatNum(obj, this.pattern);
                } else {
                    return obj;
                }
            };
            return new formatter();
        },
        _numFormatter: [],
        getNumberFormatter(pattern) {
            let formatter = this._numFormatter.find(function (f) {
                return f.pattern === pattern;
            });
            if (!formatter) {
                formatter = this.baseNumberFormatter();
                formatter.pattern = pattern;
                this._numFormatter.push(formatter);
            }
            return formatter;
        },
    },

    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,
        }
    })(),
};