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 formula somewhere in the worksheet
 189      *
 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      */
 195     function write_formula($row, $col, $formula, $format=null) {
 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
 202     /**
 203      * Write one blanck somewhere in the worksheet
 204      *
 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      */
 209     function write_blank($row, $col, $format=null) {
 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
 216     /**
 217      * Write anything somewhere in the worksheet
 218      * Type will be automatically detected
 219      *
 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      */
 225     function write($row, $col, $token, $format=null) {
 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
 255     /**
 256      * Sets the height (and other settings) of one row
 257      *
 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      */
 264     function set_row ($row, $height, $format = null, $hidden = false, $level = 0) {
 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
 271     /**
 272      * Sets the width (and other settings) of one column
 273      *
 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      */
 281     function set_column ($firstcol, $lastcol, $width, $format = null, $hidden = false, $level = 0) {
 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
 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     }
 338
 339     /**
 340      * Returns the PEAR Excel Format for one Moodle Excel Format
 341      *
 342      * @param mixed MoodleExcelFormat object
 343      * @return mixed PEAR Excel Format object
 344      */
 345     function MoodleExcelFormat2PearExcelFormat($format) {
 346         if ($format) {
 347             return $format->pear_excel_format;
 348         } else {
 349             return null;
 350         }
 351     }
 352 }
 353
 354
 355 /**
 356 * Define and operate over one Format.
 357 *
 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 */
 362 class MoodleExcelFormat {
 363
 364     var $pear_excel_format;
 365
 366     /**
 367      * Constructs one Moodle Format.
 368      *
 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
 383     /**
 384      * Set the size of the text in the format (in pixels).
 385      * By default all texts in generated sheets are 10px.
 386      *
 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
 394     /**
 395      * Set weight of the format
 396      *
 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
 406     /**
 407      * Set underline of the format
 408      *
 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
 417     /**
 418      * Set italic of the format
 419      */
 420     function set_italic() {
 421     /// Set the italic safely to the PEAR Format
 422         $this->pear_excel_format->setItalic();
 423     }
 424
 425     /**
 426      * Set strikeout of the format
 427      */
 428     function set_strikeout() {
 429     /// Set the strikeout safely to the PEAR Format
 430         $this->pear_excel_format->setStrikeOut();
 431     }
 432
 433     /**
 434      * Set outlining of the format
 435      */
 436     function set_outline() {
 437     /// Set the outlining safely to the PEAR Format
 438         $this->pear_excel_format->setOutLine();
 439     }
 440
 441     /**
 442      * Set shadow of the format
 443      */
 444     function set_shadow() {
 445     /// Set the shadow safely to the PEAR Format
 446         $this->pear_excel_format->setShadow();
 447     }
 448
 449     /**
 450      * Set the script of the text
 451      *
 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
 460     /**
 461      * Set color of the format. Used to specify the color of the text to be formatted.
 462      *
 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
 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      *
 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
 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      *
 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
 497     /**
 498      * Set the fill pattern of the format
 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
 507     /**
 508      * Set text wrap of the format
 509      */
 510     function set_text_wrap() {
 511     /// Set the shadow safely to the PEAR Format
 512         $this->pear_excel_format->setTextWrap();
 513     }
 514
 515     /**
 516      * Set the cell alignment of the format
 517      *
 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
 525     /**
 526      * Set the cell horizontal alignment of the format
 527      *
 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
 535     /**
 536      * Set the cell vertical alignment of the format
 537      *
 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
 545     /**
 546      * Set the top border of the format
 547      *
 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
 555     /**
 556      * Set the bottom border of the format
 557      *
 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
 565     /**
 566      * Set the left border of the format
 567      *
 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
 575     /**
 576      * Set the right border of the format
 577      *
 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
 587      *
 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
 595     /**
 596      * Set the numerical format of the format
 597      * It can be date, time, currency, etc...
 598      *
 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 ?>