Inheritance diagram for ilExcel:

Collaboration diagram for ilExcel:

Public Member Functions
	__construct ()
	Constructor. More...

	loadFromFile ($filename)
	Loads a spreadsheet from file. More...

	getValidFormats ()
	Get valid file formats. More...

	setFormat ($a_format)
	Set file format. More...

	addSheet ($a_name, $a_activate=true)
	Add sheet. More...

	setActiveSheet ($a_index)
	Set active sheet. More...

	getSheetCount ()
	Returns number of sheets. More...

	getSheetTitle ()
	Return the current sheet title. More...

	setCellByCoordinates ($a_coords, $a_value)
	Set cell value by coordinates. More...

	setCell ($a_row, $a_col, $a_value)
	Set cell value. More...

	setCellArray (array $a_values, $a_top_left="A1", $a_null_value=NULL)
	Set cell values from array. More...

	getCell ($a_row, $a_col)
	Returns the value of a cell. More...

	getSheetAsArray ()
	Returns the active sheet as an array. More...

	getColumnCount ()
	Returns the number of columns the sheet contains. More...

	getColumnCoord ($a_col)
	Get column "name" from number. More...

	sendToClient ($a_file_name)
	Send workbook to client. More...

	writeToFile ($a_file)
	Save workbook to file. More...

	writeToTmpFile ()

	setBold ($a_coords)
	Set cell(s) to bold. More...

	setColors ($a_coords, $a_background, $a_font=null)
	Set cell(s) colors. More...

	setBorders ($a_coords, $a_top, $a_right=false, $a_bottom=false, $a_left=false)
	Toggle cell(s) borders. More...

	getCoordByColumnAndRow ($pColumn=0, $pRow=1)
	Get cell coordinate (e.g. More...

Data Fields
const	FORMAT_XML = "Excel2007"

const	FORMAT_BIFF = "Excel5"

Protected Member Functions
	prepareValue ($a_value)
	Prepare value for cell. More...

	prepareDateValue (ilDateTime $a_value)

	prepareBooleanValue ($a_value)

	prepareString ($a_value)

	setDateFormat (PHPExcel_Cell $a_cell, $a_value)
	Set date format. More...

	setGlobalAutoSize ()
	Set all existing columns on all sheets to autosize. More...

	prepareStorage ($a_file_name)
	Prepare workbook for storage/delivery. More...

Protected Attributes
	$workbook

	$type

Detailed Description

Definition at line 12 of file class.ilExcel.php.

Constructor & Destructor Documentation

◆ __construct()

ilExcel::__construct ( )

Constructor.

Returns: self

Definition at line 32 of file class.ilExcel.php.

References setFormat(), and workbook.

         {
                 $this->setFormat(self::FORMAT_XML);             
                 $this->workbook = new PHPExcel();       
                 $this->workbook->removeSheetByIndex(0);
         }               

Here is the call graph for this function:

Member Function Documentation

◆ addSheet()

ilExcel::addSheet	(	$a_name,
		$a_activate = `true`
	)

Add sheet.

Parameters

string	$a_name
bool	$a_activate

Returns: int index

Definition at line 90 of file class.ilExcel.php.

References array, setActiveSheet(), ilUtil\shortenText(), and workbook.

Referenced by ilSurveyEvaluationGUI\exportResultsDetailsExcel().

         {
                 // 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;              
         }

Here is the call graph for this function:

Here is the caller graph for this function:

◆ getCell()

ilExcel::getCell	(	$a_row,
		$a_col
	)

Returns the value of a cell.

Parameters

int	$a_row
int	$a_col

Returns: mixed

Definition at line 351 of file class.ilExcel.php.

References workbook.

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

◆ getColumnCoord()

ilExcel::getColumnCoord ( $a_col )

Get column "name" from number.

Parameters

int $a_col

Returns: string

Definition at line 381 of file class.ilExcel.php.

References PHPExcel_Cell\stringFromColumnIndex().

Referenced by ilTrObjectUsersPropsTableGUI\fillHeaderExcel(), ilTrUserObjectsPropsTableGUI\fillHeaderExcel(), ilTrMatrixTableGUI\fillHeaderExcel(), ilBookingReservationsTableGUI\fillHeaderExcel(), ilTrSummaryTableGUI\fillHeaderExcel(), and ilTable2GUI\fillHeaderExcel().

         {
                 return PHPExcel_Cell::stringFromColumnIndex($a_col);
         }

Here is the call graph for this function:

Here is the caller graph for this function:

◆ getColumnCount()

ilExcel::getColumnCount ( )

Returns the number of columns the sheet contains.

Returns: int

Definition at line 371 of file class.ilExcel.php.

References PHPExcel_Cell\columnIndexFromString(), and workbook.

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

Here is the call graph for this function:

◆ getCoordByColumnAndRow()

ilExcel::getCoordByColumnAndRow	(	$pColumn = `0`,
		$pRow = `1`
	)

Get cell coordinate (e.g.

"B2") for column and row number

Parameters

int	$pColumn
int	$pRow

Returns: string

Definition at line 573 of file class.ilExcel.php.

References PHPExcel_Cell\stringFromColumnIndex().

Referenced by ilSurveyEvaluationGUI\exportResultsDetailsExcel().

         {
                 $columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
                 return $columnLetter . $pRow;
         }

Here is the call graph for this function:

Here is the caller graph for this function:

◆ getSheetAsArray()

ilExcel::getSheetAsArray ( )

Returns the active sheet as an array.

Returns: array

Definition at line 361 of file class.ilExcel.php.

References workbook.

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

◆ getSheetCount()

ilExcel::getSheetCount ( )

Returns number of sheets.

Returns: int

Definition at line 130 of file class.ilExcel.php.

References workbook.

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

◆ getSheetTitle()

ilExcel::getSheetTitle ( )

Return the current sheet title.

Returns: string

Definition at line 140 of file class.ilExcel.php.

References workbook.

                                         {
                 return $this->workbook->getActiveSheet()->getTitle();
         }

◆ getValidFormats()

ilExcel::getValidFormats ( )

Get valid file formats.

Returns: array

Definition at line 60 of file class.ilExcel.php.

References array.

Referenced by setFormat().

         {
                 return array(self::FORMAT_XML, self::FORMAT_BIFF);
         }

Here is the caller graph for this function:

◆ loadFromFile()

ilExcel::loadFromFile ( $filename )

Loads a spreadsheet from file.

Parameters

$filename

Definition at line 47 of file class.ilExcel.php.

References $filename, PHPExcel_IOFactory\load(), and workbook.

                                                 {
                 $this->workbook = PHPExcel_IOFactory::load($filename);
         }

Here is the call graph for this function:

◆ prepareBooleanValue()

ilExcel::prepareBooleanValue ( $a_value )

protected

Parameters

bool $a_value

Returns: string

Definition at line 200 of file class.ilExcel.php.

References $lng.

Referenced by prepareValue().

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

Here is the caller graph for this function:

◆ prepareDateValue()

ilExcel::prepareDateValue ( ilDateTime $a_value )

protected

Parameters

ilDateTime $a_value

Returns: string

Definition at line 180 of file class.ilExcel.php.

References ilDateTime\get(), IL_CAL_DATE, IL_CAL_DATETIME, and PHPExcel_Shared_Date\stringToExcel().

Referenced by prepareValue().

         {
                 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;
         }

Here is the call graph for this function:

Here is the caller graph for this function:

◆ prepareStorage()

ilExcel::prepareStorage ( $a_file_name )

protected

Prepare workbook for storage/delivery.

Definition at line 412 of file class.ilExcel.php.

References format, setGlobalAutoSize(), and workbook.

Referenced by sendToClient(), and writeToFile().

         {
                 $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;
         }

Here is the call graph for this function:

Here is the caller graph for this function:

◆ prepareString()

ilExcel::prepareString ( $a_value )

protected

Parameters

string $a_value

Returns: string

Definition at line 211 of file class.ilExcel.php.

Referenced by prepareValue().

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

Here is the caller graph for this function:

◆ prepareValue()

ilExcel::prepareValue ( $a_value )

protected

Prepare value for cell.

Parameters

mixed $a_value

Returns: mixed

Definition at line 155 of file class.ilExcel.php.

References $lng, prepareBooleanValue(), prepareDateValue(), and prepareString().

Referenced by ilAssExcelFormatHelper\setCell(), setCell(), setCellArray(), ilAssExcelFormatHelper\setCellByCoordinates(), and setCellByCoordinates().

         {
                 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;
         }

Here is the call graph for this function:

Here is the caller graph for this function:

◆ sendToClient()

ilExcel::sendToClient ( $a_file_name )

Send workbook to client.

Parameters

string $a_file_name

Definition at line 442 of file class.ilExcel.php.

References ilMimeTypeUtil\APPLICATION__OCTET_STREAM, ilMimeTypeUtil\APPLICATION__VND_MS_EXCEL, ilMimeTypeUtil\APPLICATION__VND_OPENXMLFORMATS_OFFICEDOCUMENT_SPREADSHEETML_SHEET, PHPExcel_IOFactory\createWriter(), ilFileDelivery\DIRECT_PHP_OUTPUT, format, prepareStorage(), and workbook.

         {
                 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();
         }

Here is the call graph for this function:

◆ setActiveSheet()

ilExcel::setActiveSheet ( $a_index )

Set active sheet.

Parameters

int $a_index

Definition at line 119 of file class.ilExcel.php.

References workbook.

Referenced by addSheet().

         {
                 $this->workbook->setActiveSheetIndex($a_index);
         }

Here is the caller graph for this function:

◆ setBold()

ilExcel::setBold ( $a_coords )

Set cell(s) to bold.

Parameters

string $a_coords

Definition at line 503 of file class.ilExcel.php.

References workbook.

Referenced by ilSurveyEvaluationGUI\exportResultsDetailsExcel(), ilLPProgressTableGUI\fillHeaderExcel(), ilTrObjectUsersPropsTableGUI\fillHeaderExcel(), ilTrUserObjectsPropsTableGUI\fillHeaderExcel(), ilTrMatrixTableGUI\fillHeaderExcel(), ilBookingReservationsTableGUI\fillHeaderExcel(), ilTrSummaryTableGUI\fillHeaderExcel(), ilTable2GUI\fillHeaderExcel(), and ilAssExcelFormatHelper\setFormattedExcelTitle().

         {
                 $this->workbook->getActiveSheet()->getStyle($a_coords)->getFont()->setBold(true);
         }

Here is the caller graph for this function:

◆ setBorders()

ilExcel::setBorders	(	$a_coords,
		$a_top,
		$a_right = `false`,
		$a_bottom = `false`,
		$a_left = `false`
	)

Toggle cell(s) borders.

Parameters

string	$a_coords
bool	$a_top
bool	$a_right
bool	$a_bottom
bool	$a_left

Definition at line 543 of file class.ilExcel.php.

References $style, PHPExcel_Style_Border\BORDER_THIN, and workbook.

         {
                 $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);
                 }
         }

