Groovy web console

Subscribe to this site

RX

Published 6 months ago by test1 with tags test1

//ReadXLSX
//INPUT: XLSXPATH-Path to the Excel file-/app/resolve/netcool/
//XLSXFILE -Filename of Excel file - rjbragglistexcel.xlsx
//OUPUT:SQLLIST

//Content
import org.apache.poi.ss.usermodel.*
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.xssf.usermodel.*
import org.apache.poi.ss.util.*
import org.apache.poi.ss.usermodel.*
import java.io.*
import java.security.MessageDigest

class GroovyExcelParser {
    //http://poi.apache.org/spreadsheet/quick-guide.html#Iterator
    
    def parse(path) {
        InputStream inp = new FileInputStream(path)
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        Iterator<Row> rowIt = sheet.rowIterator()
        Row row = rowIt.next()
        def headers = getRowData(row)

        def rows = []
        while(rowIt.hasNext()) {
            row = rowIt.next()
            rows << getRowData(row)
        }
        inp.close()
        [headers, rows]
    }
    
    def getRowData(Row row) {
        def data = []
        for (Cell cell : row) {
            getValue(row, cell, data)
        }
        data
    }
    
    def getValue(Row row, Cell cell, List data) {
        def rowIndex = row.getRowNum()
        def colIndex = cell.getColumnIndex()
        def value = ""
        
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            value = "'"+cell.getRichStringCellValue().getString()+"'";
            
            // try to handle conversions (e.g. Class) where we want the numeric values not all the text
            value = value.replaceAll(/\'.*\((\d+)\)\'$/){full,code -> return code}
            break;
        
        case Cell.CELL_TYPE_NUMERIC:
            value = cell.getNumericCellValue();
            // Netcool wants UTC date/time so this takes the Excel numberic coded value and converts
            if (DateUtil.isCellDateFormatted(cell)) {
                value = ( value - 25569 ) * 86400
            }
            value = value.round()
            break;
        
        case Cell.CELL_TYPE_BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        
        case Cell.CELL_TYPE_FORMULA:
            value = cell.getCellFormula();
            break;

        default:
            value = ""
        }
        data[colIndex] = value
        data
    }


}



def filePath = "/app/resolve/tmp/excel";
def fileName = "Abschaltung_50224539.xlsx";
def filePathName = filePath+'/'+fileName;

//def filename = INPUTS["XLSXPATH"] + INPUTS["XLSXFILE"]
def filename = filePathName;

GroovyExcelParser parser = new GroovyExcelParser()
def (headers, rows) = parser.parse(filename)

/*
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


def fs = new FileInputStream("/app/resolve/tmp/excel/Abschaltung_50224539.xlsx")
def wb = new XSSFWorkbook(fs);
def ws = wb.getSheet("Sheet1");
def r = ws.getPhysicalNumberOfRows()
def detail;
for(def i=0; i<r; i++)
{
    def row = ws.getRow(i);
    def c = row.getPhysicalNumberOfCells();

    for (def j=0;j<c;j++)
    {
        def cell = row.getCell(j);
        def d = cell.getStringCellValue();
        //log.info d
        detail = d;
    }
}*/

//assess
def condition = "bad"
def severity = "critical"
def summary = ""
def detail = RAW

//detail += detail;


def getIndex(def str, def dtl) {
        
    def rtnIndex= dtl.indexOf(str);
    rtnIndex;
}

if (detail.contains("FileNotFound")) {
    detail = "File not found"
} 
else 
{            
    condition = "good"
    severity = "good"
        
    //def check = detail.contains("VPSZ");    
    detail = detail.replaceAll("\\[", "").replaceAll("\\]", "").replaceAll("\'", '');    
    if(detail.contains("VPSZ") && detail.contains("ASB"))
    {
        detail += "\n\n"+getIndex("VPSZ", detail);        
        detail += "\n\n"+detail.substring(getIndex("VPSZ", detail), getIndex("ASB", detail));    
        detail += "\n"+detail.substring(getIndex("ASB", detail), getIndex("ASB", detail).plus(6));
    }    
    detail = "\n\nOutput:"+detail;
    //detail = "Final Output:$check \n"+detail;
}

RESULT.condition = condition
RESULT.severity = severity
RESULT.summary = summary
RESULT.detail = detail