页面历史

function main(page: IPage, portlet: ITableSheetPortlet) {
    portlet.setSheetVisible("Sheet3", false);
    portlet.setSheetVisible("Sheet4", false);
}

function main(page: IPage, portlet: ITableSheetPortlet) {
    // 3秒钟切换一次sheet页
    let visibleSheets = portlet.getVisibleSheets();
    setInterval(() => {
        let activeSheetIndex = visibleSheets.indexOf(portlet.getActiveSheet());
        activeSheetIndex = (activeSheetIndex + 1) % visibleSheets.length;
        portlet.setActiveSheet(visibleSheets[activeSheetIndex]);
    }, 3 * 1000);
}

function main(page: IPage, portlet: ITableSheetPortlet) {
    let originalBackgroundColors: string[] = [];
    let prevRow: ISheetRow;
    
    for (let rowIndex = 0; rowIndex < portlet.getRowCount(); rowIndex++) {
        let row = portlet.getRow(rowIndex);
        row.addEventListener('click', function (e: SheetEvent) { 
            // 还原上次点击的单元格背景色
            if (prevRow) {
                for (let columnIndex = 0; columnIndex < portlet.getColumnCount(); columnIndex++) {
                    let cell = prevRow.getCell(columnIndex);
                    cell.setStyle({
                        'background-color': originalBackgroundColors[cell.getColumnIndex()]
                    });                    
                }
            }
            prevRow = row;
            
            // 设置点击的单元格的背景色
            for (let columnIndex = 0; columnIndex < portlet.getColumnCount(); columnIndex++) {
                let cell = row.getCell(columnIndex);
                // 单元格背景色备份
                originalBackgroundColors[cell.getColumnIndex()] = cell.getStyle("background-color");
                if (cell.getColumnIndex() > 2) { 
                    break;
                }
                cell.setStyle({
                    'background-color': "#abe5fe"
                });                 
            }
        });      
    }
    
}

function main(page: IPage, portlet: ITableSheetPortlet) {
    let cell = portlet.getCell(2, 2);
    var value = cell.getText();
    var tailHTML = "<marquee direction='left' scrollamount='4' onmouseover='this.stop()' onmouseout='this.start()' height='40' style='line-height: 40px;'>";
    tailHTML += value + "</marquee>";
    cell.setHtml(tailHTML) ;
}

function main(page: IPage, portlet: ITableSheetPortlet) {
    portlet.marquee("C3", {
        speed: 10 * 1000
    });
}

function main(page: IPage, portlet: ITableSheetPortlet) {
    let cell = portlet.getCell(0, 0);
    let html = cell.getHtml() || "";
    cell.setHtml("- " + html);
    cell.setStyle({
        cursor: "pointer"
    });

    let collapsed = false;
    cell.addEventListener("click", function () {
        if (collapsed) {
            cell.setHtml("- " + html);
            for (var i = 1; i <= 3; i++) {
                for (var j = 0; j < portlet.getRowCount(); j++) {
                    let currentCell = portlet.getCell(j, i);
                    currentCell.setStyle({ display: ""});
                }
            }
        } else {
            cell.setHtml("+ " + html);
            for (var i = 1; i <= 3; i++) {
                for (var j = 0; j < portlet.getRowCount(); j++) {
                    let currentCell = portlet.getCell(j, i);
                    currentCell.setStyle({ display: "none"});
                }
            }            
        }
        collapsed = !collapsed;
    });
}

function main(page: IPage, portlet: ITableSheetPortlet) {
    let span = appendCollapseElement(portlet, "A1");
    let collapsed = false;
    span.addEventListener('click', function () {
        let spanText = collapsed ? "-" : "+";
        span.setHtml(spanText);
        hideColumns(portlet, 1, 4, !collapsed);
        collapsed = !collapsed;
    });
}

function appendCollapseElement(portlet: ITableSheetPortlet, cellName: string) {
    let cell = portlet.getCell(cellName);
    let text = cell.getText() || "";
    cell.setText("");
    let span = document.createElement("span");
    span.innerHTML = "-";
    span.style.cursor = "pointer";

    let s = cell.appendChild(span);
    cell.appendChild(document.createTextNode(" " + text));
    return s;
}

function hideColumns(portlet: ITableSheetPortlet,
    from: number, to: number, hidden: boolean) { 
    for (let c = from; c < to; c++) {
        var column = portlet.getColumn(c);
        column.setVisible(!hidden)
    }
}