◆ setCell()

ilExcel::setCell	(	$a_row,
		$a_col,
		$a_value
	)

Set cell value.

Parameters

int	$a_row
int	$a_col
mixed	$a_value

Definition at line 281 of file class.ilExcel.php.

References prepareValue(), setDateFormat(), PHPExcel_Cell_DataType\TYPE_NUMERIC, PHPExcel_Cell_DataType\TYPE_STRING, and workbook.

         {
                 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
                         );
                 }
         }

Here is the call graph for this function:

Here is the caller graph for this function:

◆ setCellArray()

ilExcel::setCellArray	(	array	$a_values,
			$a_top_left = `"A1"`,
			$a_null_value = `NULL`
	)

Set cell values from array.

Parameters

array	$a_values
string	$a_top_left
mixed	$a_null_value

Definition at line 322 of file class.ilExcel.php.

References prepareValue(), and workbook.

         {
                 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);
         }

Here is the call graph for this function:

◆ setCellByCoordinates()

ilExcel::setCellByCoordinates	(	$a_coords,
		$a_value
	)

Set cell value by coordinates.

Parameters

string	$a_coords
mixed	$a_value

Definition at line 242 of file class.ilExcel.php.

References prepareValue(), setDateFormat(), PHPExcel_Cell_DataType\TYPE_NUMERIC, PHPExcel_Cell_DataType\TYPE_STRING, and workbook.

         {
                 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
                         );
                 }
 
         }

