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