function main(pageServer: IPageServer, portletServer: ITableSheetPortletServer) {
    let sheet = portletServer.getSheetByName("Sheet1"); //根据sheet名称获取sheet
 
    //下面的内容根据单元格，获取要显示出来的内容。并且添加到customProperty中去
    let valueI5 = getDisplayCellValue(sheet, "I5");
    portletServer.setCustomProperty("I5", valueI5);
    let valueH5 = getDisplayCellValue(sheet, "H5");
    portletServer.setCustomProperty("H5", valueH5);
    let valueG5 = getDisplayCellValue(sheet, "G5");
    portletServer.setCustomProperty("G5", valueG5);
}

//获取单元格的值
function getCellValue(cell: any) {
    if (!cell) return null;
    if (typeof cell.length == 'number') {
        let ret = [];
        for (let i = 0; i < cell.length; i++) {
            ret.push(cell[i].value);
        }
        return ret;
    } else {
        return cell.value;
    }
}

//获取单元格的显示值
function getDisplayCellValue(sheet: ISheetServer, position: string) {
    let expandedPositions = sheet.getExpandedPositions(position);
    let cell = sheet.getCell(expandedPositions[0]);
    let value = getCellValue(cell).toString().replaceAll("\\n", "<br>"); //替换换行符。
    return value;
}

function main(page: IPage, portlet: ITableSheetPortlet) {
    createTooltip(portlet.getCell('I2'), portlet.getCustomProperty("I5"));
    createTooltip(portlet.getCell('H2'), portlet.getCustomProperty("H5"));
    createTooltip(portlet.getCell('G2'), portlet.getCustomProperty("G5"));
}

function createTooltip(cell: ISheetCell, tip: Object) {
    let div = document.createElement("div");
    div.style.position = "absolute";
    div.style.border = "1px solid #C4E1FF";
    div.style.padding = "10px";
    div.style.backgroundColor = "#fff"; //设置提示框背景颜色
    div.style.textAlign = 'left';
    div.style.color = 'red';
    div.style.display = 'none';
    div.innerHTML = tip.toString(); //设置提示内容
    
    let ele = cell.appendChild(div);
    cell.addEventListener('mousemove', function (e: SheetEvent) {
        ele.setStyle({
            left: (e.clientX + 10) + 'px',
            top: e.clientY + 'px'
        });        
        ele.setVisible(true);
    }); 
    cell.addEventListener('mouseleave', function () {
        ele.setVisible(false);
    });
    return ele;
}       

function main(page: IPage, portlet: ITableSheetPortlet) {
    let r = portlet.getRowCount(); //获取电子表格总行数
    for (let i = 2; i < r - 1; i++) {
        // 设置类别
        let cell = portlet.getCell(i, 1); //根据行列号获取单元格
        truncateCellInnerText(cell, 5); //设置显示的字符个数
        // 设置名称
        cell = portlet.getCell(i, 2);
        truncateCellInnerText(cell, 2);
        //设置数量
        cell = portlet.getCell(i, 3);
        truncateCellInnerText(cell, 4);
    }    
}

// 如果单元格内容超出指定长度、将其截短
function truncateCellInnerText(cell: ISheetCell, textMaxLength: number) {
    if (!cell) {
        return;
    }
    let len = textMaxLength || 10;
    let txt = cell.getText();
    if (txt.length > len) {
        cell.setText(txt.substring(0, len) + "...");
        cell.setAttribute("title", txt);
    }
}

function strlen(str: string) {
    let len = 0;
    for (let i = 0; i < str.length; i++) {
        let c = str.charCodeAt(i);
        //单字节加1
        if ((c >= 0x0001 && c <= 0x007e) || (0xff60 <= c && c <= 0xff9f)) {
            len++;
        } else {
            len += 2;
        }
    }
    return len;
}

function main(pageServer: IPageServer, portletServer: ITableSheetPortletServer) {
    let sheet = portletServer.getWorkbook().getWorksheets().get(0);
    if (sheet === null) {
        return;
    }
    let counts = sheet.getCells().getMaxDisplayRange().getColumnCount(); //获取电子表格列数
    sheet.autoFitColumns(0, counts); //从0开始，到counts列结束。
    //sheet.autoFitColumn(3); //只对某列进行自适应，第一列为0    
}

function main(pageServer: IPageServer, portletServer: ITableSheetPortletServer) {
    //获取D5单元格中的值做判断,隐藏行
    var posList = portletServer.getSheets()[0].getExpandedPositions("D5");
    var cells = portletServer.getWorkbook().getWorksheets().get(0).getCells();
    for (var i = 0; i < posList.length; i++) {
        var pos = posList[i]
        var value = cells.get(pos.getRow(), pos.getColumn()).getValue();
        if (!value) {
            break;
        }
        //隐藏值小于2100的行
        if (value < 2100) {
            cells.hideRow(pos.getRow());
        }
    }
}

