ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
class.ilExcel.php
Go to the documentation of this file.
1<?php
2/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3
4include_once './libs/composer/vendor/autoload.php';
5
6/*
7 * Wrapper for Microsoft Excel Import/Export (based on PHPExcel)
8 *
9 * @author Jörg Lützenkirchen <luetzenkirchen@leifos.com>
10 * @ingroup ServicesExcel
11 */
13{
17 protected $lng;
18
22 protected $workbook; // [PHPExcel]
23
27 protected $type; // [string]
28
29 const FORMAT_XML = "Excel2007";
30 const FORMAT_BIFF = "Excel5";
31
37 public function __construct()
38 {
39 global $DIC;
40
41 $this->lng = $DIC->language();
42 $this->setFormat(self::FORMAT_XML);
43 $this->workbook = new PHPExcel();
44 $this->workbook->removeSheetByIndex(0);
45 }
46
47 //
48 // loading files
49 //
50
55 public function loadFromFile($filename)
56 {
57 $this->workbook = PHPExcel_IOFactory::load($filename);
58 }
59
60 //
61 // type/format
62 //
63
69 public function getValidFormats()
70 {
71 return array(self::FORMAT_XML, self::FORMAT_BIFF);
72 }
73
79 public function setFormat($a_format)
80 {
81 if (in_array($a_format, $this->getValidFormats())) {
82 $this->format = $a_format;
83 }
84 }
85
86
87 //
88 // sheets
89 //
90
98 public function addSheet($a_name, $a_activate = true)
99 {
100 // see PHPExcel_Worksheet::$_invalidCharacters;
101 #20749
102 $invalid = array('*', ':', '/', '\\', '?', '[', ']', '\'-','\'');
103
104 $a_name = str_replace($invalid, "", $a_name);
105
106 // #19056 - phpExcel only allows 31 chars
107 // see https://github.com/PHPOffice/PHPExcel/issues/79
108 $a_name = ilUtil::shortenText($a_name, 31);
109
110 $sheet = new PHPExcel_Worksheet($this->workbook, $a_name);
111 $this->workbook->addSheet($sheet);
112 $new_index = $this->workbook->getSheetCount()-1;
113
114 if ((bool) $a_activate) {
115 $this->setActiveSheet($new_index);
116 }
117
118 return $new_index;
119 }
120
126 public function setActiveSheet($a_index)
127 {
128 $this->workbook->setActiveSheetIndex($a_index);
129 }
130
131
137 public function getSheetCount()
138 {
139 return $this->workbook->getSheetCount();
140 }
141
142
148 public function getSheetTitle()
149 {
150 return $this->workbook->getActiveSheet()->getTitle();
151 }
152
153
154 //
155 // cells
156 //
157
164 protected function prepareValue($a_value)
165 {
167
168 // :TODO: does this make sense?
169 if (is_bool($a_value)) {
170 $a_value = $this->prepareBooleanValue($a_value);
171 } elseif ($a_value instanceof ilDateTime) {
172 $a_value = $this->prepareDateValue($a_value);
173 } elseif (is_string($a_value)) {
174 $a_value = $this->prepareString($a_value);
175 }
176
177 return $a_value;
178 }
179
184 protected function prepareDateValue(ilDateTime $a_value)
185 {
186 switch (true) {
187 case $a_value instanceof ilDate:
189 break;
190
191 default:
193 break;
194 }
195
196 return $a_value;
197 }
198
203 protected function prepareBooleanValue($a_value)
204 {
206
207 return $a_value ? $lng->txt('yes') : $lng->txt('no');
208 }
209
214 protected function prepareString($a_value)
215 {
216 return strip_tags($a_value); // #14542
217 }
218
225 protected function setDateFormat(PHPExcel_Cell $a_cell, $a_value)
226 {
227 if ($a_value instanceof ilDate) {
228 // :TODO: i18n?
229 $a_cell->getStyle()->getNumberFormat()->setFormatCode("dd.mm.yyyy");
230 } elseif ($a_value instanceof ilDateTime) {
231 // :TODO: i18n?
232 $a_cell->getStyle()->getNumberFormat()->setFormatCode("dd.mm.yyyy hh:mm:ss");
233 }
234 }
235
242 public function setCellByCoordinates($a_coords, $a_value)
243 {
244 if ($a_value instanceof ilDateTime) {
245 $cell = $this->workbook->getActiveSheet()->setCellValue(
246 $a_coords,
247 $this->prepareValue($a_value),
248 true
249 );
250 $this->setDateFormat($cell, $a_value);
251 } elseif (is_numeric($a_value)) {
252 $this->workbook->getActiveSheet()->setCellValueExplicit(
253 $a_coords,
254 $this->prepareValue($a_value),
256 false
257 );
258 } else {
259 $this->workbook->getActiveSheet()->setCellValueExplicit(
260 $a_coords,
261 $this->prepareValue($a_value),
263 false
264 );
265 }
266 }
267
276 public function setCell($a_row, $a_col, $a_value, $a_datatype = null)
277 {
278 if (!is_null($a_datatype)) {
279 $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
280 $a_col,
281 $a_row,
282 $this->prepareValue($a_value),
283 $a_datatype
284 );
285 } elseif ($a_value instanceof ilDateTime) {
286 $cell = $this->workbook->getActiveSheet()->setCellValueByColumnAndRow(
287 $a_col,
288 $a_row,
289 $this->prepareValue($a_value),
290 true
291 );
292 $this->setDateFormat($cell, $a_value);
293 } elseif (is_numeric($a_value)) {
294 $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
295 $a_col,
296 $a_row,
297 $this->prepareValue($a_value),
299 false
300 );
301 } else {
302 $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
303 $a_col,
304 $a_row,
305 $this->prepareValue($a_value),
307 false
308 );
309 }
310 }
311
319 public function setCellArray(array $a_values, $a_top_left = "A1", $a_null_value = null)
320 {
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);
325 }
326 } else {
327 $a_values[$row_idx] = $this->prepareValue($cols);
328 }
329 }
330
331 $this->workbook->getActiveSheet()->fromArray($a_values, $a_null_value, $a_top_left);
332 }
333
334
343 public function getCell($a_row, $a_col)
344 {
345 return $this->workbook->getActiveSheet()->getCellByColumnAndRow($a_col, $a_row)->getValue();
346 }
347
348
354 public function getSheetAsArray()
355 {
356 return $this->workbook->getActiveSheet()->toArray();
357 }
358
359
365 public function getColumnCount()
366 {
367 return PHPExcel_Cell::columnIndexFromString($this->workbook->getActiveSheet()->getHighestDataColumn());
368 }
369
376 public function getColumnCoord($a_col)
377 {
379 }
380
384 protected function setGlobalAutoSize()
385 {
386 // this may change the active sheet
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);
394 }
395 }
396 }
397
398 //
399 // deliver/save
400 //
401
405 protected function prepareStorage($a_file_name)
406 {
407 $this->setGlobalAutoSize();
408 $this->workbook->setActiveSheetIndex(0);
409
410 switch ($this->format) {
412 if (!stristr($a_file_name, ".xls")) {
413 $a_file_name .= ".xls";
414 }
415 break;
416
417 case self::FORMAT_XML:
418 if (!stristr($a_file_name, ".xlsx")) {
419 $a_file_name .= ".xlsx";
420 }
421 break;
422 }
423
424 return $a_file_name;
425 }
426
432 public function sendToClient($a_file_name)
433 {
434 require_once('./Services/FileDelivery/classes/class.ilPHPOutputDelivery.php');
435
436 $a_file_name = $this->prepareStorage($a_file_name);
437 switch ($this->format) {
440 break;
441
442 case self::FORMAT_XML:
444 break;
445 default:
447 break;
448 }
449 $tmp_name = ilUtil::ilTempnam();
450
451 $writer = PHPExcel_IOFactory::createWriter($this->workbook, $this->format);
452 $writer->save($tmp_name);
453
454 ilFileDelivery::deliverFileAttached($tmp_name, $a_file_name, $a_mime_type, true);
455 }
456
462 public function writeToFile($a_file)
463 {
464 $a_file = $this->prepareStorage($a_file);
465
466 $writer = PHPExcel_IOFactory::createWriter($this->workbook, $this->format);
467 $writer->save($a_file);
468 }
469
470
475 public function writeToTmpFile()
476 {
477 $writer = PHPExcel_IOFactory::createWriter($this->workbook, $this->format);
479 $writer->save($filename);
480
481 return $filename;
482 }
483
484 //
485 // style (:TODO: more options?)
486 //
487
493 public function setBold($a_coords)
494 {
495 $this->workbook->getActiveSheet()->getStyle($a_coords)->getFont()->setBold(true);
496 }
497
505 public function setColors($a_coords, $a_background, $a_font = null)
506 {
507 $opts = array(
508 'fill' => array(
510 'color' => array('rgb' => $a_background)
511 )
512 );
513
514 if ($a_font) {
515 $opts['font'] = array(
516 'color' => array('rgb' => $a_font)
517 );
518 }
519
520 $this->workbook->getActiveSheet()->getStyle($a_coords)->applyFromArray($opts);
521 }
522
532 public function setBorders($a_coords, $a_top, $a_right = false, $a_bottom = false, $a_left = false)
533 {
534 $style = $this->workbook->getActiveSheet()->getStyle($a_coords);
535
536 // :TODO: border styles?
537 if ($a_top) {
538 $style->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
539 }
540 if ($a_right) {
541 $style->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
542 }
543 if ($a_bottom) {
544 $style->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
545 }
546 if ($a_left) {
547 $style->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
548 }
549 }
550
558 public function getCoordByColumnAndRow($pColumn = 0, $pRow = 1)
559 {
560 $columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
561 return $columnLetter . $pRow;
562 }
563}
$worksheet
An exception for terminatinating execution or to throw for unit testing.
static stringFromColumnIndex($pColumnIndex=0)
String from columnindex.
Definition: Cell.php:825
getStyle()
Get cell style.
Definition: Cell.php:536
static columnIndexFromString($pString='A')
Column index from string.
Definition: Cell.php:782
static createWriter(PHPExcel $phpExcel, $writerType='')
Create PHPExcel_Writer_IWriter.
Definition: IOFactory.php:132
static load($pFilename)
Loads PHPExcel from file using automatic PHPExcel_Reader_IReader resolution.
Definition: IOFactory.php:190
static stringToExcel($dateValue='')
Convert a date/time string to Excel time.
Definition: Date.php:350
const FILL_SOLID
Definition: Fill.php:40
const IL_CAL_DATE
const IL_CAL_DATETIME
@classDescription Date and time handling
get($a_format, $a_format_str='', $a_tz='')
get formatted date
Class for single dates.
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.
prepareString($a_value)
prepareStorage($a_file_name)
Prepare workbook for storage/delivery.
const FORMAT_BIFF
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.
const FORMAT_XML
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__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.
$style
Definition: example_012.php:70
global $DIC
Definition: saml.php:7
$cols
Definition: xhr_table.php:11