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