4include_once 
'./libs/composer/vendor/autoload.php';
 
   36                $this->workbook->removeSheetByIndex(0);
 
   62                return array(self::FORMAT_XML, self::FORMAT_BIFF);
 
   74                        $this->format = $a_format;
 
   90        public function addSheet($a_name, $a_activate = 
true)
 
   94                $invalid = array(
'*', 
':', 
'/', 
'\\', 
'?', 
'[', 
']', 
'\'-
','\
'');
 
   96                $a_name = str_replace($invalid, 
"", $a_name);
 
  103                $this->workbook->addSheet($sheet);
 
  104                $new_index = $this->workbook->getSheetCount()-1;
 
  106                if((
bool)$a_activate)
 
  121                $this->workbook->setActiveSheetIndex($a_index);
 
  131                return $this->workbook->getSheetCount();
 
  141                return $this->workbook->getActiveSheet()->getTitle();
 
  160                if(is_bool($a_value))
 
  168                else if(is_string($a_value))
 
  184                        case $a_value instanceof 
ilDate:
 
  204                return $a_value ? 
$lng->txt(
'yes') : 
$lng->txt(
'no');
 
  213                return strip_tags($a_value); 
 
  224                if($a_value instanceof 
ilDate)
 
  227                        $a_cell->
getStyle()->getNumberFormat()->setFormatCode(
"dd.mm.yyyy");
 
  232                        $a_cell->
getStyle()->getNumberFormat()->setFormatCode(
"dd.mm.yyyy hh:mm:ss");
 
  246                        $cell = $this->workbook->getActiveSheet()->setCellValue(
 
  253                elseif(is_numeric($a_value))
 
  255                        $this->workbook->getActiveSheet()->setCellValueExplicit(
 
  264                        $this->workbook->getActiveSheet()->setCellValueExplicit(
 
  281        public function setCell($a_row, $a_col, $a_value)
 
  285                        $cell = $this->workbook->getActiveSheet()->setCellValueByColumnAndRow(
 
  293                elseif(is_numeric($a_value))
 
  295                        $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
 
  305                        $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
 
  322        public function setCellArray(array $a_values, $a_top_left = 
"A1", $a_null_value = NULL) 
 
  324                foreach($a_values as $row_idx => $cols)
 
  328                                foreach($cols as $col_idx => $col_value)
 
  330                                        $a_values[$row_idx][$col_idx] = $this->
prepareValue($col_value);
 
  339                $this->workbook->getActiveSheet()->fromArray($a_values, $a_null_value, $a_top_left);
 
  352                return $this->workbook->getActiveSheet()->getCellByColumnAndRow($a_col, $a_row)->getValue();
 
  362                return $this->workbook->getActiveSheet()->toArray();
 
  392                foreach($this->workbook->getWorksheetIterator() as 
$worksheet) 
 
  394                        $this->workbook->setActiveSheetIndex($this->workbook->getIndex(
$worksheet));
 
  395                        $sheet = $this->workbook->getActiveSheet();
 
  396                        $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
 
  397                        $cellIterator->setIterateOnlyExistingCells(
true);
 
  398                        foreach($cellIterator as $cell) 
 
  400                                $sheet->getColumnDimension($cell->getColumn())->setAutoSize(
true);
 
  415                $this->workbook->setActiveSheetIndex(0);
 
  417                switch($this->format)
 
  420                                if(!stristr($a_file_name, 
".xls"))
 
  422                                        $a_file_name .= 
".xls";
 
  427                                if(!stristr($a_file_name, 
".xlsx"))
 
  429                                        $a_file_name .= 
".xlsx";
 
  444                require_once(
'./Services/FileDelivery/classes/class.ilPHPOutputDelivery.php');
 
  447                switch ($this->format) {
 
  460                $ilPHPOutputDelivery->start($a_file_name, $a_mime_type);
 
  465                $ilPHPOutputDelivery->stop();
 
  478                $writer->save($a_file);
 
  505                $this->workbook->getActiveSheet()->getStyle($a_coords)->getFont()->setBold(
true);
 
  515        public function setColors($a_coords, $a_background, $a_font = 
null)
 
  520                                'color' => array(
'rgb' => $a_background)
 
  526                        $opts[
'font'] = array(
 
  527                                'color' => array(
'rgb' => $a_font)
 
  531                $this->workbook->getActiveSheet()->getStyle($a_coords)->applyFromArray($opts);
 
  543        public function setBorders($a_coords, $a_top, $a_right = 
false, $a_bottom = 
false, $a_left = 
false)
 
  545                $style = $this->workbook->getActiveSheet()->getStyle($a_coords);
 
  576                return $columnLetter . $pRow;
 
An exception for terminatinating execution or to throw for unit testing.
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
getStyle()
Get cell style.
static columnIndexFromString($pString='A')
Column index from string.
static createWriter(PHPExcel $phpExcel, $writerType='')
Create PHPExcel_Writer_IWriter.
static load($pFilename)
Loads PHPExcel from file using automatic PHPExcel_Reader_IReader resolution.
static stringToExcel($dateValue='')
Convert a date/time string to Excel time.
@classDescription Date and time handling
get($a_format, $a_format_str='', $a_tz='')
get formatted date
setDateFormat(PHPExcel_Cell $a_cell, $a_value)
Set date format.
setCellArray(array $a_values, $a_top_left="A1", $a_null_value=NULL)
Set cell values from array.
writeToFile($a_file)
Save workbook to file.
getSheetAsArray()
Returns the active sheet as an array.
getSheetCount()
Returns number of sheets.
prepareValue($a_value)
Prepare value for cell.
getCoordByColumnAndRow($pColumn=0, $pRow=1)
Get cell coordinate (e.g.
setGlobalAutoSize()
Set all existing columns on all sheets to autosize.
setCell($a_row, $a_col, $a_value)
Set cell value.
sendToClient($a_file_name)
Send workbook to client.
prepareStorage($a_file_name)
Prepare workbook for storage/delivery.
setCellByCoordinates($a_coords, $a_value)
Set cell value by coordinates.
__construct()
Constructor.
getColumnCount()
Returns the number of columns the sheet contains.
setColors($a_coords, $a_background, $a_font=null)
Set cell(s) colors.
loadFromFile($filename)
Loads a spreadsheet from file.
setBold($a_coords)
Set cell(s) to bold.
prepareDateValue(ilDateTime $a_value)
setBorders($a_coords, $a_top, $a_right=false, $a_bottom=false, $a_left=false)
Toggle cell(s) borders.
getSheetTitle()
Return the current sheet title.
getColumnCoord($a_col)
Get column "name" from number.
prepareBooleanValue($a_value)
addSheet($a_name, $a_activate=true)
Add sheet.
setFormat($a_format)
Set file format.
getValidFormats()
Get valid file formats.
setActiveSheet($a_index)
Set active sheet.
getCell($a_row, $a_col)
Returns the value of a cell.
const APPLICATION__OCTET_STREAM
const APPLICATION__VND_MS_EXCEL
const APPLICATION__VND_OPENXMLFORMATS_OFFICEDOCUMENT_SPREADSHEETML_SHEET
Class ilPHPOutputDelivery.
static shortenText($a_str, $a_len, $a_dots=false, $a_next_blank=false, $a_keep_extension=false)
shorten a string to given length.
static ilTempnam($a_temp_path=null)
Create a temporary file in an ILIAS writable directory.