class.ilExcel.php

Go to the documentation of this file.

<?php
/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */

include_once './libs/composer/vendor/autoload.php';

/*
 * Wrapper for Microsoft Excel Import/Export (based on PHPExcel)
 *
 * @author Jörg Lützenkirchen <luetzenkirchen@leifos.com>
 * @ingroup ServicesExcel
 */
class ilExcel
{
        protected $workbook; // [PHPExcel]
        
        protected $type; // [string]
        
        const FORMAT_XML = "Excel2007";
        const FORMAT_BIFF = "Excel5";
        
        public function __construct()
        {
                $this->setFormat(self::FORMAT_XML);             
                $this->workbook = new PHPExcel();       
                $this->workbook->removeSheetByIndex(0);
        }               

        //
        // loading files
        //

        public function loadFromFile($filename) {
                $this->workbook = PHPExcel_IOFactory::load($filename);
        }
        
        // 
        // type/format
        // 
        
        public function getValidFormats()
        {
                return array(self::FORMAT_XML, self::FORMAT_BIFF);
        }
        
        public function setFormat($a_format)
        {
                if(in_array($a_format, $this->getValidFormats()))
                {
                        $this->format = $a_format;
                }
        }
        
        
        //
        // sheets
        //
        
        public function addSheet($a_name, $a_activate = true)
        {
                // see PHPExcel_Worksheet::$_invalidCharacters;
                #20749
                $invalid = array('*', ':', '/', '\\', '?', '[', ']', '\'-','\'');
                
                $a_name = str_replace($invalid, "", $a_name);
                
                // #19056 - phpExcel only allows 31 chars
                // see https://github.com/PHPOffice/PHPExcel/issues/79
                $a_name = ilUtil::shortenText($a_name, 31); 
                
                $sheet = new PHPExcel_Worksheet($this->workbook, $a_name);
                $this->workbook->addSheet($sheet);
                $new_index = $this->workbook->getSheetCount()-1;
                
                if((bool)$a_activate)
                {
                        $this->setActiveSheet($new_index);
                }
                
                return $new_index;              
        }
        
        public function setActiveSheet($a_index)
        {
                $this->workbook->setActiveSheetIndex($a_index);
        }


        public function getSheetCount() {
                return $this->workbook->getSheetCount();
        }


        public function getSheetTitle() {
                return $this->workbook->getActiveSheet()->getTitle();
        }
        
        
        //
        // cells
        //
        
        protected function prepareValue($a_value)
        {
                global $lng;
                
                // :TODO: does this make sense?
                if(is_bool($a_value))
                {
                        $a_value = $this->prepareBooleanValue($a_value);
                }
                else if($a_value instanceof ilDateTime)
                {
                        $a_value = $this->prepareDateValue($a_value);
                }       
                else if(is_string($a_value))
                {
                        $a_value = $this->prepareString($a_value);
                }
                
                return $a_value;
        }

        protected function prepareDateValue(ilDateTime $a_value)
        {
                switch(true)
                {
                        case $a_value instanceof ilDate:
                                $a_value = PHPExcel_Shared_Date::stringToExcel($a_value->get(IL_CAL_DATE));
                                break;

                        default:
                                $a_value = PHPExcel_Shared_Date::stringToExcel($a_value->get(IL_CAL_DATETIME));
                                break;
                }

                return $a_value;
        }

        protected function prepareBooleanValue($a_value)
        {
                global $lng;

                return $a_value ? $lng->txt('yes') : $lng->txt('no');
        }

        protected function prepareString($a_value)
        {
                return strip_tags($a_value); // #14542
        }

        protected function setDateFormat(PHPExcel_Cell $a_cell, $a_value)
        {
                if($a_value instanceof ilDate)
                {
                        // :TODO: i18n?
                        $a_cell->getStyle()->getNumberFormat()->setFormatCode("dd.mm.yyyy");
                }
                else if($a_value instanceof ilDateTime)
                {
                        // :TODO: i18n?
                        $a_cell->getStyle()->getNumberFormat()->setFormatCode("dd.mm.yyyy hh:mm:ss");
                }
        }
        
        public function setCellByCoordinates($a_coords, $a_value)
        {
                if($a_value instanceof ilDateTime)
                {
                        $cell = $this->workbook->getActiveSheet()->setCellValue(
                                $a_coords,
                                $this->prepareValue($a_value),
                                true
                        );
                        $this->setDateFormat($cell, $a_value);
                }
                elseif(is_numeric($a_value))
                {
                        $this->workbook->getActiveSheet()->setCellValueExplicit(
                                $a_coords,
                                $this->prepareValue($a_value),
                                PHPExcel_Cell_DataType::TYPE_NUMERIC,
                                false
                        );
                }
                else
                {
                        $this->workbook->getActiveSheet()->setCellValueExplicit(
                                $a_coords,
                                $this->prepareValue($a_value),
                                PHPExcel_Cell_DataType::TYPE_STRING,
                                false
                        );
                }

        }
        
