SmartBI常用报表宏代码

水晶心泉

已于 2023-11-29 15:46:49 修改

阅读量2.7k

点赞数

文章标签： javascript 前端开发语言

于 2022-09-21 23:45:23 首次发布

本文链接：https://blog.csdn.net/zidieq/article/details/126983164

版权

1.服务端宏-图片列无图片时默认显示一条斜线

因为客户端宏只影响在浏览器中的显示，导出的文件是不受影响的。如果实际需求是导出的文件中也要求无图片的单元格默认填充上斜线的话，就可以使用下面的方法了。

(1).首先添加一个服务端宏

名称：自己取一个就成
类型：ServerSide
对象：spreadsheetReport
事件：onBeforeOutput

(2).在宏代码中，main方法外添加如下自定义方法

// 检测如果没有图片就画一个斜线出来
function handlePictureCells(cellAddress) {
    var sheet = spreadsheetReport.sheets[0]; //获取sheet[0]对象
    //根据cellAddress单元格获取"数据"字段扩展出的所有数据
    var positions = sheet.getExpandedPositions(cellAddress);
    var worksheet = spreadsheetReport.workbook.worksheets.get(0);
    var cells = worksheet.cells;
    for (var i = 0; i < positions.length; i++) {
        var pos = positions[i];
		// 所有扩展出来的图片默认都是有一个名称的：SSR_Image_行号_列号
		// 其中行号从0开始，这个例子中行列号都是从对象中取的，所以不影响
        var imgName = "SSR_Image_"+(pos.row)+"_"+pos.column;
		// 通过这个方法可以获取到图片对象
        var shp = worksheet.shapes.get(imgName);
		// 获得cell对象，以设置值
        cell = cells.get(pos.row, pos.column);
		// 如果没有图片的话，就把值设置成斜线
        if(!shp){
            cell.value='/';
        }
    }
}

(3).在main方法中调用该方法

这里以安全咨询问题整改的季报报表为例，在表格中可以看到图片的原始单元格有F4和J4

所以在main方法中调用方法，并传入这两个单元格

function main(spreadsheetReport) {
	handlePictureCells("F4");
	handlePictureCells("J4");
}

看下网页端已经生效了
在这里插入图片描述

导出的excel中也是
在这里插入图片描述

2.服务端宏-在当前分页中对指定列的相同值的单元格进行合并

不分页的情况下只需要将扩展属性由“列表”设置为“分组”即可。但是如果报表使用了分页，即 xx 条就分成一页，比如安全咨询问题整改的季报，因为一个报表中图片数量太多，且比较大，因此不分页的话，可能会导致报表需要下载几十上百M的数据才能展示，甚至可能直接报错。但是分页后分组这种扩展方式就不太适合了，如果仍然直接使用分组的话，大概会显示成如下的样子
在这里插入图片描述

目前使用的解决办法就是取消扩展属性里面的分组，转为使用服务端宏的形式对当前页面的指定列进行相同值单元格合并的操作，官方文档中有一个横向合并单元格的，可以到下面的连接里面去看
参考官方文档:https://wiki.smartbi.com.cn/pages/viewpage.action?pageId=52625784

(1).首先还是添加服务端宏

名称：自己取一个就成
类型：ServerSide
对象：spreadsheetReport
事件：onBeforeOutput

(2).在宏代码中，main方法外添加如下自定义方法

