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