【泛微E9】数据填报excel表格赋高亮

技术细节

获取当前sheet某行某列字段数据

var llvalue = sheet.getCell(rowIndex, colIndex).value();

运用accDiv，accAdd计算excel列得平均数与总数

accDiv(accAdd(accAdd(llvalue,ll1value),ll2value),3)

遍历明细表当前行得所有单元格，赋高亮

for (var index = startCol; index < startCol + colCount; index++) {
                        var style1 = sheet.getStyle(rowIndex, index);
                        style1.backColor = "red"; 
                        sheet.setStyle(rowIndex, index, style1);
                         }

整体代码如下：

function afterUploadData() {

    var sheet = excel.getActiveSheet();
    var table = sheet.tables.findByName('detail1');//取明细表1的对象
    if (table) {
        var colIndex = 6;//来料1 ll字段的列索引
        var dataRange = table.dataRange();
        var startCol = dataRange.col;   
        var colCount = dataRange.colCount;
        var startRow = dataRange.row;//明细表开始行
        var rowCount = dataRange.rowCount;//明细表行数
        excel.workbook.suspendPaint();

        var jh3value;
        for (var rowIndex = startRow; rowIndex < startRow + rowCount; rowIndex++) {

            var llvalue = sheet.getCell(rowIndex, colIndex).value();//ll字段的值
            var ll1value = sheet.getCell(rowIndex, colIndex+1).value();//ll1字段的值
            var ll2value = sheet.getCell(rowIndex, colIndex+2).value();//ll2字段的值
            jh3value = sheet.getCell(rowIndex, colIndex+3).value();//jh3字段的值


            var isHistory = false;
            //当来料1、来料2、来料3中有一个有值，则为历史数据
            if((llvalue != null && typeof llvalue !='undefined') || (ll1value != null && typeof ll1value !='undefined') || (ll2value != null && typeof ll2value !='undefined') ){
                isHistory = true;
            }

            if(jh3value>=100){
                var llzvalue = accDiv(accAdd(accAdd(llvalue,ll1value),ll2value),3);
                var value1 = accDiv(jh3value,llzvalue);      //计算当月计划与前三个月来料均值对比
                if (value1 < 0.5 || value1>1.5) {//满足条件设置背景色
                    for (var index = startCol; index < startCol + colCount; index++) {
                        //遍历明细表当前行的所有单元格，赋高亮
                        var style1 = sheet.getStyle(rowIndex, index);
                        style1.backColor = "red";
                        sheet.setStyle(rowIndex, index, style1);
                    }
                }
                else{
                    for (var index = startCol; index < startCol + colCount; index++) {
                        var style1 = sheet.getStyle(rowIndex, index);
                        if(isHistory){
                            style1.backColor = null;
                        } else {
                            style1.backColor = "yellow";
                        }
                        sheet.setStyle(rowIndex, index, style1);
                    }
                }
            }
            else {
                for (var index = startCol; index < startCol + colCount; index++) {
                    var style1 = sheet.getStyle(rowIndex, index);
                    if(isHistory){
                        style1.backColor = null;
                    } else {
                        style1.backColor = "yellow";
                    }
                    sheet.setStyle(rowIndex, index, style1);
                }
            }

            var value = sheet.getCell(rowIndex, colIndex).value();//ll字段的值
            if (value < 0&&ll1value<0&&ll2value<0) {//满足条件设置背景色
                for (var index = startCol; index < startCol + colCount; index++) {
                    //遍历明细表当前行的所有单元格，赋高亮
                    var style1 = sheet.getStyle(rowIndex, index);
                    style1.backColor = "yellow";
                    sheet.setStyle(rowIndex, index, style1);
                }
            } else {
                
            }

        }
        excel.workbook.resumePaint();
    }
    return { triggerFieldAssign: false };//导入数据后不触发字段赋值
};

结果