xp-dev.com Git - namibia/blob - module/Utility/src/Utility/Export/ExcelReport.php

   1 <?php
   2 namespace Utility\Export;
   3
   4
   5
   6 /**
   7  * For excel report building.
   8  * @author andre.fourie, tjaart.viljoen
   9  */
  10 class ExcelReport
  11 {
  12
  13         /**
  14          * Report class
  15          * @var Struct_Abstract_Report
  16          */
  17         protected $_objReport   = null;
  18         /**
  19          * Excel writer
  20          * @var PHPExcel_Writer_Excel2007
  21          */
  22         protected $_objWriter   = null;
  23         /**
  24          * Excel base class
  25          * @var PHPExcel
  26          */
  27         protected $_objPHPExcel = null;
  28
  29         /**
  30          * Filename to use for output
  31          * @var string
  32          */
  33         protected $_fileName    = null;
  34
  35         /**
  36          * Formatting
  37          * @var string
  38          */
  39         protected $_format;
  40         /**
  41          * Report query details.
  42          * @var array
  43          */
  44         protected $_queries = array();
  45         /**
  46          * Column headers
  47          * @var array
  48          */
  49         protected $_headers = array();
  50         /**
  51          * Column fields
  52          * @var array
  53          */
  54         protected $_fields  = array();
  55         /**
  56          * Columns to provide totals for.
  57          * @var array
  58          */
  59         protected $_totals = array();
  60         /**
  61          * Columns for currency format.
  62          * @var array
  63          */
  64         protected $_currencyFields = array();
  65         /**
  66          * Columns totals accumulated.
  67          * @var array
  68          */
  69         protected $_totalAccum = array();
  70         /**
  71          * Notes to display.
  72          * @var array
  73          */
  74         protected $_notes = array();
  75         /**
  76          * Chart from report data.
  77          * @var array
  78          */
  79         protected $_chart = array();
  80         /**
  81          * Report data
  82          * @var array
  83          */
  84         protected $_data    = array();
  85         /**
  86          * Useful mapping
  87          * @var array
  88          */
  89         protected $numberToColumn = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ');
  90
  91
  92         /**
  93          * Constructor.
  94          * @param \Utility\Service\Report $report
  95          */
  96         public function __construct(\Utility\Service\Report $report)
  97         {
  98                 $this->_fileName = str_replace(' ', '-', $report->getTitle());
  99                 $objPHPExcel = new \PHPExcel();
 100                 $objWriter   = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
 101                 $this->_objReport      = $report;
 102                 $this->_format         = $report->getFormat();
 103                 $this->_queries        = $report->getQueries();
 104                 $this->_headers        = $report->getHeaders();
 105                 $this->_fields         = $report->getFields();
 106                 $this->_totals         = $report->getTotalFields();
 107                 $this->_currencyFields = $report->getCurrencyFields();
 108                 $this->_notes          = $report->getNotes();
 109                 $this->_chart          = $report->getChart();
 110                 $this->_data           = $report->getData();
 111                 $this->_objWriter      = $objWriter;
 112                 $this->_objPHPExcel    = $objPHPExcel;
 113                 $this->_objPHPExcel->getProperties()->setCreator(APPLICATION);
 114                 $this->_objPHPExcel->getProperties()->setLastModifiedBy(APPLICATION);
 115                 $this->_objPHPExcel->getProperties()->setTitle($report->getTitle());
 116                 $this->_objPHPExcel->getProperties()->setSubject($report->getSubject());
 117                 $this->_objPHPExcel->getProperties()->setDescription($report->getDescription());
 118                 $this->_addLogo();
 119                 $numQueries = count($this->_queries);
 120                 $numRecords = count($this->_data);
 121                 $numNotes   = count($this->_notes);
 122                 $numChart   = !empty($this->_chart)
 123                         ? 15 : 0;
 124                 $numTotals = (!empty($this->_totals))
 125                         ? 1
 126                         : 0;
 127                 if ($numQueries)
 128                 {
 129                         $this->_writeQueries(2);
 130                         $numQueries += 2;
 131                 }
 132                 $this->_createHeaders(2 + $numQueries);
 133                 $this->_writeRecords(3 + $numQueries);
 134                 if (!empty($this->_totals))
 135                 {
 136                         $this->_writeTotals(3 + $numQueries + $numRecords);
 137                 }
 138                 $this->_setCurrencyFormat(3 + $numQueries, $numRecords + $numTotals);
 139                 if ($numChart)
 140                 {
 141                         $this->_writeChart(
 142                                         5 + $numQueries + $numRecords,
 143                                         3 + $numQueries,
 144                                         2 + $numQueries + $numRecords
 145                                         );
 146                         $this->_objWriter->setIncludeCharts(true);
 147                         $numChart += 2;
 148                 }
 149                 if ($numNotes)
 150                 {
 151                         $this->_writeNotes(5 + $numQueries + $numRecords + $numChart);
 152                 }
 153                 return $this;
 154         }
 155
 156         /**
 157          * Set formatting.
 158          * @param string $format
 159          * @return Struct_Abstract_ExcelReport
 160          */
 161         public function setFormat($format)
 162         {
 163                 $this->_format = $format;
 164                 return $this;
 165         }
 166
 167         /**
 168          * Output result.
 169          * @todo  add email, filesystem and ftp as potential targets.
 170          * @param null|string $target
 171          */
 172         public function output($target = null)
 173         {
 174                 set_time_limit(900);
 175                 ini_set('memory_limit', '-1');
 176
 177                 if (is_null($target))
 178                 {
 179                         #-> Direct output.
 180                         header("Content-Type: application/vnd.ms-excel");
 181                         header('Content-Disposition: attachment; filename="' .$this->_fileName.'_'. time() . '.xlsx"' );
 182                         $this->_objWriter->save('php://output');
 183                 }
 184                 elseif (strpos($target, '@'))
 185                 {
 186                         #-> Send to email.
 187                 }
 188                 else
 189                 {
 190                         #-> Write to file.
 191                         $this->_objWriter->save($target);
 192                 }
 193         }
 194
 195         /**
 196          * Add logo to top of spreadsheet.
 197          */
 198         protected function _addLogo()
 199         {
 200                 $totalCols = count($this->_headers);
 201
 202                 // Add a drawing to the worksheet
 203                 $objDrawing = new \PHPExcel_Worksheet_Drawing();
 204                 $objDrawing->setName('Logo');
 205                 $objDrawing->setDescription('Logo');
 206                 $objDrawing->setPath(__DIR__ . '/../../../../../public/img/app/logos/logo-excel.png');
 207                 $objDrawing->setHeight(56);
 208
 209                 $this->_objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(56);
 210                 $style_logo = array(
 211                                 'fill' => array(
 212                                                 'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 213                                                 'color' => array('rgb'=>'222222'),
 214                                 )
 215                 );
 216                 $style_header = array(
 217                                 'fill' => array(
 218                                                 'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 219                                                 'color' => array('rgb'=>'bbbbbb'),
 220                                 ),
 221                                 'font' => array(
 222                                                 'bold' => true,
 223                                 )
 224                 );
 225
 226                 $convertTotalToString = $this->numberToColumn[$totalCols-1];
 227                 $this->_objPHPExcel->getActiveSheet()->getStyle("A1:{$convertTotalToString}1")->applyFromArray($style_logo);
 228
 229                 $objDrawing->setWorksheet($this->_objPHPExcel->getActiveSheet());
 230         }
 231
 232         /**
 233          * Set text wrapping for specified column.
 234          * @param integer $column
 235          * @param boolean $wrap
 236          */
 237         protected function _setColumnWrapping($column, $wrap = true)
 238         {
 239                 $this->_objPHPExcel->getActiveSheet()->getStyle($column)->getAlignment()->setWrapText($wrap);
 240         }
 241
 242         protected function _setCurrencyFormat($fromRow, $numRows)
 243         {
 244                 foreach ($this->_currencyFields as $field)
 245                 {
 246                         $endRow = $fromRow + $numRows;
 247                         $labelInd = array_search($field, $this->_fields);
 248                         $labelInd = $this->numberToColumn[$labelInd];
 249                         $this->_objPHPExcel
 250                                 ->getActiveSheet()
 251                                 ->getStyle($labelInd . $fromRow . ':' . $labelInd . $endRow)
 252                                 ->getNumberFormat()
 253                                 ->setFormatCode(\Utility\Definitions\Locale::getExcelCurrencyFormat());
 254                 }
 255         }
 256
 257         /**
 258          * Insert report query details.
 259          * @param integer $lineNumber
 260          */
 261         protected function _writeQueries($lineNumber)
 262         {
 263                 $convertTotalToString = $this->numberToColumn[count($this->_headers)-1];
 264                 $this->_objPHPExcel->getActiveSheet()->setCellValue('A'.$lineNumber, 'Filters applied to report:');
 265                 $style_header = array(
 266                                 'fill' => array(
 267                                                 'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 268                                                 'color' => array('rgb'=>'aaaaaa'),
 269                                 ),
 270                                 'font' => array(
 271                                                 'bold' => true,
 272                                 )
 273                 );
 274                 $this->_objPHPExcel->getActiveSheet()->getStyle("A$lineNumber:$convertTotalToString".$lineNumber)->applyFromArray($style_header);
 275                 $lineNumber++;
 276
 277                 foreach ($this->_queries as $param => $value)
 278                 {
 279                         $this->_objPHPExcel->getActiveSheet()->setCellValue('A'.$lineNumber, $param);
 280                         $this->_objPHPExcel->getActiveSheet()->setCellValue('B'.$lineNumber, $value);
 281                         $this->_objPHPExcel->getActiveSheet()->getStyle('A'.$lineNumber)->applyFromArray(
 282                                                         array(
 283                                                                         'font' => array('bold' => true),
 284                                                                         'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT),
 285                                                                         'fill' => array(
 286                                                                                         'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 287                                                                                         'color' => array('rgb'=>'dddddd'),
 288                                                                         ))
 289                                                         );
 290                         $this->_objPHPExcel->getActiveSheet()->getStyle('A'.$lineNumber.':'.$convertTotalToString.$lineNumber)->applyFromArray(
 291                                                         array(
 292                                                                         'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT),
 293                                                                         'fill' => array(
 294                                                                                         'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 295                                                                                         'color' => array('rgb'=>'dddddd'),
 296                                                                         ))
 297                                                         );
 298                         $lineNumber++;
 299                 }
 300         }
 301
 302         /**
 303          * Insert report totals.
 304          * @param integer $lineNumber
 305          */
 306         protected function _writeTotals($lineNumber)
 307         {
 308                 $i = 0;
 309                 $convertTotalToString = $this->numberToColumn[count($this->_headers)-1];
 310                 $this->_objPHPExcel->getActiveSheet()->getStyle($this->numberToColumn[$i].$lineNumber.':'.$convertTotalToString.$lineNumber)->applyFromArray(
 311                                 array(
 312                                                 'font' => array('bold' => true),
 313                                                 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_RIGHT),
 314                                                 'fill' => array(
 315                                                                 'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 316                                                                 'color' => array('rgb'=>'dddddd')
 317                                                 )
 318                                 )
 319                 );
 320                 foreach ($this->_fields as $field)
 321                 {
 322                         if (isset($this->_totalAccum[$field]))
 323                         {
 324                                 $this->_objPHPExcel->getActiveSheet()->setCellValue(
 325                                                 $this->numberToColumn[$i].$lineNumber,
 326                                                 $this->_totalAccum[$field]
 327                                                 );
 328                         }
 329                         $i++;
 330                 }
 331         }
 332
 333         /**
 334          * Insert report notes.
 335          * @param integer $lineNumber
 336          */
 337         protected function _writeNotes($lineNumber)
 338         {
 339                 foreach ($this->_notes as $note)
 340                 {
 341                         $this->_objPHPExcel->getActiveSheet()->setCellValue('A'.$lineNumber, $note);
 342                         $this->_objPHPExcel->getActiveSheet()->getStyle('A'.$lineNumber)->applyFromArray(
 343                                                         array(
 344                                                                         'font' => array('bold' => true),
 345                                                                         'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT)
 346                                                                         )
 347                                                         );
 348                         $lineNumber++;
 349                 }
 350         }
 351
 352         /**
 353          * Insert column headers.
 354          * @param integer $lineNumber
 355          */
 356         protected function _createHeaders($lineNumber)
 357         {
 358                 $i = 0;
 359
 360                 foreach($this->_headers as $key => $val)
 361                 {
 362                         $this->_objPHPExcel->getActiveSheet()->setCellValue($this->numberToColumn[$i].$lineNumber, $val);
 363                         $this->_objPHPExcel->getActiveSheet()->getStyle($this->numberToColumn[$i].$lineNumber)->applyFromArray(array('alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT) ) );
 364                         $i++;
 365                 }
 366
 367                 #-> Set column sizes
 368                 $this->_objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
 369                 for ($i = 1; $i < count($this->_headers); $i++)
 370                 {
 371                         $this->_objPHPExcel->getActiveSheet()->getColumnDimension($this->numberToColumn[$i])->setAutoSize(true);
 372                 }
 373                 $this->_objPHPExcel->getActiveSheet()->duplicateStyleArray(
 374                                 array(
 375                                                 'font' => array('bold' => true),
 376                                                 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT),
 377                                                 'fill' => array(
 378                                                                 'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 379                                                                 'color' => array('rgb'=>'aaaaaa')
 380                                                 )
 381                                 ),
 382                                 $this->numberToColumn[0]. $lineNumber .':'.$this->numberToColumn[$i - 1]. $lineNumber
 383                 );
 384         }
 385
 386         /**
 387          * Write data rows starting at specified line number.
 388          * @param integer $lineNumber
 389          */
 390         protected function _writeRecords($lineNumber)
 391         {
 392                 //[green]$#,##0.00;[red]$(-#,##0.00)
 393                 if (!empty($this->_totals))
 394                 {
 395                         foreach ($this->_totals as $field)
 396                         {
 397                                 $this->_totalAccum[$field] = 0;
 398                         }
 399                 }
 400                 $i = 0;
 401                 foreach ($this->_data as $record)
 402                 {
 403                         if (empty($record))
 404                         {
 405                                 $lineNumber++;
 406                                 continue;
 407                         }
 408                     if($i > 30000)
 409                     {
 410                         return;
 411                     }
 412                     // Totals
 413                     if (!empty($this->_totals))
 414                     {
 415                         foreach ($this->_totals as $field)
 416                         {
 417                                 if (is_numeric($record[$field]))
 418                                 {
 419                                         $this->_totalAccum[$field] += $record[$field];
 420                                 }
 421                         }
 422                     }
 423                         // Format record
 424                         $tmpRecord = array();
 425                         $style = isset($record['excel.rowStyle'])
 426                                 ? $record['excel.rowStyle']
 427                                 : false;
 428                         foreach($this->_fields as $field)
 429                         {
 430                                 $tmpRecord[] = $record[$field];
 431                         }
 432                         $this->_insertValues($tmpRecord, $lineNumber, 0, $style);
 433                         $lineNumber++;
 434                         $i++;
 435                 }
 436         }
 437
 438         /**
 439          * Insert cell values for a data row.
 440          * @param array $values
 441          * @param integer $lineNumber
 442          * @param integer $offset
 443          * @param string|false $style
 444          */
 445         protected function _insertValues($values, $lineNumber, $offset = 0, $style = false)
 446         {
 447                 $i = 0 + $offset;
 448                 if ('Totals' == $style)
 449                 {
 450                         $convertTotalToString = $this->numberToColumn[count($this->_headers)-1];
 451                         $this->_objPHPExcel->getActiveSheet()->getStyle($this->numberToColumn[$i].$lineNumber.':'.$convertTotalToString.$lineNumber)->applyFromArray(
 452                                         array(
 453                                                         'font' => array('bold' => true),
 454                                                         'fill' => array(
 455                                                                         'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 456                                                                         'color' => array('rgb'=>'dddddd')
 457                                                         )
 458                                         )
 459                         );
 460                 }
 461                 if ('Titles' == $style)
 462                 {
 463                         $convertTotalToString = $this->numberToColumn[count($this->_headers)-1];
 464                         $this->_objPHPExcel->getActiveSheet()->getStyle($this->numberToColumn[$i].$lineNumber.':'.$convertTotalToString.$lineNumber)->applyFromArray(
 465                                         array(
 466                                                 'font' => array('bold' => true),
 467                                                 'alignment' => array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_LEFT),
 468                                                 'fill' => array(
 469                                                                 'type' => \PHPExcel_Style_Fill::FILL_SOLID,
 470                                                                 'color' => array('rgb'=>'aaaaaa')
 471                                                 )
 472                                         )
 473                         );
 474                 }
 475                 foreach ($values as $key => $val)
 476                 {
 477                         if (is_a($val, 'DateTime'))
 478                         {
 479                                 $this->_objPHPExcel->getActiveSheet()->getCell($this->numberToColumn[$i].$lineNumber)
 480                                         ->setValueExplicit(
 481                                                         utf8_encode(
 482                                                                         $val->format(\Utility\Definitions\Locale::getDateTimeFormat())
 483                                                                         ), \PHPExcel_Cell_DataType::TYPE_STRING
 484                                                         );
 485                                 $i++;
 486                                 continue;
 487                         }
 488                         $output = $values[$key];
 489                         if (strpos($val, "<br />") || strpos($val, "<br/>") || strpos($val, "<br>"))
 490                         {
 491                                 $this->_setColumnWrapping($this->numberToColumn[$i], true);
 492                         }
 493                         $output = str_replace(array("<br />", "<br/>", "<br>"), "\r", $output);
 494                         $output = str_replace(
 495                                         array('/', '&', '�'),
 496                                         array('-', 'and', ' degrees'),
 497                                         $output
 498                         );
 499                         $output = str_replace(
 500                                         array("</u>", "<u>", "</b>", "<b>", "</i>", "<i>", "\\", "(", ")"),
 501                                         '',
 502                                         $output
 503                         );
 504                         /* $replacable = array("</u>", "<u>", "</b>", "<b>", "</i>", "<i>", "\\", "(", ")");
 505                         foreach($replacable as $val)
 506                         {
 507                                 $output = str_replace(
 508                                                 array($val, '/', '&', '�'),
 509                                                 array('', ' - ', 'and', ' degrees'),
 510                                                 $output
 511                                 );
 512                         } */
 513                         $type = is_numeric($output)
 514                                 ? \PHPExcel_Cell_DataType::TYPE_NUMERIC
 515                                 : \PHPExcel_Cell_DataType::TYPE_STRING;
 516                         $this->_objPHPExcel->getActiveSheet()->getCell($this->numberToColumn[$i].$lineNumber)
 517                                         ->setValueExplicit(
 518                                                         utf8_encode($output), $type
 519                                                         );
 520                         $i++;
 521                 }
 522         }
 523
 524         protected function _writeChart($lineNumber, $dataStart, $dataEnd)
 525         {
 526                 $dataCount  = $dataEnd - $dataStart;
 527                 $labelRow   = $dataStart - 1;
 528                 $labelInd   = array_search($this->_chart['LabelSource'], $this->_fields);
 529                 $labelInd   = $this->numberToColumn[$labelInd];
 530                 $labels     = array();
 531                 $categories = array();
 532                 $values     = array();
 533                 foreach ($this->_chart['DataSource'] as $field)
 534                 {
 535                         $fieldInd = array_search($field, $this->_fields);
 536                         $fieldInd = $this->numberToColumn[$fieldInd];
 537                         $labels[] = new \PHPExcel_Chart_DataSeriesValues(
 538                                         'String',
 539                                         'Worksheet!$' . $fieldInd . '$' . $labelRow,
 540                                         null, 1
 541                                         );
 542                         $categories[] = new \PHPExcel_Chart_DataSeriesValues(
 543                                         'String',
 544                                         'Worksheet!$' . $labelInd . '$' . $dataStart . ':$' . $labelInd . '$' . $dataEnd,
 545                                         null, $dataCount
 546                                         );
 547                         $values[] = new \PHPExcel_Chart_DataSeriesValues(
 548                                         'Number',
 549                                         'Worksheet!$' . $fieldInd . '$' . $dataStart . ':$' . $fieldInd . '$' . $dataEnd,
 550                                         null, $dataCount
 551                                         );
 552                 }
 553
 554                 $sheet = $this->_objPHPExcel->getActiveSheet();
 555                 $series = new \PHPExcel_Chart_DataSeries(
 556                                 $this->_chart['Type'],                          // plotType
 557                                 $this->_chart['Grouping'],                      // plotGrouping
 558                                 $this->_chart['Order'],                         // plotOrder
 559                                 $labels,                                        // plotLabel
 560                                 $categories,                                    // plotCategory
 561                                 $values                                         // plotValues
 562                 );
 563                 $series->setPlotDirection(\PHPExcel_Chart_DataSeries::DIRECTION_COL);
 564                 $plotarea = new \PHPExcel_Chart_PlotArea(null, array($series));
 565                 $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT, null, false);
 566                 $chart = new \PHPExcel_Chart(
 567                                 $this->_chart['Name'],                          // name
 568                                 null,                                           // title
 569                                 $legend,                                        // legend
 570                                 $plotarea,                                      // plotArea
 571                                 true,                                           // plotVisibleOnly
 572                                 0,                                              // displayBlanksAs
 573                                 null,                                           // xAxisLabel
 574                                 null                                            // yAxisLabel
 575                 );
 576                 $chart->setTopLeftPosition('A' . $lineNumber);
 577                 $chart->setBottomRightPosition('J' . ($lineNumber + 15));
 578                 $sheet->addChart($chart);
 579         }
 580
 581
 582
 583 }