Rick Beddoe's picture
Posted by Rick Beddoe
Posted on 2012-01-12 in Reports & Macros

When dealing with large numbers of scripts it is a good idea to categorize those reports using a directory structure:

A common desire when creating reports is to have the ability to call one report with another report. In order for that to work, you have to have both reports in the same directory. This can be a problem if you want to share a report across directories.

The solution to this is to create a Javascript file and place it in the Common Files folder.  Copy the code from the report you wish to share and put it in this Javascript file.

This is fine if all you’re going to do is call the report and run it. However, this can still result in a great deal of duplication of functions, variables, etc. throughout your Reports structure. The best way to eliminate this is to take an object oriented approach to developing ARIS scripts. The key is to remember that you have the Javascript domain at your disposal. Javascript is inherently object oriented. By taking advantage of this, you can greatly reduce the amount of duplication you may encounter when developing large numbers of individual reports.

I will attempt to demonstrate the approach I’ve taken to help cut down on a lot of code-copying.  I will use some Excel Formatting Classes that I built that I can call from any report. This design pattern separates data from presentation. That is a key component of efficient data processing.

Classes

A key component to OOP (Object Oriented Programming) is the Class. A Class is nothing more than a collection of functions and variables that have a common ‘theme’.  In this example in ARIS, the classes are individual Javascript files stored under Common files.

By placing this class here, I will be able to call it from any report by going to the properties of my report and selecting ‘Imported files’. This will display all files available under Common files as well as any other reports that are available in my current directory. We are only concerned with Common Files.

Creating a Class in ARIS

To create a class, you’ll need to import an existing file. You can simply make an empty text file and give it an extension of ‘js’. Or you can put some predefined calls in there or possibly comments. In any case, you create this file outside of ARIS using Notepad or some other text editor. I created a file simply called ‘script.js’. Then, right click on Common files and select Add files. Navigate to the file you created. When you import, you will see ‘script.js’ in the list of files. Rename that file. You will want to create a class for each ‘theme’ you are creating the class for. In the example, I’ve created a single class to deal with the ‘theme’ of building an Excel Report.

Naming Classes

A common way to name a class is to use dot notation (similar to how web addresses are formatted). This allows you to give the file a meaningful name such My.Excel.Report.js.

By using an intelligent naming scheme, you will be able to easily identify the intended purpose of the class. You will also be able to relate this to the internal name of the class which I will explain in a moment.

Public and Private Members

Hopefully you have had at least a minimum of exposure to writing scripts in ARIS. If so, then you are already familiar with ‘private’ members. A ‘Member’ is either a variable or a function. You have declared private variables using ‘var’:

var MyPrivateVariable = “foo”;

and you have declared private functions using ‘function’:

 function MyPrivateFunction(){
  Dialogs.MsgBox(MyVariable);
 }

Private members can only be accessed within the class where they’re declared. This doesn’t do much good if you want to access a function within a class that you’ve imported. In order to make it visible to your importing class, you have to make it public. The way to do it is to use the keyword ‘this’:

 this.MyPublicVariable = “foo”;

and for public functions:

 this.MyPublicFunction = function(){
  Dialogs.MsgBox(“foo”);
 }

There is an additional keyword ‘prototype’ that can be used, but to keep things simple I will not be discussing its use.

Declaring the Internal Class

To start building your class, open up the file you created earlier. The internal class is the first thing you’ll define by, oddly enough, declaring a private function:

function MY_EXCEL_REPORT(){
}

The Internal Class name is ‘MY_EXCEL_REPORT’. Notice I’ve followed a similar naming convention as the file. This will help keep things organized. As written though, it doesn’t really do anything. So let’s start populating it. We will probably want to pass in a parameter to let the class know what name we want to give our Excel Workbook.

Function MY_EXCEL_REPORT(_workbookName){
}

Then we’ll actually want the class to initialize an Excel Workbook

Function MY_EXCEL_REPORT(_workbookName){
var oWorkbook = Context.createExcelWorkbook(_workbookName + ".xls");  
 this.WorkBook = oWorkbook;
}

Accessing the Class

There are two simple steps needed for your report to have access to the class.

Step 1.) Import the class. In the properties of a Report, select ‘Imported files’.  You should see Common Files/My.Excel.Report.js in the list. Click the check box to import the file, creating a reference to the class.

Step2.) Declare the class inside your report somewhere near the top:

var oMyExcelReport = new MY_EXCEL_REPORT(“My Excel Report File”);

Now, you will be able to access the public ‘WorkBook’ variable that’s inside the MY_EXCEL_REPORT class simply using:

var oMyWorkbook = oMyExcelReport.Workbook;

Using the 'new' keyword

Notice that when we first initialized the 'oMyExcelReport' variable, we used the 'new' keyword in front of the class name. It is important to understand how this works. Using the 'new' keyword creates a copy of the class or 'object' in memory that the script can access. Inside the class itself, the class itself, this will initialize all the members in the class. This is important to know as it allows you to have pre-set values within the class when you initialize it. Note that things like colors and fonts are already set in the class so you don't have to mess with any of that in your main code.

Hopefully that simple example demonstrates the power of making your ARIS reports more Object Oriented. You will no longer need to write the code necessary in each and every report. You simply reference the class that will take care of that for you.

Still, you may not be convinced. I mean, referencing a class then getting a value out seems to be more work than simply using ‘createExcelWorkbook’ at the top of your report. However, that’s just the tip of the iceberg. Beyond just declaring the Workbook, you need to also declare all the formatting for fonts, column units, widths, heights, etc. This is where the class takes a real load off. You use the class to manage all the details that you have been re-writing into all your reports. Below is the final class with all public and private members.

