Joomla PhpExcel layout working example

<?php defined('_JEXEC') or die(); ?>
<?php
JHtml::addIncludePath(JPATH_COMPONENT.'/helpers/html');
//
// see also http://pastebin.com/71iRCXB1 (Joomla PhpExcel working example first report)
// see also http://pastebin.com/TfKq8NZ4 (xlsx.php for joomla and PhpExcel)
// see also http://pastebin.com/kM9wtd1c (xls.php for joomla and PhpExcel)

$params = JComponentHelper::getParams('com_cn_reports');
$cartTz = null;
$useTz = filter_var($params->get('use_opencart_timezone',false), FILTER_VALIDATE_BOOLEAN);
if ($useTz == true) {
    $cartTz = $params->get('opencart_timezone','America/Toronto');
}

$objPhpExcel = $this->phpexcel;
$rowOffset = 7; // athe amount of rows to leave above the sheet data rows.
$col_count = 11; // the number of colums in the commissions data
$data_row_offset = 3; // the number of rows to increas by before writing data into the table. Makes room for header rows

// rowRptDisplayTitle is the row to set the title for the report on.
// set rowRptDisplayTitle to 0 (zero) to hide the row
$rowRptDisplayTitle = 4;
if (filter_var($this->showTitle, FILTER_VALIDATE_BOOLEAN) == false) {
    $rowRptDisplayTitle = 0;
}
// rowHeaderText is the row to display the header text on for the report
// set rowHeaderText to 0 (zero) to hide the row.
$rowHeaderText = 6;
if (filter_var($this->showHeader, FILTER_VALIDATE_BOOLEAN) == false) {
    $rowHeaderText = 0;
}
// overrideOffset: set this value to the number of rows below the commissions that
// you want the overrides to be in . Later the rowOffset and the number of rows in
// the commissions will be added to this value.
$overrideOffset = 2;
// rowFooterText is the offset to display the footer text below the last row of
// the report data. If 0 (zero) will not be displayed
$rowFooterText = 2;
if (filter_var($this->showFooter, FILTER_VALIDATE_BOOLEAN) == false) {
    $rowFooterText = 0;
}

// set up font for report title on top of page below logo
$rowRptTitleFontStyle = array(
        'font'  => array(
                'bold'  => true,
                'color' => array('rgb' => $this->rowRptTitleFontcolor),
                'size'  => 13,
                'name'  => 'Verdana'
        ));
// set up font for The merger title above each table
$rowColumnTitleFontStyle = array(
        'font'  => array(
                'bold'  => true,
                'color' => array('rgb' => $this->rowColumnTitleFontColor),
                'size'  => 11,
                'name'  => 'Verdana'
        ));
// set up font for the column headers for each table
$rowColumnHeaderFontStyle = array(
        'font'  => array(
                'bold'  => false,
                'color' => array('rgb' => $this->rowColumnHeaderFontColor),
                'size'  => 10,
                'name'  => 'Verdana'
        ));
// set up font for the column footer for each table
$rowColumnFooterFontStyle = array(
        'font'  => array(
                'bold'  => false,
                'color' => array('rgb' => $this->rowcolumnfooterfontcolor),
                'size'  => 10,
                'name'  => 'Verdana'
        ));
// set up font for the alternate rows for each table
$rowAltRowFontStyle = array(
        'font'  => array(
                'bold'  => false,
                'color' => array('rgb' => $this->rowAltFontColor),
                'size'  => 11,
                'name'  => 'Calibri'
        ));
// set up array to place borders around sections
$styleOutline = array(
        'borders' => array(
                'outline' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THICK,
                        'color' => array('rgb' => '000000'),
                ),
        ),
);

// add commissions
//Setup header row
$column_index = 0;
$row_index = $rowOffset + 1;
$as = $objPhpExcel->getActiveSheet();
// an array of all the column widths. Later this will be applied to the columns
$cwidth = array(8.0,6.2,12.0,15.0,8.0,8.0,10.0,10.0,12.9,11.0,5.0,13.0);
// grab the header row for the commissions section here because we want to add the columns and do
// an auto resize on the columns. with a merged cell the columns will not rezize automatically