Here is the call graph for this function:

◆ setColors()

ilExcel::setColors	(	$a_coords,
		$a_background,
		$a_font = `null`
	)

Set cell(s) colors.

Parameters

string	$a_coords
string	$a_background
string	$a_font

Definition at line 515 of file class.ilExcel.php.

References array, PHPExcel_Style_Fill\FILL_SOLID, and workbook.

Referenced by ilSurveyEvaluationGUI\exportResultsDetailsExcel(), ilSurveyEvaluationGUI\parseResultsToExcel(), and ilAssExcelFormatHelper\setFormattedExcelTitle().

         {
                 $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);
         }

Here is the caller graph for this function:

◆ setDateFormat()

ilExcel::setDateFormat	(	PHPExcel_Cell	$a_cell,
			$a_value
	)

protected

Set date format.

Parameters

PHPExcel_Cell	$a_cell
mixed	$a_value

Definition at line 222 of file class.ilExcel.php.

References PHPExcel_Cell\getStyle().

Referenced by setCell(), and setCellByCoordinates().

         {
                 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");
                 }
         }

Here is the call graph for this function:

Here is the caller graph for this function:

◆ setFormat()

ilExcel::setFormat ( $a_format )

Set file format.

Parameters

string $a_format

Definition at line 70 of file class.ilExcel.php.

