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 (core_useragent::is_ie()) {
 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 = core_text::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 other libraries
 448  * maintaining Moodle functions isolated from underlying code.
 449  *
 450  * @copyright 1999 onwards Martin Dougiamas  {@link http://moodle.com}
 451  * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 452  * @package moodlecore
 453  */
 454 class MoodleExcelFormat {
 455     /** @var array */
 456     protected $format = array('font'=>array('size'=>10, 'name'=>'Arial'));
 457
 458     /**
 459      * Constructs one Moodle Format.
 460      *
 461      * @param array $properties
 462      */
 463     public function __construct($properties = array()) {
 464         // If we have something in the array of properties, compute them
 465         foreach($properties as $property => $value) {
 466             if(method_exists($this,"set_$property")) {
 467                 $aux = 'set_'.$property;
 468                 $this->$aux($value);
 469             }
 470         }
 471     }
 472
 473     /**
 474      * Returns standardised Excel format array.
 475      * @private
 476      *
 477      * @return array
 478      */
 479     public function get_format_array() {
 480         return $this->format;
 481     }
 482     /**
 483      * Set the size of the text in the format (in pixels).
 484      * By default all texts in generated sheets are 10pt.
 485      *
 486      * @param integer $size Size of the text (in points)
 487      */
 488     public function set_size($size) {
 489         $this->format['font']['size'] = $size;
 490     }
 491
 492     /**
 493      * Set weight of the format.
 494      *
 495      * @param integer $weight Weight for the text, 0 maps to 400 (normal text),
 496      *                        1 maps to 700 (bold text). Valid range is: 100-1000.
 497      *                        It's Optional, default is 1 (bold).
 498      */
 499     public function set_bold($weight = 1) {
 500         if ($weight == 1) {
 501             $weight = 700;
 502         }
 503         $this->format['font']['bold'] = ($weight > 400);
 504     }
 505
 506     /**
 507      * Set underline of the format.
 508      *
 509      * @param integer $underline The value for underline. Possible values are:
 510      *                           1 => underline, 2 => double underline
 511      */
 512     public function set_underline($underline) {
 513         if ($underline == 1) {
 514             $this->format['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_SINGLE;
 515         } else if ($underline == 2) {
 516             $this->format['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_DOUBLE;
 517         } else {
 518             $this->format['font']['underline'] = PHPExcel_Style_Font::UNDERLINE_NONE;
 519         }
 520     }
 521
 522     /**
 523      * Set italic of the format.
 524      */
 525     public function set_italic() {
 526         $this->format['font']['italic'] = true;
 527     }
 528
 529     /**
 530      * Set strikeout of the format.
 531      */
 532     public function set_strikeout() {
 533         $this->format['font']['strike'] = true;
 534     }
 535
 536     /**
 537      * Set outlining of the format.
 538      */
 539     public function set_outline() {
 540         // Not implemented.
 541     }
 542
 543     /**
 544      * Set shadow of the format.
 545      */
 546     public function set_shadow() {
 547         // Not implemented.
 548     }
 549
 550     /**
 551      * Set the script of the text.
 552      *
 553      * @param integer $script The value for script type. Possible values are:
 554      *                        1 => superscript, 2 => subscript
 555      */
 556     public function set_script($script) {
 557         if ($script == 1) {
 558             $this->format['font']['superScript'] = true;
 559         } else if ($script == 2) {
 560             $this->format['font']['subScript'] = true;
 561         } else {
 562             $this->format['font']['superScript'] = false;
 563             $this->format['font']['subScript'] = false;
 564         }
 565     }
 566
 567     /**
 568      * Set color of the format. Used to specify the color of the text to be formatted.
 569      *
 570      * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
 571      */
 572     public function set_color($color) {
 573         $this->format['font']['color']['rgb'] = $this->parse_color($color);
 574     }
 575
 576     /**
 577      * Standardise colour name.
 578      *
 579      * @param mixed $color name of the color (i.e.: 'blue', 'red', etc..), or an integer (range is [8...63]).
 580      * @return string the RGB color value
 581      */
 582     protected function parse_color($color) {
 583         if (strpos($color, '#') === 0) {
 584             // No conversion should be needed.
 585             return substr($color, 1);
 586         }
 587
 588         if ($color > 7 and $color < 53) {
 589             $numbers = array(
 590                 8  => 'black',
 591                 12 => 'blue',
 592                 16 => 'brown',
 593                 15 => 'cyan',
 594                 23 => 'gray',
 595                 17 => 'green',
 596                 11 => 'lime',
 597                 14 => 'magenta',
 598                 18 => 'navy',
 599                 53 => 'orange',
 600                 33 => 'pink',
 601                 20 => 'purple',
 602                 10 => 'red',
 603                 22 => 'silver',
 604                 9  => 'white',
 605                 13 => 'yellow',
 606             );
 607             if (isset($numbers[$color])) {
 608                 $color = $numbers[$color];
 609             } else {
 610                 $color = 'black';
 611             }
 612         }
 613
 614         $colors = array(
 615             'aqua'    => '00FFFF',
 616             'black'   => '000000',
 617             'blue'    => '0000FF',
 618             'brown'   => 'A52A2A',
 619             'cyan'    => '00FFFF',
 620             'fuchsia' => 'FF00FF',
 621             'gray'    => '808080',
 622             'grey'    => '808080',
 623             'green'   => '00FF00',
 624             'lime'    => '00FF00',
 625             'magenta' => 'FF00FF',
 626             'maroon'  => '800000',
 627             'navy'    => '000080',
 628             'orange'  => 'FFA500',
 629             'olive'   => '808000',
 630             'pink'    => 'FAAFBE',
 631             'purple'  => '800080',
 632             'red'     => 'FF0000',
 633             'silver'  => 'C0C0C0',
 634             'teal'    => '008080',
 635             'white'   => 'FFFFFF',
 636             'yellow'  => 'FFFF00',
 637         );
 638
 639         if (isset($colors[$color])) {
 640             return($colors[$color]);
 641         }
 642
 643         return($colors['black']);
 644     }
 645
 646     /**
 647      * Not used.
 648      *
 649      * @param mixed $color
 650      */
 651     public function set_fg_color($color) {
 652         // Not implemented.
 653     }
 654
 655     /**
 656      * Set background color of the cell.
 657      *
 658      * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
 659      */
 660     public function set_bg_color($color) {
 661         if (!isset($this->format['fill']['type'])) {
 662             $this->format['fill']['type'] = PHPExcel_Style_Fill::FILL_SOLID;
 663         }
 664         $this->format['fill']['color']['rgb'] = $this->parse_color($color);
 665     }
 666
 667     /**
 668      * Set the cell fill pattern.
 669      *
 670      * @deprecated use set_bg_color() instead.
 671      * @param integer
 672      */
 673     public function set_pattern($pattern=1) {
 674         if ($pattern > 0) {
 675             if (!isset($this->format['fill']['color']['rgb'])) {
 676                 $this->set_bg_color('black');
 677             }
 678         } else {
 679             unset($this->format['fill']['color']['rgb']);
 680             unset($this->format['fill']['type']);
 681         }
 682     }
 683
 684     /**
 685      * Set text wrap of the format.
 686      */
 687     public function set_text_wrap() {
 688         $this->format['alignment']['wrap'] = true;
 689     }
 690
 691     /**
 692      * Set the cell alignment of the format.
 693      *
 694      * @param string $location alignment for the cell ('left', 'right', 'justify', etc...)
 695      */
 696     public function set_align($location) {
 697         if (in_array($location, array('left', 'centre', 'center', 'right', 'fill', 'merge', 'justify', 'equal_space'))) {
 698             $this->set_h_align($location);
 699
 700         } else if (in_array($location, array('top', 'vcentre', 'vcenter', 'bottom', 'vjustify', 'vequal_space'))) {
 701             $this->set_v_align($location);
 702         }
 703     }
 704
 705     /**
 706      * Set the cell horizontal alignment of the format.
 707      *
 708      * @param string $location alignment for the cell ('left', 'right', 'justify', etc...)
 709      */
 710     public function set_h_align($location) {
 711         switch ($location) {
 712             case 'left':
 713                 $this->format['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
 714                 break;
 715             case 'center':
 716             case 'centre':
 717                 $this->format['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
 718                 break;
 719             case 'right':
 720                 $this->format['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
 721                 break;
 722             case 'justify':
 723                 $this->format['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY;
 724                 break;
 725             default:
 726                 $this->format['alignment']['horizontal'] = PHPExcel_Style_Alignment::HORIZONTAL_GENERAL;
 727         }
 728     }
 729
 730     /**
 731      * Set the cell vertical alignment of the format.
 732      *
 733      * @param string $location alignment for the cell ('top', 'bottom', 'center', 'justify')
 734      */
 735     public function set_v_align($location) {
 736         switch ($location) {
 737             case 'top':
 738                 $this->format['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_TOP;
 739                 break;
 740             case 'vcentre':
 741             case 'vcenter':
 742             case 'centre':
 743             case 'center':
 744                 $this->format['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_CENTER;
 745                 break;
 746             case 'vjustify':
 747             case 'justify':
 748                 $this->format['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_JUSTIFY;
 749                 break;
 750             default:
 751                 $this->format['alignment']['vertical'] = PHPExcel_Style_Alignment::VERTICAL_BOTTOM;
 752         }
 753     }
 754
 755     /**
 756      * Set the top border of the format.
 757      *
 758      * @param integer $style style for the cell. 1 => thin, 2 => thick
 759      */
 760     public function set_top($style) {
 761         if ($style == 1) {
 762             $this->format['borders']['top']['style'] = PHPExcel_Style_Border::BORDER_THIN;
 763         } else if ($style == 2) {
 764             $this->format['borders']['top']['style'] = PHPExcel_Style_Border::BORDER_THICK;
 765         } else {
 766             $this->format['borders']['top']['style'] = PHPExcel_Style_Border::BORDER_NONE;
 767         }
 768     }
 769
 770     /**
 771      * Set the bottom border of the format.
 772      *
 773      * @param integer $style style for the cell. 1 => thin, 2 => thick
 774      */
 775     public function set_bottom($style) {
 776         if ($style == 1) {
 777             $this->format['borders']['bottom']['style'] = PHPExcel_Style_Border::BORDER_THIN;
 778         } else if ($style == 2) {
 779             $this->format['borders']['bottom']['style'] = PHPExcel_Style_Border::BORDER_THICK;
 780         } else {
 781             $this->format['borders']['bottom']['style'] = PHPExcel_Style_Border::BORDER_NONE;
 782         }
 783     }
 784
 785     /**
 786      * Set the left border of the format.
 787      *
 788      * @param integer $style style for the cell. 1 => thin, 2 => thick
 789      */
 790     public function set_left($style) {
 791         if ($style == 1) {
 792             $this->format['borders']['left']['style'] = PHPExcel_Style_Border::BORDER_THIN;
 793         } else if ($style == 2) {
 794             $this->format['borders']['left']['style'] = PHPExcel_Style_Border::BORDER_THICK;
 795         } else {
 796             $this->format['borders']['left']['style'] = PHPExcel_Style_Border::BORDER_NONE;
 797         }
 798     }
 799
 800     /**
 801      * Set the right border of the format.
 802      *
 803      * @param integer $style style for the cell. 1 => thin, 2 => thick
 804      */
 805     public function set_right($style) {
 806         if ($style == 1) {
 807             $this->format['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_THIN;
 808         } else if ($style == 2) {
 809             $this->format['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_THICK;
 810         } else {
 811             $this->format['borders']['right']['style'] = PHPExcel_Style_Border::BORDER_NONE;
 812         }
 813     }
 814
 815     /**
 816      * Set cells borders to the same style.
 817      *
 818      * @param integer $style style to apply for all cell borders. 1 => thin, 2 => thick.
 819      */
 820     public function set_border($style) {
 821         $this->set_top($style);
 822         $this->set_bottom($style);
 823         $this->set_left($style);
 824         $this->set_right($style);
 825     }
 826
 827     /**
 828      * Set the numerical format of the format.
 829      * It can be date, time, currency, etc...
 830      *
 831      * @param mixed $num_format The numeric format
 832      */
 833     public function set_num_format($num_format) {
 834         $numbers = array();
 835
 836         $numbers[1] = '0';
 837         $numbers[2] = '0.00';
 838         $numbers[3] = '#,##0';
 839         $numbers[4] = '#,##0.00';
 840         $numbers[11] = '0.00E+00';
 841         $numbers[12] = '# ?/?';
 842         $numbers[13] = '# ??/??';
 843         $numbers[14] = 'mm-dd-yy';
 844         $numbers[15] = 'd-mmm-yy';
 845         $numbers[16] = 'd-mmm';
 846         $numbers[17] = 'mmm-yy';
 847         $numbers[22] = 'm/d/yy h:mm';
 848         $numbers[49] = '@';
 849
 850         if ($num_format !== 0 and in_array($num_format, $numbers)) {
 851             $this->format['numberformat']['code'] = $num_format;
 852         }
 853
 854         if (!isset($numbers[$num_format])) {
 855             return;
 856         }
 857
 858         $this->format['numberformat']['code'] = $numbers[$num_format];
 859     }
 860 }