//  spreadsheetReport 是全局对象
// cellAddress 是指需要合并单元格的、会扩展成列的那个单元格。比如
function myMergeCells(cellAddress) {
    var sheet = spreadsheetReport.sheets[0]; //获取sheet[0]对象
    // var sheet =  spreadsheetReport.workbook.worksheets.get(0); //获取sheet[0]对象
    //根据C4单元格获取"数据"字段扩展出的所有数据
    var positions = sheet.getExpandedPositions(cellAddress);
    var worksheet = spreadsheetReport.workbook.worksheets.get(0);
    var cells = worksheet.cells;
    var cell = undefined,
        row1 = 0,
        row2 = 0;
    for (var i = 0; i < positions.length; i++) {
        var pos = positions[i];
        //判断当前行的值和下一行的值是否一样,如果一样则合并,否则不处理
        if (!cell) {
            cell = cells.get(pos.row, pos.column);
            row1 = pos.row;
        }
        var nextCell = cells.get(pos.row + 1, pos.column);
        if (nextCell && cell && cell.value == nextCell.value) {
            row2 = pos.row + 1;
            var value = cell.value;
            try {
                cells.merge(row1, pos.column, row2 - row1 + 1, 1);
            } catch (exception) {
				// 只是为了防止报错影响数据展示，也可以在这里加处理方法
            }
        } else {
            cell = null;
        }
        // var value = cell.value;
        // cells.get(pos.row, pos.column - 2).value = value;
        // cells.merge(pos.row, pos.column - 2, 1, 3);
    }
}

(3).在main方法中调用该方法

这里以安全咨询问题整改的季报报表为例，在表格中可以看到需要纵向合并相同值的原始单元格有C4和D4
在这里插入图片描述

所以在main方法中执行调用方法

function main(spreadsheetReport) {
    myMergeCells("C4");
    myMergeCells("D4");
}

看效果，合并单元格已经不再会跨页了

在这里插入图片描述

3 服务端宏-添加空单元格填充默认值

3.1 按单元格来指定

(1).首先还是添加服务端宏

名称：自己取一个就成
类型：ServerSide
对象：spreadsheetReport
事件：onBeforeOutput
(2).在宏代码中，main方法外添加如下自定义方法

/**
 * 指定的cell及其扩展出来的单元格里面如果值为"" 则填充 “-”
 * @param cellAddress 需要填充“-”的、有着数据源定义的单元格
 * @param sheetIndex 第几个sheet，从0开始，默认0，也就是第一个sheet
 * @param notEmeptyCell 用来表示是否是空表的单元格，如果传入了该参数，且对应单元格里面有值则不填充，否则填充
 * @param fillText 为空时填充的内容，默认 "-"
 */
function fillEmptyCell(cellAddress,sheetIndex,notEmeptyCell,fillText) {
    // 设置默认值，如果不传则默认处理第一个sheet
    if(!sheetIndex){
        sheetIndex = 0;
    }
    // 设置默认填充内容为"-"
    if(!fillText){
        fillText = "-";
    }
    // 获取sheet[0]对象
    var sheet = spreadsheetReport.sheets[sheetIndex]; 
    // 获得整个sheet对象
    var worksheet = spreadsheetReport.workbook.worksheets.get(sheetIndex);
    // 获得sheet对象中所有的单元格对象，下面会根据位置获得对应的具体的单元格
    var cells = worksheet.cells;
    // 如果有传notEmptyCell参数，且单元格里面值为空，则说明是空表，不再填充对应内容
    if(notEmeptyCell){
        // 用来表示是否需要填充
        var isEmptyGrid = true;
        // 根据指定单元格获取到所有扩展出来的单元格的位置的数组
        var emptyCellPostions = sheet.getExpandedPositions(notEmeptyCell);
        // 遍历该位置数组
        for(var j = 0;j < emptyCellPostions.length; j++){
            // 获得当前位置
            var emptyCellPos = emptyCellPostions[j];
            // 根据位置得到具体的单元格对象
            var emptyCell = cells.get(emptyCellPos.row,emptyCellPos.column);
            // 如果该单元格不为空，则说明不是空表
            if(emptyCell.value){
                isEmptyGrid = false;
                break;
            }
        }
        // 如果是空表的话，就不再填充内容了
        if(isEmptyGrid){
            return;
        }
    }
    // 如果不是空表，则进行填充
    // 根据指定单元格获取到所有扩展出来的单元格的位置的数组
    var positions = sheet.getExpandedPositions(cellAddress);    
    for (var i = 0; i < positions.length; i++) {
        var pos = positions[i];
        //合并单元格并设置合并后的单元格的值
        var cell = cells.get(pos.row, pos.column);
        if (!cell.value || cell.value == "") { //判断值是否为空
            cell.value = fillText;
        }
    }
}

