// ==UserScript==
// @name         在线表格增强功能
// @namespace    http://tampermonkey.net/
// @version      1.2.2
// @description  在线表格增强插件
// @author       pianpianluoye
// @include        *://*suip*
// @icon         https://www.google.com/s2/favicons?sz=64&domain=sinopec.com
// @grant        none
// @license      AGPL-3.0-or-later
// @downloadURL none
// ==/UserScript==

(function() {
    'use strict';

    const lockColor = "#f0f3fb"
    const unlockColor = "#fff"
    const container = "gc-container"
    const containerCls = "js-spread-container"
    const containerDesign = "gc-designer-container"
    const containerDesignCls = "designer-container"
    let isDesigner = false
    let oldColor = []


    const cssText = `
        #appPlug {
        padding: 2px 10px;
        background-color: #fff;
        border-radius: 20px;
        box-shadow: 0 0 5px #ccc;
        position:absolute;
        width:100%;
        bottom:-50px;
        display:block;
        z-index:9999999999999999999;
      }
      .toolbarop1 {
        opacity:1;
        transition: 0.5s ease !important;
      }
      .toolbarop0{
        opacity:0;
        transition: 0.5s ease !important;
      }
      .toolbardisp {
        bottom:1px !important;
      }
      .toolbarhide {
        bottom:-50px !important;
      }
      #appPlug > input {
        width: 90px;
        border-radius: 4px;
        border: 1px solid #ccc;
        margin: 2px;
        padding: 5px;
        font-size: 12px;
        outline: none;
      }
      .dvdiv .dvtable table,
      .dvdiv .dvtable td,
      .dvdiv .dvtable th {
        text-align: center;
        border-collapse: collapse;
        border: 1px solid #ccccff;
        padding: 5px;
        font-size: 12px;
        color: rgb(0, 0, 0, 0.7);
      }
      .dvdiv .dvtable th {
        background-color: #cccccc;
        color: #fff;
      }
      .dvdiv .dvtable {
        width: 100%;
      }
      .dvdiv .dvtable tr:hover {
        background-color: rgb(204, 204, 204, 0.3);
      }
      .dvdiv {
        max-width:91%;
        border-radius:6px;
        padding:5px;
        top: 100px !important;
        left: 5% !important;
        background-color: #fff;
        box-shadow: 0 0 5px;
        position:fixed;
        z-index:9999999999999;
      }
      #toolbar-container{
        display:flex;
        justify-content: center;
        align-items: center;
        position: relative;
      }
      @keyframes toolbar-show {
        0% {
          opacity: 0;
        }
        100% {
          opacity: 1;
        }
      }
      `
    const msg = `
            <div class="ant-message-notice">
            <div class="ant-message-notice-content">
                <div class="ant-message-custom-content ant-message-success">
                    <i aria-label="icon: check-circle" class="anticon anticon-check-circle">
                        <svg viewBox="64 64 896 896" data-icon="check-circle" width="1em" height="1em" fill="currentColor" aria-hidden="true" focusable="false" class="">
                            <path d="M512 64C264.6 64 64 264.6 64 512s200.6 448 448 448 448-200.6 448-448S759.4 64 512 64zm193.5 301.7l-210.6 292a31.8 31.8 0 0 1-51.7 0L318.5 484.9c-3.8-5.3 0-12.7 6.5-12.7h46.9c10.2 0 19.9 4.9 25.9 13.3l71.2 98.8 157.2-218c6-8.3 15.6-13.3 25.9-13.3H699c6.5 0 10.3 7.4 6.5 12.7z"></path>
                        </svg>
                    </i>
                    <span>右键菜单开启</span>
                </div>
            </div>
        </div>
    `
    const myStyle = document.createElement("style");
    myStyle.textContent = cssText;

    let addCount = 1

    const enumTypes = {
        0:"任何值",
        1:"整数",
        2:"小数",
        3:"序列",
        4:"日期",
        5:"时间",
        6:"文本长度",
        7:"自定义"
    }

    const enumOperators = {
        0:"等于",
        1:"不等于",
        2:"大于",
        3:"大于等于",
        4:"小于",
        5:"小于等于",
        6:"介于",
        7:"未介于"
    }

    const enumErrorStyle = {
        0:"停止",
        1:"警告",
        2:"信息"
    }

    const enumHighlightType = {
        0:"圆圈",
        1:"折角",
        2:"图标"
    }

    const enumTrueFalse = {
        true:"是",
        false:"否"
    }

    const toolbar = `
          <div id = "toolbar-container">
            <div id="btn1" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">解锁工具栏</div>
            <div id="btn2" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">锁定工具栏</div>
            <div id="btn3" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">解锁单元格</div>
            <div id="btn4" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">锁定单元格</div>
            <div id="btn5" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">数据有效性</div>
            <div id="btn6" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">清除校验</div>
            <div id="btn7" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">显示值模式</div>
            <div id="btn8" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">清除单元格值</div>
            <div id="btn9" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">清除公式</div>
            <div id="btn10" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">显示公式</div>
            <div id="btn11" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">隐藏行列</div>
            <div id="btn12" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">取消隐藏</div>
            <div id="btn13" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">向后插入列</div>
            <div id="btn14" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">向下插入行</div>
            <input maxlength="4" id="inp1" placeholder="请输入增加数" style="width: 100px;" class = "ant-input"/>
            <div id="btn15" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">显示表头</div>
            <div id="btn16" class="ant-btn ant-btn-primary ant-btn-sm" style="margin:0 5px">指标维度</div>
          </div>
    `

    const appPlug = document.createElement("div");
    appPlug.innerHTML = toolbar;
    appPlug.id = "appPlug"
    appPlug.classList.add("toolbarop0")
    appPlug.classList.add("toolbarhide")
    document.body.appendChild(appPlug);

    const btn1 = document.getElementById("btn1");
    const btn2 = document.getElementById("btn2");
    const btn3 = document.getElementById("btn3");
    const btn4 = document.getElementById("btn4");
    const btn5 = document.getElementById("btn5");
    const btn6 = document.getElementById("btn6");
    const btn7 = document.getElementById("btn7");
    const btn8 = document.getElementById("btn8");
    const btn9 = document.getElementById("btn9");
    const btn10 = document.getElementById("btn10");
    const btn11 = document.getElementById("btn11");
    const btn12 = document.getElementById("btn12");
    const btn13 = document.getElementById("btn13");
    const btn14 = document.getElementById("btn14");
    const inp1 = document.getElementById("inp1");
    const btn15 = document.getElementById("btn15");
    inp1.maxLength = 4
    const btn16 = document.getElementById("btn16");

    btn1.addEventListener("click", () => {
        unlockToolBar()
    });
    btn2.addEventListener("click", () => {
        lockToolBar()
    });

    btn3.addEventListener("click", () => {
        lockCell(false)
    });

    btn4.addEventListener("click", () => {
        lockCell(true)
    });

    btn5.addEventListener("click", () => {
        getDataValidator()
    });

    btn6.addEventListener("click", () => {
        cleanDataValidator()
    });

    btn7.addEventListener("click", () => {
        try{
            const inputBarVal = document.getElementsByClassName("spread-tool")[0].querySelector("#inputBarVal")
            if(!inputBarVal){
                if(!isDesigner){
                    showValueAndFormula()
                }
                showPrecedents()
            }
        }
        catch(error){

        }
    });

    btn8.addEventListener("click", () => {
        setValue(null)
    });

    btn9.addEventListener("click", () => {
        setFormula(undefined)
    });

    btn10.addEventListener("click", () => {
        isShowFormula()
    });

    btn11.addEventListener("click", () => {
        setRowColVisible(false)
    });

    btn12.addEventListener("click", () => {
        setRowColVisible(true)
    });

    btn13.addEventListener("click", () => {
        insertCol(addCount)
    });

    btn14.addEventListener("click", () => {
        insertRow(addCount)
    });

    inp1.addEventListener("input", () => {
        addCount = inp1.value = inp1.value.replace(/[^0-9]|^0/g, "");
        if(!addCount){
            addCount = 1
        }
    });

    btn15.addEventListener("click", () => {
        isShowTableHeader()
    })

    btn16.addEventListener("click", () => {
        showIndiDim()
    })

    const btnplugin = document.createElement("span");
    btnplugin.classList.add("ant-dropdown-trigger");
    btnplugin.classList.add("action");
    btnplugin.innerHTML = `
        <i class="anticon">
        <svg width="1em" height="1em" fill="currentColor" aria-hidden="true" focusable="false" class="">
            <use xlink:href="#iconsetting"></use>
        </svg>
        </i>`;

    let mtoggle = true
    let timeoutId
    btnplugin.addEventListener("dblclick", (e) => {
        clearTimeout(timeoutId)
        if (mtoggle) {
            appPlug.classList.remove("toolbarhide")
            appPlug.classList.add("toolbardisp")
            appPlug.classList.remove("toolbarop0")
            appPlug.classList.add("toolbarop1")
            mtoggle = false
        }
        else{
            appPlug.classList.remove("toolbardisp")
            appPlug.classList.add("toolbarhide")
            appPlug.classList.remove("toolbarop1")
            appPlug.classList.add("toolbarop0")
            mtoggle = true
        }
    },false)

    btnplugin.addEventListener("click", (e) => {
        clearTimeout(timeoutId);
        timeoutId = setTimeout(() => {
            getSp()
            if(!isDesigner){
                addCustomMenu()
                let parentDiv = document.querySelector(".ant-message")
                let span
                let div
                let msgTimer
                if(parentDiv){
                    span = parentDiv.querySelector("span")
                    if(span){
                        div = span.querySelector("div")
                        if(div){
                            span.removeChild(div)
                        }
                        div = document.createElement("div")
                        div.innerHTML = msg
                        span.appendChild(div)
                        div.addEventListener("mouseover", function() {
                            clearTimeout(msgTimer)
                        })
                        div.addEventListener("mouseout", function() {
                            msgTimer = setTimeout(function() {
                                span.removeChild(div);
                            }, 2000)
                        })
                    }
                }
                msgTimer = setTimeout(function() {
                    span.removeChild(div)
                }, 2000)
            }
        }, 300)
    },false)

    setToolBtn(0)

    function setToolBtn(reTryCounts){
        if(reTryCounts <= 50){
            setTimeout(() => {
                let toolbar = document.getElementsByClassName("content-box")[0];
                if(toolbar){
                    let doc = document.head || document.documentElement
                    doc.appendChild(myStyle)
                    toolbar.prepend(btnplugin)
                    reTryCounts = 0
                }
                else{
                    reTryCounts += 1
                    setToolBtn(reTryCounts)
                }
            }, 2000)
        }
    }

    function unlockToolBar(){
        const inputBar = document.getElementsByClassName("spread-tool")[0].querySelector("input")
        inputBar.className ="ant-input"
        inputBar.removeAttribute("disabled")
    }

    function lockToolBar(){
        const inputBar = document.getElementsByClassName("spread-tool")[0].querySelector("input")
        inputBar.classList.add ="ant-input-disabled"
        inputBar.setAttribute("disabled", "disabled")
    }

    function setValue(para){
        const sp = getSp()
        stopUpdate(sp)
        let sh = sp.getActiveSheet()
        let selCells = sh.getSelections()
        setToolBarValue(sh,!para)
        selCells.forEach((e) => {
            sh.getRange(e.row,e.col,e.rowCount,e.colCount).value(para)
        })
        startUpdate(sp)
    }

    function setFormula(para){
        const sp = getSp()
        stopUpdate(sp)
        let sh = sp.getActiveSheet()
        let selCells = sh.getSelections()
        setToolBarValue(sh,!para)
        selCells.forEach((e) => {
            sh.setArrayFormula(e.row,e.col,e.rowCount,e.colCount,para,null)
        })
        startUpdate(sp)
    }

    function cleanDataValidator(){
        const sp = getSp()
        let sh = sp.getActiveSheet()
        let selCells = sh.getSelections()
        selCells.forEach((e) => {
            sh.setDataValidator(e.row,e.col,e.rowCount,e.colCount,null)
        })
    }

    function getDataValidator(){
        const sp = getSp()
        let sh = sp.getActiveSheet()
        let selCells = sh.getSelections()
        let arr = []
        let eles = document.getElementsByClassName("dvdiv")
        for(let i = 0; i < eles.length;i++){
            eles[i].remove()
        }
        const ele = document.createElement("div");

        selCells.forEach((e) => {
            for(let i = e.row; i < e.row + e.rowCount;i++){
                for(let j = e.col; j < e.col + e.colCount;j++){
                    let dvObj = {}
                    dvObj.dv = sh.getDataValidator(i,j)
                    if(dvObj.dv){
                        dvObj.rng = getRangeAddress(sh.getCell(i,j))
                        dvObj.inputTitle = dvObj.dv.inputTitle()
                        dvObj.inputMessage = dvObj.dv.inputMessage()
                        dvObj.errorTitle = dvObj.dv.errorTitle()
                        dvObj.errorMessage = dvObj.dv.errorMessage()
                        dvObj.errorStyle = enumErrorStyle[dvObj.dv.errorStyle()]
                        dvObj.dvtype = enumTypes[dvObj.dv.type()]
                        dvObj.comparisonOperator = enumOperators[dvObj.dv.comparisonOperator()]
                        dvObj.highlightStyle = enumHighlightType[dvObj.dv.highlightStyle().type]
                        dvObj.ignoreBlank = enumTrueFalse[dvObj.dv.ignoreBlank()]
                        dvObj.inCellDropdown = enumTrueFalse[dvObj.dv.inCellDropdown()]
                        if(dvObj.dv.type()!==7){
                            dvObj.formulaStr = ""
                        }else{
                            dvObj.formulaStr = dvObj.dv.condition().getFormulaString()? dvObj.dv.condition().getFormulaString() : ""
                        }
                        try {
                            dvObj.validList = dvObj.dv.getValidList() ? dvObj.dv.getValidList(): ""
                        } catch (error) {
                            dvObj.validList = dvObj.dv.condition().getFormulaString() ? dvObj.dv.condition().getFormulaString() : ""
                            dvObj.formulaStr = ""
                        }
                        dvObj.value1 = dvObj.formulaStr || dvObj.validList ? "" :dvObj.dv.value1()
                        dvObj.value2 = dvObj.formulaStr || dvObj.validList ? "" :dvObj.dv.value2()
                        arr.push(dvObj)
                    }
                }
            }
        })
        if(arr.length){
            ele.classList.add("dvdiv");
            ele.innerHTML = `
        <div style="margin:5px 10px;text-align: right;">
          <button id="dvclose" class = "ant-btn ant-btn-primary">关闭</button>
        </div>
        <div style="overflow-y: scroll;height: 500px;margin:0 10px 10px 10p;border: 1px solid #ccc">
        <table class="dvtable">
         <thead>
          <tr>
            <th>序号</th>
            <th>单元格</th>
            <th>提示标题</th>
            <th>提示消息</th>
            <th>类型</th>
            <th>操作符</th>
            <th>忽略空值</th>
            <th>提供下拉按钮</th>
            <th>错误类型</th>
            <th>错误标题</th>
            <th>错误消息</th>
            <th>高亮样式</th>
            <th>最小值</th>
            <th>最大值</th>
            <th>序列</th>
            <th>公式</th>
          </tr>
        </thead>
        <tbody>
        </tbody>
      </table>
      <div></div>
        `
        let eletable = ele.querySelector("tbody")
        for(let i = 0; i< arr.length;i++){
            let tmpDom = document.createElement("tr")
            tmpDom.innerHTML = `
              <td>${i+1}</td>
              <td>${arr[i].rng}</td>
              <td>${arr[i].inputTitle}</td>
              <td>${arr[i].inputMessage}</td>
              <td>${arr[i].dvtype}</td>
              <td>${arr[i].comparisonOperator}</td>
              <td>${arr[i].ignoreBlank}</td>
              <td>${arr[i].inCellDropdown}</td>
              <td>${arr[i].errorStyle}</td>
              <td>${arr[i].errorTitle}</td>
              <td>${arr[i].errorMessage}</td>
              <td>${arr[i].highlightStyle}</td>
              <td>${arr[i].value1}</td>
              <td>${arr[i].value2}</td>
              <td>${arr[i].validList}</td>
              <td>${arr[i].formulaStr}</td>`
            eletable.appendChild(tmpDom)
        }
            let btnclose = ele.querySelector("#dvclose")
            if(btnclose){
                btnclose.addEventListener("click", () => {
                    ele.style.display = "none"
                })
            }
            document.body.appendChild(ele);
        }
    }

    function getRangeAddress(rng){
        let addressValue = GC.Spread.Sheets.CalcEngine.rangeToFormula(rng, 0, 0, GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.allRelative)
        return addressValue
    }

    function lockCell(para){
        const sp = getSp()
        stopUpdate(sp)
        let sh = sp.getActiveSheet()
        let selCells = sh.getSelections()
        let bgColor = unlockColor
        if(para){
            bgColor = lockColor
        }
        selCells.forEach((e) => {
            sh.getRange(e.row,e.col,e.rowCount,e.colCount).locked(para)
            sh.getRange(e.row,e.col,e.rowCount,e.colCount).backColor(bgColor)
        })
        startUpdate(sp)
    }

    function isShowFormula(){
        const sp = getSp()
        let sh = sp.getActiveSheet()
        sh.options.showFormulas = !sh.options.showFormulas
        if(sh.options.showFormulas){
            btn10.innerText = "隐藏公式"
        }
        else{
            btn10.innerText = "显示公式"
        }
    }

    function setRowColVisible(para){
        const sp = getSp()
        stopUpdate(sp)
        let sh = sp.getActiveSheet()
        let selCells = sh.getSelections()
        selCells.forEach((e) => {
            if((e.row + e.col) == -2){
                sh.getRange(e.row,0,1,sh.getColumnCount()).visible(para)
                sh.getRange(0,e.col,sh.getRowCount(),1).visible(para)
            }
            else {
                sh.getRange(e.row,e.col,e.rowCount,e.colCount).visible(para)
            }
        })
        startUpdate(sp)
    }

    function insertRow(para){
        const sp = getSp()
        stopUpdate(sp)
        let sh = sp.getActiveSheet()
        let selCells = sh.getSelections()
        let count = parseInt(para)
        selCells.forEach((e) => {
            sh.addRows(e.row + 1,count)
        })
        startUpdate(sp)
    }

    function insertCol(para){
        const sp = getSp()
        stopUpdate(sp)
        let sh = sp.getActiveSheet()
        let selCells = sh.getSelections()
        let count = parseInt(para)
        selCells.forEach((e) => {
            sh.addColumns(e.col + 1,count)
        })
        startUpdate(sp)
    }

    function showValueAndFormula(){
        const sp = getSp()
        const inputBar = document.getElementsByClassName("spread-tool")[0].querySelector("input")
        let inputBarVal = document.getElementsByClassName("spread-tool")[0].querySelector("#inputBarVal")
        inputBar.parentNode.style.display = 'flex'
        let sh = sp.getActiveSheet()
        inputBar.style.width = '50%'
        if(!inputBarVal){
            inputBarVal = inputBar.cloneNode()
            inputBarVal.id = 'inputBarVal'
            inputBarVal.style.width = '50%'
            inputBarVal.classList.add('ant-input-disabled')
            inputBarVal.disabled = 'disabled'
            inputBar.parentNode.appendChild(inputBarVal)
        }
        setToolBarValue(sh,false)
        sh.bind(GC.Spread.Sheets.Events.SelectionChanged,function (sender,rng) {
            setToolBarValue(sh,false)
        })
    }

    function turnOnFormulaRef(){
        const sp = getSp()
        let sh = sp.getActiveSheet()
        sh.bind(GC.Spread.Sheets.Events.SelectionChanged,function (sender,rng) {
            showPrecedents(sp,sh,rng)
        })
    }

    function showPrecedents(sp,sh,rng){
        let newChildNodes = []
        let oldChildNodes = []
        let currChildNodes = []
        let colorInfo = {}
        try {
            newChildNodes = sh.getPrecedents(rng.newSelections[0].row, rng.newSelections[0].col)
        } catch (error) {

        }

        try {
            oldChildNodes = sh.getPrecedents(rng.oldSelections[0].row, rng.oldSelections[0].col)
        } catch (error) {

        }

        if(oldColor.length > 0){
            for(let i = 0;i<oldColor.length;i++){
                let tarSh = sp.getSheetFromName(oldColor[i].sh)
                try{
                    tarSh.getRange(oldColor[i].row,oldColor[i].col,oldColor[i].rowCount,oldColor[i].colCount).backColor(oldColor[i].color)
                }
                catch(error){

                }
            }
            oldColor = []
        }
        if (newChildNodes.length > 0) {
            newChildNodes.forEach((e)=>{
                oldColor.push({row:e.row,col:e.col,rowCount:e.rowCount,colCount:e.colCount,color:sh.getCell(e.row,e.col).backColor(),sh:e.sheetName})
                let tarSh = sp.getSheetFromName(e.sheetName)
                tarSh.getRange(e.row,e.col,e.rowCount,e.colCount).backColor('red')
            })
        }
    }

    function setToolBarValue(sh,isClean){
        if (isDesigner){return}
        const inputBar = document.getElementsByClassName("spread-tool")[0].querySelector("input")
        const inputBarVal = document.getElementsByClassName("spread-tool")[0].querySelector("#inputBarVal")
        let row = sh.getActiveRowIndex()
        let col = sh.getActiveColumnIndex()
        if(sh.hasFormula(row,col)){
            isClean ? inputBar.value = "" : inputBar.value = sh.getFormula(row,col)
            if(inputBarVal){
                inputBar.style.width = '50%'
                inputBarVal.value = sh.getValue(row,col)
                inputBarVal.style.width = '50%'
            }
        }else{
            isClean ? inputBar.value = "" : inputBar.value = sh.getValue(row,col)
            if(inputBarVal){
                inputBar.style.width = '50%'
                inputBarVal.style.width = '50%'
                inputBarVal.value = inputBar.value
            }
        }
    }

    function isShowTableHeader(){
        const sp = getSp()
        let sh = sp.getActiveSheet()
        let tables = sh.tables.all()
        btn15.innerText = "显示表头"
        for(let i = 0; i < tables.length; i++){
            let startRow = sh.tables.all()[i].startRow()
            if(tables[i].hasHeadersRow()){
                tables[i].showHeader(false)
                btn15.innerText = "显示表头"
                sh.deleteRows(startRow,1)
            }else{
                sh.addRows(startRow,1)
                tables[i].showHeader(true)
                btn15.innerText = "隐藏表头"
            }
        }
    }

    function showIndiDim(){
        const sp = getSp()
        let reportInfoVue = document.querySelector('.router-alive').__vue__.$children
        let reportInfo
        for(let i = 0; i <reportInfoVue.length; i++){
            if(reportInfoVue[i].hasOwnProperty("record")){
                reportInfo = reportInfoVue[i].record
                if(reportInfoVue[i].record !== ""){break}
            }
        }
        if(!reportInfo){return}
        let uri = "/sctj/api/companyReportVersion/selectAllSheet"
        let xhr = new XMLHttpRequest()
        xhr.withCredentials = true
        let data = JSON.stringify({
            "reportCodeEq":reportInfo.reportCode,
            "orgCodeEq": reportInfo.orgCode,
            "versionEq":reportInfo.version
        })
        let token = getCookie("Spss-Prod-Access-Token") || getCookie("Spss-Test-Access-Token") || getCookie("Spss-Train-Access-Token")
        let refreshToken = getCookie("Spss-Prod-Refresh-Token") || getCookie("Spss-Test-Refresh-Token") || getCookie("Spss-Train-Refresh-Token")
        let resultData={}
        let dataList = []
        if(sp){
            let sh = sp.getActiveSheet()
            let selCells = sh.getSelections()
            let mgcIndex = sh.tables.all()[0].getColumnIndexInTable("materialGroupCode")
            let rowMaterialGroupCode = sh.getValue(selCells[0].row,mgcIndex)
            let colField = sh.tables.all()[0].getColumnDataField(selCells[0].col)
            selCells.forEach((e) => {

            })

            xhr.addEventListener("readystatechange", function() {
                if(this.readyState === 4) {
                    resultData = JSON.parse(this.responseText).data
                    for(let i = 0; i <resultData.length;i++){
                        if(resultData[i].hasOwnProperty("dto") && resultData[i].dto.hasOwnProperty("dataList")){
                            dataList = resultData[i].dto.dataList
                            let r1 = dataList.filter(e=>{return e.materialGroupCode === rowMaterialGroupCode})
                            if(r1.length>0){
                                let resultStr = ""
                                if(r1[0][colField].hasOwnProperty('indicatorCode')){
                                    resultStr += "指标：" + r1[0][colField].indicatorCode + "\n"
                                }
                                if(r1[0][colField].hasOwnProperty('dimensionPeriodCode')){
                                    resultStr += "期间：" + r1[0][colField].dimensionPeriodCode + "\n"
                                }
                                if(r1[0][colField].hasOwnProperty('dimensionConstituteCode')){
                                    resultStr += "产品分类：" + r1[0][colField].dimensionConstituteCode + "\n"
                                }
                                if(r1[0][colField].hasOwnProperty('dimensionBlockCode')){
                                    resultStr += "区块：" + r1[0][colField].dimensionBlockCode + "\n"
                                }
                                if(r1[0][colField].hasOwnProperty('dimensionAreaCode')){
                                    resultStr += "地区：" + r1[0][colField].dimensionAreaCode + "\n"
                                }
                                if(r1[0][colField].hasOwnProperty('dimensionAssessCode')){
                                    resultStr += "评估类型：" + r1[0][colField].dimensionAssessCode + "\n"
                                }
                                if(r1[0][colField].hasOwnProperty('dimensionDistributionCode')){
                                    resultStr += "分销渠道：" + r1[0][colField].dimensionDistributionCode + "\n"
                                }
                                if(r1[0][colField].hasOwnProperty('dimensionPlateCode')){
                                    resultStr += "板块：" + r1[0][colField].dimensionPlateCode + "\n"
                                }
                                if(r1[0][colField].hasOwnProperty('dimensionCooperateCode')){
                                    resultStr += "合作方式：" + r1[0][colField].dimensionCooperateCode + "\n"
                                }
                                if(r1[0][colField].hasOwnProperty('selfFormula')){
                                    resultStr += "自定义公式：" + "\n" + r1[0][colField].selfFormula.type + "\n" + r1[0][colField].selfFormula.dataKey
                                }
                                if(resultStr !=""){alert(resultStr)}
                            }
                        }
                    }
                }
            })
            xhr.open("POST", uri)
            xhr.setRequestHeader("Content-Type", "application/json")
            xhr.setRequestHeader("Authorization", "Bearer " + token)
            xhr.send(data)
        }
    }

    function getCookie(name) {
      let arr,reg=new RegExp("(^| )"+name+"=([^;]*)(;|$)");
      if(arr=document.cookie.match(reg))
        return unescape(arr[2]);
      else
        return null;
    }

    function getRefreshToken(refreshToken){
        let fData = new FormData()
        fData.append("refreshToken",refreshToken)
        let xhr = new XMLHttpRequest()
        xhr.withCredentials = true
        xhr.addEventListener("readystatechange", function() {
            if(this.readyState === 4) {
                console.log(this.responseText);
            }
        })
        xhr.open("POST", "/sctj/api/auth/refreshToken")
        xhr.send(fData)
    }

    function getSp() {
        var spread;

        try {
            if ((spread = GC.Spread.Sheets.findControl(container)) !== null) {
                if (spread !== undefined) {
                    isDesigner = false
                    return spread;
                }
            }
        } catch (error) {

        }

        try {
            if ((spread = GC.Spread.Sheets.findControl(document.getElementsByClassName(containerCls)[0])) !== null) {
                if (spread !== undefined) {
                    isDesigner = false
                    return spread;
                }
            }
        } catch (error) {

        }

        try {
            if ((spread = GC.Spread.Sheets.Designer.findControl(containerDesign)) !== null) {
                if (spread.Spread !== undefined) {
                    isDesigner = true
                    return spread.Spread;
                }
            }
        } catch (error) {

        }

        try {
            if ((spread = GC.Spread.Sheets.Designer.findControl(document.getElementsByClassName(containerDesignCls)[0])) !== null) {
                if (spread.Spread !== undefined) {
                    isDesigner = true
                    return spread.Spread;
                }
            }
        } catch (error) {

        }

        throw new Error('无法找到Spread元素');
    }

    function addCustomMenu(){
        const sp = getSp()
        let commandManager
        let customMenu = [
            {
            text: '锁定/解锁',
            name: 'zLockOnOff',
            workArea: 'viewport',
            command: "zLockOnOff",
            iconClass:"gc-spread-locked",
            subMenu:[
                {
                    text: '锁定单元格(L)',
                    name: 'zLockCell',
                    workArea: 'viewport',
                    command: "zLockCell",
                    iconClass: "ribbon-control-dropdown-lockcells"
                },
                {
                    text: '解锁单元格(K)',
                    name: 'zUnLockCell',
                    workArea: 'viewport',
                    command: "zUnLockCell",
                    iconClass: "gc-spread-editComment"
                }
            ]
            },
            {
                text: '清除',
                name: 'zCleanContentFormulaVal',
                workArea: 'viewport',
                command: "zCleanContentFormulaVal",
                subMenu:[
                    {
                        text: '清除值(G)',
                        name: 'zCleanValue',
                        workArea: 'viewport',
                        command: "zCleanValue",
                        iconClass:"ribbon-button-clear-table-style-element"
                    },
                    {
                        text: '清除公式 (H)',
                        name: 'zCleanFormula',
                        workArea: 'viewport',
                        command: "zCleanFormula",
                        iconClass: "gc-spread-removeHyperlink"
                    },
                    {
                        text: '清除内容 (J)',
                        name: 'zCleanContent',
                        workArea: 'viewport',
                        command: "zCleanContent",
                        iconClass:"ribbon-button-clearall"
                    }
                ]
            },
            {
                text: '数据有效性',
                name: 'zDataValidatorMenu',
                workArea: 'viewport',
                command: "zDataValidatorMenu",
                subMenu:[
                    {
                        text: '数据有效性',
                        name: 'zDataValidator',
                        workArea: 'viewport',
                        command: "zDataValidator",
                        iconClass: "ribbon-button-datavalidation"
                    },
                    {
                        text: '清除校验',
                        name: 'zCleanDataValidator',
                        workArea: 'viewport',
                        command: "zCleanDataValidator",
                        iconClass: "ribbon-control-dropdown-clear-rules"
                    },
                ]
            },
            {
                text: '公式',
                name: 'zFormulaMenu',
                workArea: 'viewport',
                command: "zFormulaMenu",
                subMenu:[
                    {
                        text: '显示/隐藏公式(O)',
                        name: 'zShowFormula',
                        workArea: 'viewport',
                        command: "zShowFormula",
                        iconClass: "ribbon-show-formulas"
                    },
                    {
                        text: '工具栏增强(I)',
                        name: 'zShowFormulaVal',
                        workArea: 'viewport',
                        command: "zShowFormulaVal",
                        iconClass: "ribbon-button-textfunction"
                    },
                    {
                        text: '开启公式追踪',
                        name: 'zRefRange',
                        workArea: 'viewport',
                        command: "zRefRange",
                        iconClass: "gc-spread-link"
                    }
                ]
            },
            {
                text: '隐藏/显示',
                name: 'zVisibleMenu',
                workArea: 'viewport',
                command: "zVisibleMenu",
                subMenu:[
                    {
                        text: '隐藏行列(Y)',
                        name: 'zHideRowCol',
                        workArea: 'viewport',
                        command: "zHideRowCol",
                        iconClass: "gc-spread-pivotCollapse"
                    },
                    {
                        text: '取消隐藏(U)',
                        name: 'zShowRowCol',
                        workArea: 'viewport',
                        command: "zShowRowCol",
                        iconClass: "gc-spread-pivotExpand"
                    }
                ]
            },
            {
            text: '插入',
            name: 'zInsertRowCol',
            workArea: 'viewport',
            command: "zInsertRowCol",
                subMenu:[
                    {
                        text: '向后插入列',
                        name: 'zInsertCol',
                        workArea: 'viewport',
                        command: "zInsertCol",
                        iconClass:"gc-spread-tableInsertColumnsRight"
                    },
                    {
                        text: '向下插入行',
                        name: 'zInsertRow',
                        workArea: 'viewport',
                        command: "zInsertRow",
                        iconClass:"gc-spread-tableInsertRowsBelow"
                    }
                ]
            },
            {
            text: '冻结窗格',
            name: 'zFreezePaneMenu',
            workArea: 'viewport',
            command: "zFreezePaneMenu",
                subMenu:[
                    {
                        text: '冻结窗格(E)',
                        name: 'zFreezePane',
                        workArea: 'viewport',
                        command: "zFreezePane",
                        iconClass:"ribbon-button-freezepane"
                    },
                    {
                        text: '冻结底部(B)',
                        name: 'zFreezePaneTail',
                        workArea: 'viewport',
                        command: "zFreezePaneTail",
                        iconClass:"ribbon-button-freezepane"
                    },
                    {
                        text: '取消冻结(M)',
                        name: 'zUnFreezePane',
                        workArea: 'viewport',
                        command: "zUnFreezePane",
                        iconClass:"ribbon-button-unfreezepane"
                    }
                ]
            },
            {
                text: '显示/隐藏表头(P)',
                name: 'zShowTableHeader',
                workArea: 'viewport',
                command: "zShowTableHeader",
                iconClass:"gc-spread-Totals"
            },
            {
                text: '指标维度(Q)',
                name: 'zShowIndiDim',
                workArea: 'viewport',
                command: "zShowIndiDim",
                iconClass:"ribbon-control-dropdown-datalabels-show"
            }
        ]

        if(sp){
//          解锁单元格
            sp.commandManager().register("zUnLockCell",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zUnLockCell";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        lockCell(false)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zUnLockCell', 'K', true, false, false, false)
//          锁定单元格
            sp.commandManager().register("zLockCell",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zLockCell";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        lockCell(true)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zLockCell', 'L', true, false, false, false)
//          显示/隐藏公式
            sp.commandManager().register("zShowFormula",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zShowFormula";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        isShowFormula()
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zShowFormula', 'O', true, false, false, false)
//          工具栏显示公式和值
            sp.commandManager().register("zShowFormulaVal",
                                         {
                canUndo: false,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zShowFormulaVal";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        showValueAndFormula()
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zShowFormulaVal', 'I', true, false, false, false)
//          公式追踪
            sp.commandManager().register("zRefRange",
                                         {
                canUndo: false,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zRefRange";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        turnOnFormulaRef()
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
//          数据有效性
            sp.commandManager().register("zDataValidator",
                                         {
                canUndo: false,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zDataValidator";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        getDataValidator()
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
//          清除校验
            sp.commandManager().register("zCleanDataValidator",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zCleanDataValidator";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        cleanDataValidator()
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
//          清除值
            sp.commandManager().register("zCleanValue",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zCleanValue";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        setValue(null)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zCleanValue', 'G', true, false, false, false)
//          清除公式
            sp.commandManager().register("zCleanFormula",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zCleanFormula";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        setFormula(undefined)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zCleanFormula', 'H', true, false, false, false)
//          清除内容
            sp.commandManager().register("zCleanContent",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zCleanContent";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        setFormula(undefined)
                        setValue(null)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zCleanContent', 'J', true, false, false, false)
//          隐藏行列
            sp.commandManager().register("zHideRowCol",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zHideRowCol";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        setRowColVisible(false)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zHideRowCol', 'Y', true, false, false, false)
//          取消隐藏
            sp.commandManager().register("zShowRowCol",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zShowRowCol";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        setRowColVisible(true)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zShowRowCol', 'U', true, false, false, false)
//          向后插入列
            sp.commandManager().register("zInsertCol",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zInsertCol";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        insertCol(1)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
//          向后插入行
            sp.commandManager().register("zInsertRow",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zInsertRow";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        insertRow(1)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
//          冻结窗格
            sp.commandManager().register("zFreezePane",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zFreezePane";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        setFreezePane(true,1,1,0,0)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zFreezePane', 'E', true, false, false, false)
//          冻结尾行尾列
            sp.commandManager().register("zFreezePaneTail",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zFreezePaneTail";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        setFreezePane(true,0,0,1,1)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zFreezePaneTail', 'B', true, false, false, false)
//          取消冻结
            sp.commandManager().register("zUnFreezePane",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zUnFreezePane";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        setFreezePane(false,1,1,1,1)
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zUnFreezePane', 'M', true, false, false, false)
//          显示/隐藏表头
            sp.commandManager().register("zShowTableHeader",
                                         {
                canUndo: true,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zShowTableHeader";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        isShowTableHeader()
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zShowTableHeader', 'P', true, false, false, false)
//          指标维度
            sp.commandManager().register("zShowIndiDim",
                                         {
                canUndo: false,
                execute: function (context, options, isUndo) {
                    let Commands = GC.Spread.Sheets.Commands;
                    options.cmd = "zShowIndiDim";
                    if (isUndo) {
                        Commands.undoTransaction(context, options);
                        return true;
                    } else {
                        Commands.startTransaction(context, options);
                        showIndiDim()
                        Commands.endTransaction(context, options);
                        return true;
                    }
                }
            })
            sp.commandManager().setShortcutKey('zShowIndiDim', 'Q', true, false, false, false)
//          添加菜单
            if(customMenu.length > 0){
                for(let i = 0;i < customMenu.length;i++){
                    sp.contextMenu.menuData.push(customMenu[i])
                }
            }
        }
    }

    function setFreezePane(isFreeze,row,col,trailingRow,trailingCol){
        const sp = getSp()
        let sh = sp.getActiveSheet()
        let selCells = sh.getSelections()
        if(isFreeze){
            if(selCells.length > 0){
                if(row == 1){
                    sh.frozenRowCount(selCells[0].row)
                }
                if(col == 1){
                    sh.frozenColumnCount(selCells[0].col)
                }
                if(trailingRow == 1){
                    if(selCells[0].row != -1){
                        sh.frozenTrailingRowCount(sh.getRowCount() - selCells[0].row)
                    }
                }
                if(trailingCol == 1){
                    if(selCells[0].col != -1){
                        sh.frozenTrailingColumnCount(sh.getColumnCount() - selCells[0].col)
                    }
                }
            }
        }else{
            if(row == 1){
                sh.frozenRowCount(null)
            }
            if(col == 1){
                sh.frozenColumnCount(null)
            }
            if(trailingRow == 1){
                sh.frozenTrailingRowCount(null)
            }
            if(trailingCol == 1){
                sh.frozenTrailingColumnCount(null)
            }
        }
    }

    function stopUpdate(sp){
        sp.suspendPaint()
        sp.suspendEvent()
        sp.suspendCalcService(true)
    }

    function startUpdate(sp){
        sp.resumeCalcService(true)
        sp.resumeEvent()
        sp.resumePaint()
    }
})();