If you find this useful (or if you find any major mistakes!) please give feedback.

In my next article, I will post the Dataset class I use to populate this Excel Report class.

Enjoy!

Rick Beddoe
Cargill Aris Technical Analyst
Minneapolis, MN, USA

 

///My Excel Report Builder
function MY_EXCEL_REPORT(_workbookName) {
    //My.Excel class definition 
    var oWorkbook = Context.createExcelWorkbook(_workbookName + ".xls");  
    this.WorkBook = oWorkbook;
    
    ///creates a Worksheet
    this.Report = function(oReportName) {

        ///public properties
        this.Name = oReportName;
        this.Table = null;
        this.Rows = [];
        this.Data = [];
        this.Worksheet = oWorkbook.createSheet(this.Name);
        //default font for Header
        this.HeaderFont = {
                            name:"Arial", 
                            height:8, 
                            color:Constants.C_BLACK, 
                            foregroundColor:Constants.C_GREY_80_PERCENT,
                            fillPattern:Constants.SOLID_FOREGROUND};
                            
        //default font for Data
        this.DataFont = {
                            name:"Arial", 
                            height:8, 
                            color:Constants.C_BLACK, 
                            foregroundColor:Constants.C_TRANSPARENT,
                            fillPattern:Constants.NO_FILL};
                            
        ///public functions
        ///populate the Excel Worksheet
        this.Write = function(_sort){
            var oHeaderCell = _setCell(this.HeaderFont);
            var oDataCell = _setCell(this.DataFont);
            var oColumnIndex = 0;
            for (var oColumn in this.Table.Columns.sort(_columnSort)){
                var oColumnValue = this.Table.Columns[oColumn].value;
                var _excelRow = this.Worksheet.createRow(0);
                writeXlsCell(_excelRow,oColumnIndex,oHeaderCell,oColumnValue);
                oColumnIndex++;
            }
            
            var oOrderedRows = JSLINQ(this.Table.Rows)    
            .OrderBy(function(row){return row.ItemArray[_sort];}).ToArray();
            
            //for (var oRow in this.Table.Rows.sort(function _rowSort(a,b){return a.ItemArray[_sort]-b.ItemArray[_sort];})){
                for (var oRow in oOrderedRows){
                var _excelRow = this.Worksheet.createRow(parseInt(oRow) + 1);
                var oDataRow = oOrderedRows[oRow];
                oColumnIndex = 0;
                
                for (var oColumn in this.Table.Columns.sort(_columnSort)){
                    var oColumnKey = this.Table.Columns[oColumn].key;
                    writeXlsCell(_excelRow,oColumnIndex,oDataCell,oDataRow.ItemArray[oColumnKey]);
                    oColumnIndex++;
                }                   
            }
            _autoFitColumns(oWorkbook,this.Worksheet,this.Table.Columns.length);
        }
        
        
       ///private functions 
       ///set cell formatting
       function _setCell(_fontStyle){    
           var oFont = oWorkbook.createFont();
           with (oFont) {
               setFontName(_fontStyle.name);
               setColor(_fontStyle.color);
               setFontHeight(_fontStyle.height * 20);               
           }
           var oCell = oWorkbook.createCellStyle(oFont,1,1,1,1,1,1,1,1,1,1,1,1,1);
           with (oCell) {
               setFont(oFont);
               setFillForegroundColor(_fontStyle.foregroundColor);
               setFillPattern(_fontStyle.fillPattern);
           }
           return oCell;
       }
              
       ///ensure column data is sorted via sort order - ascending
       function _columnSort(a,b){
            return a.index-b.index;
       }       
        
      function _rowSort(a,b){
            return a._sort-b._sort;
       }       
        ///format and fill a cell
        function writeXlsCell(row,cellIndex,cellStyle,p_String) {
            var xlsCell = row.createCell(cellIndex);            
            xlsCell.setCellStyle(cellStyle);
            xlsCell.setCellValue(p_String);
        }
        
        ///automatically fit column widths
        function _autoFitColumns(_workBook, _workSheet) {
            var oHeaderRow = _workSheet.getRowAt(0);
            if (oHeaderRow == null){return;}
            var _columnCount = oHeaderRow.getAbsoluteFilledCellCount();
            var oRowCount = _workSheet.getAbsoluteFilledRowCount();
            for (i=0;i<_columnCount;i++)
            {
                
                 var oColWidth = 0;
                 var oCellWidth = 0;
                 var oCellFontHeight = 0;
                 
                 for (j=0;j<oRowCount;j++)
                 {
                     var oCell = _workSheet.getCell(j, i);
                     var oFont = _workBook.getFontAt(oCell.getCellStyle().getFontIndex());
                     
                     if (oCell != null)
                     {
                         var oHeight = oCellFontHeight / 20;
                         var oValue = oCell.getCellValue();
                         var oCharCount = oValue.length();
                         if (oCharCount > oCellWidth)
                         {
                             oCellWidth = oCharCount ;
                             oCellFontHeight = oFont.getFontHeight();
                         }
                     }
                 }
                 oColWidth = (oCellWidth + 0) * 256  * (oHeight / 9);
                _workSheet.setColumnWidth(i,oColWidth);
            }
        }
    }     
}
Tags: Report javascript excel tutorial ARIS script 
31007
7
2