在线表格增强插件
// ==UserScript==
// @name 在线表格增强功能
// @namespace http://tampermonkey.net/
// @version 1.2.6
// @description 在线表格增强插件
// @author pianpianluoye
// @include *://*suip.*.com/sctj*
// @include *://*intuat.*.com/ibss*
// @icon https://www.google.com/s2/favicons?sz=64&domain=sinopec.com
// @grant none
// @license AGPL-3.0-or-later
// ==/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('.form-box').__vue__
let reportForm = reportInfoVue._data.reportForm
if(!reportForm){return}
let uri = "/sctj/api/companyReportVersion/selectAllSheet"
let xhr = new XMLHttpRequest()
xhr.withCredentials = true
let data = JSON.stringify({
"reportCodeEq":reportForm.reportCode,
"orgCodeEq": reportForm.orgCode,
"versionEq":reportForm.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 !=""){
let comment = new GC.Spread.Sheets.Comments.Comment()
comment.text(resultStr)
comment.backColor("yellow")
comment.foreColor("green")
comment.displayMode(GC.Spread.Sheets.Comments.DisplayMode.alwaysShown)
comment.dynamicMove(true)
comment.dynamicSize(true)
comment.lockText(false)
comment.locked(false)
sh.getCell(selCells[0].row,selCells[0].col).comment(comment)
}
}
}
}
}
})
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()
}
})();