(3).在main方法中调用该方法，以下(每一行都是B13单元格)为调用示例，实际使用时对应的单元格只需要调用一次即可

function main(spreadsheetReport) {
    // 第一个sheet中的B13单元格及其扩展出来的单元格值为空时填充“-”
    fillEmptyCell("B13")
    // 第一个sheet中的B13单元格及其扩展出来的单元格值为空时填充“-”
    fillEmptyCell("B13",0)
    // 第二个sheet中的B13单元格及其扩展出来的单元格值为空时填充“-”
    fillEmptyCell("B13",1)
    // 第一个sheet中的B13单元格及其扩展出来的单元格值为空，且O1单元格(一般是样品ID，用于判断是否空表)里面有值时，则说明不是空表，填充“-”，否则不填充
    fillEmptyCell("B13",null,"O1")
    // 第一个sheet中的B13单元格及其扩展出来的单元格值为空时填充“/”
    fillEmptyCell("B13",0,null,"/")
}

3.2 优化-按区域指定

上面的是针对单个单元格来进行填充的，如果需要配置的单元格比较多，那么使用和维护都是一个比较麻烦的过程，如果可以直接指定区域，那就更加灵活一些了，所以下面改成了可以传入单元格区域的
(1). 同上
(2).在宏代码中，main方法外添加如下自定义方法

/**
 * 指定的区里面有绑定数据源的cell及其扩展出来的单元格里面如果值为空串(即"") 则填充 “-”
 * @param range 需要填充“-”的、有着数据源定义的单元格区域，只会对里面有绑定数据源的单元格做处理
 * @param sheetIndex 第几个sheet，从0开始，默认0，也就是第一个sheet
 * @param notEmeptyCell 用来表示是否是空表的单元格，如果传入了该参数，且对应单元格里面有值则不填充，否则填充
 * @param fillText 为空时填充的内容，默认 "-"
 */
function fillEmptyRange(range,sheetIndex,notEmeptyCell,fillText) {
    if(range.indexOf(":")>-1){
        var re = /([a-zA-Z]+)([0-9]+):([a-zA-Z]+)([0-9]+)/;
        var oarr = re.exec(range);
        if(oarr.length>4){
            var columnStart = oarr[1];
            var columnEnd = oarr[3];
            var rowStart = parseInt(oarr[2]);
            var rowEnd = parseInt(oarr[4]);
            var columnStartValue = parseRangeToInt(columnStart);
            var columnEndValue = parseRangeToInt(columnEnd);
            for(var i = columnStartValue;i<= columnEndValue;i++){
                var columnName = parseIntToRange(i);
                for(var j = rowStart;j<=rowEnd;j++){
                    fillEmptyCell(columnName+j,sheetIndex,notEmeptyCell,fillText);
                }
            }
        }
    }
}

/**
 * 指定的cell及其扩展出来的单元格里面如果值为"" 则填充 “-”
 * @param cellAddress 需要填充“-”的、有着数据源定义的单元格
 * @param sheetIndex 第几个sheet，从0开始，默认0，也就是第一个sheet
 * @param notEmeptyCell 用来表示是否是空表的单元格，如果传入了该参数，且对应单元格里面有值则不填充，否则填充
 * @param fillText 为空时填充的内容，默认 "-"
 */
