- 由 黎灏锋创建于九月 06, 2021
客户端宏
1. 隐藏sheet页
原有电子表格宏实现:https://wiki.smartbi.com.cn/pages/viewpage.action?pageId=52625571
原效果
预期效果
代码
事件 onBeforeRender
function main(page: IPage, portlet: ITableSheetPortlet) { portlet.setSheetVisible("Sheet3", false); portlet.setSheetVisible("Sheet4", false); }
2. sheet页轮播
原效果
一直停留在某一个sheet页
预期效果
每隔一段时间从左往右切换sheet页
代码
事件 onBeforeRender
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); }
3. 选中行高亮
原有电子表格宏实现:https://wiki.smartbi.com.cn/pages/viewpage.action?pageId=51943252
原效果
选中行时,行不会高亮
期望效果
代码
事件 onAfterRender
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" }); } }); } }
4. 跑马灯效果
原有电子表格宏实现:https://wiki.smartbi.com.cn/pages/viewpage.action?pageId=51943141
原效果
没有跑马灯效果
期望效果
代码
使用WIKI上的方式实现:
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 }); }
5. 加号控制隐藏/显示列
原有电子表格宏实现:https://wiki.smartbi.com.cn/pages/viewpage.action?pageId=51943260
原效果
期望效果
通过加减号来控制行列的隐藏
代码
参考WIKI上的方式实现(点击整个单元格隐藏/显示列):
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) } }
6. 鼠标提示
电子表格鼠标提示WIKI:https://wiki.smartbi.com.cn/pages/viewpage.action?pageId=52625442
效果如下
代码
服务端输出提示信息(onBeforeOutput)
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; }
客户端(onAfterRender)
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; }
7. 数据过长时,显示省略号
原有电子表格宏实现:https://wiki.smartbi.com.cn/pages/viewpage.action?pageId=51943078
原效果
期待效果
代码
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; }
服务端宏
1. 列宽自适应
原效果
期望效果
列的宽度根据内容的长度自动进行调整,如下图:
代码
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 }
2. 根据条件隐藏整行记录
原效果
期待效果
隐藏合计小于2100的整行记录
代码
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()); } } }
3. 根据单元格值设置其他单元格是否显示
原有电子表格效果:https://wiki.smartbi.com.cn/pages/viewpage.action?pageId=52626737
根据"是否"字段的值,若值为0,则不显示对应的明细。若值为1,则显示对应的明细
原效果
期待效果
代码
事件:onBeforeOutput
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"内容为空,即不显示 } } }
4. 根据条件隐藏
原有电子表格宏示例:https://wiki.smartbi.com.cn/pages/viewpage.action?pageId=52626216
原效果
期待效果
代码
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); } }
5. 缺少数据不合并单元格
原效果
期待效果
代码
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); } } } }
跳转规则示例
点击分类时tab页打开资源,并传递参数
示例效果
服务端宏(onBeforeOutput)
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()]); }
客户端宏(onLinkClick)
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(); }
- 无标签