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