How to import values through Excel?

Hi,

I have this problem 6months ago. And I made this for read all the XLS File :

function FaireImport()
{
    var test = true;
    var tab_SelecFile = new Array() ;
    // DialBox Pour recup un fichier xls
    tab_SelecFile = Dialogs.getFilePath("","*.xls",Context.getSelectedPath(),"Selection du fichier Excel",0);
    
    if(tab_SelecFile==null || tab_SelecFile==undefined )
    {
        Dialogs.MsgBox("Aucun fichier selectionné !")
        test = false
    }
    if ( test == true )
    {
        var ParamFile= null ;
        ParamFile= tab_SelecFile[0] ;
        
        //FICNAME = ParamFile.getName() ;
        var xlsReader = Context.getExcelReader(ParamFile.getData()) // XlsWorkbook 
        
        var fichierExcel= new fichierXLS(xlsReader )
        TraiterFichierImport( fichierExcel )
    }
}


// modelisation memoire d'un fichier xls
function fichierXLS(xlsReader)
{
    this.fichier = xlsReader ; 
    this.listeFeuille = new Array();
    this.init = function()
    {
        var listeFeuille = new Array() 
        listeFeuille =this.fichier.getSheets() ;
        //recup des lignes
        for(var i = 0 ; i< listeFeuille.length;i++)
        {
            this.listeFeuille.push( new feuille_clss(listeFeuille[i],i)); // i = 0 avant
        }
    }
    this.init();    
}

function feuille_clss(feuille, Objfeuille)
{
    this.feuille = feuille ;
    this.nomFeuille = feuille.getName() ;
    this.num = Objfeuille ; 
    this.listeLigne = new Array();
    
    this.init = function()
    {
        var i = 0 ; 
        while( i<this.feuille.getLastFilledRowNum()+1 )
        {
            if(this.feuille.getRowAt(i)!=null && this.feuille.getRowAt(i)!=undefined )
            {
                new_ligne = new ligne_clss(this.feuille.getRowAt(i),i) ;                             
                this.listeLigne.push(new_ligne);
            }
            i++ ; 
        } 
    }
    this.init();
}

function ligne_clss(ligne, numLigne)
{
    this.ligne = ligne ;
    this.num = numLigne ; 
    this.listeCellules = new Array() ;
    this.ligneStr = "" ;
    this.init = function()
    {
        if(this.ligne.getLastCellNum() >0)
        {  
            var i = 0 ;
            // recup des cellules.
            while( i< this.ligne.getLastCellNum() )
            {
                var currCell = new cellule_clss(this.ligne.getCellAt(i),i) 
                this.ligneStr += currCell.texte+ ";"
                this.listeCellules.push(currCell );
                i++ ; 
            }
        }      
    }
    this.init();        
}

function cellule_clss(cellule, numCellule)
{
    this.cellule = cellule ;
    this.texte = "" ;
    this.col=0
    this.num = numCellule ;
    
    this.init = function()
    {
        if(this.cellule !=null)
        {
            this.texte = this.cellule.getCellValue ( ) ;
            this.col=  this.cellule.getCellIndex ( ) ;
        }
        else
        {
            this.col= this.num ;
        }
        this.cellule =null ; 
    }
    this.init();    
}

Here you got all your XLS File in class and you can get all informations you need with :

function TraiterFichierImport (ObjetFichierExcel){

//ObjetFichierExcel.listeFeuille => list of sheets
//ObjetFichierExcel.listeFeuille[0].listeLigne => sheet 1 , list of lignes
//ObjetFichierExcel.listeFeuille[0].listeLigne[0].listeCellules => sheet 1 , ligne 1, list of cells
String str = ObjetFichierExcel.listeFeuille[0].listeLigne[0].listeCellules[1].texte;  // here you have the text in the sheet 1, ligne 1, cell 2
    
 //[...]
 }

And you can search in your XLS File the information you need.

PS : sorry for script and comments langage , I'm french

Hi

Self created  EPC model having default relationship type for a control symbol in multiple places. Now I want to change the relationship type for the same.

Is there a way to change "relationshiptype" in one go.

I have no access to write and run script for this. Please let us know.

Any upate please shre

Thanks

Ephem, thank you sooooo much for this code ! . Awesome !  It saves me a lot of time.

Hello.  The code loads an xls file into memory to use Array functions to navigate through cells.

Below is a translation.

