///MY Excel Report Builder
function MY_EXCEL(_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);
            }
        }
    }     
}