2 // This file is part of Moodle - http://moodle.org/
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.
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.
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/>.
18 * Excel writer abstraction layer.
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
25 defined('MOODLE_INTERNAL') ||
die();
28 * Define and operate over one Moodle Workbook.
30 * This class acts as a wrapper around another library
31 * maintaining Moodle functions isolated from underlying code.
33 * @copyright 1999 onwards Martin Dougiamas {@link http://moodle.com}
34 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
37 class MoodleExcelWorkbook
{
39 protected $objPHPExcel;
44 /** @var string format type */
48 * Constructs one Moodle Workbook.
50 * @param string $filename The name of the file
51 * @param string $type file format type 'Excel5' or 'Excel2007'
53 public function __construct($filename, $type = 'Excel2007') {
55 require_once("$CFG->libdir/phpexcel/PHPExcel.php");
57 $this->objPHPExcel
= new PHPExcel();
58 $this->objPHPExcel
->removeSheetByIndex(0);
60 $this->filename
= $filename;
62 if (strtolower($type) === 'excel5') {
63 $this->type
= 'Excel5';
65 $this->type
= 'Excel2007';
70 * Create one Moodle Worksheet
72 * @param string $name Name of the sheet
73 * @return MoodleExcelWorksheet
75 public function add_worksheet($name = '') {
76 return new MoodleExcelWorksheet($name, $this->objPHPExcel
);
80 * Create one cell Format.
82 * @param array $properties array of properties [name]=value;
83 * valid names are set_XXXX existing
84 * functions without the set_ part
85 * i.e: [bold]=1 for set_bold(1)...Optional!
86 * @return MoodleExcelFormat
88 public function add_format($properties = array()) {
89 return new MoodleExcelFormat($properties);
93 * Close the Moodle Workbook
95 public function close() {
98 foreach ($this->objPHPExcel
->getAllSheets() as $sheet){
99 $sheet->setSelectedCells('A1');
101 $this->objPHPExcel
->setActiveSheetIndex(0);
103 $filename = preg_replace('/\.xlsx?$/i', '', $this->filename
);
105 if ($this->type
=== 'Excel5') {
106 $mimetype = 'application/vnd.ms-excel';
107 $filename = $filename.'.xls';
109 $mimetype = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
110 $filename = $filename.'.xlsx';
113 if (strpos($CFG->wwwroot
, 'https://') === 0) { //https sites - watch out for IE! KB812935 and KB316431
114 header('Cache-Control: max-age=10');
115 header('Expires: '. gmdate('D, d M Y H:i:s', 0) .' GMT');
117 } else { //normal http - prevent caching at all cost
118 header('Cache-Control: private, must-revalidate, pre-check=0, post-check=0, max-age=0');
119 header('Expires: '. gmdate('D, d M Y H:i:s', 0) .' GMT');
120 header('Pragma: no-cache');
123 if (check_browser_version('MSIE')) {
124 $filename = rawurlencode($filename);
126 $filename = s($filename);
129 header('Content-Type: '.$mimetype);
130 header('Content-Disposition: attachment;filename="'.$filename.'"');
132 $objWriter = PHPExcel_IOFactory
::createWriter($this->objPHPExcel
, $this->type
);
133 $objWriter->save('php://output');
137 * Not required to use.
138 * @param string $filename Name of the downloaded file
140 public function send($filename) {
141 $this->filename
= $filename;
146 * Define and operate over one Worksheet.
148 * This class acts as a wrapper around another library
149 * maintaining Moodle functions isolated from underlying code.
151 * @copyright 1999 onwards Martin Dougiamas {@link http://moodle.com}
152 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
155 class MoodleExcelWorksheet
{
156 /** @var PHPExcel_Worksheet */
157 protected $worksheet;
160 * Constructs one Moodle Worksheet.
162 * @param string $name The name of the file
163 * @param PHPExcel $workbook The internal Workbook object we are creating.
165 public function __construct($name, PHPExcel
$workbook) {
166 // Replace any characters in the name that Excel cannot cope with.
167 $name = strtr($name, '[]*/\?:', ' ');
168 // Shorten the title if necessary.
169 $name = textlib
::substr($name, 0, 31);
173 $name = 'Sheet'.($workbook->getSheetCount()+
1);
176 $this->worksheet
= new PHPExcel_Worksheet($workbook, $name);
177 $this->worksheet
->setPrintGridlines(false);
179 $workbook->addSheet($this->worksheet
);
183 * Write one string somewhere in the worksheet.
185 * @param integer $row Zero indexed row
186 * @param integer $col Zero indexed column
187 * @param string $str The string to write
188 * @param mixed $format The XF format for the cell
190 public function write_string($row, $col, $str, $format = null) {
191 $this->worksheet
->getStyleByColumnAndRow($col, $row+
1)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat
::FORMAT_TEXT
);
192 $this->worksheet
->setCellValueExplicitByColumnAndRow($col, $row+
1, $str, PHPExcel_Cell_DataType
::TYPE_STRING
);
193 $this->apply_format($row, $col, $format);
197 * Write one number somewhere in the worksheet.
199 * @param integer $row Zero indexed row
200 * @param integer $col Zero indexed column
201 * @param float $num The number to write
202 * @param mixed $format The XF format for the cell
204 public function write_number($row, $col, $num, $format = null) {
205 $this->worksheet
->getStyleByColumnAndRow($col, $row+
1)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat
::FORMAT_GENERAL
);
206 $this->worksheet
->setCellValueExplicitByColumnAndRow($col, $row+
1, $num, PHPExcel_Cell_DataType
::TYPE_NUMERIC
);
207 $this->apply_format($row, $col, $format);
211 * Write one url somewhere in the worksheet.
213 * @param integer $row Zero indexed row
214 * @param integer $col Zero indexed column
215 * @param string $url The url to write
216 * @param mixed $format The XF format for the cell
218 public function write_url($row, $col, $url, $format = null) {
219 $this->worksheet
->setCellValueByColumnAndRow($col, $row+
1, $url);
220 $this->worksheet
->getCellByColumnAndRow($col, $row+
1)->getHyperlink()->setUrl($url);
221 $this->apply_format($row, $col, $format);
225 * Write one date somewhere in the worksheet.
226 * @param integer $row Zero indexed row
227 * @param integer $col Zero indexed column
228 * @param string $date The date to write in UNIX timestamp format
229 * @param mixed $format The XF format for the cell
231 public function write_date($row, $col, $date, $format = null) {
232 $getdate = usergetdate($date);
233 $exceldate = PHPExcel_Shared_Date
::FormattedPHPToExcel(
242 $this->worksheet
->setCellValueByColumnAndRow($col, $row+
1, $exceldate);
243 $this->worksheet
->getStyleByColumnAndRow($col, $row+
1)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat
::FORMAT_DATE_XLSX22
);
244 $this->apply_format($row, $col, $format);
248 * Write one formula somewhere in the worksheet.
250 * @param integer $row Zero indexed row
251 * @param integer $col Zero indexed column
252 * @param string $formula The formula to write
253 * @param mixed $format The XF format for the cell
255 public function write_formula($row, $col, $formula, $format = null) {
256 $this->worksheet
->setCellValueExplicitByColumnAndRow($col, $row+
1, $formula, PHPExcel_Cell_DataType
::TYPE_FORMULA
);
257 $this->apply_format($row, $col, $format);
261 * Write one blank somewhere in the worksheet.
263 * @param integer $row Zero indexed row
264 * @param integer $col Zero indexed column
265 * @param mixed $format The XF format for the cell
267 public function write_blank($row, $col, $format = null) {
268 $this->worksheet
->setCellValueByColumnAndRow($col, $row+
1, '');
269 $this->apply_format($row, $col, $format);
273 * Write anything somewhere in the worksheet,
274 * type will be automatically detected.
276 * @param integer $row Zero indexed row
277 * @param integer $col Zero indexed column
278 * @param mixed $token What we are writing
279 * @param mixed $format The XF format for the cell
281 public function write($row, $col, $token, $format = null) {
282 // Analyse what are we trying to send.
283 if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
285 return $this->write_number($row, $col, $token, $format);
286 } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
287 // Match http or ftp URL
288 return $this->write_url($row, $col, $token, '', $format);
289 } elseif (preg_match("/^mailto:/", $token)) {
291 return $this->write_url($row, $col, $token, '', $format);
292 } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
293 // Match internal or external sheet link
294 return $this->write_url($row, $col, $token, '', $format);
295 } elseif (preg_match("/^=/", $token)) {
297 return $this->write_formula($row, $col, $token, $format);
298 } elseif (preg_match("/^@/", $token)) {
300 return $this->write_formula($row, $col, $token, $format);
301 } elseif ($token == '') {
303 return $this->write_blank($row, $col, $format);
305 // Default: match string
306 return $this->write_string($row, $col, $token, $format);
311 * Sets the height (and other settings) of one row.
313 * @param integer $row The row to set
314 * @param integer $height Height we are giving to the row (null to set just format without setting the height)
315 * @param mixed $format The optional format we are giving to the row
316 * @param bool $hidden The optional hidden attribute
317 * @param integer $level The optional outline level (0-7)
319 public function set_row($row, $height, $format = null, $hidden = false, $level = 0) {
322 } else if ($level > 7) {
325 if (isset($height)) {
326 $this->worksheet
->getRowDimension($row+
1)->setRowHeight($height);
328 $this->worksheet
->getRowDimension($row+
1)->setVisible(!$hidden);
329 $this->worksheet
->getRowDimension($row+
1)->setOutlineLevel($level);
330 $this->apply_row_format($row, $format);
334 * Sets the width (and other settings) of one column.
336 * @param integer $firstcol first column on the range
337 * @param integer $lastcol last column on the range
338 * @param integer $width width to set (null to set just format without setting the width)
339 * @param mixed $format The optional format to apply to the columns
340 * @param bool $hidden The optional hidden attribute
341 * @param integer $level The optional outline level (0-7)
343 public function set_column($firstcol, $lastcol, $width, $format = null, $hidden = false, $level = 0) {
346 } else if ($level > 7) {
350 while($i <= $lastcol) {
352 $this->worksheet
->getColumnDimensionByColumn($i)->setWidth($width);
354 $this->worksheet
->getColumnDimensionByColumn($i)->setVisible(!$hidden);
355 $this->worksheet
->getColumnDimensionByColumn($i)->setOutlineLevel($level);
356 $this->apply_column_format($i, $format);
362 * Set the option to hide grid lines on the printed page.
364 public function hide_gridlines() {
365 // Not implemented - always off.
369 * Set the option to hide gridlines on the worksheet (as seen on the screen).
371 public function hide_screen_gridlines() {
372 $this->worksheet
->setShowGridlines(false);
376 * Insert an image in a worksheet.
378 * @param integer $row The row we are going to insert the bitmap into
379 * @param integer $col The column we are going to insert the bitmap into
380 * @param string $bitmap The bitmap filename
381 * @param integer $x The horizontal position (offset) of the image inside the cell.
382 * @param integer $y The vertical position (offset) of the image inside the cell.
383 * @param integer $scale_x The horizontal scale
384 * @param integer $scale_y The vertical scale
386 public function insert_bitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1) {
387 $objDrawing = new PHPExcel_Worksheet_Drawing();
388 $objDrawing->setPath($bitmap);
389 $objDrawing->setCoordinates(PHPExcel_Cell
::stringFromColumnIndex($col) . ($row+
1));
390 $objDrawing->setOffsetX($x);
391 $objDrawing->setOffsetY($y);
392 $objDrawing->setWorksheet($this->worksheet
);
394 $objDrawing->setResizeProportional(false);
395 $objDrawing->getWidth($objDrawing->getWidth()*$scale_x);
398 $objDrawing->setResizeProportional(false);
399 $objDrawing->setHeight($objDrawing->getHeight()*$scale_y);
404 * Merges the area given by its arguments.
406 * @param integer $first_row First row of the area to merge
407 * @param integer $first_col First column of the area to merge
408 * @param integer $last_row Last row of the area to merge
409 * @param integer $last_col Last column of the area to merge
411 public function merge_cells($first_row, $first_col, $last_row, $last_col) {
412 $this->worksheet
->mergeCellsByColumnAndRow($first_col, $first_row+
1, $last_col, $last_row+
1);
415 protected function apply_format($row, $col, $format = null) {
417 $format = new MoodleExcelFormat();
418 } else if (is_array($format)) {
419 $format = new MoodleExcelFormat($format);
421 $this->worksheet
->getStyleByColumnAndRow($col, $row+
1)->applyFromArray($format->get_format_array());
424 protected function apply_column_format($col, $format = null) {
426 $format = new MoodleExcelFormat();
427 } else if (is_array($format)) {
428 $format = new MoodleExcelFormat($format);
430 $this->worksheet
->getStyle(PHPExcel_Cell
::stringFromColumnIndex($col))->applyFromArray($format->get_format_array());
433 protected function apply_row_format($row, $format = null) {
435 $format = new MoodleExcelFormat();
436 } else if (is_array($format)) {
437 $format = new MoodleExcelFormat($format);
439 $this->worksheet
->getStyle($row+
1)->applyFromArray($format->get_format_array());
445 * Define and operate over one Format.
447 * A big part of this class acts as a wrapper over the PEAR
448 * Spreadsheet_Excel_Writer_Workbook and OLE libraries
449 * maintaining Moodle functions isolated from underlying code.
451 * @copyright 1999 onwards Martin Dougiamas {@link http://moodle.com}
452 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
453 * @package moodlecore
455 class MoodleExcelFormat
{
457 protected $format = array('font'=>array('size'=>10, 'name'=>'Arial'));
460 * Constructs one Moodle Format.
462 * @param array $properties
464 public function __construct($properties = array()) {
465 // If we have something in the array of properties, compute them
466 foreach($properties as $property => $value) {
467 if(method_exists($this,"set_$property")) {
468 $aux = 'set_'.$property;
475 * Returns standardised Excel format array.
480 public function get_format_array() {
481 return $this->format
;
484 * Set the size of the text in the format (in pixels).
485 * By default all texts in generated sheets are 10pt.
487 * @param integer $size Size of the text (in points)
489 public function set_size($size) {
490 $this->format
['font']['size'] = $size;
494 * Set weight of the format.
496 * @param integer $weight Weight for the text, 0 maps to 400 (normal text),
497 * 1 maps to 700 (bold text). Valid range is: 100-1000.
498 * It's Optional, default is 1 (bold).
500 public function set_bold($weight = 1) {
504 $this->format
['font']['bold'] = ($weight > 400);
508 * Set underline of the format.
510 * @param integer $underline The value for underline. Possible values are:
511 * 1 => underline, 2 => double underline
513 public function set_underline($underline) {
514 if ($underline == 1) {
515 $this->format
['font']['underline'] = PHPExcel_Style_Font
::UNDERLINE_SINGLE
;
516 } else if ($underline == 2) {
517 $this->format
['font']['underline'] = PHPExcel_Style_Font
::UNDERLINE_DOUBLE
;
519 $this->format
['font']['underline'] = PHPExcel_Style_Font
::UNDERLINE_NONE
;
524 * Set italic of the format.
526 public function set_italic() {
527 $this->format
['font']['italic'] = true;
531 * Set strikeout of the format.
533 public function set_strikeout() {
534 $this->format
['font']['strike'] = true;
538 * Set outlining of the format.
540 public function set_outline() {
545 * Set shadow of the format.
547 public function set_shadow() {
552 * Set the script of the text.
554 * @param integer $script The value for script type. Possible values are:
555 * 1 => superscript, 2 => subscript
557 public function set_script($script) {
559 $this->format
['font']['superScript'] = true;
560 } else if ($script == 2) {
561 $this->format
['font']['subScript'] = true;
563 $this->format
['font']['superScript'] = false;
564 $this->format
['font']['subScript'] = false;
569 * Set color of the format. Used to specify the color of the text to be formatted.
571 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
573 public function set_color($color) {
574 $this->format
['font']['color']['rgb'] = $this->parse_color($color);
578 * Standardise colour name.
580 * @param mixed $color name of the color (i.e.: 'blue', 'red', etc..), or an integer (range is [8...63]).
581 * @return string the RGB color value
583 protected function parse_color($color) {
584 if (strpos($color, '#') === 0) {
585 // No conversion should be needed.
586 return substr($color, 1);
589 if ($color > 7 and $color < 53) {
608 if (isset($numbers[$color])) {
609 $color = $numbers[$color];
621 'fuchsia' => 'FF00FF',
626 'magenta' => 'FF00FF',
627 'maroon' => '800000',
629 'orange' => 'FFA500',
632 'purple' => '800080',
634 'silver' => 'C0C0C0',
637 'yellow' => 'FFFF00',
640 if (isset($colors[$color])) {
641 return($colors[$color]);
644 return($colors['black']);
650 * @param mixed $color
652 public function set_fg_color($color) {
657 * Set background color of the cell.
659 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
661 public function set_bg_color($color) {
662 if (!isset($this->format
['fill']['type'])) {
663 $this->format
['fill']['type'] = PHPExcel_Style_Fill
::FILL_SOLID
;
665 $this->format
['fill']['color']['rgb'] = $this->parse_color($color);
669 * Set the cell fill pattern.
671 * @deprecated use set_bg_color() instead.
674 public function set_pattern($pattern=1) {
676 if (!isset($this->format
['fill']['color']['rgb'])) {
677 $this->set_bg_color('black');
680 unset($this->format
['fill']['color']['rgb']);
681 unset($this->format
['fill']['type']);
686 * Set text wrap of the format.
688 public function set_text_wrap() {
689 $this->format
['alignment']['wrap'] = true;
693 * Set the cell alignment of the format.
695 * @param string $location alignment for the cell ('left', 'right', 'justify', etc...)
697 public function set_align($location) {
698 if (in_array($location, array('left', 'centre', 'center', 'right', 'fill', 'merge', 'justify', 'equal_space'))) {
699 $this->set_h_align($location);
701 } else if (in_array($location, array('top', 'vcentre', 'vcenter', 'bottom', 'vjustify', 'vequal_space'))) {
702 $this->set_v_align($location);
707 * Set the cell horizontal alignment of the format.
709 * @param string $location alignment for the cell ('left', 'right', 'justify', etc...)
711 public function set_h_align($location) {
714 $this->format
['alignment']['horizontal'] = PHPExcel_Style_Alignment
::HORIZONTAL_LEFT
;
718 $this->format
['alignment']['horizontal'] = PHPExcel_Style_Alignment
::HORIZONTAL_CENTER
;
721 $this->format
['alignment']['horizontal'] = PHPExcel_Style_Alignment
::HORIZONTAL_RIGHT
;
724 $this->format
['alignment']['horizontal'] = PHPExcel_Style_Alignment
::HORIZONTAL_JUSTIFY
;
727 $this->format
['alignment']['horizontal'] = PHPExcel_Style_Alignment
::HORIZONTAL_GENERAL
;
732 * Set the cell vertical alignment of the format.
734 * @param string $location alignment for the cell ('top', 'bottom', 'center', 'justify')
736 public function set_v_align($location) {
739 $this->format
['alignment']['vertical'] = PHPExcel_Style_Alignment
::VERTICAL_TOP
;
745 $this->format
['alignment']['vertical'] = PHPExcel_Style_Alignment
::VERTICAL_CENTER
;
749 $this->format
['alignment']['vertical'] = PHPExcel_Style_Alignment
::VERTICAL_JUSTIFY
;
752 $this->format
['alignment']['vertical'] = PHPExcel_Style_Alignment
::VERTICAL_BOTTOM
;
757 * Set the top border of the format.
759 * @param integer $style style for the cell. 1 => thin, 2 => thick
761 public function set_top($style) {
763 $this->format
['borders']['top']['style'] = PHPExcel_Style_Border
::BORDER_THIN
;
764 } else if ($style == 2) {
765 $this->format
['borders']['top']['style'] = PHPExcel_Style_Border
::BORDER_THICK
;
767 $this->format
['borders']['top']['style'] = PHPExcel_Style_Border
::BORDER_NONE
;
772 * Set the bottom border of the format.
774 * @param integer $style style for the cell. 1 => thin, 2 => thick
776 public function set_bottom($style) {
778 $this->format
['borders']['bottom']['style'] = PHPExcel_Style_Border
::BORDER_THIN
;
779 } else if ($style == 2) {
780 $this->format
['borders']['bottom']['style'] = PHPExcel_Style_Border
::BORDER_THICK
;
782 $this->format
['borders']['bottom']['style'] = PHPExcel_Style_Border
::BORDER_NONE
;
787 * Set the left border of the format.
789 * @param integer $style style for the cell. 1 => thin, 2 => thick
791 public function set_left($style) {
793 $this->format
['borders']['left']['style'] = PHPExcel_Style_Border
::BORDER_THIN
;
794 } else if ($style == 2) {
795 $this->format
['borders']['left']['style'] = PHPExcel_Style_Border
::BORDER_THICK
;
797 $this->format
['borders']['left']['style'] = PHPExcel_Style_Border
::BORDER_NONE
;
802 * Set the right border of the format.
804 * @param integer $style style for the cell. 1 => thin, 2 => thick
806 public function set_right($style) {
808 $this->format
['borders']['right']['style'] = PHPExcel_Style_Border
::BORDER_THIN
;
809 } else if ($style == 2) {
810 $this->format
['borders']['right']['style'] = PHPExcel_Style_Border
::BORDER_THICK
;
812 $this->format
['borders']['right']['style'] = PHPExcel_Style_Border
::BORDER_NONE
;
817 * Set cells borders to the same style.
819 * @param integer $style style to apply for all cell borders. 1 => thin, 2 => thick.
821 public function set_border($style) {
822 $this->set_top($style);
823 $this->set_bottom($style);
824 $this->set_left($style);
825 $this->set_right($style);
829 * Set the numerical format of the format.
830 * It can be date, time, currency, etc...
832 * @param mixed $num_format The numeric format
834 public function set_num_format($num_format) {
838 $numbers[2] = '0.00';
839 $numbers[3] = '#,##0';
840 $numbers[4] = '#,##0.00';
841 $numbers[11] = '0.00E+00';
842 $numbers[12] = '# ?/?';
843 $numbers[13] = '# ??/??';
844 $numbers[14] = 'mm-dd-yy';
845 $numbers[15] = 'd-mmm-yy';
846 $numbers[16] = 'd-mmm';
847 $numbers[17] = 'mmm-yy';
848 $numbers[22] = 'm/d/yy h:mm';
851 if ($num_format !== 0 and in_array($num_format, $numbers)) {
852 $this->format
['numberformat']['code'] = $num_format;
855 if (!isset($numbers[$num_format])) {
859 $this->format
['numberformat']['code'] = $numbers[$num_format];