References format, and getValidFormats().

Referenced by __construct().

         {
                 if(in_array($a_format, $this->getValidFormats()))
                 {
                         $this->format = $a_format;
                 }
         }

Here is the call graph for this function:

Here is the caller graph for this function:

◆ setGlobalAutoSize()

ilExcel::setGlobalAutoSize ( )

protected

Set all existing columns on all sheets to autosize.

Definition at line 389 of file class.ilExcel.php.

References $worksheet, and workbook.

Referenced by prepareStorage().

         {
                 // 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);
                         }
                 }
         }

Here is the caller graph for this function:

◆ writeToFile()

ilExcel::writeToFile ( $a_file )

Save workbook to file.

Parameters

string $a_file full path

Definition at line 473 of file class.ilExcel.php.

References PHPExcel_IOFactory\createWriter(), format, prepareStorage(), and workbook.

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

Here is the call graph for this function:

◆ writeToTmpFile()

ilExcel::writeToTmpFile ( )

Returns: string

Exceptions

Definition at line 486 of file class.ilExcel.php.

References $filename, PHPExcel_IOFactory\createWriter(), format, ilUtil\ilTempnam(), and workbook.

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

Here is the call graph for this function:

Field Documentation

◆ $type

ilExcel::$type

protected

Definition at line 22 of file class.ilExcel.php.

◆ $workbook

ilExcel::$workbook

protected

Definition at line 17 of file class.ilExcel.php.

◆ FORMAT_BIFF

const ilExcel::FORMAT_BIFF = "Excel5"

Definition at line 25 of file class.ilExcel.php.

◆ FORMAT_XML

const ilExcel::FORMAT_XML = "Excel2007"

Definition at line 24 of file class.ilExcel.php.

The documentation for this class was generated from the following file:

Services/Excel/classes/class.ilExcel.php

Public Member Functions

Data Fields

Protected Member Functions

Protected Attributes

Detailed Description

Constructor & Destructor Documentation

◆ __construct()

Member Function Documentation

◆ addSheet()

◆ getCell()

◆ getColumnCoord()

◆ getColumnCount()

◆ getCoordByColumnAndRow()

◆ getSheetAsArray()

◆ getSheetCount()

◆ getSheetTitle()

◆ getValidFormats()

◆ loadFromFile()

◆ prepareBooleanValue()

◆ prepareDateValue()

◆ prepareStorage()

◆ prepareString()

◆ prepareValue()

◆ sendToClient()

◆ setActiveSheet()

◆ setBold()

◆ setBorders()

◆ setCell()

◆ setCellArray()

◆ setCellByCoordinates()

◆ setColors()

◆ setDateFormat()

◆ setFormat()

◆ setGlobalAutoSize()

◆ writeToFile()

◆ writeToTmpFile()

Field Documentation

◆ $type

◆ $workbook

◆ FORMAT_BIFF

◆ FORMAT_XML