function fillEmptyCell(cellAddress,sheetIndex,notEmeptyCell,fillText) {
    // 设置默认值，如果不传则默认处理第一个sheet
    if(!sheetIndex){
        sheetIndex = 0;
    }
    // 设置默认填充内容为"-"
    if(!fillText){
        fillText = "-";
    }
    // 获取sheet[0]对象
    var sheet = spreadsheetReport.sheets[sheetIndex]; 
    // 获得整个sheet对象
    var worksheet = spreadsheetReport.workbook.worksheets.get(sheetIndex);
    // 获得sheet对象中所有的单元格对象，下面会根据位置获得对应的具体的单元格
    var cells = worksheet.cells;
    // 如果有传notEmptyCell参数，且单元格里面值为空，则说明是空表，不再填充对应内容
    if(notEmeptyCell){
        // 用来表示是否需要填充
        var isEmptyGrid = true;
        // 根据指定单元格获取到所有扩展出来的单元格的位置的数组
        var emptyCellPostions = null;
        try{
            emptyCellPostions = sheet.getExpandedPositions(notEmeptyCell);    
        }catch(e){
            return;
        }
        
        if(!emptyCellPostions){
            return;
        }
        // 遍历该位置数组
        for(var j = 0;j < emptyCellPostions.length; j++){
            // 获得当前位置
            var emptyCellPos = emptyCellPostions[j];
            // 根据位置得到具体的单元格对象
            var emptyCell = cells.get(emptyCellPos.row,emptyCellPos.column);
            // 如果该单元格不为空，则说明不是空表
            if(emptyCell.value){
                isEmptyGrid = false;
                break;
            }
        }
        // 如果是空表的话，就不再填充内容了
        if(isEmptyGrid){
            return;
        }
    }
    // 如果不是空表，则进行填充
    // 根据指定单元格获取到所有扩展出来的单元格的位置的数组
    var positions = sheet.getExpandedPositions(cellAddress);    
    for (var i = 0; i < positions.length; i++) {
        var pos = positions[i];
        //合并单元格并设置合并后的单元格的值
        var cell = cells.get(pos.row, pos.column);
        logger.debug("cell: "+cell)
        logger.debug("cell.value: "+cell.value)
        if (!cell.value || cell.value == "" || Number(cell.value) == 0) { //判断值是否为空
            logger.info("set")
            cell.value = fillText;
        }
    }
}


// 做AA=>26,AB=> 27的转换
function parseRangeToInt(rangeString){
    var arr = getLetterArray();
    var result = 0;
    var brr = rangeString.toUpperCase().split('');
    for(var j = 0;j<brr.length;j++){
        var code = brr[brr.length-j-1];
        var jindex = arr.indexOf(code);
        if(j>0){
            jindex +=1;
        }
        if(jindex>-1){
            result += jindex * Math.pow(26,j);
        }
    }
    return result;
}

// 获得一个从A到Z的数组，用来做索引
function getLetterArray(){
    var arr = [];
    for(var i = 1;i<27;i++){
        arr.push(String.fromCharCode(i+64));
    }
    return arr;
}
// 做26=>AA,27=>AB的转换
function parseIntToRange(val){
    var arr = getLetterArray();
    var str = val.toString(26);
    var srr = str.split('');
    var result = '';
    for(var i = 0;i < srr.length;i++){
        var letter = srr[i];
        var nv = parseInt(letter,26);
        if(i<srr.length-1){
            result+=arr[nv-1];
        }else{
            result+=arr[nv];
        }
    }
    return result;
}

(3).在main方法中调用该方法，这次只需要传入区域就可以了。当然了后面三个参数的用法还是和3中的用法是一样的

function main(spreadsheetReport) {
    // 第一个sheet中的A1:M24区域中的、有绑定数据源的单元格及其扩展出来的单元格值为空时填充“-”
    fillEmptyRange("A1:M24");
}

4 服务端宏解决自动换行不会在单词中间换行的问题

在这里插入图片描述

服务端宏

function main(spreadsheetReport) {
	// 将A2单元格里面的长字符串格式化成每19个字符就插入一个硬回车符
    formatStringToWrapedLine("A19", 92)
}