        public function setCell($a_row, $a_col, $a_value)
        {
                if($a_value instanceof ilDateTime)
                {
                        $cell = $this->workbook->getActiveSheet()->setCellValueByColumnAndRow(
                                $a_col,
                                $a_row,
                                $this->prepareValue($a_value),
                                true
                        );
                        $this->setDateFormat($cell, $a_value);
                }
                elseif(is_numeric($a_value))
                {
                        $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
                                $a_col,
                                $a_row,
                                $this->prepareValue($a_value),
                                PHPExcel_Cell_DataType::TYPE_NUMERIC,
                                false
                        );
                }
                else
                {
                        $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
                                $a_col,
                                $a_row,
                                $this->prepareValue($a_value),
                                PHPExcel_Cell_DataType::TYPE_STRING,
                                false
                        );
                }
        }
        
        public function setCellArray(array $a_values, $a_top_left = "A1", $a_null_value = NULL) 
        {
                foreach($a_values as $row_idx => $cols)
                {
                        if(is_array($cols))
                        {
                                foreach($cols as $col_idx => $col_value)
                                {
                                        $a_values[$row_idx][$col_idx] = $this->prepareValue($col_value);
                                }
                        }
                        else
                        {
                                $a_values[$row_idx] = $this->prepareValue($cols);
                        }
                }
                
                $this->workbook->getActiveSheet()->fromArray($a_values, $a_null_value, $a_top_left);
        }


        public function getCell($a_row, $a_col) {
                return $this->workbook->getActiveSheet()->getCellByColumnAndRow($a_col, $a_row)->getValue();
        }


        public function getSheetAsArray() {
                return $this->workbook->getActiveSheet()->toArray();
        }


        public function getColumnCount() {
                return PHPExcel_Cell::columnIndexFromString($this->workbook->getActiveSheet()->getHighestDataColumn());
        }

        public function getColumnCoord($a_col)
        {
                return PHPExcel_Cell::stringFromColumnIndex($a_col);
        }
        
        protected function setGlobalAutoSize()
        {
                // this may change the active sheet
                foreach($this->workbook->getWorksheetIterator() as $worksheet) 
                {
                        $this->workbook->setActiveSheetIndex($this->workbook->getIndex($worksheet));
                        $sheet = $this->workbook->getActiveSheet();
                        $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
                        $cellIterator->setIterateOnlyExistingCells(true);
                        foreach($cellIterator as $cell) 
                        {
                                $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
                        }
                }
        }
        
        //
        // deliver/save
        // 
        
        protected function prepareStorage($a_file_name)
        {
                $this->setGlobalAutoSize();
                $this->workbook->setActiveSheetIndex(0);
                
                switch($this->format)
                {
                        case self::FORMAT_BIFF:                         
                                if(!stristr($a_file_name, ".xls"))
                                {
                                        $a_file_name .= ".xls";
                                }
                                break;
                        
                        case self::FORMAT_XML:                          
                                if(!stristr($a_file_name, ".xlsx"))
                                {
                                        $a_file_name .= ".xlsx";
                                }
                                break;
                }
                
                return $a_file_name;
        }
        
        public function sendToClient($a_file_name)
        {
                require_once('./Services/FileDelivery/classes/class.ilPHPOutputDelivery.php');

                $a_file_name = $this->prepareStorage($a_file_name);
                switch ($this->format) {
                        case self::FORMAT_BIFF:
                                $a_mime_type = ilMimeTypeUtil::APPLICATION__VND_MS_EXCEL;
                                break;

                        case self::FORMAT_XML:
                                $a_mime_type = ilMimeTypeUtil::APPLICATION__VND_OPENXMLFORMATS_OFFICEDOCUMENT_SPREADSHEETML_SHEET;
                                break;
                        default:
                                $a_mime_type = ilMimeTypeUtil::APPLICATION__OCTET_STREAM;
                                break;
                }
                $ilPHPOutputDelivery = new ilPHPOutputDelivery();
                $ilPHPOutputDelivery->start($a_file_name, $a_mime_type);

                $writer = PHPExcel_IOFactory::createWriter($this->workbook, $this->format);
                $writer->save(ilFileDelivery::DIRECT_PHP_OUTPUT);

                $ilPHPOutputDelivery->stop();
        }
        
        public function writeToFile($a_file)
        {
                $a_file = $this->prepareStorage($a_file);
                
                $writer = PHPExcel_IOFactory::createWriter($this->workbook, $this->format);
                $writer->save($a_file);
        }


        public function writeToTmpFile() {
                $writer = PHPExcel_IOFactory::createWriter($this->workbook, $this->format);
                $filename = ilUtil::ilTempnam();
                $writer->save($filename);
                
                return $filename;
        }

        // 
        // style (:TODO: more options?)
        // 
        
        public function setBold($a_coords)
        {
                $this->workbook->getActiveSheet()->getStyle($a_coords)->getFont()->setBold(true);
        }
        
        public function setColors($a_coords, $a_background, $a_font = null)
        {
                $opts = array(
                        'fill' => array(
                                'type' => PHPExcel_Style_Fill::FILL_SOLID,
                                'color' => array('rgb' => $a_background)
                        )
                );   
                
                if($a_font)
                {
                        $opts['font'] = array(
                                'color' => array('rgb' => $a_font)
                        );
                }
                
                $this->workbook->getActiveSheet()->getStyle($a_coords)->applyFromArray($opts);
        }
        
        public function setBorders($a_coords, $a_top, $a_right = false, $a_bottom = false, $a_left = false)
        {
                $style = $this->workbook->getActiveSheet()->getStyle($a_coords);
                
                // :TODO: border styles?
                if($a_top)
                {
                        $style->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                }
                if($a_right)
                {
                        $style->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                }
                if($a_bottom)
                {
                        $style->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                }
                if($a_left)
                {
                        $style->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                }
        }

        function getCoordByColumnAndRow($pColumn = 0, $pRow = 1)
        {
                $columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
                return $columnLetter . $pRow;
        }

}