Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 4.2.x will end 22 April 2024 (12 months).
  • Bug fixes for security issues in 4.2.x will end 7 October 2024 (18 months).
  • PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.1.x is supported too.

Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402] [Versions 401 and 402]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * Excel writer abstraction layer.
  19   *
  20   * @copyright  (C) 2001-3001 Eloy Lafuente (stronk7) {@link http://contiento.com}
  21   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  22   * @package    core
  23   */
  24  
  25  defined('MOODLE_INTERNAL') || die();
  26  
  27  require_once("$CFG->libdir/phpspreadsheet/vendor/autoload.php");
  28  
  29  use \PhpOffice\PhpSpreadsheet\Spreadsheet;
  30  use \PhpOffice\PhpSpreadsheet\IOFactory;
  31  use \PhpOffice\PhpSpreadsheet\Cell\Coordinate;
  32  use \PhpOffice\PhpSpreadsheet\Cell\DataType;
  33  use \PhpOffice\PhpSpreadsheet\Shared\Date;
  34  use \PhpOffice\PhpSpreadsheet\Style\Alignment;
  35  use \PhpOffice\PhpSpreadsheet\Style\Border;
  36  use \PhpOffice\PhpSpreadsheet\Style\Fill;
  37  use \PhpOffice\PhpSpreadsheet\Style\Font;
  38  use \PhpOffice\PhpSpreadsheet\Style\NumberFormat;
  39  use \PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
  40  use \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
  41  
  42  /**
  43   * Define and operate over one Moodle Workbook.
  44   *
  45   * This class acts as a wrapper around another library
  46   * maintaining Moodle functions isolated from underlying code.
  47   *
  48   * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
  49   * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  50   * @package moodlecore
  51   */
  52  class MoodleExcelWorkbook {
  53      /** @var \PhpOffice\PhpSpreadsheet\Spreadsheet */
  54      protected $objspreadsheet;
  55  
  56      /** @var string */
  57      protected $filename;
  58  
  59      /** @var string format type */
  60      protected $type;
  61  
  62      /**
  63       * Constructs one Moodle Workbook.
  64       *
  65       * @param string $filename The name of the file
  66       * @param string $type file format type used to be 'Xls or Xlsx' but now only 'Xlsx'
  67       */
  68      public function __construct($filename, $type = 'Xlsx') {
  69          global $CFG;
  70  
  71          $this->objspreadsheet = new Spreadsheet();
  72          $this->objspreadsheet->removeSheetByIndex(0);
  73  
  74          $this->filename = $filename;
  75  
  76          if (strtolower($type) === 'Xls') {
  77              debugging('Xls is no longer supported, using Xlsx instead');
  78              $this->type = 'Xlsx';
  79          } else {
  80              $this->type = 'Xlsx';
  81          }
  82      }
  83  
  84      /**
  85       * Create one Moodle Worksheet
  86       *
  87       * @param string $name Name of the sheet
  88       * @return MoodleExcelWorksheet
  89       */
  90      public function add_worksheet($name = '') {
  91          return new MoodleExcelWorksheet($name, $this->objspreadsheet);
  92      }
  93  
  94      /**
  95       * Create one cell Format.
  96       *
  97       * @param array $properties array of properties [name]=value;
  98       *                          valid names are set_XXXX existing
  99       *                          functions without the set_ part
 100       *                          i.e: [bold]=1 for set_bold(1)...Optional!
 101       * @return MoodleExcelFormat
 102       */
 103      public function add_format($properties = array()) {
 104          return new MoodleExcelFormat($properties);
 105      }
 106  
 107      /**
 108       * Close the Moodle Workbook
 109       */
 110      public function close() {
 111          global $CFG;
 112  
 113          foreach ($this->objspreadsheet->getAllSheets() as $sheet) {
 114              $sheet->setSelectedCells('A1');
 115          }
 116          $this->objspreadsheet->setActiveSheetIndex(0);
 117  
 118          $filename = preg_replace('/\.xlsx?$/i', '', $this->filename);
 119  
 120          $mimetype = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
 121          $filename = $filename.'.xlsx';
 122  
 123          if (is_https()) { // HTTPS sites - watch out for IE! KB812935 and KB316431.
 124              header('Cache-Control: max-age=10');
 125              header('Expires: '. gmdate('D, d M Y H:i:s', 0) .' GMT');
 126              header('Pragma: ');
 127          } else { //normal http - prevent caching at all cost
 128              header('Cache-Control: private, must-revalidate, pre-check=0, post-check=0, max-age=0');
 129              header('Expires: '. gmdate('D, d M Y H:i:s', 0) .' GMT');
 130              header('Pragma: no-cache');
 131          }
 132  
 133          if (core_useragent::is_ie() || core_useragent::is_edge()) {
 134              $filename = rawurlencode($filename);
 135          } else {
 136              $filename = s($filename);
 137          }
 138  
 139          header('Content-Type: '.$mimetype);
 140          header('Content-Disposition: attachment;filename="'.$filename.'"');
 141  
 142          $objwriter = IOFactory::createWriter($this->objspreadsheet, $this->type);
 143          $objwriter->save('php://output');
 144      }
 145  
 146      /**
 147       * Not required to use.
 148       * @param string $filename Name of the downloaded file
 149       */
 150      public function send($filename) {
 151          $this->filename = $filename;
 152      }
 153  }
 154  
 155  /**
 156   * Define and operate over one Worksheet.
 157   *
 158   * This class acts as a wrapper around another library
 159   * maintaining Moodle functions isolated from underlying code.
 160   *
 161   * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
 162   * @license   http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 163   * @package   core
 164   */
 165  class MoodleExcelWorksheet {
 166      /** @var Worksheet */
 167      protected $worksheet;
 168  
 169      /**
 170       * Constructs one Moodle Worksheet.
 171       *
 172       * @param string $name The name of the file
 173       * @param Spreadsheet $workbook The internal Workbook object we are creating.
 174       */
 175      public function __construct($name, Spreadsheet $workbook) {
 176          // Replace any characters in the name that Excel cannot cope with.
 177          $name = strtr(trim($name, "'"), '[]*/\?:', '       ');
 178          // Shorten the title if necessary.
 179          $name = core_text::substr($name, 0, 31);
 180          // After the substr, we might now have a single quote on the end.
 181          $name = trim($name, "'");
 182  
 183          if ($name === '') {
 184              // Name is required!
 185              $name = 'Sheet'.($workbook->getSheetCount()+1);
 186          }
 187  
 188          $this->worksheet = new Worksheet($workbook, $name);
 189          $this->worksheet->setPrintGridlines(false);
 190  
 191          $workbook->addSheet($this->worksheet);
 192      }
 193  
 194      /**
 195       * Write one string somewhere in the worksheet.
 196       *
 197       * @param integer $row    Zero indexed row
 198       * @param integer $col    Zero indexed column
 199       * @param string  $str    The string to write
 200       * @param mixed   $format The XF format for the cell
 201       */
 202      public function write_string($row, $col, $str, $format = null) {
 203          // For PhpSpreadsheet library, the column indexes start on 1 (instead of 0 as before).
 204          $col += 1;
 205  
 206          $this->worksheet->getStyleByColumnAndRow($col, $row + 1)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
 207          $this->worksheet->setCellValueExplicitByColumnAndRow($col, $row + 1, $str, DataType::TYPE_STRING);
 208          $this->apply_format($row, $col, $format);
 209      }
 210  
 211      /**
 212       * Write one number somewhere in the worksheet.
 213       *
 214       * @param integer $row    Zero indexed row
 215       * @param integer $col    Zero indexed column
 216       * @param float   $num    The number to write
 217       * @param mixed   $format The XF format for the cell
 218       */
 219      public function write_number($row, $col, $num, $format = null) {
 220          // For PhpSpreadsheet library, the column indexes start on 1 (instead of 0 as before).
 221          $col += 1;
 222  
 223          $this->worksheet->getStyleByColumnAndRow($col, $row + 1)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_GENERAL);
 224          $this->worksheet->setCellValueExplicitByColumnAndRow($col, $row + 1, $num, DataType::TYPE_NUMERIC);
 225          $this->apply_format($row, $col, $format);
 226      }
 227  
 228      /**
 229       * Write one url somewhere in the worksheet.
 230       *
 231       * @param integer $row    Zero indexed row
 232       * @param integer $col    Zero indexed column
 233       * @param string  $url    The url to write
 234       * @param mixed   $format The XF format for the cell
 235       */
 236      public function write_url($row, $col, $url, $format = null) {
 237          // For PhpSpreadsheet library, the column indexes start on 1 (instead of 0 as before).
 238          $col += 1;
 239  
 240          $this->worksheet->setCellValueByColumnAndRow($col, $row + 1, $url);
 241          $this->worksheet->getCellByColumnAndRow($col, $row + 1)->getHyperlink()->setUrl($url);
 242          $this->apply_format($row, $col, $format);
 243      }
 244  
 245      /**
 246       * Write one date somewhere in the worksheet.
 247       * @param integer $row    Zero indexed row
 248       * @param integer $col    Zero indexed column
 249       * @param int     $date   The date to write in UNIX timestamp format
 250       * @param mixed   $format The XF format for the cell
 251       */
 252      public function write_date($row, $col, $date, $format = null) {
 253          // For PhpSpreadsheet library, the column indexes start on 1 (instead of 0 as before).
 254          $col += 1;
 255  
 256          $getdate = usergetdate($date);
 257          $exceldate = Date::FormattedPHPToExcel(
 258              $getdate['year'],
 259              $getdate['mon'],
 260              $getdate['mday'],
 261              $getdate['hours'],
 262              $getdate['minutes'],
 263              $getdate['seconds']
 264          );
 265  
 266          $this->worksheet->setCellValueByColumnAndRow($col, $row + 1, $exceldate);
 267          $style = $this->worksheet->getStyleByColumnAndRow($col, $row + 1);
 268          $style->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_XLSX22);
 269          $this->apply_format($row, $col, $format);
 270      }
 271  
 272      /**
 273       * Write one formula somewhere in the worksheet.
 274       *
 275       * @param integer $row    Zero indexed row
 276       * @param integer $col    Zero indexed column
 277       * @param string  $formula The formula to write
 278       * @param mixed   $format The XF format for the cell
 279       */
 280      public function write_formula($row, $col, $formula, $format = null) {
 281          // For PhpSpreadsheet library, the column indexes start on 1 (instead of 0 as before).
 282          $col += 1;
 283  
 284          $this->worksheet->setCellValueExplicitByColumnAndRow($col, $row + 1, $formula, DataType::TYPE_FORMULA);
 285          $this->apply_format($row, $col, $format);
 286      }
 287  
 288      /**
 289       * Write one blank somewhere in the worksheet.
 290       *
 291       * @param integer $row    Zero indexed row
 292       * @param integer $col    Zero indexed column
 293       * @param mixed   $format The XF format for the cell
 294       */
 295      public function write_blank($row, $col, $format = null) {
 296          // For PhpSpreadsheet library, the column indexes start on 1 (instead of 0 as before).
 297          $col += 1;
 298  
 299          $this->worksheet->setCellValueByColumnAndRow($col, $row + 1, '');
 300          $this->apply_format($row, $col, $format);
 301      }
 302  
 303      /**
 304       * Write anything somewhere in the worksheet,
 305       * type will be automatically detected.
 306       *
 307       * @param integer $row    Zero indexed row
 308       * @param integer $col    Zero indexed column
 309       * @param mixed   $token  What we are writing
 310       * @param mixed   $format The XF format for the cell
 311       */
 312      public function write($row, $col, $token, $format = null) {
 313          // Analyse what are we trying to send.
 314          if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
 315              // Match number
 316              return $this->write_number($row, $col, $token, $format);
 317          } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
 318              // Match http or ftp URL
 319              return $this->write_url($row, $col, $token, '', $format);
 320          } elseif (preg_match("/^mailto:/", $token)) {
 321              // Match mailto:
 322              return $this->write_url($row, $col, $token, '', $format);
 323          } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
 324              // Match internal or external sheet link
 325              return $this->write_url($row, $col, $token, '', $format);
 326          } elseif (preg_match("/^=/", $token)) {
 327              // Match formula
 328              return $this->write_formula($row, $col, $token, $format);
 329          } elseif (preg_match("/^@/", $token)) {
 330              // Match formula
 331              return $this->write_formula($row, $col, $token, $format);
 332          } elseif ($token == '') {
 333              // Match blank
 334              return $this->write_blank($row, $col, $format);
 335          } else {
 336              // Default: match string
 337              return $this->write_string($row, $col, $token, $format);
 338          }
 339      }
 340  
 341      /**
 342       * Sets the height (and other settings) of one row.
 343       *
 344       * @param integer $row    The row to set
 345       * @param integer $height Height we are giving to the row (null to set just format without setting the height)
 346       * @param mixed   $format The optional format we are giving to the row
 347       * @param bool    $hidden The optional hidden attribute
 348       * @param integer $level  The optional outline level (0-7)
 349       */
 350      public function set_row($row, $height, $format = null, $hidden = false, $level = 0) {
 351          if ($level < 0) {
 352              $level = 0;
 353          } else if ($level > 7) {
 354              $level = 7;
 355          }
 356          if (isset($height)) {
 357              $this->worksheet->getRowDimension($row + 1)->setRowHeight($height);
 358          }
 359          $this->worksheet->getRowDimension($row + 1)->setVisible(!$hidden);
 360          $this->worksheet->getRowDimension($row + 1)->setOutlineLevel($level);
 361          $this->apply_row_format($row, $format);
 362      }
 363  
 364      /**
 365       * Sets the width (and other settings) of one column.
 366       *
 367       * @param integer $firstcol first column on the range
 368       * @param integer $lastcol  last column on the range
 369       * @param integer $width    width to set  (null to set just format without setting the width)
 370       * @param mixed   $format   The optional format to apply to the columns
 371       * @param bool    $hidden   The optional hidden attribute
 372       * @param integer $level    The optional outline level (0-7)
 373       */
 374      public function set_column($firstcol, $lastcol, $width, $format = null, $hidden = false, $level = 0) {
 375          if ($level < 0) {
 376              $level = 0;
 377          } else if ($level > 7) {
 378              $level = 7;
 379          }
 380          // For PhpSpreadsheet library, the column indexes start on 1 (instead of 0 as before).
 381          $i = $firstcol + 1;
 382          while ($i <= $lastcol + 1) {
 383              if (isset($width)) {
 384                  $this->worksheet->getColumnDimensionByColumn($i)->setWidth($width);
 385              }
 386              $this->worksheet->getColumnDimensionByColumn($i)->setVisible(!$hidden);
 387              $this->worksheet->getColumnDimensionByColumn($i)->setOutlineLevel($level);
 388              $this->apply_column_format($i, $format);
 389              $i++;
 390          }
 391      }
 392  
 393     /**
 394      * Set the option to hide grid lines on the printed page.
 395      */
 396      public function hide_gridlines() {
 397          // Not implemented - always off.
 398      }
 399  
 400     /**
 401      * Set the option to hide gridlines on the worksheet (as seen on the screen).
 402      */
 403      public function hide_screen_gridlines() {
 404          $this->worksheet->setShowGridlines(false);
 405      }
 406  
 407     /**
 408      * Insert an image in a worksheet.
 409      *
 410      * @param integer $row     The row we are going to insert the bitmap into
 411      * @param integer $col     The column we are going to insert the bitmap into
 412      * @param string  $bitmap  The bitmap filename
 413      * @param integer $x       The horizontal position (offset) of the image inside the cell.
 414      * @param integer $y       The vertical position (offset) of the image inside the cell.
 415      * @param integer $scalex The horizontal scale
 416      * @param integer $scaley The vertical scale
 417      */
 418      public function insert_bitmap($row, $col, $bitmap, $x = 0, $y = 0, $scalex = 1, $scaley = 1) {
 419          // For PhpSpreadsheet library, the column indexes start on 1 (instead of 0 as before).
 420          $col += 1;
 421  
 422          $objdrawing = new Drawing();
 423          $objdrawing->setPath($bitmap);
 424          $objdrawing->setCoordinates(Coordinate::stringFromColumnIndex($col) . ($row + 1));
 425          $objdrawing->setOffsetX($x);
 426          $objdrawing->setOffsetY($y);
 427          $objdrawing->setWorksheet($this->worksheet);
 428          if ($scalex != 1) {
 429              $objdrawing->setResizeProportional(false);
 430              $objdrawing->getWidth($objdrawing->getWidth() * $scalex);
 431          }
 432          if ($scaley != 1) {
 433              $objdrawing->setResizeProportional(false);
 434              $objdrawing->setHeight($objdrawing->getHeight() * $scaley);
 435          }
 436      }
 437  
 438     /**
 439      * Merges the area given by its arguments.
 440      *
 441      * @param integer $firstrow First row of the area to merge
 442      * @param integer $firstcol First column of the area to merge
 443      * @param integer $lastrow  Last row of the area to merge
 444      * @param integer $lastcol  Last column of the area to merge
 445      */
 446      public function merge_cells($firstrow, $firstcol, $lastrow, $lastcol) {
 447          // For PhpSpreadsheet library, the column indexes start on 1 (instead of 0 as before).
 448          $this->worksheet->mergeCellsByColumnAndRow($firstcol + 1, $firstrow + 1, $lastcol + 1, $lastrow + 1);
 449      }
 450  
 451      protected function apply_format($row, $col, $format = null) {
 452          if (!$format) {
 453              $format = new MoodleExcelFormat();
 454          } else if (is_array($format)) {
 455              $format = new MoodleExcelFormat($format);
 456          }
 457          $this->worksheet->getStyleByColumnAndRow($col, $row + 1)->applyFromArray($format->get_format_array());
 458      }
 459  
 460      protected function apply_column_format($col, $format = null) {
 461          if (!$format) {
 462              $format = new MoodleExcelFormat();
 463          } else if (is_array($format)) {
 464              $format = new MoodleExcelFormat($format);
 465          }
 466          $this->worksheet->getStyle(Coordinate::stringFromColumnIndex($col))->applyFromArray($format->get_format_array());
 467      }
 468  
 469      protected function apply_row_format($row, $format = null) {
 470          if (!$format) {
 471              $format = new MoodleExcelFormat();
 472          } else if (is_array($format)) {
 473              $format = new MoodleExcelFormat($format);
 474          }
 475          $this->worksheet->getStyle($row + 1)->applyFromArray($format->get_format_array());
 476      }
 477  }
 478  
 479  
 480  /**
 481   * Define and operate over one Format.
 482   *
 483   * A big part of this class acts as a wrapper over other libraries
 484   * maintaining Moodle functions isolated from underlying code.
 485   *
 486   * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
 487   * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 488   * @package moodlecore
 489   */
 490  class MoodleExcelFormat {
 491      /** @var array */
 492      protected $format = array();
 493  
 494      /**
 495       * Constructs one Moodle Format.
 496       *
 497       * @param array $properties
 498       */
 499      public function __construct($properties = array()) {
 500          // If we have something in the array of properties, compute them
 501          foreach($properties as $property => $value) {
 502              if(method_exists($this,"set_$property")) {
 503                  $aux = 'set_'.$property;
 504                  $this->$aux($value);
 505              }
 506          }
 507      }
 508  
 509      /**
 510       * Returns standardised Excel format array.
 511       * @private
 512       *
 513       * @return array
 514       */
 515      public function get_format_array() {
 516          return $this->format;
 517      }
 518      /**
 519       * Set the size of the text in the format (in pixels).
 520       * By default all texts in generated sheets are 10pt.
 521       *
 522       * @param integer $size Size of the text (in points)
 523       */
 524      public function set_size($size) {
 525          $this->format['font']['size'] = $size;
 526      }
 527  
 528      /**
 529       * Set weight of the format.
 530       *
 531       * @param integer $weight Weight for the text, 0 maps to 400 (normal text),
 532       *                        1 maps to 700 (bold text). Valid range is: 100-1000.
 533       *                        It's Optional, default is 1 (bold).
 534       */
 535      public function set_bold($weight = 1) {
 536          if ($weight == 1) {
 537              $weight = 700;
 538          }
 539          $this->format['font']['bold'] = ($weight > 400);
 540      }
 541  
 542      /**
 543       * Set underline of the format.
 544       *
 545       * @param integer $underline The value for underline. Possible values are:
 546       *                           1 => underline, 2 => double underline
 547       */
 548      public function set_underline($underline) {
 549          if ($underline == 1) {
 550              $this->format['font']['underline'] = Font::UNDERLINE_SINGLE;
 551          } else if ($underline == 2) {
 552              $this->format['font']['underline'] = Font::UNDERLINE_DOUBLE;
 553          } else {
 554              $this->format['font']['underline'] = Font::UNDERLINE_NONE;
 555          }
 556      }
 557  
 558      /**
 559       * Set italic of the format.
 560       */
 561      public function set_italic() {
 562          $this->format['font']['italic'] = true;
 563      }
 564  
 565      /**
 566       * Set strikeout of the format.
 567       */
 568      public function set_strikeout() {
 569          $this->format['font']['strikethrough'] = true;
 570      }
 571  
 572      /**
 573       * Set outlining of the format.
 574       */
 575      public function set_outline() {
 576          // Not implemented.
 577      }
 578  
 579      /**
 580       * Set shadow of the format.
 581       */
 582      public function set_shadow() {
 583          // Not implemented.
 584      }
 585  
 586      /**
 587       * Set the script of the text.
 588       *
 589       * @param integer $script The value for script type. Possible values are:
 590       *                        1 => superscript, 2 => subscript
 591       */
 592      public function set_script($script) {
 593          if ($script == 1) {
 594              $this->format['font']['superscript'] = true;
 595          } else if ($script == 2) {
 596              $this->format['font']['subscript'] = true;
 597          } else {
 598              $this->format['font']['superscript'] = false;
 599              $this->format['font']['subscript'] = false;
 600          }
 601      }
 602  
 603      /**
 604       * Set color of the format. Used to specify the color of the text to be formatted.
 605       *
 606       * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
 607       */
 608      public function set_color($color) {
 609          $this->format['font']['color']['rgb'] = $this->parse_color($color);
 610      }
 611  
 612      /**
 613       * Standardise colour name.
 614       *
 615       * @param mixed $color name of the color (i.e.: 'blue', 'red', etc..), or an integer (range is [8...63]).
 616       * @return string the RGB color value
 617       */
 618      protected function parse_color($color) {
 619          if (strpos($color, '#') === 0) {
 620              // No conversion should be needed.
 621              return substr($color, 1);
 622          }
 623  
 624          if ($color > 7 and $color < 53) {
 625              $numbers = array(
 626                  8  => 'black',
 627                  12 => 'blue',
 628                  16 => 'brown',
 629                  15 => 'cyan',
 630                  23 => 'gray',
 631                  17 => 'green',
 632                  11 => 'lime',
 633                  14 => 'magenta',
 634                  18 => 'navy',
 635                  53 => 'orange',
 636                  33 => 'pink',
 637                  20 => 'purple',
 638                  10 => 'red',
 639                  22 => 'silver',
 640                  9  => 'white',
 641                  13 => 'yellow',
 642              );
 643              if (isset($numbers[$color])) {
 644                  $color = $numbers[$color];
 645              } else {
 646                  $color = 'black';
 647              }
 648          }
 649  
 650          $colors = array(
 651              'aqua'    => '00FFFF',
 652              'black'   => '000000',
 653              'blue'    => '0000FF',
 654              'brown'   => 'A52A2A',
 655              'cyan'    => '00FFFF',
 656              'fuchsia' => 'FF00FF',
 657              'gray'    => '808080',
 658              'grey'    => '808080',
 659              'green'   => '00FF00',
 660              'lime'    => '00FF00',
 661              'magenta' => 'FF00FF',
 662              'maroon'  => '800000',
 663              'navy'    => '000080',
 664              'orange'  => 'FFA500',
 665              'olive'   => '808000',
 666              'pink'    => 'FAAFBE',
 667              'purple'  => '800080',
 668              'red'     => 'FF0000',
 669              'silver'  => 'C0C0C0',
 670              'teal'    => '008080',
 671              'white'   => 'FFFFFF',
 672              'yellow'  => 'FFFF00',
 673          );
 674  
 675          if (isset($colors[$color])) {
 676              return($colors[$color]);
 677          }
 678  
 679          return($colors['black']);
 680      }
 681  
 682      /**
 683       * Not used.
 684       *
 685       * @param mixed $color
 686       */
 687      public function set_fg_color($color) {
 688          // Not implemented.
 689      }
 690  
 691      /**
 692       * Set background color of the cell.
 693       *
 694       * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
 695       */
 696      public function set_bg_color($color) {
 697          if (!isset($this->format['fill']['fillType'])) {
 698              $this->format['fill']['fillType'] = Fill::FILL_SOLID;
 699          }
 700          $this->format['fill']['color']['rgb'] = $this->parse_color($color);
 701      }
 702  
 703      /**
 704       * Set the cell fill pattern.
 705       *
 706       * @deprecated use set_bg_color() instead.
 707       * @param integer
 708       */
 709      public function set_pattern($pattern=1) {
 710          if ($pattern > 0) {
 711              if (!isset($this->format['fill']['color']['rgb'])) {
 712                  $this->set_bg_color('black');
 713              }
 714          } else {
 715              unset($this->format['fill']['color']['rgb']);
 716              unset($this->format['fill']['fillType']);
 717          }
 718      }
 719  
 720      /**
 721       * Set text wrap of the format.
 722       */
 723      public function set_text_wrap() {
 724          $this->format['alignment']['wrapText'] = true;
 725      }
 726  
 727      /**
 728       * Set the cell alignment of the format.
 729       *
 730       * @param string $location alignment for the cell ('left', 'right', 'justify', etc...)
 731       */
 732      public function set_align($location) {
 733          if (in_array($location, array('left', 'centre', 'center', 'right', 'fill', 'merge', 'justify', 'equal_space'))) {
 734              $this->set_h_align($location);
 735  
 736          } else if (in_array($location, array('top', 'vcentre', 'vcenter', 'bottom', 'vjustify', 'vequal_space'))) {
 737              $this->set_v_align($location);
 738          }
 739      }
 740  
 741      /**
 742       * Set the cell horizontal alignment of the format.
 743       *
 744       * @param string $location alignment for the cell ('left', 'right', 'justify', etc...)
 745       */
 746      public function set_h_align($location) {
 747          switch ($location) {
 748              case 'left':
 749                  $this->format['alignment']['horizontal'] = Alignment::HORIZONTAL_LEFT;
 750                  break;
 751              case 'center':
 752              case 'centre':
 753                  $this->format['alignment']['horizontal'] = Alignment::HORIZONTAL_CENTER;
 754                  break;
 755              case 'right':
 756                  $this->format['alignment']['horizontal'] = Alignment::HORIZONTAL_RIGHT;
 757                  break;
 758              case 'justify':
 759                  $this->format['alignment']['horizontal'] = Alignment::HORIZONTAL_JUSTIFY;
 760                  break;
 761              default:
 762                  $this->format['alignment']['horizontal'] = Alignment::HORIZONTAL_GENERAL;
 763          }
 764      }
 765  
 766      /**
 767       * Set the cell vertical alignment of the format.
 768       *
 769       * @param string $location alignment for the cell ('top', 'bottom', 'center', 'justify')
 770       */
 771      public function set_v_align($location) {
 772          switch ($location) {
 773              case 'top':
 774                  $this->format['alignment']['vertical'] = Alignment::VERTICAL_TOP;
 775                  break;
 776              case 'vcentre':
 777              case 'vcenter':
 778              case 'centre':
 779              case 'center':
 780                  $this->format['alignment']['vertical'] = Alignment::VERTICAL_CENTER;
 781                  break;
 782              case 'vjustify':
 783              case 'justify':
 784                  $this->format['alignment']['vertical'] = Alignment::VERTICAL_JUSTIFY;
 785                  break;
 786              default:
 787                  $this->format['alignment']['vertical'] = Alignment::VERTICAL_BOTTOM;
 788          }
 789      }
 790  
 791      /**
 792       * Set the top border of the format.
 793       *
 794       * @param integer $style style for the cell. 1 => thin, 2 => thick
 795       */
 796      public function set_top($style) {
 797          if ($style == 1) {
 798              $this->format['borders']['top']['borderStyle'] = Border::BORDER_THIN;
 799          } else if ($style == 2) {
 800              $this->format['borders']['top']['borderStyle'] = Border::BORDER_THICK;
 801          } else {
 802              $this->format['borders']['top']['borderStyle'] = Border::BORDER_NONE;
 803          }
 804      }
 805  
 806      /**
 807       * Set the bottom border of the format.
 808       *
 809       * @param integer $style style for the cell. 1 => thin, 2 => thick
 810       */
 811      public function set_bottom($style) {
 812          if ($style == 1) {
 813              $this->format['borders']['bottom']['borderStyle'] = Border::BORDER_THIN;
 814          } else if ($style == 2) {
 815              $this->format['borders']['bottom']['borderStyle'] = Border::BORDER_THICK;
 816          } else {
 817              $this->format['borders']['bottom']['borderStyle'] = Border::BORDER_NONE;
 818          }
 819      }
 820  
 821      /**
 822       * Set the left border of the format.
 823       *
 824       * @param integer $style style for the cell. 1 => thin, 2 => thick
 825       */
 826      public function set_left($style) {
 827          if ($style == 1) {
 828              $this->format['borders']['left']['borderStyle'] = Border::BORDER_THIN;
 829          } else if ($style == 2) {
 830              $this->format['borders']['left']['borderStyle'] = Border::BORDER_THICK;
 831          } else {
 832              $this->format['borders']['left']['borderStyle'] = Border::BORDER_NONE;
 833          }
 834      }
 835  
 836      /**
 837       * Set the right border of the format.
 838       *
 839       * @param integer $style style for the cell. 1 => thin, 2 => thick
 840       */
 841      public function set_right($style) {
 842          if ($style == 1) {
 843              $this->format['borders']['right']['borderStyle'] = Border::BORDER_THIN;
 844          } else if ($style == 2) {
 845              $this->format['borders']['right']['borderStyle'] = Border::BORDER_THICK;
 846          } else {
 847              $this->format['borders']['right']['borderStyle'] = Border::BORDER_NONE;
 848          }
 849      }
 850  
 851      /**
 852       * Set cells borders to the same style.
 853       *
 854       * @param integer $style style to apply for all cell borders. 1 => thin, 2 => thick.
 855       */
 856      public function set_border($style) {
 857          $this->set_top($style);
 858          $this->set_bottom($style);
 859          $this->set_left($style);
 860          $this->set_right($style);
 861      }
 862  
 863      /**
 864       * Set the numerical format of the format.
 865       * It can be date, time, currency, etc...
 866       *
 867       * @param mixed $numformat The numeric format
 868       */
 869      public function set_num_format($numformat) {
 870          $numbers = array();
 871  
 872          $numbers[1] = '0';
 873          $numbers[2] = '0.00';
 874          $numbers[3] = '#,##0';
 875          $numbers[4] = '#,##0.00';
 876          $numbers[11] = '0.00E+00';
 877          $numbers[12] = '# ?/?';
 878          $numbers[13] = '# ??/??';
 879          $numbers[14] = 'mm-dd-yy';
 880          $numbers[15] = 'd-mmm-yy';
 881          $numbers[16] = 'd-mmm';
 882          $numbers[17] = 'mmm-yy';
 883          $numbers[22] = 'm/d/yy h:mm';
 884          $numbers[49] = '@';
 885  
 886          if ($numformat !== 0 and in_array($numformat, $numbers)) {
 887              $this->format['numberFormat']['formatCode'] = $numformat;
 888          }
 889  
 890          if (!isset($numbers[$numformat])) {
 891              return;
 892          }
 893  
 894          $this->format['numberFormat']['formatCode'] = $numbers[$numformat];
 895      }
 896  }