/**
 * 根据给定的一行可容纳字符数，来插入硬回车
 * @param cell 要进行格式化的单元格
 * @param wordsLimit 一行可以容纳的字符数，excel中就是列宽，默认1000个字符
 * @param sheetIndex 当前工作薄的第几个sheet，默认0，即第一个sheet
 */
function formatStringToWrapedLine(cell, wordsLimit, sheetIndex) {
	// 用于存储for循环中每一行已累计的字符数
    var count = 0;
    // 用于拼接结果字符串
    var resultArr = [];
	// 第几个sheet，默认0，即第一个sheet页
    if (!sheetIndex) {
        sheetIndex = 0
    }
	// 每行可容纳字符数限制，默认给1000个
    if (!wordsLimit) {
        wordsLimit = 1000;
    }
	// 获得sheet页对象
    var sheet = spreadsheetReport.sheets[sheetIndex]
    // 获得指定单元格对象
    var cell = sheet.getCell(cell);
    // 单元格的值
    var str = cell.value;
    // 把原文拆成char数组
    var arr = str.split('');
    // 用于判断是否是中文的正则
    var reg = /[\u4e00-\u9fff]/
    for (var i = 0; i < arr.length; i++) {
    	// 当前char字符
        var curStr = arr[i];
        // 当前字符个数
        var curLen = 1;
        // 一个汉字占2个字符，其他的按1个算
        if (reg.test(curStr)) {
            curLen = 2;
        }
        // 如果当前行已累计的字符数 再加上2个字符长度后 >= 每行的字符限制，则插入Excel里面的硬换行符
        if (count + 2 >= wordsLimit) {
        	// 插入换行符
            resultArr.push(String.fromCharCode(10));
            resultArr.push(String.fromCharCode(13));
            // 拼接上本轮的字符
            resultArr.push(curStr);
            // 重置计数
            count = 1;
        } else {
        	// 不需要换行的话就直接拼接即可
            resultArr.push(curStr);
            count += curLen;
        }
    }
    // 指定单元格的位置
    var loc = cell.cellPosition;
    // 向指定单元格回写格式化后的字符串
    sheet.setCellValue(loc.row, loc.column, resultArr.join(''));
}

5 设置对齐

function main(spreadsheetReport) {
    modifyAlignment("k1","g3") // 如果k1单元格中为空，则修改g3单元格为左对齐，否则不操作
}

/**
 * 修改单元格对齐方式，如果给定的样品ID单元格里面的值为空，则将记录编号单元格的对齐方式修改为左对齐
 * @param sampleIdCellAddress 样品ID单元格地址，用于判定是否是空表
 * @param recordCodeCellAddress 记录编号单元格地址，如果是空表的话，会将该单元格的对齐方式修改为左对齐
 * @param sheetIndex sheet索引，用于指定是第几个sheet
 * @author zhangwenxuan
 */
function modifyAlignment(sampleIdCellAddress,recordCodeCellAddress,sheetIndex){
    sheetIndex = sheetIndex || 0;
    var sheet = spreadsheetReport.sheets[sheetIndex]; //获取sheet[0]对象
    var cells = sheet.cells;
    var sampleIdCells = spreadsheetReport.sheets[sheetIndex].getExpandedPositions(sampleIdCellAddress);
    var sampleIdCell = cells.get(sampleIdCells[0].row, sampleIdCells[0].column);
    if (sampleIdCell.value === null || sampleIdCell.value.length != 36) {
        var recordCodeCells = spreadsheetReport.sheets[sheetIndex].getExpandedPositions(recordCodeCellAddress);
        var cell = cells.get(recordCodeCells[0].row, recordCodeCells[0].column); //获取A4单元格对象
        var style = cell.getStyle(); //获取style
        style.setHorizontalAlignment(7); //7偏左  8偏右   1居中
        cell.setStyle(style);
    }
}