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