C:/xampp/htdocs/moodle/lib/excellib.class.php

Go to the documentation of this file.

<?php

// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Moodle is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.

defined('MOODLE_INTERNAL') || die();

require_once 'Spreadsheet/Excel/Writer.php';

class MoodleExcelWorkbook {
    var $pear_excel_workbook;
    var $latin_output;

    function MoodleExcelWorkbook($filename) {
        global $CFG;
        $this->pear_excel_workbook = new Spreadsheet_Excel_Writer($filename);
        if (empty($CFG->latinexcelexport)) { 
            $this->pear_excel_workbook->setVersion(8);
            $this->latin_output = false;
        } else { 
            $this->latin_output = true;
        }
        make_temp_directory('excel');
        $this->pear_excel_workbook->setTempDir($CFG->tempdir.'/excel');
    }

    function &add_worksheet($name = '') {
        $ws = new MoodleExcelWorksheet ($name, $this->pear_excel_workbook, $this->latin_output);
        return $ws;
    }

    function &add_format($properties = array()) {
        $ft = new MoodleExcelFormat ($this->pear_excel_workbook, $properties);
        return $ft;
    }

    function close() {
        $this->pear_excel_workbook->close();
    }

    function send($filename) {
        $this->pear_excel_workbook->send($filename);
    }
}

class MoodleExcelWorksheet {
    var $pear_excel_worksheet;
    var $latin_output;

    function MoodleExcelWorksheet($name, &$workbook, $latin_output=false) {

        if (strlen($name) > 31) {
            // Excel does not seem able to cope with sheet names > 31 chars.
            // With $latin_output = false, it does not cope at all.
            // With $latin_output = true it is supposed to work, but in our experience,
            // it doesn't. Therefore, truncate in all circumstances.
            $textlib = textlib_get_instance();
            $name = $textlib->substr($name, 0, 31);
        }

        $this->pear_excel_worksheet =& $workbook->addWorksheet($name);
        $this->latin_output = $latin_output;
        if (!$this->latin_output) { 
            $this->pear_excel_worksheet->setInputEncoding('UTF-16LE');
        }
    }

    function write_string($row, $col, $str, $format=null) {
        $format = $this->MoodleExcelFormat2PearExcelFormat($format);
        $textlib = textlib_get_instance();
        if (!$this->latin_output) { 
            $str = $textlib->convert($str, 'utf-8', 'utf-16le');
        } else { 
            $str = $textlib->convert($str, 'utf-8', 'windows-1252');
        }
        $this->pear_excel_worksheet->writeString($row, $col, $str, $format);
    }

    function write_number($row, $col, $num, $format=null) {
        $format = $this->MoodleExcelFormat2PearExcelFormat($format);
        $this->pear_excel_worksheet->writeNumber($row, $col, $num, $format);
    }

    function write_url($row, $col, $url, $format=null) {
        $format = $this->MoodleExcelFormat2PearExcelFormat($format);
        $this->pear_excel_worksheet->writeUrl($row, $col, $url, $format);
    }

    function write_date($row, $col, $date, $format=null) {
        $format = $this->MoodleExcelFormat2PearExcelFormat($format);
        $timezone = get_user_timezone_offset();
        if ($timezone == 99) {
            // system timezone offset in seconds
            $offset = (int)date('Z');
        } else {
            $offset = (int)($timezone * HOURSECS * 2);
        }
        $value = ((usertime($date) + $offset) / 86400) + 25569;
        $this->pear_excel_worksheet->writeNumber($row, $col, $value, $format);
    }

    function write_formula($row, $col, $formula, $format=null) {
        $format = $this->MoodleExcelFormat2PearExcelFormat($format);
        $this->pear_excel_worksheet->writeFormula($row, $col, $formula, $format);
    }

    function write_blank($row, $col, $format=null) {
        $format = $this->MoodleExcelFormat2PearExcelFormat($format);
        $this->pear_excel_worksheet->writeBlank($row, $col, $format);
    }

