ILIAS  release_5-2 Revision v5.2.25-18-g3f80b828510
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 $workbook; // [PHPExcel]
18
22 protected $type; // [string]
23
24 const FORMAT_XML = "Excel2007";
25 const FORMAT_BIFF = "Excel5";
26
32 public function __construct()
33 {
34 $this->setFormat(self::FORMAT_XML);
35 $this->workbook = new PHPExcel();
36 $this->workbook->removeSheetByIndex(0);
37 }
38
39 //
40 // loading files
41 //
42
47 public function loadFromFile($filename) {
48 $this->workbook = PHPExcel_IOFactory::load($filename);
49 }
50
51 //
52 // type/format
53 //
54
60 public function getValidFormats()
61 {
62 return array(self::FORMAT_XML, self::FORMAT_BIFF);
63 }
64
70 public function setFormat($a_format)
71 {
72 if(in_array($a_format, $this->getValidFormats()))
73 {
74 $this->format = $a_format;
75 }
76 }
77
78
79 //
80 // sheets
81 //
82
90 public function addSheet($a_name, $a_activate = true)
91 {
92 // see PHPExcel_Worksheet::$_invalidCharacters;
93 #20749
94 $invalid = array('*', ':', '/', '\\', '?', '[', ']', '\'-','\'');
95
96 $a_name = str_replace($invalid, "", $a_name);
97
98 // #19056 - phpExcel only allows 31 chars
99 // see https://github.com/PHPOffice/PHPExcel/issues/79
100 $a_name = ilUtil::shortenText($a_name, 31);
101
102 $sheet = new PHPExcel_Worksheet($this->workbook, $a_name);
103 $this->workbook->addSheet($sheet);
104 $new_index = $this->workbook->getSheetCount()-1;
105
106 if((bool)$a_activate)
107 {
108 $this->setActiveSheet($new_index);
109 }
110
111 return $new_index;
112 }
113
119 public function setActiveSheet($a_index)
120 {
121 $this->workbook->setActiveSheetIndex($a_index);
122 }
123
124
130 public function getSheetCount() {
131 return $this->workbook->getSheetCount();
132 }
133
134
140 public function getSheetTitle() {
141 return $this->workbook->getActiveSheet()->getTitle();
142 }
143
144
145 //
146 // cells
147 //
148
155 protected function prepareValue($a_value)
156 {
157 global $lng;
158
159 // :TODO: does this make sense?
160 if(is_bool($a_value))
161 {
162 $a_value = $this->prepareBooleanValue($a_value);
163 }
164 else if($a_value instanceof ilDateTime)
165 {
166 $a_value = $this->prepareDateValue($a_value);
167 }
168 else if(is_string($a_value))
169 {
170 $a_value = $this->prepareString($a_value);
171 }
172
173 return $a_value;
174 }
175
180 protected function prepareDateValue(ilDateTime $a_value)
181 {
182 switch(true)
183 {
184 case $a_value instanceof ilDate:
186 break;
187
188 default:
190 break;
191 }
192
193 return $a_value;
194 }
195
200 protected function prepareBooleanValue($a_value)
201 {
202 global $lng;
203
204 return $a_value ? $lng->txt('yes') : $lng->txt('no');
205 }
206
211 protected function prepareString($a_value)
212 {
213 return strip_tags($a_value); // #14542
214 }
215
222 protected function setDateFormat(PHPExcel_Cell $a_cell, $a_value)
223 {
224 if($a_value instanceof ilDate)
225 {
226 // :TODO: i18n?
227 $a_cell->getStyle()->getNumberFormat()->setFormatCode("dd.mm.yyyy");
228 }
229 else if($a_value instanceof ilDateTime)
230 {
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 {
246 $cell = $this->workbook->getActiveSheet()->setCellValue(
247 $a_coords,
248 $this->prepareValue($a_value),
249 true
250 );
251 $this->setDateFormat($cell, $a_value);
252 }
253 elseif(is_numeric($a_value))
254 {
255 $this->workbook->getActiveSheet()->setCellValueExplicit(
256 $a_coords,
257 $this->prepareValue($a_value),
259 false
260 );
261 }
262 else
263 {
264 $this->workbook->getActiveSheet()->setCellValueExplicit(
265 $a_coords,
266 $this->prepareValue($a_value),
268 false
269 );
270 }
271
272 }
273
281 public function setCell($a_row, $a_col, $a_value)
282 {
283 if($a_value instanceof ilDateTime)
284 {
285 $cell = $this->workbook->getActiveSheet()->setCellValueByColumnAndRow(
286 $a_col,
287 $a_row,
288 $this->prepareValue($a_value),
289 true
290 );
291 $this->setDateFormat($cell, $a_value);
292 }
293 elseif(is_numeric($a_value))
294 {
295 $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
296 $a_col,
297 $a_row,
298 $this->prepareValue($a_value),
300 false
301 );
302 }
303 else
304 {
305 $this->workbook->getActiveSheet()->setCellValueExplicitByColumnAndRow(
306 $a_col,
307 $a_row,
308 $this->prepareValue($a_value),
310 false
311 );
312 }
313 }
314
322 public function setCellArray(array $a_values, $a_top_left = "A1", $a_null_value = NULL)
323 {
324 foreach($a_values as $row_idx => $cols)
325 {
326 if(is_array($cols))
327 {
328 foreach($cols as $col_idx => $col_value)
329 {
330 $a_values[$row_idx][$col_idx] = $this->prepareValue($col_value);
331 }
332 }
333 else
334 {
335 $a_values[$row_idx] = $this->prepareValue($cols);
336 }
337 }
338
339 $this->workbook->getActiveSheet()->fromArray($a_values, $a_null_value, $a_top_left);
340 }
341
342
351 public function getCell($a_row, $a_col) {
352 return $this->workbook->getActiveSheet()->getCellByColumnAndRow($a_col, $a_row)->getValue();
353 }
354
355
361 public function getSheetAsArray() {
362 return $this->workbook->getActiveSheet()->toArray();
363 }
364
365
371 public function getColumnCount() {
372 return PHPExcel_Cell::columnIndexFromString($this->workbook->getActiveSheet()->getHighestDataColumn());
373 }
374
381 public function getColumnCoord($a_col)
382 {
384 }
385
389 protected function setGlobalAutoSize()
390 {
391 // this may change the active sheet
392 foreach($this->workbook->getWorksheetIterator() as $worksheet)
393 {
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)
399 {
400 $sheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
401 }
402 }
403 }
404
405 //
406 // deliver/save
407 //
408
412 protected function prepareStorage($a_file_name)
413 {
414 $this->setGlobalAutoSize();
415 $this->workbook->setActiveSheetIndex(0);
416
417 switch($this->format)
418 {
419 case self::FORMAT_BIFF:
420 if(!stristr($a_file_name, ".xls"))
421 {
422 $a_file_name .= ".xls";
423 }
424 break;
425
426 case self::FORMAT_XML:
427 if(!stristr($a_file_name, ".xlsx"))
428 {
429 $a_file_name .= ".xlsx";
430 }
431 break;
432 }
433
434 return $a_file_name;
435 }
436
442 public function sendToClient($a_file_name)
443 {
444 require_once('./Services/FileDelivery/classes/class.ilPHPOutputDelivery.php');
445
446 $a_file_name = $this->prepareStorage($a_file_name);
447 switch ($this->format) {
450 break;
451
452 case self::FORMAT_XML:
454 break;
455 default:
457 break;
458 }
459 $ilPHPOutputDelivery = new ilPHPOutputDelivery();
460 $ilPHPOutputDelivery->start($a_file_name, $a_mime_type);
461
462 $writer = PHPExcel_IOFactory::createWriter($this->workbook, $this->format);
464
465 $ilPHPOutputDelivery->stop();
466 }
467
473 public function writeToFile($a_file)
474 {
475 $a_file = $this->prepareStorage($a_file);
476
477 $writer = PHPExcel_IOFactory::createWriter($this->workbook, $this->format);
478 $writer->save($a_file);
479 }
480
481
486 public function writeToTmpFile() {
487 $writer = PHPExcel_IOFactory::createWriter($this->workbook, $this->format);
489 $writer->save($filename);
490
491 return $filename;
492 }
493
494 //
495 // style (:TODO: more options?)
496 //
497
503 public function setBold($a_coords)
504 {
505 $this->workbook->getActiveSheet()->getStyle($a_coords)->getFont()->setBold(true);
506 }
507
515 public function setColors($a_coords, $a_background, $a_font = null)
516 {
517 $opts = array(
518 'fill' => array(
520 'color' => array('rgb' => $a_background)
521 )
522 );
523
524 if($a_font)
525 {
526 $opts['font'] = array(
527 'color' => array('rgb' => $a_font)
528 );
529 }
530
531 $this->workbook->getActiveSheet()->getStyle($a_coords)->applyFromArray($opts);
532 }
533
543 public function setBorders($a_coords, $a_top, $a_right = false, $a_bottom = false, $a_left = false)
544 {
545 $style = $this->workbook->getActiveSheet()->getStyle($a_coords);
546
547 // :TODO: border styles?
548 if($a_top)
549 {
550 $style->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
551 }
552 if($a_right)
553 {
554 $style->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
555 }
556 if($a_bottom)
557 {
558 $style->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
559 }
560 if($a_left)
561 {
562 $style->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
563 }
564 }
565
573 function getCoordByColumnAndRow($pColumn = 0, $pRow = 1)
574 {
575 $columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
576 return $columnLetter . $pRow;
577 }
578
579}
$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.
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.
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.
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.
$style
Definition: example_012.php:70
global $lng
Definition: privfeed.php:17