lib/excellib.class.php

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