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