function main(pageServer: IPageServer, portletServer: ITableSheetPortletServer) {
    let cells = portletServer.getWorkbook().getWorksheets().get(0).getCells(); //获取行值
    let B4List = portletServer.getSheets()[0].getExpandedPositions("B4"); //获取第一个表格"是否"扩展字段扩展出来的所有单元格
    
    for (let i = 0; i < B4List.length; i++) {
        let B4 = B4List[i];
        if (parseInt(cells.get(B4.getRow(), B4.getColumn()).getValue()) == 0) { //判断值是否为0
            cells.get(B4.getRow() - 1, B4.getColumn() + 3).setValue(""); //设置"明细1"内容为空，即不显示
            cells.get(B4.getRow(), B4.getColumn() + 3).setValue(""); //设置"明细2"内容为空，即不显示
        }
    }    
}  

function main(pageServer: IPageServer, portletServer: ITableSheetPortletServer) {
    let cells = portletServer.getWorkbook().getWorksheets().get(0).getCells(); //获取行值
    let list = portletServer.getSheets()[0].getExpandedPositions("G13"); //获取的列值

    var hideRowCount = 0;
    for (var i = 0; i < list.length; i++) {
        let position = list[i];
        let value = cells.get(position.getRow(), position.getColumn()).getValue();
        if (value == null || value == "") {
            cells.hideRow(position.getRow());
            hideRowCount++;
        }
    }
    // 数据全部被隐藏时，隐藏标题
    if (hideRowCount > 0 && hideRowCount == list.length) {
        cells.hideRow(list[0].getRow() - 1);
    }
}    

function main(pageServer: IPageServer, portletServer: ITableSheetPortletServer) {
    /*工作表中的单元格*/
    let cells = portletServer.getWorkbook().getWorksheets().get("Sheet1").getCells();
    let C5PosList = portletServer.getSheets()[0].getExpandedPositions("C5");
    let D5PosList = portletServer.getSheets()[0].getExpandedPositions("D5");
    /*获取原始的D5、E5样式*/
    let D5Style = cells.get("D5").getStyle();
    let E5Style = cells.get("E5").getStyle();
    for (let i = 0; i < C5PosList.length; i++) {
        let C5Sub = cells.get(C5PosList[i].getRow(), C5PosList[i].getColumn());
        let fromRow = C5Sub.getRow();
        let toRow = C5Sub.getRow();
        /*计算行数*/
        let mergedRange = C5Sub.getMergedRange();
        if (mergedRange) {
            toRow += mergedRange.getRowCount() - 1;
        }
        for (let z = fromRow; z <= toRow; z++) {
            for (let j = 0; j < D5PosList.length; j++) {
                /*获取单元格*/
                let cell = cells.get(z, D5PosList[j].getColumn());
                mergedRange = cell.getMergedRange();
                if (mergedRange) {
                    /*如果产生了合并单元格，取消合并*/
                    cells.unMerge(mergedRange.getFirstRow(), mergedRange.getFirstColumn(),
                        mergedRange.getRowCount(), mergedRange.getColumnCount());
                }
                /*设置样式*/
                cell.setStyle(D5Style);
                /*获取单元格*/
                cell = cells.get(z, D5PosList[j].getColumn() + 1);
                mergedRange = cell.getMergedRange();
                if (mergedRange) {
                    /*如果产生了合并单元格，取消合并*/
                    cells.unMerge(mergedRange.getFirstRow(), mergedRange.getFirstColumn(),
                        mergedRange.getRowCount(), mergedRange.getColumnCount());
                }
                /*设置样式*/
                cell.setStyle(E5Style);
            }
        }
    }
} 

function main(pageServer: IPageServer, portletServer: ITableSheetPortletServer) {
    let sheet = portletServer.getSheetByName("Sheet2");
    let positions = "B2".split(",");
    for (let i = 0; i < positions.length; i++) {
        let position = positions[i].trim();
        let expandedPositions = sheet.getExpandedPositions(position);
        for (let j = 0; j < expandedPositions.length; j++) {
            let cell = sheet.getCell(expandedPositions[j]);
            if (true) {
                addLink(portletServer, sheet, cell);
            }
        }
    }
}

function addLink(portletServer: ITableSheetPortletServer,
    sheet: ISheetServer, cell: ISheetCellServer) {
    sheet.addRuleLink(cell.getCellPosition(), "跳转_onBeforeOutput", [cell.getValue()]);
}

function main(page: IPage, portlet: ITableSheetPortlet, ruleName: string, parameterValues: object[]) {
    if (ruleName != "跳转_onBeforeOutput") return;
    page.openResourceInTab("I402882c701552f492f49736e01552f4df2c50013",
        [{ name: "sqlMapName", alias: "SQL映射名称或描述", value: parameterValues[0] }]);

}

function paramToString(v: any) {
    return v == null ? null : v.toString();
}