assignment MDL-19294 Corrected PhpDoc
[moodle.git] / lib / excellib.class.php
CommitLineData
6aaa17c7 1<?php // $Id$
2
3///////////////////////////////////////////////////////////////////////////
4// //
5// NOTICE OF COPYRIGHT //
6// //
7// Moodle - Modular Object-Oriented Dynamic Learning Environment //
8// http://moodle.com //
9// //
6d5b31e1 10// Copyright (C) 1999 onwards Martin Dougiamas http://dougiamas.com //
6aaa17c7 11// (C) 2001-3001 Eloy Lafuente (stronk7) http://contiento.com //
12// //
13// This program is free software; you can redistribute it and/or modify //
14// it under the terms of the GNU General Public License as published by //
15// the Free Software Foundation; either version 2 of the License, or //
16// (at your option) any later version. //
17// //
18// This program is distributed in the hope that it will be useful, //
19// but WITHOUT ANY WARRANTY; without even the implied warranty of //
20// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the //
21// GNU General Public License for more details: //
22// //
23// http://www.gnu.org/copyleft/gpl.html //
24// //
25///////////////////////////////////////////////////////////////////////////
26
7fc1a27d 27//setup.php icludes our hacked pear libs first
6aaa17c7 28require_once 'Spreadsheet/Excel/Writer.php';
29
30/**
31* Define and operate over one Moodle Workbook.
6d5b31e1 32*
6aaa17c7 33* A big part of this class acts as a wrapper over the PEAR
34* Spreadsheet_Excel_Writer_Workbook and OLE libraries
35* maintaining Moodle functions isolated from underlying code.
36*/
37class MoodleExcelWorkbook {
38
39 var $pear_excel_workbook;
63620581 40 var $latin_output;
6aaa17c7 41
6d5b31e1 42 /**
43 * Constructs one Moodle Workbook.
44 *
6aaa17c7 45 * @param string $filename The name of the file
46 */
47 function MoodleExcelWorkbook($filename) {
ce5ce136 48 global $CFG;
6aaa17c7 49 /// Internally, create one PEAR Spreadsheet_Excel_Writer_Workbook class
50 $this->pear_excel_workbook = new Spreadsheet_Excel_Writer($filename);
51 /// Prepare it to accept UTF-16LE data and to encode it properly
767e6e45 52 if (empty($CFG->latinexcelexport)) { /// Only if don't want to use latin (win1252) stronger output
2f078622 53 $this->pear_excel_workbook->setVersion(8);
63620581 54 $this->latin_output = false;
55 } else { /// We want latin (win1252) output
56 $this->latin_output = true;
2f078622 57 }
ffbf004f 58 /// Choose our temporary directory - see MDL-7176, found by paulo.matos
ce5ce136 59 make_upload_directory('temp/excel', false);
60 $this->pear_excel_workbook->setTempDir($CFG->dataroot.'/temp/excel');
6aaa17c7 61 }
62
6d5b31e1 63 /**
64 * Create one Moodle Worksheet
65 *
6aaa17c7 66 * @param string $name Name of the sheet
67 */
68 function &add_worksheet($name = '') {
69 /// Create the Moodle Worksheet. Returns one pointer to it
63620581 70 $ws =& new MoodleExcelWorksheet ($name, $this->pear_excel_workbook, $this->latin_output);
6aaa17c7 71 return $ws;
72 }
73
6d5b31e1 74 /**
75 * Create one Moodle Format
76 *
6aaa17c7 77 * @param array $properties array of properties [name]=value;
78 * valid names are set_XXXX existing
79 * functions without the set_ part
80 * i.e: [bold]=1 for set_bold(1)...Optional!
81 */
82 function &add_format($properties = array()) {
83 /// Create the Moodle Format. Returns one pointer to it
84 $ft =& new MoodleExcelFormat ($this->pear_excel_workbook, $properties);
85 return $ft;
86 }
87
6d5b31e1 88 /**
89 * Close the Moodle Workbook
6aaa17c7 90 */
91 function close() {
92 $this->pear_excel_workbook->close();
93 }
94
6d5b31e1 95 /**
96 * Write the correct HTTP headers
97 *
6aaa17c7 98 * @param string $name Name of the downloaded file
99 */
100 function send($filename) {
101 $this->pear_excel_workbook->send($filename);
102 }
103}
104
105/**
106* Define and operate over one Worksheet.
6d5b31e1 107*
6aaa17c7 108* A big part of this class acts as a wrapper over the PEAR
109* Spreadsheet_Excel_Writer_Workbook and OLE libraries
110* maintaining Moodle functions isolated from underlying code.
111*/
112class MoodleExcelWorksheet {
113
114 var $pear_excel_worksheet;
63620581 115 var $latin_output;
6aaa17c7 116
6d5b31e1 117 /**
118 * Constructs one Moodle Worksheet.
119 *
6aaa17c7 120 * @param string $filename The name of the file
121 * @param object $workbook The internal PEAR Workbook onject we are creating
122 */
63620581 123 function MoodleExcelWorksheet($name, &$workbook, $latin_output=false) {
2f078622 124
6aaa17c7 125 /// Internally, add one sheet to the workbook
126 $this->pear_excel_worksheet =& $workbook->addWorksheet($name);
63620581 127 $this->latin_output = $latin_output;
6aaa17c7 128 /// Set encoding to UTF-16LE
63620581 129 if (!$this->latin_output) { /// Only if don't want to use latin (win1252) stronger output
2f078622 130 $this->pear_excel_worksheet->setInputEncoding('UTF-16LE');
131 }
6aaa17c7 132 }
133
6d5b31e1 134 /**
135 * Write one string somewhere in the worksheet
136 *
6aaa17c7 137 * @param integer $row Zero indexed row
138 * @param integer $col Zero indexed column
139 * @param string $str The string to write
140 * @param mixed $format The XF format for the cell
141 */
21fed7a3 142 function write_string($row, $col, $str, $format=null) {
6aaa17c7 143 /// Calculate the internal PEAR format
144 $format = $this->MoodleExcelFormat2PearExcelFormat($format);
145 /// Loading the textlib singleton instance. We are going to need it.
146 $textlib = textlib_get_instance();
147 /// Convert the text from its original encoding to UTF-16LE
63620581 148 if (!$this->latin_output) { /// Only if don't want to use latin (win1252) stronger output
2f078622 149 $str = $textlib->convert($str, 'utf-8', 'utf-16le');
63620581 150 } else { /// else, convert to latin (win1252)
151 $str = $textlib->convert($str, 'utf-8', 'windows-1252');
2f078622 152 }
6aaa17c7 153 /// Add the string safely to the PEAR Worksheet
154 $this->pear_excel_worksheet->writeString($row, $col, $str, $format);
155 }
156
6d5b31e1 157 /**
158 * Write one number somewhere in the worksheet
159 *
6aaa17c7 160 * @param integer $row Zero indexed row
161 * @param integer $col Zero indexed column
162 * @param float $num The number to write
163 * @param mixed $format The XF format for the cell
164 */
21fed7a3 165 function write_number($row, $col, $num, $format=null) {
6aaa17c7 166 /// Calculate the internal PEAR format
167 $format = $this->MoodleExcelFormat2PearExcelFormat($format);
168 /// Add the number safely to the PEAR Worksheet
169 $this->pear_excel_worksheet->writeNumber($row, $col, $num, $format);
170 }
171
6d5b31e1 172 /**
173 * Write one url somewhere in the worksheet
174 *
6aaa17c7 175 * @param integer $row Zero indexed row
176 * @param integer $col Zero indexed column
177 * @param string $url The url to write
178 * @param mixed $format The XF format for the cell
179 */
21fed7a3 180 function write_url($row, $col, $url, $format=null) {
6aaa17c7 181 /// Calculate the internal PEAR format
182 $format = $this->MoodleExcelFormat2PearExcelFormat($format);
183 /// Add the url safely to the PEAR Worksheet
184 $this->pear_excel_worksheet->writeUrl($row, $col, $url, $format);
185 }
186
6d5b31e1 187 /**
188 * Write one formula somewhere in the worksheet
189 *
6aaa17c7 190 * @param integer $row Zero indexed row
191 * @param integer $col Zero indexed column
192 * @param string $formula The formula to write
193 * @param mixed $format The XF format for the cell
194 */
21fed7a3 195 function write_formula($row, $col, $formula, $format=null) {
6aaa17c7 196 /// Calculate the internal PEAR format
197 $format = $this->MoodleExcelFormat2PearExcelFormat($format);
198 /// Add the formula safely to the PEAR Worksheet
199 $this->pear_excel_worksheet->writeFormula($row, $col, $formula, $format);
200 }
201
6d5b31e1 202 /**
203 * Write one blanck somewhere in the worksheet
204 *
6aaa17c7 205 * @param integer $row Zero indexed row
206 * @param integer $col Zero indexed column
207 * @param mixed $format The XF format for the cell
208 */
21fed7a3 209 function write_blank($row, $col, $format=null) {
6aaa17c7 210 /// Calculate the internal PEAR format
211 $format = $this->MoodleExcelFormat2PearExcelFormat($format);
212 /// Add the blank safely to the PEAR Worksheet
213 $this->pear_excel_worksheet->writeBlank($row, $col, $format);
214 }
215
6d5b31e1 216 /**
217 * Write anything somewhere in the worksheet
6aaa17c7 218 * Type will be automatically detected
6d5b31e1 219 *
6aaa17c7 220 * @param integer $row Zero indexed row
221 * @param integer $col Zero indexed column
222 * @param mixed $token What we are writing
223 * @param mixed $format The XF format for the cell
224 */
21fed7a3 225 function write($row, $col, $token, $format=null) {
6aaa17c7 226
227 /// Analyse what are we trying to send
228 if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
229 /// Match number
230 return $this->write_number($row, $col, $token, $format);
231 } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
232 /// Match http or ftp URL
233 return $this->write_url($row, $col, $token, '', $format);
234 } elseif (preg_match("/^mailto:/", $token)) {
235 /// Match mailto:
236 return $this->write_url($row, $col, $token, '', $format);
237 } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
238 /// Match internal or external sheet link
239 return $this->write_url($row, $col, $token, '', $format);
240 } elseif (preg_match("/^=/", $token)) {
241 /// Match formula
242 return $this->write_formula($row, $col, $token, $format);
243 } elseif (preg_match("/^@/", $token)) {
244 /// Match formula
245 return $this->write_formula($row, $col, $token, $format);
246 } elseif ($token == '') {
247 /// Match blank
248 return $this->write_blank($row, $col, $format);
249 } else {
250 /// Default: match string
251 return $this->write_string($row, $col, $token, $format);
252 }
253 }
254
6d5b31e1 255 /**
256 * Sets the height (and other settings) of one row
257 *
e6af2cad 258 * @param integer $row The row to set
259 * @param integer $height Height we are giving to the row (null to set just format withouth setting the height)
260 * @param mixed $format The optional XF format we are giving to the row
261 * @param bool $hidden The optional hidden attribute
262 * @param integer $level The optional outline level (0-7)
263 */
21fed7a3 264 function set_row ($row, $height, $format = null, $hidden = false, $level = 0) {
e6af2cad 265 /// Calculate the internal PEAR format
266 $format = $this->MoodleExcelFormat2PearExcelFormat($format);
267 /// Set the row safely to the PEAR Worksheet
268 $this->pear_excel_worksheet->setRow($row, $height, $format, $hidden, $level);
269 }
270
6d5b31e1 271 /**
272 * Sets the width (and other settings) of one column
273 *
e6af2cad 274 * @param integer $firstcol first column on the range
275 * @param integer $lastcol last column on the range
276 * @param integer $width width to set
277 * @param mixed $format The optional XF format to apply to the columns
278 * @param integer $hidden The optional hidden atribute
279 * @param integer $level The optional outline level (0-7)
280 */
21fed7a3 281 function set_column ($firstcol, $lastcol, $width, $format = null, $hidden = false, $level = 0) {
e6af2cad 282 /// Calculate the internal PEAR format
283 $format = $this->MoodleExcelFormat2PearExcelFormat($format);
284 /// Set the column safely to the PEAR Worksheet
285 $this->pear_excel_worksheet->setColumn($firstcol, $lastcol, $width, $format, $hidden, $level);
286 }
287
dd41247e 288 /**
289 * Set the option to hide gridlines on the printed page.
290 *
291 * @access public
292 */
293 function hide_gridlines() {
294 $this->pear_excel_worksheet->hideGridLines();
295 }
296
297 /**
298 * Set the option to hide gridlines on the worksheet (as seen on the screen).
299 *
300 * @access public
301 */
302 function hide_screen_gridlines() {
303 $this->pear_excel_worksheet->hideScreenGridlines();
304 }
305
306 /**
307 * Insert a 24bit bitmap image in a worksheet.
308 *
309 * @access public
310 * @param integer $row The row we are going to insert the bitmap into
311 * @param integer $col The column we are going to insert the bitmap into
312 * @param string $bitmap The bitmap filename
313 * @param integer $x The horizontal position (offset) of the image inside the cell.
314 * @param integer $y The vertical position (offset) of the image inside the cell.
315 * @param integer $scale_x The horizontal scale
316 * @param integer $scale_y The vertical scale
317 */
318 function insert_bitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1) {
319 /// Add the bitmap safely to the PEAR Worksheet
320 $this->pear_excel_worksheet->insertBitmap($row, $col, $bitmap, $x, $y, $scale_x, $scale_y);
321 }
322
323 /**
324 * Merges the area given by its arguments.
325 * This is an Excel97/2000 method. It is required to perform more complicated
326 * merging than the normal setAlign('merge').
327 *
328 * @access public
329 * @param integer $first_row First row of the area to merge
330 * @param integer $first_col First column of the area to merge
331 * @param integer $last_row Last row of the area to merge
332 * @param integer $last_col Last column of the area to merge
333 */
334 function merge_cells($first_row, $first_col, $last_row, $last_col) {
335 /// Merge cells safely to the PEAR Worksheet
336 $this->pear_excel_worksheet->mergeCells($first_row, $first_col, $last_row, $last_col);
337 }
6d5b31e1 338
339 /**
340 * Returns the PEAR Excel Format for one Moodle Excel Format
341 *
6aaa17c7 342 * @param mixed MoodleExcelFormat object
343 * @return mixed PEAR Excel Format object
344 */
345 function MoodleExcelFormat2PearExcelFormat($format) {
21fed7a3 346 if ($format) {
6aaa17c7 347 return $format->pear_excel_format;
348 } else {
21fed7a3 349 return null;
6aaa17c7 350 }
351 }
352}
353
354
355/**
356* Define and operate over one Format.
6d5b31e1 357*
6aaa17c7 358* A big part of this class acts as a wrapper over the PEAR
359* Spreadsheet_Excel_Writer_Workbook and OLE libraries
360* maintaining Moodle functions isolated from underlying code.
361*/
362class MoodleExcelFormat {
363
364 var $pear_excel_format;
365
6d5b31e1 366 /**
367 * Constructs one Moodle Format.
368 *
6aaa17c7 369 * @param object $workbook The internal PEAR Workbook onject we are creating
370 */
371 function MoodleExcelFormat(&$workbook, $properties = array()) {
372 /// Internally, add one sheet to the workbook
373 $this->pear_excel_format =& $workbook->addFormat();
374 /// If we have something in the array of properties, compute them
375 foreach($properties as $property => $value) {
376 if(method_exists($this,"set_$property")) {
377 $aux = 'set_'.$property;
378 $this->$aux($value);
379 }
380 }
381 }
382
21c36940 383 /**
384 * Set the size of the text in the format (in pixels).
385 * By default all texts in generated sheets are 10px.
6d5b31e1 386 *
21c36940 387 * @param integer $size Size of the text (in pixels)
388 */
389 function set_size($size) {
390 /// Set the size safely to the PEAR Format
391 $this->pear_excel_format->setSize($size);
392 }
393
6d5b31e1 394 /**
395 * Set weight of the format
396 *
6aaa17c7 397 * @param integer $weight Weight for the text, 0 maps to 400 (normal text),
398 * 1 maps to 700 (bold text). Valid range is: 100-1000.
399 * It's Optional, default is 1 (bold).
400 */
401 function set_bold($weight = 1) {
402 /// Set the bold safely to the PEAR Format
403 $this->pear_excel_format->setBold($weight);
404 }
405
6d5b31e1 406 /**
407 * Set underline of the format
408 *
6aaa17c7 409 * @param integer $underline The value for underline. Possible values are:
410 * 1 => underline, 2 => double underline
411 */
412 function set_underline($underline) {
413 /// Set the underline safely to the PEAR Format
414 $this->pear_excel_format->setUnderline($underline);
415 }
416
6d5b31e1 417 /**
418 * Set italic of the format
6aaa17c7 419 */
420 function set_italic() {
421 /// Set the italic safely to the PEAR Format
422 $this->pear_excel_format->setItalic();
423 }
424
6d5b31e1 425 /**
426 * Set strikeout of the format
6aaa17c7 427 */
428 function set_strikeout() {
429 /// Set the strikeout safely to the PEAR Format
430 $this->pear_excel_format->setStrikeOut();
431 }
432
6d5b31e1 433 /**
434 * Set outlining of the format
6aaa17c7 435 */
436 function set_outline() {
437 /// Set the outlining safely to the PEAR Format
438 $this->pear_excel_format->setOutLine();
439 }
440
6d5b31e1 441 /**
442 * Set shadow of the format
6aaa17c7 443 */
444 function set_shadow() {
445 /// Set the shadow safely to the PEAR Format
446 $this->pear_excel_format->setShadow();
447 }
448
6d5b31e1 449 /**
450 * Set the script of the text
451 *
6aaa17c7 452 * @param integer $script The value for script type. Possible values are:
453 * 1 => superscript, 2 => subscript
454 */
455 function set_script($script) {
456 /// Set the script safely to the PEAR Format
457 $this->pear_excel_format->setScript($script);
458 }
459
6d5b31e1 460 /**
461 * Set color of the format. Used to specify the color of the text to be formatted.
462 *
6aaa17c7 463 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
464 */
465 function set_color($color) {
466 /// Set the background color safely to the PEAR Format
467 $this->pear_excel_format->setColor($color);
468 }
469
6d5b31e1 470 /**
471 * Set foreground color (top layer) of the format. About formatting colors note that cells backgrounds
472 * have TWO layers, in order to support patterns and paint them with two diferent colors.
473 * This method set the color of the TOP layer of the background format. So, when filling
474 * cells with plain colors (no patterns) this is the method to use.
475 *
6aaa17c7 476 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
477 */
478 function set_fg_color($color) {
479 /// Set the foreground color safely to the PEAR Format
480 $this->pear_excel_format->setFgColor($color);
481 }
482
6d5b31e1 483 /**
484 * Set background color (bottom layer) of the format. About formatting colors note that cells backgrounds
485 * have TWO layers, in order to support patterns and paint them with two diferent colors.
486 * This method set the color of the BOTTOM layer of the background format. So, the color
487 * specified here only will be visible if using patterns. Use set_fg_color() to fill
488 * cells with plain colors (no patterns).
489 *
6aaa17c7 490 * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
491 */
492 function set_bg_color($color) {
493 /// Set the background color safely to the PEAR Format
494 $this->pear_excel_format->setBgColor($color);
495 }
496
6d5b31e1 497 /**
498 * Set the fill pattern of the format
6aaa17c7 499 * @param integer Optional. Defaults to 1. Meaningful values are: 0-18
500 * 0 meaning no background.
501 */
502 function set_pattern($pattern=1) {
503 /// Set the fill pattern safely to the PEAR Format
504 $this->pear_excel_format->setPattern($pattern);
505 }
506
6d5b31e1 507 /**
508 * Set text wrap of the format
6aaa17c7 509 */
510 function set_text_wrap() {
511 /// Set the shadow safely to the PEAR Format
512 $this->pear_excel_format->setTextWrap();
513 }
514
6d5b31e1 515 /**
516 * Set the cell alignment of the format
517 *
6aaa17c7 518 * @param string $location alignment for the cell ('left', 'right', etc...)
519 */
520 function set_align($location) {
521 /// Set the alignment of the cell safely to the PEAR Format
522 $this->pear_excel_format->setAlign($location);
523 }
524
6d5b31e1 525 /**
526 * Set the cell horizontal alignment of the format
527 *
6aaa17c7 528 * @param string $location alignment for the cell ('left', 'right', etc...)
529 */
530 function set_h_align($location) {
531 /// Set the alignment of the cell safely to the PEAR Format
532 $this->pear_excel_format->setHAlign($location);
533 }
534
6d5b31e1 535 /**
536 * Set the cell vertical alignment of the format
537 *
6aaa17c7 538 * @param string $location alignment for the cell ('top', 'vleft', etc...)
539 */
540 function set_v_align($location) {
541 /// Set the alignment of the cell safely to the PEAR Format
542 $this->pear_excel_format->setVAlign($location);
543 }
544
6d5b31e1 545 /**
546 * Set the top border of the format
547 *
6aaa17c7 548 * @param integer $style style for the cell. 1 => thin, 2 => thick
549 */
550 function set_top($style) {
551 /// Set the top border of the cell safely to the PEAR Format
552 $this->pear_excel_format->setTop($style);
553 }
554
6d5b31e1 555 /**
556 * Set the bottom border of the format
557 *
6aaa17c7 558 * @param integer $style style for the cell. 1 => thin, 2 => thick
559 */
560 function set_bottom($style) {
561 /// Set the bottom border of the cell safely to the PEAR Format
562 $this->pear_excel_format->setBottom($style);
563 }
564
6d5b31e1 565 /**
566 * Set the left border of the format
567 *
6aaa17c7 568 * @param integer $style style for the cell. 1 => thin, 2 => thick
569 */
570 function set_left($style) {
571 /// Set the left border of the cell safely to the PEAR Format
572 $this->pear_excel_format->setLeft($style);
573 }
574
6d5b31e1 575 /**
576 * Set the right border of the format
577 *
6aaa17c7 578 * @param integer $style style for the cell. 1 => thin, 2 => thick
579 */
580 function set_right($style) {
581 /// Set the right border of the cell safely to the PEAR Format
582 $this->pear_excel_format->setRight($style);
583 }
584
585 /**
586 * Set cells borders to the same style
6d5b31e1 587 *
6aaa17c7 588 * @param integer $style style to apply for all cell borders. 1 => thin, 2 => thick.
589 */
590 function set_border($style) {
591 /// Set all the borders of the cell safely to the PEAR Format
592 $this->pear_excel_format->setBorder($style);
593 }
594
6d5b31e1 595 /**
596 * Set the numerical format of the format
6aaa17c7 597 * It can be date, time, currency, etc...
6d5b31e1 598 *
6aaa17c7 599 * @param integer $num_format The numeric format
600 */
601 function set_num_format($num_format) {
602 /// Set the numerical format safely to the PEAR Format
603 $this->pear_excel_format->setNumFormat($num_format);
604 }
605
606}
607
608?>