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