4include_once 
'./libs/composer/vendor/autoload.php';
 
   41        $this->lng = 
$DIC->language();
 
   44        $this->workbook->removeSheetByIndex(0);
 
   71        return array(self::FORMAT_XML, self::FORMAT_BIFF);
 
   82            $this->format = $a_format;
 
   98    public function addSheet($a_name, $a_activate = 
true)
 
  102        $invalid = array(
'*', 
':', 
'/', 
'\\', 
'?', 
'[', 
']', 
'\'-
','\
'');
 
  104        $a_name = str_replace($invalid, 
"", $a_name);
 
  111        $this->workbook->addSheet($sheet);
 
  112        $new_index = $this->workbook->getSheetCount()-1;
 
  114        if ((
bool) $a_activate) {
 
  128        $this->workbook->setActiveSheetIndex($a_index);
 
  139        return $this->workbook->getSheetCount();
 
  150        return $this->workbook->getActiveSheet()->getTitle();
 
  169        if (is_bool($a_value)) {
 
  173        } elseif (is_string($a_value)) {
 
  187            case $a_value instanceof 
ilDate:
 
  207        return $a_value ? 
$lng->txt(
'yes') : 
$lng->txt(
'no');
 
  216        return strip_tags($a_value); 
 
  227        if ($a_value instanceof 
ilDate) {
 
  229            $a_cell->
getStyle()->getNumberFormat()->setFormatCode(
"dd.mm.yyyy");
 
  232            $a_cell->
getStyle()->getNumberFormat()->setFormatCode(
"dd.mm.yyyy hh:mm:ss");
 
  245            $cell = $this->workbook->getActiveSheet()->setCellValue(
 
  251        } elseif (is_numeric($a_value)) {
 
  252            $this->workbook->getActiveSheet()->setCellValueExplicit(
 
  259            $this->workbook->getActiveSheet()->setCellValueExplicit(
 
  276    public function setCell($a_row, $a_col, $a_value, $a_datatype = 
null)
 
  278        if (!is_null($a_datatype)) {
 
  279            $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
 
  286            $cell = $this->workbook->getActiveSheet()->setCellValueByColumnAndRow(
 
  293        } elseif (is_numeric($a_value)) {
 
  294            $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
 
  302            $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
 
  319    public function setCellArray(array $a_values, $a_top_left = 
"A1", $a_null_value = 
null)
 
  321        foreach ($a_values as $row_idx => 
$cols) {
 
  322            if (is_array(
$cols)) {
 
  323                foreach (
$cols as $col_idx => $col_value) {
 
  324                    $a_values[$row_idx][$col_idx] = $this->
prepareValue($col_value);
 
  331        $this->workbook->getActiveSheet()->fromArray($a_values, $a_null_value, $a_top_left);
 
  345        return $this->workbook->getActiveSheet()->getCellByColumnAndRow($a_col, $a_row)->getValue();
 
  356        return $this->workbook->getActiveSheet()->toArray();
 
  387        foreach ($this->workbook->getWorksheetIterator() as 
$worksheet) {
 
  388            $this->workbook->setActiveSheetIndex($this->workbook->getIndex(
$worksheet));
 
  389            $sheet = $this->workbook->getActiveSheet();
 
  390            $cellIterator = $sheet->getRowIterator()->current()->getCellIterator();
 
  391            $cellIterator->setIterateOnlyExistingCells(
true);
 
  392            foreach ($cellIterator as $cell) {
 
  393                $sheet->getColumnDimension($cell->getColumn())->setAutoSize(
true);
 
  408        $this->workbook->setActiveSheetIndex(0);
 
  410        switch ($this->format) {
 
  412                if (!stristr($a_file_name, 
".xls")) {
 
  413                    $a_file_name .= 
".xls";
 
  418                if (!stristr($a_file_name, 
".xlsx")) {
 
  419                    $a_file_name .= 
".xlsx";
 
  434        require_once(
'./Services/FileDelivery/classes/class.ilPHPOutputDelivery.php');
 
  437        switch ($this->format) {
 
  452        $writer->save($tmp_name);
 
  467        $writer->save($a_file);
 
  495        $this->workbook->getActiveSheet()->getStyle($a_coords)->getFont()->setBold(
true);
 
  505    public function setColors($a_coords, $a_background, $a_font = 
null)
 
  510                'color' => array(
'rgb' => $a_background)
 
  515            $opts[
'font'] = array(
 
  516                'color' => array(
'rgb' => $a_font)
 
  520        $this->workbook->getActiveSheet()->getStyle($a_coords)->applyFromArray($opts);
 
  532    public function setBorders($a_coords, $a_top, $a_right = 
false, $a_bottom = 
false, $a_left = 
false)
 
  534        $style = $this->workbook->getActiveSheet()->getStyle($a_coords);
 
  561        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.
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.
setCell($a_row, $a_col, $a_value, $a_datatype=null)
Set cell value.
getCoordByColumnAndRow($pColumn=0, $pRow=1)
Get cell coordinate (e.g.
setGlobalAutoSize()
Set all existing columns on all sheets to autosize.
setCellArray(array $a_values, $a_top_left="A1", $a_null_value=null)
Set cell values from array.
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.
static deliverFileAttached($path_to_file, $download_file_name='', $mime_type='', $delete_file=false)
void
const APPLICATION__OCTET_STREAM
const APPLICATION__VND_MS_EXCEL
const APPLICATION__VND_OPENXMLFORMATS_OFFICEDOCUMENT_SPREADSHEETML_SHEET
static ilTempnam($a_temp_path=null)
Create a temporary file in an ILIAS writable directory.
static shortenText( $a_str, $a_len, $a_dots=false, $a_next_blank=false, $a_keep_extension=false)
shorten a string to given length.