$row_commission_header = $row_index;
$row_index++;

$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_ORDERID'));
$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_COUNT'));
$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_OT_TOTAL'));
$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_WHOLESALE'));
$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_VOUCHER'));
$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_COUPON'));
$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_SHIPPING'));
$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_TAX'));
$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_APM'));
$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_DATE_ADDED'));
$as->getStyle("J$row_index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_COMMISSION_PERCENT'));
$as->setCellValueByColumnAndRow($column_index++, $row_index, JText::_('COM_CN_REPORTS_FORM_LBL_COMMISSION'));



// Array that contains all the colums that require right alignment
$rArray = array('A','B','C','D','E','F','G','H','I','K','L');
// loop the array and set all the cells to right alignment
for ($i = 0; $i < count($rArray); $i++) {
    $as->getStyle($rArray[$i] . $row_index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
}
//   $as->setCellValueByColumnAndRow($column_index++, 1, 'Time Zone');
//   $as->setCellValueByColumnAndRow($column_index, 2, $cartTz);

$as->getStyle("A$row_index:L$row_index")->getFill()
    ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
    ->getStartColor()->setRGB($this->rowColumnHeaderBgColor);
$as->getStyle("A$row_index:L$row_index")->applyFromArray($rowColumnHeaderFontStyle);
?>
<?php
  // Set autosizes on the columns
// for($i = 0; $i < $col_count; $i++)
// {
//  $as->getColumnDimensionByColumn($i)->setAutoSize(true);
//   //Calulate the column widths to attempt to autosize them

// }
    $as->calculateColumnWidths();
for ($i = 0; $i < count($cwidth); $i++)
{
    $as->getColumnDimensionByColumn($i)->setWidth($cwidth[$i]);
}
?>
<?php
// set the column header row
$row_commissions_start = $row_commission_header;
$as->mergeCells("A$row_commission_header:L$row_commission_header");
$as->getStyle("A$row_commission_header")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$as->getStyle("A$row_commission_header")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$as->setCellValue("A$row_commission_header",
        JText::sprintf('COM_CN_REPORTS_FORM_TITLE_COMISSIONS',
                $this->reportStart->format('Y-m-d'), $this->reportEnd->format('Y-m-d'),$this->partnerName));
$as->getStyle("A$row_commission_header")->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
->getStartColor()->setRGB($this->rowColumnTitleBgColor);
$as->getStyle("A$row_commission_header")->applyFromArray($rowColumnTitleFontStyle);
?>

<?php

for ($i = 0, $n = count($this->rows[0]); $i < $n; $i++)
{
    $row = &$this->rows[0][$i];
    //$as->getColumnDimensionByColumn($i)->setAutoSize(true);

    //Write out data rows
    // convert the order date to unix time including cart timezone
    // this seems to convert better to excel date time
    $time = JFactory::getDate($row->OrderDate, $cartTz)->toUnix();
    $column_index = 0;

    $row_index = $i + $data_row_offset + $rowOffset;
    $as->setCellValueByColumnAndRow($column_index++, $row_index, $row->OrderID);            // A
    $as->setCellValueByColumnAndRow($column_index++, $row_index, $row->Count);              // B
    $as->setCellValueByColumnAndRow($column_index++, $row_index, $row->otTotal);            // C
    $as->setCellValueByColumnAndRow($column_index++, $row_index, $row->Wholesale);          // D
    $as->setCellValueByColumnAndRow($column_index++, $row_index, $row->Voucher);            // E
    $as->setCellValueByColumnAndRow($column_index++, $row_index, $row->Coupon);             // F
    $as->setCellValueByColumnAndRow($column_index++, $row_index, $row->Shipping);           // G
    $as->setCellValueByColumnAndRow($column_index++, $row_index, $row->Tax);                // H
    $as->setCellValueByColumnAndRow($column_index++, $row_index, $row->ActualProfitMargin); // I

    // Set the order date column to excel time from unix. This seems to convert better.
    $as->setCellValueByColumnAndRow($column_index++, $row_index,
            PHPExcel_Shared_Date::PHPToExcel($time));   // J

    // set the date formate on the order date cell for the current row
    $as
        ->getStyle("J$row_index")
        ->getNumberFormat()
        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
    // set the alignment to left for the date
    $as->getStyle("J$row_index")
        ->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

    $as->setCellValueByColumnAndRow($column_index++, $row_index, $row->commissionpercent);  // K
    $as->setCellValueByColumnAndRow($column_index++, $row_index, $row->Commission);         // L
    // set up alternate row formating for even rows
    if (($i % 2) == 0) {
        $as->getStyle("A$row_index:L$row_index")->getFill()
            ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setRGB($this->altRowaltBgColor);
        $as->getStyle("A$row_index:L$row_index")->applyFromArray($rowAltRowFontStyle);
    }
 }
 ?>
<?php
    // write formulas
    $startRow = $rowOffset + $data_row_offset;
    $finalRow = $row_index;
    //$finalRow = count($this->rows[0]) + 1 + $rowOffset;
    $totalRow = $finalRow + 1;
    // set the row for the overrides sections to start
    // add the current value + the rows
    $overrideOffset = $overrideOffset + $totalRow;

    $as
        ->setCellValue("A$totalRow",JText::_('COM_CN_REPORTS_TOTAL'))
        ->setCellValue("B$totalRow","=SUM(B$startRow:B$finalRow)") // COUNT
        ->setCellValue("C$totalRow","=SUM(C$startRow:C$finalRow)") // ocTotal
        ->setCellValue("D$totalRow","=SUM(D$startRow:D$finalRow)") // Wholesale
        ->setCellValue("E$totalRow","=SUM(E$startRow:E$finalRow)") // Voucher
        ->setCellValue("F$totalRow","=SUM(F$startRow:F$finalRow)") // coupon
        ->setCellValue("G$totalRow","=SUM(G$startRow:G$finalRow)") // Shipping
        ->setCellValue("H$totalRow","=SUM(H$startRow:H$finalRow)") // Tax
        ->setCellValue("I$totalRow","=SUM(I$startRow:I$finalRow)") // ActualProfitMargin
        ->setCellValue("L$totalRow","=SUM(L$startRow:L$finalRow)"); // Commission

    // format teh footer background color
    $as->getStyle("A$totalRow:L$totalRow")->getFill()
    ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
    ->getStartColor()->setRGB($this->rowcolumnfooterbgcolor);
    $as->getStyle("A$totalRow:L$totalRow")->applyFromArray($rowColumnFooterFontStyle);

    $cArray = array('C','D','E','F','G','H','I','L'); // array of columns that are to be formated as currency

    // loop the arrray and format the columns as currency
    for ($i = 0; $i < count($cArray); $i++) {
        $currentCol = $cArray[$i];
        $range = $currentCol . $startRow . ':' . $currentCol . $totalRow;
        $as->getStyle($range)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
    }
    // format the commission percent column to percentage
    $as->getStyle("K$startRow:K$totalRow")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE);
    $row_commissions_totals = $totalRow;
    $row_commissions_end = $totalRow;
    $row_final = $totalRow;

?>
<?php
// set a border around the commisssions section
$as->getStyle("A$row_commissions_start:L$row_commissions_end")->applyFromArray($styleOutline);
?>
<?php
$overridesCount = count($this->rows[1]);
// add overrides
// only add overrides if there are some to add
if ($overridesCount > 0)
{
    // $objPhpExcel->setActiveSheetIndex(1);
    // $as = $objPhpExcel->getActiveSheet();
    //Setup header row
    $row_overrides_title = $overrideOffset + 1;
    $row_index = $row_overrides_title + 1;
    $data_row_offset = 3;

    //$as->setCellValueByColumnAndRow($column_index++, 1, JText::_('USERS.USERNAME'));
    $as->mergeCells("E$row_index:H$row_index");
    $as->getStyle("E$row_index")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $as->getStyle("E$row_index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $as->setCellValue("E$row_index", JText::_('COM_CN_REPORTS_FORM_LBL_OVERRIDES_SHORT_DESC'));

    $as->setCellValue("I$row_index", JText::_('COM_CN_REPORTS_FORM_LBL_ORDERID'));

    $as->mergeCells("J$row_index:K$row_index");
    $as->getStyle("J$row_index")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $as->getStyle("J$row_index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $as->setCellValue("J$row_index", JText::_('COM_CN_REPORTS_FORM_LBL_OVERRIDES_CREATED_TIME'));

    $as->setCellValue("L$row_index", JText::_('COM_CN_REPORTS_FORM_LBL_OVERRIDES_AMOUNT'));
    $as->getStyle("E$row_index:L$row_index")->getFill()
        ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
        ->getStartColor()->setRGB($this->rowColumnHeaderBgColor);
    $as->getStyle("E$row_index:L$row_index")->applyFromArray($rowColumnHeaderFontStyle);

    // Array that contains all the colums that require right alignment
    $rArray = array('I','L');
    // loop the array and set all the cells to right alignment
    for ($i = 0; $i < count($rArray); $i++) {
        $as->getStyle($rArray[$i] . $row_index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
    }
    $row_final = $row_index;
}
?>
<?php
// only add overrides if we have some
if ($overridesCount > 0)
{
    for ($i = 0, $n = $overridesCount; $i < $n; $i++)
    {
        $row = &$this->rows[1][$i];

      //Write out data rows

        // convert the order date to unix time including cart timezone
        // this seems to convert better to excel date time
        $time = JFactory::getDate($row->created_time, $cartTz)->toUnix();
        $column_index = 0;
        $row_index = $i + $data_row_offset + $overrideOffset;
        $as->mergeCells("E$row_index:H$row_index");
        $as->getStyle("E$row_index")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
        $as->getStyle("E$row_index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        $as->getStyle("E$row_index")->getAlignment()->setWrapText(true);
        $as->setCellValue("E$row_index", $row->override_short_desc);

        // only add the order_id if it is greater than zero. Less than 1 is a null
        if(isset($row->order_id) && ((int)$row->order_id > 0))
        {
            $as->setCellValue("I$row_index", $row->order_id);
        }

        $as->mergeCells("J$row_index:K$row_index"); // merge the date columns
        // set the date formate on the order date cell for the current row
        // Set the order date column to excel time from unix. This seems to convert better.
        $as->setCellValue("J$row_index", PHPExcel_Shared_Date::PHPToExcel($time));
        $as
        ->getStyle("J$row_index")
        ->getNumberFormat()
        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);


        // set the alignment to left for the date
        $as->getStyle("J$row_index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

        $as->getStyle("L$row_index")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
        $as->setCellValue("L$row_index", $row->amount);
        // set up alternate row formating for even rows
        if (($i % 2) == 0) {
            $as->getStyle("E$row_index:L$row_index")->getFill()
            ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setRGB($this->altRowaltBgColor);
            $as->getStyle("E$row_index:L$row_index")->applyFromArray($rowAltRowFontStyle);
        }
        $row_final = $row_index;
    }
}
?>
<?php
// only add overrides if we have some
if ($overridesCount > 0)
{
    // write the totals row for commission overrides
    $finalRow = $row_index;
    $startRow = $data_row_offset + $overrideOffset;
    // $finalRow = count($this->rows[1]) + 1 + $overrideOffset;
    $totalRow = $finalRow + 1;

    $as->setCellValue("E$totalRow",JText::_('COM_CN_REPORTS_TOTAL'));
    $as->setCellValue("L$totalRow","=SUM(L$startRow:L$finalRow)"); // amount
    // format teh footer background color
    $as->getStyle("E$totalRow:L$totalRow")->getFill()
            ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
            ->getStartColor()->setRGB($this->rowcolumnfooterbgcolor);

    $as->getStyle("L$totalRow")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
    $as->getStyle("E$totalRow:L$totalRow")->applyFromArray($rowColumnFooterFontStyle);
    $row_overrides_final = $totalRow;
    $row_final = $totalRow;
}
?>
<?php
// only add overrides if we have some
if ($overridesCount > 0)
{
    $row_overrides_start = $row_overrides_title;
    // set the column header title row for commissios
    $as->mergeCells("E$row_overrides_title:L$row_overrides_title");
    $as->getStyle("E$row_overrides_title")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $as->getStyle("E$row_overrides_title")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    $as->setCellValue("E$row_overrides_title",JText::_('COM_CN_REPORTS_FORM_TITLE_COMMISSIONS_OVERRIDES'));
    $as->getStyle("E$row_overrides_title")->getFill()
    ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
    ->getStartColor()->setRGB($this->rowColumnTitleBgColor);
    $as->getStyle("E$row_overrides_title")->applyFromArray($rowColumnTitleFontStyle);
}
?>
<?php
// set a border around the overrides section
if ($overridesCount > 0)
{
    $as->getStyle("E$row_overrides_start:L$row_overrides_final")->applyFromArray($styleOutline);
}
?>
<?php
// set the grand total sections
// we will not need a grand total section if we have not overrides in this report
if ($overridesCount > 0)
{

    //Setup header row
    $row_index = $row_overrides_final + 3;
    $row_total_start = $row_index;
    $as->mergeCells("E$row_index:L$row_index");
    $as->getStyle("E$row_index")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $as->getStyle("E$row_index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $as->setCellValue("E$row_index", JText::_('COM_CN_REPORTS_FORM_TITLE_GRAND_TOTAL'));

    $as->getStyle("E$row_index")->getFill()
        ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
        ->getStartColor()->setRGB($this->rowColumnTitleBgColor);
    $as->getStyle("E$row_index")->applyFromArray($rowColumnTitleFontStyle);

    // set up the commissions totals
    $row_index++;
    $row_total_total_overrides = $row_index;
    $as->mergeCells("E$row_index:K$row_index");
    $as->getStyle("E$row_index")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $as->getStyle("E$row_index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $as->setCellValue("E$row_index",JText::_('COM_CN_REPORTS_FORM_LBL_COMMISSION_TOTAL'));
    $as->setCellValue("L$row_index","=L$row_overrides_final");
    $as->getStyle("L$row_index")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
    // set the alternative style on the commission row
    $as->getStyle("E$row_index:L$row_index")->getFill()
    ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
    ->getStartColor()->setRGB($this->altRowaltBgColor);
    $as->getStyle("E$row_index:L$row_index")->applyFromArray($rowAltRowFontStyle);

    // set up the overrides total row
    $row_index++;
    $row_total_total_commissions = $row_index;
    $as->mergeCells("E$row_index:K$row_index");
    $as->getStyle("E$row_index")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $as->getStyle("E$row_index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $as->setCellValue("E$row_index",JText::_('COM_CN_REPORTS_FORM_LBL_OVERRIDES_TOTALS'));
    $as->setCellValue("L$row_index","=L$row_commissions_totals");
    $as->getStyle("L$row_index")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);

    // set up the total of totals row
    $row_index++;
    $as->mergeCells("E$row_index:K$row_index");
    $as->getStyle("E$row_index")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $as->getStyle("E$row_index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $as->setCellValue("E$row_index",JText::_('COM_CN_REPORTS_FORM_LBL_GRAND_TOTAL'));
    $as->setCellValue("L$row_index","=(L$row_total_total_commissions - L$row_total_total_overrides)");
    $as->getStyle("L$row_index")->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);

    $as->getStyle("E$row_index:L$row_index")->getFill()
    ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
    ->getStartColor()->setRGB($this->rowcolumnfooterbgcolor);
    $as->getStyle("E$row_index:L$row_index")->applyFromArray($rowColumnFooterFontStyle);

    $row_final = $row_index;
    $row_total_end = $row_final;
}
?>
<?php
// set a border around the totals section
if ($overridesCount > 0)
{
    $as->getStyle("E$row_total_start:L$row_total_end")->applyFromArray($styleOutline);
}
?>
<?php
// display the logo on the excel page for the compnent
if (filter_var($this->showLogo, FILTER_VALIDATE_BOOLEAN) == true) {
    $company_logo = $params->get('company_logo','');

    if ($company_logo !== "") {
        // wrap in try block just incase the image fails
        try {
            $objDrawing = new PHPExcel_Worksheet_Drawing();
            $objDrawing->setName('Logo');
            $objDrawing->setDescription('Logo');
            // setPath expects a filepath instead of a url
            // Using forward slash to ensure php compatibality on other servers.
            $objDrawing->setPath(JPATH_ROOT . '/' . $company_logo);
            $objDrawing->setCoordinates('A1');
            $objDrawing->setWorksheet($as);
        } catch (Exception $e) {
        }

    }
}

?>
<?php
// display the title text just under the logo
// only display the title if the $rowRptDisplayTitle is greater than zero
if ($rowRptDisplayTitle > 0)
{
    $as->mergeCells(cellsToMergeByColsRow(0,$col_count,$rowRptDisplayTitle));
    $as->getStyle("A$rowRptDisplayTitle")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    $as->getStyle("A$rowRptDisplayTitle")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $as->getStyle("A$rowRptDisplayTitle")->applyFromArray($rowRptTitleFontStyle);
    $as->getRowDimension($rowRptDisplayTitle)->setRowHeight(20);
    $as->setCellValue("A$rowRptDisplayTitle",JText::sprintf('COM_CN_REPORTS_FORM_TITLE_COMISSIONS',
                    $this->reportStart->format('Y-m-d'), $this->reportEnd->format('Y-m-d'),$this->partnerName));
}
?>
<?php
// $rowHeaderText
// display the report header
$headerText = JHtml::_('contentadministrator.reportHeader');
if (($rowHeaderText > 0) && ($headerText !== '')) {
    $headerText = strip_tags($headerText);
    $as->mergeCells(cellsToMergeByColsRow(0,$col_count,$rowHeaderText));
    $as->getStyle("A$rowHeaderText")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $as->getStyle("A$rowHeaderText")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $as->getRowDimension($rowHeaderText)->setRowHeight(40);
    $as->getStyle("A$rowHeaderText")->getAlignment()->setWrapText(true);
    $as->setCellValue("A$rowHeaderText",$headerText);
}
?>
<?php
// $rowHeaderText
// display the report footer
$footerText = JHtml::_('contentadministrator.reportFooter');
if (($rowFooterText > 0) && ($footerText !== '')) {
    $footerText = strip_tags($footerText);
    $row_index = $rowFooterText + $row_final;
    $as->mergeCells(cellsToMergeByColsRow(0,$col_count,$row_index));
    $as->getStyle("A$row_index")->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
    $as->getStyle("A$row_index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
    $as->getRowDimension($row_index)->setRowHeight(40);
    $as->getStyle("A$row_index")->getAlignment()->setWrapText(true);
    $as->setCellValue("A$row_index",$footerText);
    $row_final = $row_index;
}
?>
<?php
// set the print area
$as->getPageSetup()->setPrintArea("A1:L$row_final");
$as->setShowGridlines(filter_var($this->showGrid, FILTER_VALIDATE_BOOLEAN));
?>
<?php
function cellsToMergeByColsRow($start = -1, $end = -1, $row = -1){
    $merge = 'A1:A1';
    if($start>=0 && $end>=0 && $row>=0){
        $start = PHPExcel_Cell::stringFromColumnIndex($start);
        $end = PHPExcel_Cell::stringFromColumnIndex($end);
        $merge = "$start{$row}:$end{$row}";
    }
    return $merge;
}
?>