[Summary view] [Print] [Text view]

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