Bgrds,

function ImportXLS() 
{     
    var test = true;     
    var tab_SelecFile = new Array() ;     
    // DialBox to get xls file     
    tab_SelecFile = Dialogs.getFilePath("","*.xls",Context.getSelectedPath(),"Select Excel file",0);           
    if(tab_SelecFile==null || tab_SelecFile==undefined )     {
        Dialogs.MsgBox("No file selected !")
        test = false    
    }     
    if (test == true)     {
        var ParamFile= null ;
        ParamFile= tab_SelecFile[0] ;
        //FICNAME = ParamFile.getName() ;
        var xlsReader = Context.getExcelReader(ParamFile.getData()) 
        // XlsWorkbook                    
        var Excelfile= new XLSfile(xlsReader)
        return Excelfile
    } 
}     
// function to load XLS file in memory 
function XLSfile(xlsReader) 
{
    this.file = xlsReader;
    this.listSheet = new Array();
    this.init = function() {         
        var listSheet = new Array()
        listSheet = this.file.getSheets() ;
        //get the lines
        for(var i = 0 ; i< listSheet.length;i++) {
            this.listSheet.push( new sheet_clss(listSheet[i],i));  // i = 0 avant         
        }     
    }     
    this.init();
}
function sheet_clss(sheet, Objsheet) 
{     
    this.sheet = sheet ;
    this.sheetname = sheet.getName() ;
    this.num = Objsheet ;
    this.listLine = new Array();
    this.init = function()     {
        var i = 0 ;
        while( i<this.sheet.getLastFilledRowNum()+1 ) {
            if(this.sheet.getRowAt(i)!=null && this.sheet.getRowAt(i)!=undefined ) {
                new_line = new line_clss(this.sheet.getRowAt(i),i) ;
                this.listLine.push(new_line);
            }
            i++ ;
        }
    }
    this.init();
}
function line_clss(line, numLine) 
{
    this.line = line ;
    this.num = numLine ;
    this.listCells = new Array() ;
    this.lineStr = "" ;
    this.init = function()
    {
        if(this.line.getLastCellNum() >0)
        {
            var i = 0 ;
            // get the cells
            while( i< this.line.getLastCellNum() )
            {
                var currCell = new cell_clss(this.line.getCellAt(i),i)
                this.lineStr += currCell.txt+ ";"
                this.listCells.push(currCell );
                i++ ;
            }
        }
    }
    this.init();
}
function cell_clss(xlscell, numCell)
{
    this.xlscell = xlscell;
    this.txt = "";
    this.col=0;
    this.num = numCell ;
    this.init = function()          {
        if(this.xlscell !=null)         {
            this.txt = this.xlscell.getCellValue ( ) ;
            this.col=  this.xlscell.getCellIndex ( ) ;
        }
        else        {
            this.col= this.num ;
        }
        this.xlscell =null ;
    }
    this.init();
}       

Hi,

First of all, thank you Ephem for sharing your piece of code. It helped me a lot.

However, I tried a bit myself and now got the following, which I believe does the same, but is much simpler, and I thought I might share it with you:

var source_xls = Dialogs.getFilePath("", "*.xls", Context.getSelectedPath(), "Choose XLS file that should be parsed", 0);
var xls_workbook = Context.getExcelReader(source_xls[0].getData());

var data = parse_xls(xls_workbook);

function parse_xls (xls) {
    // This function parses an XlsWorkbook object into an array.
    // Structure: data[sheet_num][row_num][cell_num]
    // Example: 1st sheet, 2nd row, 3rd cell: data[0][1][2]
    
    var data = [];
    
    for (var i = 0; i < xls.getSheetCount(); i++) {
        // This loop iterates through each sheet
        
        var rows = xls.getSheetAt(i).getRows();
        data[i] = [];
        
        for (var j = 0; j < rows.length; j++) {
            // This loop iterates through each filled row
            
            var cells = rows[j].getCells();
            data[i][j] = [];
            
            for (var k = 0; k < cells.length; k++) {
                // This loop iterates through each filled cell
                
                data[i][j][k] = cells[k].getCellValue();
            }
        }
    }
    
    return data;
}

Dialogs.MsgBox(data[0][1][2]);

Hope this helps :)

Good day,

I want to create customised reports in ARIS.
Could you please share some references/videos to learn scripting in ARIS Cloud Administration.

Best Regards,
Naveen