Greasy Fork is available in English.
在线表格增强插件
当前为
// ==UserScript==
// @name 在线表格增强功能
// @namespace http://tampermonkey.net/
// @version 1.0.10
// @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
// ==/UserScript==
(function() {
'use strict';
const lockColor = "#f0f3fb"
const unlockColor = "#fff"
const container = "gc-container"
const containerDesign = "gc-designer-container"
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 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", () => {
showValueAndFormula()
});
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
btnplugin.addEventListener("click", () => {
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
}
})
setTimeout(() => {
const toolbar = document.getElementsByClassName("content-box")[0];
const doc = document.head || document.documentElement;
doc.appendChild(myStyle);
if(toolbar){toolbar.prepend(btnplugin)}
}, 1000);
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 = GC.Spread.Sheets.findControl(container) || GC.Spread.Sheets.Designer.findControl(containerDesign).Spread
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)
})
}
function setFormula(para){
const sp = GC.Spread.Sheets.findControl(container) || GC.Spread.Sheets.Designer.findControl(containerDesign).Spread
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)
})
}
function cleanDataValidator(){
const sp = GC.Spread.Sheets.findControl(container) || GC.Spread.Sheets.Designer.findControl(containerDesign).Spread
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 = GC.Spread.Sheets.findControl(container) || GC.Spread.Sheets.Designer.findControl(containerDesign).Spread
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 = GC.Spread.Sheets.findControl(container) || GC.Spread.Sheets.Designer.findControl(containerDesign).Spread
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)
})
}
function isShowFormula(){
const sp = GC.Spread.Sheets.findControl(container) || GC.Spread.Sheets.Designer.findControl(containerDesign).Spread
let sh = sp.getActiveSheet()
sh.options.showFormulas = !sh.options.showFormulas
if(sh.options.showFormulas){
btn10.innerText = "隐藏公式"
}
else{
btn10.innerText = "显示公式"
}
}
function setRowColVisible(para){
const sp = GC.Spread.Sheets.findControl(container) || GC.Spread.Sheets.Designer.findControl(containerDesign).Spread
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)
}
})
}
function insertRow(para){
const sp = GC.Spread.Sheets.findControl(container) || GC.Spread.Sheets.Designer.findControl(containerDesign).Spread
let sh = sp.getActiveSheet()
let selCells = sh.getSelections()
let count = parseInt(para)
selCells.forEach((e) => {
sh.addRows(e.row + 1,count)
})
}
function insertCol(para){
const sp = GC.Spread.Sheets.findControl(container) || GC.Spread.Sheets.Designer.findControl(containerDesign).Spread
let sh = sp.getActiveSheet()
let selCells = sh.getSelections()
let count = parseInt(para)
selCells.forEach((e) => {
sh.addColumns(e.col + 1,count)
})
}
function showValueAndFormula(){
const sp = GC.Spread.Sheets.findControl(container) || GC.Spread.Sheets.Designer.findControl(containerDesign).Spread
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) {
setTimeout(() => {
setToolBarValue(sh,false)
}, 100);
})
}
function setToolBarValue(sh,isClean){
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)
inputBar.style.width = '100%'
if(inputBarVal){
inputBarVal.style.width = '0%'
}
}
}
function isShowTableHeader(){
const sp = GC.Spread.Sheets.findControl(container) || GC.Spread.Sheets.Designer.findControl(containerDesign).Spread
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 = GC.Spread.Sheets.findControl(container)
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)
}
})();