// ==UserScript==
// @name 在线表格增强功能
// @namespace http://tampermonkey.net/
// @version 1.3.4
// @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
// @run-at document-end
// @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 = `
/* 按钮样式 */
.tm-btn {
display: inline-block;
padding: 6px 15px;
font-size: 14px;
font-weight: 400;
line-height: 1.5;
text-align: center;
white-space: nowrap;
vertical-align: middle;
cursor: pointer;
user-select: none;
background-color: #1890ff;
border: 1px solid #1890ff;
color: #fff;
border-radius: 2px;
transition: all 0.3s cubic-bezier(0.645, 0.045, 0.355, 1);
}
.tm-btn:hover {
background-color: #40a9ff;
border-color: #40a9ff;
}
/* 模态框样式 */
.tm-modal {
display: none;
position: fixed;
z-index: 1000;
top: 50%;
left: 50%;
transform: translate(-50%, -50%);
width: 80%;
max-width: 960px;
}
.tm-modal-content {
background-color: #fff;
padding: 24px;
border: 1px solid #e8e8e8;
border-radius: 2px;
box-shadow: 0 3px 6px -4px rgba(0, 0, 0, 0.12), 0 6px 16px 0 rgba(0, 0, 0, 0.08), 0 9px 28px 8px rgba(0, 0, 0, 0.05);
position: relative;
}
.tm-modal-header {
display: flex;
justify-content: space-between;
align-items: center;
margin-bottom: 16px;
cursor: move;
}
.tm-modal-title {
margin: 0;
font-size: 18px;
font-weight: 500;
color: rgba(0, 0, 0, 0.85);
}
.tm-modal-close {
font-size: 20px;
font-weight: 700;
line-height: 1;
color: #000;
text-shadow: 0 1px 0 #fff;
opacity: 0.5;
cursor: pointer;
transition: all 0.3s;
}
.tm-modal-close:hover {
opacity: 0.8;
}
/* 表格样式 */
.tm-table-container {
max-height: 300px;
overflow-y: auto;
position: relative;
}
.tm-table {
width: 100%;
border-collapse: collapse;
table-layout: auto;
}
.tm-table thead {
position: sticky;
top: 0;
background-color: #fafafa;
z-index: 1;
}
.tm-table th,
.tm-table td {
padding: 12px 16px;
border-bottom: 1px solid #e8e8e8;
text-align: left;
font-size: 14px;
color: rgba(0, 0, 0, 0.85);
white-space: nowrap;
position: relative;
}
.tm-table th {
font-weight: 500;
}
/* 鼠标经过行的效果 */
.tm-table tbody tr:hover {
background-color: #e6f7ff;
}
/* 选中行的效果 */
.tm-table tbody tr.selected {
background-color: #bae7ff;
}
/* 模态框底部按钮样式 */
.tm-modal-footer {
display: flex;
justify-content: flex-end;
margin-top: 24px;
}
.tm-modal-footer .btn {
margin-left: 8px;
}
.tm-btn-secondary {
background-color: #fff;
border-color: #d9d9d9;
color: rgba(0, 0, 0, 0.85);
}
.tm-btn-secondary:hover {
background-color: #fff;
border-color: #40a9ff;
color: #40a9ff;
}
/* 复制图标样式 */
.tm-copy-icon {
display: none;
position: absolute;
right: 10px;
top: 50%;
transform: translateY(-50%);
cursor: pointer;
}
.tm-table td:hover .tm-copy-icon {
display: inline-block;
}
/* 公式单元格样式，超出宽度显示省略号 */
.tm-formula-cell {
max-width: 150px; /* 可根据需要调整最大宽度 */
overflow: hidden;
text-overflow: ellipsis;
}
/* 列宽拖动条样式 */
.tm-resize-handle {
position: absolute;
top: 0;
right: 0;
width: 5px;
height: 100%;
cursor: col-resize;
}
`
const msg = `
`
const myStyle = document.createElement("style");
myStyle.textContent = cssText;
document.head.appendChild(myStyle)
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 btnplugin = document.createElement("span");
btnplugin.classList.add("ant-dropdown-trigger");
btnplugin.classList.add("action");
btnplugin.innerHTML = `
`;
const originalMount = Vue.prototype.$mount;
Vue.prototype.$mount = function() {
const result = originalMount.apply(this, arguments);
handleVueInstance(this);
return result;
};
function handleVueInstance(vm) {
vm.$watch('spreadViewer', (newVal) => {
console.log('检测到数据变化:', newVal);
if(!isDesigner){
addCustomMenu()
}
});
}
btnplugin.addEventListener("click", (e) => {
clearTimeout(timeoutId);
timeoutId = setTimeout(() => {
getSp()
if(!isDesigner){
addCustomMenu()
hasCustomMenu = true
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 data = []
const dvTempHtml = `
`
let container = document.getElementById('dvListModal')
if(container){container.innerHTML=''}else{
container = document.createElement("div");
container.id = 'dvListModal'
container.classList.add("tm-modal")
}
container.innerHTML = dvTempHtml
document.body.appendChild(container)
const closeBtn = document.querySelector('.tm-modal-close');
const okBtn = document.getElementById('okBtn');
const tableHeader = document.getElementById('tableHeader');
const tableBody = document.getElementById('tableBody');
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()
data.push(dvObj)
}
}
}
})
if(data.length){
const headers = ['序号', '单元格', '提示标题','提示消息', '类型', '操作符', '忽略空值', '提供下拉按钮', '错误类型', '错误标题', '错误消息', '高亮样式', '最小值', '最大值', '公式', '序列'];
headers.forEach(header => {
const th = document.createElement('th');
th.textContent = header;
if (header === '公式') {
const resizeHandle = document.createElement('div');
resizeHandle.className = 'tm-resize-handle';
resizeHandle.addEventListener('mousedown', (e) => {
const startX = e.clientX;
const startWidth = th.offsetWidth;
const onMouseMove = (moveEvent) => {
const dx = moveEvent.clientX - startX;
th.style.width = `${startWidth + dx}px`;
const formulaCells = document.querySelectorAll('.tm-formula-cell');
formulaCells.forEach((cell) => {
cell.style.maxWidth = `${startWidth + dx}px`;
});
};
const onMouseUp = () => {
document.removeEventListener('mousemove', onMouseMove);
document.removeEventListener('mouseup', onMouseUp);
};
document.addEventListener('mousemove', onMouseMove);
document.addEventListener('mouseup', onMouseUp);
});
th.appendChild(resizeHandle);
}
tableHeader.appendChild(th);
});
data.forEach(item => {
const row = document.createElement('tr');
const values = [1,item.rng, item.inputTitle,item.inputMessage, item.dvtype, item.comparisonOperator,
item.ignoreBlank,item.inCellDropdown,item.errorStyle,item.errorTitle,item.errorMessage,
item.highlightStyle,item.value1,item.value2,item.formulaStr,item.validList];
values.forEach((value,index) => {
const td = document.createElement('td');
td.textContent = value || '';
if(index === 14){
td.title = value;
const copyIcon = document.createElement('span');
copyIcon.className = 'tm-copy-icon';
copyIcon.textContent = '📋';
copyIcon.addEventListener('click',async function(){
const value = td.textContent.replace('📋', '').trim();
try {
await navigator.clipboard.writeText(value);
} catch (err) {
console.log(err)
const textarea = document.createElement('textarea');
textarea.value = value;
document.body.appendChild(textarea);
textarea.select();
document.execCommand('copy');
document.body.removeChild(textarea);
}
})
td.appendChild(copyIcon)
td.classList.add('tm-formula-cell');
}
row.appendChild(td);
});
tableBody.appendChild(row);
});
tableBody.addEventListener('click', function (event) {
if (event.target.tagName === 'TD') {
const rows = tableBody.querySelectorAll('tr');
rows.forEach(row => {
row.classList.remove('selected');
});
const clickedRow = event.target.parentNode;
clickedRow.classList.add('selected');
}
});
const closeModal = () => {
container.style.display = 'none';
};
let isDragging = false;
let offsetX, offsetY;
const modalHeader = document.querySelector('.tm-modal-header')
const modalFooter = document.querySelector('.tm-modal-footer')
modalHeader.addEventListener('mousedown', function (e) {
isDragging = true;
offsetX = e.clientX - container.offsetLeft;
offsetY = e.clientY - container.offsetTop;
});
modalFooter.addEventListener('mousedown', function (e) {
isDragging = true;
offsetX = e.clientX - container.offsetLeft;
offsetY = e.clientY - container.offsetTop;
});
document.addEventListener('mousemove', function (e) {
if (isDragging) {
container.style.left = (e.clientX - offsetX) + 'px';
container.style.top = (e.clientY - offsetY) + 'px';
}
});
document.addEventListener('mouseup', function () {
isDragging = false;
});
closeBtn.addEventListener('click', closeModal);
okBtn.addEventListener('click', closeModal);
}
container.style.display = 'block';
}
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 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 {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() {
let 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) {
}
}
function addCustomMenu(){
const sp = getSp()
let commandManager
let customMenu = [
{
text: '锁定/解锁',
name: 'zLockOnOff',
workArea: 'viewport',
command: "zLockOnOff",
iconClass:"gc-spread-locked",
subMenu:[
{
text: '锁定单元格(Ctrl+L)',
name: 'zLockCell',
workArea: 'viewport',
command: "zLockCell",
iconClass: "ribbon-control-dropdown-lockcells"
},
{
text: '解锁单元格(Ctrl+K)',
name: 'zUnLockCell',
workArea: 'viewport',
command: "zUnLockCell",
iconClass: "gc-spread-editComment"
}
]
},
{
text: '清除',
name: 'zCleanContentFormulaVal',
workArea: 'viewport',
command: "zCleanContentFormulaVal",
subMenu:[
{
text: '清除值(Ctrl+Shift+G)',
name: 'zCleanValue',
workArea: 'viewport',
command: "zCleanValue",
iconClass:"ribbon-button-clear-table-style-element"
},
{
text: '清除公式 (Ctrl+Shift+H)',
name: 'zCleanFormula',
workArea: 'viewport',
command: "zCleanFormula",
iconClass: "gc-spread-removeHyperlink"
},
{
text: '清除内容 (Ctrl+Shift+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: '显示/隐藏公式(Ctrl+O)',
name: 'zShowFormula',
workArea: 'viewport',
command: "zShowFormula",
iconClass: "ribbon-show-formulas"
},
{
text: '工具栏增强(Ctrl+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: '隐藏行列(Ctrl+Y)',
name: 'zHideRowCol',
workArea: 'viewport',
command: "zHideRowCol",
iconClass: "gc-spread-pivotCollapse"
},
{
text: '取消隐藏(Ctrl+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: '冻结窗格(Ctrl+E)',
name: 'zFreezePane',
workArea: 'viewport',
command: "zFreezePane",
iconClass:"ribbon-button-freezepane"
},
{
text: '冻结底部(Ctrl+B)',
name: 'zFreezePaneTail',
workArea: 'viewport',
command: "zFreezePaneTail",
iconClass:"ribbon-button-freezepane"
},
{
text: '取消冻结(Ctrl+M)',
name: 'zUnFreezePane',
workArea: 'viewport',
command: "zUnFreezePane",
iconClass:"ribbon-button-unfreezepane"
}
]
},
{
text: '显示/隐藏表头(Ctrl+P)',
name: 'zShowTableHeader',
workArea: 'viewport',
command: "zShowTableHeader",
iconClass:"gc-spread-Totals"
},
{
text: '指标维度(Ctrl+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;
}
}
})
// isCtrl, isShift, isAlt, isMeta
// ctrl? boolean 为true时, 命令需要 Ctrl 键; 若 false则不需要
// shift? boolean 为true时, 命令需要 Shift 键; 若 false则不需要
// alt? boolean 为true时, 命令需要 Alt 键; 若 false则不需要
// meta? boolean 为true时, 命令需要Mac上的Command键或Windows上的Windows键；若 false则不需要
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, true, 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, true, 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, true, 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()
}
})();