lib/excellib.class.php

   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 PhpSpreadsheet */
  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 }