[Summary view] [Print] [Text view]

   1  <?php  // $Id: excellib.class.php,v 1.12.2.4 2008/04/14 22:40:55 stronk7 Exp $
   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  ?>