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.