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

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

Hi Ephem,

Thank you so much for the code, But unfortunately It is in French, not able to understand the logic. Anyways Thanks.



Hi Thierry,

Can you please help me to solve the issue which can save my time as well? Thanks in advance.

Best Regards,

Srinivas.

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

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

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