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