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 /**
  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() || core_useragent::is_edge()) {
 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 }