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) 2001-3001 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 /// We need to add this to allow "our" PEAR package to work smoothly
  28 /// without modifying one bit, putting it in the 1st place of the
  29 /// include_path to be localised by Moodle without problems
  30 ini_set('include_path', $CFG->libdir.'/pear' . PATH_SEPARATOR . ini_get('include_path'));
  31
  32 require_once 'Spreadsheet/Excel/Writer.php';
  33
  34 /**
  35 * Define and operate over one Moodle Workbook.
  36 *
  37 * A big part of this class acts as a wrapper over the PEAR
  38 * Spreadsheet_Excel_Writer_Workbook and OLE libraries
  39 * maintaining Moodle functions isolated from underlying code.
  40 */
  41 class MoodleExcelWorkbook {
  42
  43     var $pear_excel_workbook;
  44     var $latin_output;
  45
  46     /* Constructs one Moodle Workbook.
  47      * @param string $filename The name of the file
  48      */
  49     function MoodleExcelWorkbook($filename) {
  50         global $CFG;
  51     /// Internally, create one PEAR Spreadsheet_Excel_Writer_Workbook class
  52         $this->pear_excel_workbook = new Spreadsheet_Excel_Writer($filename);
  53     /// Prepare it to accept UTF-16LE data and to encode it properly
  54         if (empty($CFG->latinexcelexport)) { /// Only if don't want to use latin (win1252) stronger output
  55             $this->pear_excel_workbook->setVersion(8);
  56             $this->latin_output = false;
  57         } else { /// We want latin (win1252) output
  58             $this->latin_output = true;
  59         }
  60     /// Choose our temporary directory - see MDL-7176, found by paulo.matos
  61         make_upload_directory('temp/excel', false);
  62         $this->pear_excel_workbook->setTempDir($CFG->dataroot.'/temp/excel');
  63     }
  64
  65     /* Create one Moodle Worksheet
  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     /* Create one Moodle Format
  75      * @param array $properties array of properties [name]=value;
  76      *                          valid names are set_XXXX existing
  77      *                          functions without the set_ part
  78      *                          i.e: [bold]=1 for set_bold(1)...Optional!
  79      */
  80     function &add_format($properties = array()) {
  81     /// Create the Moodle Format. Returns one pointer to it
  82         $ft =& new MoodleExcelFormat ($this->pear_excel_workbook, $properties);
  83         return $ft;
  84     }
  85
  86     /* Close the Moodle Workbook
  87      */
  88     function close() {
  89         $this->pear_excel_workbook->close();
  90     }
  91
  92     /* Write the correct HTTP headers
  93      * @param string $name Name of the downloaded file
  94      */
  95     function send($filename) {
  96         $this->pear_excel_workbook->send($filename);
  97     }
  98 }
  99
 100 /**
 101 * Define and operate over one Worksheet.
 102 *
 103 * A big part of this class acts as a wrapper over the PEAR
 104 * Spreadsheet_Excel_Writer_Workbook and OLE libraries
 105 * maintaining Moodle functions isolated from underlying code.
 106 */
 107 class MoodleExcelWorksheet {
 108
 109     var $pear_excel_worksheet;
 110     var $latin_output;
 111
 112     /* Constructs one Moodle Worksheet.
 113      * @param string $filename The name of the file
 114      * @param object $workbook The internal PEAR Workbook onject we are creating
 115      */
 116     function MoodleExcelWorksheet($name, &$workbook, $latin_output=false) {
 117
 118     /// Internally, add one sheet to the workbook
 119         $this->pear_excel_worksheet =& $workbook->addWorksheet($name);
 120         $this->latin_output = $latin_output;
 121     /// Set encoding to UTF-16LE
 122         if (!$this->latin_output) { /// Only if don't want to use latin (win1252) stronger output
 123             $this->pear_excel_worksheet->setInputEncoding('UTF-16LE');
 124         }
 125     }
 126
 127     /* Write one string somewhere in the worksheet
 128      * @param integer $row    Zero indexed row
 129      * @param integer $col    Zero indexed column
 130      * @param string  $str    The string to write
 131      * @param mixed   $format The XF format for the cell
 132      */
 133     function write_string($row, $col, $str, $format=null) {
 134     /// Calculate the internal PEAR format
 135         $format = $this->MoodleExcelFormat2PearExcelFormat($format);
 136     /// Loading the textlib singleton instance. We are going to need it.
 137         $textlib = textlib_get_instance();
 138     /// Convert the text from its original encoding to UTF-16LE
 139         if (!$this->latin_output) { /// Only if don't want to use latin (win1252) stronger output
 140             $str = $textlib->convert($str, 'utf-8', 'utf-16le');
 141         } else { /// else, convert to latin (win1252)
 142             $str = $textlib->convert($str, 'utf-8', 'windows-1252');
 143         }
 144     /// Add the string safely to the PEAR Worksheet
 145         $this->pear_excel_worksheet->writeString($row, $col, $str, $format);
 146     }
 147
 148     /* Write one number somewhere in the worksheet
 149      * @param integer $row    Zero indexed row
 150      * @param integer $col    Zero indexed column
 151      * @param float   $num    The number to write
 152      * @param mixed   $format The XF format for the cell
 153      */
 154     function write_number($row, $col, $num, $format=null) {
 155     /// Calculate the internal PEAR format
 156         $format = $this->MoodleExcelFormat2PearExcelFormat($format);
 157     /// Add  the number safely to the PEAR Worksheet
 158         $this->pear_excel_worksheet->writeNumber($row, $col, $num, $format);
 159     }
 160
 161     /* Write one url somewhere in the worksheet
 162      * @param integer $row    Zero indexed row
 163      * @param integer $col    Zero indexed column
 164      * @param string  $url    The url to write
 165      * @param mixed   $format The XF format for the cell
 166      */
 167     function write_url($row, $col, $url, $format=null) {
 168     /// Calculate the internal PEAR format
 169         $format = $this->MoodleExcelFormat2PearExcelFormat($format);
 170     /// Add  the url safely to the PEAR Worksheet
 171         $this->pear_excel_worksheet->writeUrl($row, $col, $url, $format);
 172     }
 173
 174     /* Write one formula somewhere in the worksheet
 175      * @param integer $row    Zero indexed row
 176      * @param integer $col    Zero indexed column
 177      * @param string  $formula The formula to write
 178      * @param mixed   $format The XF format for the cell
 179      */
 180     function write_formula($row, $col, $formula, $format=null) {
 181     /// Calculate the internal PEAR format
 182         $format = $this->MoodleExcelFormat2PearExcelFormat($format);
 183     /// Add  the formula safely to the PEAR Worksheet
 184         $this->pear_excel_worksheet->writeFormula($row, $col, $formula, $format);
 185     }
 186
 187     /* Write one blanck somewhere in the worksheet
 188      * @param integer $row    Zero indexed row
 189      * @param integer $col    Zero indexed column
 190      * @param mixed   $format The XF format for the cell
 191      */
 192     function write_blank($row, $col, $format=null) {
 193     /// Calculate the internal PEAR format
 194         $format = $this->MoodleExcelFormat2PearExcelFormat($format);
 195     /// Add  the blank safely to the PEAR Worksheet
 196         $this->pear_excel_worksheet->writeBlank($row, $col, $format);
 197     }
 198
 199     /* Write anything somewhere in the worksheet
 200      * Type will be automatically detected
 201      * @param integer $row    Zero indexed row
 202      * @param integer $col    Zero indexed column
 203      * @param mixed   $token  What we are writing
 204      * @param mixed   $format The XF format for the cell
 205      */
 206     function write($row, $col, $token, $format=null) {
 207
 208     /// Analyse what are we trying to send
 209         if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
 210         /// Match number
 211             return $this->write_number($row, $col, $token, $format);
 212         } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
 213         /// Match http or ftp URL
 214             return $this->write_url($row, $col, $token, '', $format);
 215         } elseif (preg_match("/^mailto:/", $token)) {
 216         /// Match mailto:
 217             return $this->write_url($row, $col, $token, '', $format);
 218         } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
 219         /// Match internal or external sheet link
 220             return $this->write_url($row, $col, $token, '', $format);
 221         } elseif (preg_match("/^=/", $token)) {
 222         /// Match formula
 223             return $this->write_formula($row, $col, $token, $format);
 224         } elseif (preg_match("/^@/", $token)) {
 225         /// Match formula
 226             return $this->write_formula($row, $col, $token, $format);
 227         } elseif ($token == '') {
 228         /// Match blank
 229             return $this->write_blank($row, $col, $format);
 230         } else {
 231         /// Default: match string
 232             return $this->write_string($row, $col, $token, $format);
 233         }
 234     }
 235
 236     /* Sets the height (and other settings) of one row
 237      * @param integer $row    The row to set
 238      * @param integer $height Height we are giving to the row (null to set just format withouth setting the height)
 239      * @param mixed   $format The optional XF format we are giving to the row
 240      * @param bool    $hidden The optional hidden attribute
 241      * @param integer $level  The optional outline level (0-7)
 242      */
 243     function set_row ($row, $height, $format = null, $hidden = false, $level = 0) {
 244     /// Calculate the internal PEAR format
 245         $format = $this->MoodleExcelFormat2PearExcelFormat($format);
 246     /// Set the row safely to the PEAR Worksheet
 247         $this->pear_excel_worksheet->setRow($row, $height, $format, $hidden, $level);
 248     }
 249
 250     /* Sets the width (and other settings) of one column
 251      * @param integer $firstcol first column on the range
 252      * @param integer $lastcol  last column on the range
 253      * @param integer $width    width to set
 254      * @param mixed   $format   The optional XF format to apply to the columns
 255      * @param integer $hidden   The optional hidden atribute
 256      * @param integer $level    The optional outline level (0-7)
 257      */
 258     function set_column ($firstcol, $lastcol, $width, $format = null, $hidden = false, $level = 0) {
 259     /// Calculate the internal PEAR format
 260         $format = $this->MoodleExcelFormat2PearExcelFormat($format);
 261     /// Set the column safely to the PEAR Worksheet
 262         $this->pear_excel_worksheet->setColumn($firstcol, $lastcol, $width, $format, $hidden, $level);
 263     }
 264
 265     /* Returns the PEAR Excel Format for one Moodle Excel Format
 266      * @param mixed MoodleExcelFormat object
 267      * @return mixed PEAR Excel Format object
 268      */
 269     function MoodleExcelFormat2PearExcelFormat($format) {
 270         if ($format) {
 271             return $format->pear_excel_format;
 272         } else {
 273             return null;
 274         }
 275     }
 276 }
 277
 278
 279 /**
 280 * Define and operate over one Format.
 281 *
 282 * A big part of this class acts as a wrapper over the PEAR
 283 * Spreadsheet_Excel_Writer_Workbook and OLE libraries
 284 * maintaining Moodle functions isolated from underlying code.
 285 */
 286 class MoodleExcelFormat {
 287
 288     var $pear_excel_format;
 289
 290     /* Constructs one Moodle Format.
 291      * @param object $workbook The internal PEAR Workbook onject we are creating
 292      */
 293     function MoodleExcelFormat(&$workbook, $properties = array()) {
 294     /// Internally, add one sheet to the workbook
 295         $this->pear_excel_format =& $workbook->addFormat();
 296     /// If we have something in the array of properties, compute them
 297         foreach($properties as $property => $value) {
 298             if(method_exists($this,"set_$property")) {
 299                 $aux = 'set_'.$property;
 300                 $this->$aux($value);
 301             }
 302         }
 303     }
 304
 305     /* Set weight of the format
 306      * @param integer $weight Weight for the text, 0 maps to 400 (normal text),
 307      *                        1 maps to 700 (bold text). Valid range is: 100-1000.
 308      *                        It's Optional, default is 1 (bold).
 309      */
 310     function set_bold($weight = 1) {
 311     /// Set the bold safely to the PEAR Format
 312         $this->pear_excel_format->setBold($weight);
 313     }
 314
 315     /* Set underline of the format
 316      * @param integer $underline The value for underline. Possible values are:
 317      *                           1 => underline, 2 => double underline
 318      */
 319     function set_underline($underline) {
 320     /// Set the underline safely to the PEAR Format
 321         $this->pear_excel_format->setUnderline($underline);
 322     }
 323
 324     /* Set italic of the format
 325      */
 326     function set_italic() {
 327     /// Set the italic safely to the PEAR Format
 328         $this->pear_excel_format->setItalic();
 329     }
 330
 331     /* Set strikeout of the format
 332      */
 333     function set_strikeout() {
 334     /// Set the strikeout safely to the PEAR Format
 335         $this->pear_excel_format->setStrikeOut();
 336     }
 337
 338     /* Set outlining of the format
 339      */
 340     function set_outline() {
 341     /// Set the outlining safely to the PEAR Format
 342         $this->pear_excel_format->setOutLine();
 343     }
 344
 345     /* Set shadow of the format
 346      */
 347     function set_shadow() {
 348     /// Set the shadow safely to the PEAR Format
 349         $this->pear_excel_format->setShadow();
 350     }
 351
 352     /* Set the script of the text
 353      * @param integer $script The value for script type. Possible values are:
 354      *                        1 => superscript, 2 => subscript
 355      */
 356     function set_script($script) {
 357     /// Set the script safely to the PEAR Format
 358         $this->pear_excel_format->setScript($script);
 359     }
 360
 361     /* Set color of the format
 362      * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
 363      */
 364     function set_color($color) {
 365     /// Set the background color safely to the PEAR Format
 366         $this->pear_excel_format->setColor($color);
 367     }
 368
 369     /* Set foreground color of the format
 370      * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
 371      */
 372     function set_fg_color($color) {
 373     /// Set the foreground color safely to the PEAR Format
 374         $this->pear_excel_format->setFgColor($color);
 375     }
 376
 377     /* Set background color of the format
 378      * @param mixed $color either a string (like 'blue'), or an integer (range is [8...63])
 379      */
 380     function set_bg_color($color) {
 381     /// Set the background color safely to the PEAR Format
 382         $this->pear_excel_format->setBgColor($color);
 383     }
 384
 385     /* Set the fill pattern of the format
 386      * @param integer Optional. Defaults to 1. Meaningful values are: 0-18
 387      *                0 meaning no background.
 388      */
 389     function set_pattern($pattern=1) {
 390     /// Set the fill pattern safely to the PEAR Format
 391         $this->pear_excel_format->setPattern($pattern);
 392     }
 393
 394     /* Set text wrap of the format
 395      */
 396     function set_text_wrap() {
 397     /// Set the shadow safely to the PEAR Format
 398         $this->pear_excel_format->setTextWrap();
 399     }
 400
 401     /* Set the cell alignment of the format
 402      * @param string $location alignment for the cell ('left', 'right', etc...)
 403      */
 404     function set_align($location) {
 405     /// Set the alignment of the cell safely to the PEAR Format
 406         $this->pear_excel_format->setAlign($location);
 407     }
 408
 409     /* Set the cell horizontal alignment of the format
 410      * @param string $location alignment for the cell ('left', 'right', etc...)
 411      */
 412     function set_h_align($location) {
 413     /// Set the alignment of the cell safely to the PEAR Format
 414         $this->pear_excel_format->setHAlign($location);
 415     }
 416
 417     /* Set the cell vertical alignment of the format
 418      * @param string $location alignment for the cell ('top', 'vleft', etc...)
 419      */
 420     function set_v_align($location) {
 421     /// Set the alignment of the cell safely to the PEAR Format
 422         $this->pear_excel_format->setVAlign($location);
 423     }
 424
 425     /* Set the top border of the format
 426      * @param integer $style style for the cell. 1 => thin, 2 => thick
 427      */
 428     function set_top($style) {
 429     /// Set the top border of the cell safely to the PEAR Format
 430         $this->pear_excel_format->setTop($style);
 431     }
 432
 433     /* Set the bottom border of the format
 434      * @param integer $style style for the cell. 1 => thin, 2 => thick
 435      */
 436     function set_bottom($style) {
 437     /// Set the bottom border of the cell safely to the PEAR Format
 438         $this->pear_excel_format->setBottom($style);
 439     }
 440
 441     /* Set the left border of the format
 442      * @param integer $style style for the cell. 1 => thin, 2 => thick
 443      */
 444     function set_left($style) {
 445     /// Set the left border of the cell safely to the PEAR Format
 446         $this->pear_excel_format->setLeft($style);
 447     }
 448
 449     /* Set the right border of the format
 450      * @param integer $style style for the cell. 1 => thin, 2 => thick
 451      */
 452     function set_right($style) {
 453     /// Set the right border of the cell safely to the PEAR Format
 454         $this->pear_excel_format->setRight($style);
 455     }
 456
 457     /**
 458      * Set cells borders to the same style
 459      * @param integer $style style to apply for all cell borders. 1 => thin, 2 => thick.
 460      */
 461     function set_border($style) {
 462     /// Set all the borders of the cell safely to the PEAR Format
 463         $this->pear_excel_format->setBorder($style);
 464     }
 465
 466     /* Set the numerical format of the format
 467      * It can be date, time, currency, etc...
 468     /* Set the numerical format of the format
 469      * It can be date, time, currency, etc...
 470      * @param integer $num_format The numeric format
 471      */
 472     function set_num_format($num_format) {
 473     /// Set the numerical format safely to the PEAR Format
 474         $this->pear_excel_format->setNumFormat($num_format);
 475     }
 476
 477 }
 478
 479 ?>