    function write($row, $col, $token, $format=null) {

        if (preg_match("/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/", $token)) {
            return $this->write_number($row, $col, $token, $format);
        } elseif (preg_match("/^[fh]tt?p:\/\//", $token)) {
            return $this->write_url($row, $col, $token, '', $format);
        } elseif (preg_match("/^mailto:/", $token)) {
            return $this->write_url($row, $col, $token, '', $format);
        } elseif (preg_match("/^(?:in|ex)ternal:/", $token)) {
            return $this->write_url($row, $col, $token, '', $format);
        } elseif (preg_match("/^=/", $token)) {
            return $this->write_formula($row, $col, $token, $format);
        } elseif (preg_match("/^@/", $token)) {
            return $this->write_formula($row, $col, $token, $format);
        } elseif ($token == '') {
            return $this->write_blank($row, $col, $format);
        } else {
            return $this->write_string($row, $col, $token, $format);
        }
    }

    function set_row ($row, $height, $format = null, $hidden = false, $level = 0) {
        $format = $this->MoodleExcelFormat2PearExcelFormat($format);
        $this->pear_excel_worksheet->setRow($row, $height, $format, $hidden, $level);
    }

    function set_column ($firstcol, $lastcol, $width, $format = null, $hidden = false, $level = 0) {
        $format = $this->MoodleExcelFormat2PearExcelFormat($format);
        $this->pear_excel_worksheet->setColumn($firstcol, $lastcol, $width, $format, $hidden, $level);
    }

    function hide_gridlines() {
        $this->pear_excel_worksheet->hideGridLines();
    }

    function hide_screen_gridlines() {
        $this->pear_excel_worksheet->hideScreenGridlines();
    }

    function insert_bitmap($row, $col, $bitmap, $x = 0, $y = 0, $scale_x = 1, $scale_y = 1) {
        $this->pear_excel_worksheet->insertBitmap($row, $col, $bitmap, $x, $y, $scale_x, $scale_y);
    }

    function merge_cells($first_row, $first_col, $last_row, $last_col) {
        $this->pear_excel_worksheet->mergeCells($first_row, $first_col, $last_row, $last_col);
    }

    function MoodleExcelFormat2PearExcelFormat($format) {
        if ($format) {
            return $format->pear_excel_format;
        } else {
            return null;
        }
    }
}


class MoodleExcelFormat {
    var $pear_excel_format;

    function MoodleExcelFormat(&$workbook, $properties = array()) {
        $this->pear_excel_format =& $workbook->addFormat();
        foreach($properties as $property => $value) {
            if(method_exists($this,"set_$property")) {
                $aux = 'set_'.$property;
                $this->$aux($value);
            }
        }
    }

    function set_size($size) {
        $this->pear_excel_format->setSize($size);
    }

    function set_bold($weight = 1) {
        $this->pear_excel_format->setBold($weight);
    }

    function set_underline($underline) {
        $this->pear_excel_format->setUnderline($underline);
    }

    function set_italic() {
        $this->pear_excel_format->setItalic();
    }

    function set_strikeout() {
        $this->pear_excel_format->setStrikeOut();
    }

    function set_outline() {
        $this->pear_excel_format->setOutLine();
    }

    function set_shadow() {
        $this->pear_excel_format->setShadow();
    }

    function set_script($script) {
        $this->pear_excel_format->setScript($script);
    }

    function set_color($color) {
        $this->pear_excel_format->setColor($color);
    }

    function set_fg_color($color) {
        $this->pear_excel_format->setFgColor($color);
    }

    function set_bg_color($color) {
        $this->pear_excel_format->setBgColor($color);
    }

    function set_pattern($pattern=1) {
        $this->pear_excel_format->setPattern($pattern);
    }

    function set_text_wrap() {
        $this->pear_excel_format->setTextWrap();
    }

    function set_align($location) {
        $this->pear_excel_format->setAlign($location);
    }

    function set_h_align($location) {
        $this->pear_excel_format->setHAlign($location);
    }

    function set_v_align($location) {
        $this->pear_excel_format->setVAlign($location);
    }

    function set_top($style) {
        $this->pear_excel_format->setTop($style);
    }

    function set_bottom($style) {
        $this->pear_excel_format->setBottom($style);
    }

    function set_left($style) {
        $this->pear_excel_format->setLeft($style);
    }

    function set_right($style) {
        $this->pear_excel_format->setRight($style);
    }

    function set_border($style) {
        $this->pear_excel_format->setBorder($style);
    }

    function set_num_format($num_format) {
        $this->pear_excel_format->setNumFormat($num_format);
    }

}