dolibarr 18.0.6
export_excel2007.modules.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2006-2015 Laurent Destailleur <eldy@users.sourceforge.net>
3 * Copyright (C) 2012 Marcos GarcĂ­a <marcosgdf@gmail.com>
4 *
5 * This program is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 3 of the License, or
8 * (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with this program. If not, see <https://www.gnu.org/licenses/>.
17 */
18
25require_once DOL_DOCUMENT_ROOT.'/core/modules/export/modules_export.php';
26require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
27
28use PhpOffice\PhpSpreadsheet\Spreadsheet;
29use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
30use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
31
36{
40 public $id;
41
45 public $label;
46
47 public $extension;
48
53 public $version = 'dolibarr';
54
55 public $label_lib;
56
57 public $version_lib;
58
59 public $workbook; // Handle file
60
61 public $worksheet; // Handle sheet
62
63 public $styleArray;
64
65 public $row;
66
67 public $col;
68
69 public $file; // To save filename
70
71
77 public function __construct($db)
78 {
79 global $conf, $langs;
80 $this->db = $db;
81
82 $this->id = 'excel2007'; // Same value then xxx in file name export_xxx.modules.php
83 $this->label = 'Excel 2007'; // Label of driver
84 $this->desc = $langs->trans('Excel2007FormatDesc');
85 $this->extension = 'xlsx'; // Extension for generated file by this driver
86 $this->picto = 'mime/xls'; // Picto
87 $this->version = '1.30'; // Driver version
88 $this->phpmin = array(7, 0); // Minimum version of PHP required by module
89
90 $this->disabled = 0;
91
92 if (empty($this->disabled)) {
93 require_once PHPEXCELNEW_PATH.'Spreadsheet.php';
94 $this->label_lib = 'PhpSpreadSheet';
95 $this->version_lib = '1.12.0'; // No way to get info from library
96 }
97
98 $this->row = 0;
99 }
100
106 public function getDriverId()
107 {
108 return $this->id;
109 }
110
116 public function getDriverLabel()
117 {
118 return $this->label;
119 }
120
126 public function getDriverDesc()
127 {
128 return $this->desc;
129 }
130
136 public function getDriverExtension()
137 {
138 return $this->extension;
139 }
140
146 public function getDriverVersion()
147 {
148 return $this->version;
149 }
150
156 public function getLibLabel()
157 {
158 return $this->label_lib;
159 }
160
166 public function getLibVersion()
167 {
168 return $this->version_lib;
169 }
170
171
172 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
180 public function open_file($file, $outputlangs)
181 {
182 // phpcs:enable
183 global $user, $conf, $langs;
184
185 if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
186 $outputlangs->charset_output = 'ISO-8859-1'; // Because Excel 5 format is ISO
187 }
188
189 dol_syslog(get_class($this)."::open_file file=".$file);
190 $this->file = $file;
191
192 $ret = 1;
193
194 $outputlangs->load("exports");
195
196 require_once DOL_DOCUMENT_ROOT.'/includes/phpoffice/phpspreadsheet/src/autoloader.php';
197 require_once DOL_DOCUMENT_ROOT.'/includes/Psr/autoloader.php';
198 require_once PHPEXCELNEW_PATH.'Spreadsheet.php';
199
200 if ($this->id == 'excel2007') {
201 if (!class_exists('ZipArchive')) { // For Excel2007, PHPSpreadSheet may need ZipArchive
202 $langs->load("errors");
203 $this->error = $langs->trans('ErrorPHPNeedModule', 'zip');
204 return -1;
205 }
206 }
207
208 $this->workbook = new Spreadsheet();
209 $this->workbook->getProperties()->setCreator($user->getFullName($outputlangs).' - '.DOL_APPLICATION_TITLE.' '.DOL_VERSION);
210 //$this->workbook->getProperties()->setLastModifiedBy('Dolibarr '.DOL_VERSION);
211 $this->workbook->getProperties()->setTitle(basename($file));
212 $this->workbook->getProperties()->setSubject(basename($file));
213 $this->workbook->getProperties()->setDescription(DOL_APPLICATION_TITLE.' '.DOL_VERSION);
214
215 $this->workbook->setActiveSheetIndex(0);
216 $this->workbook->getActiveSheet()->setTitle($outputlangs->trans("Sheet"));
217 $this->workbook->getActiveSheet()->getDefaultRowDimension()->setRowHeight(16);
218
219 return $ret;
220 }
221
222 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
229 public function write_header($outputlangs)
230 {
231 // phpcs:enable
232 //$outputlangs->charset_output='ISO-8859-1'; // Because Excel 5 format is ISO
233
234 return 0;
235 }
236
237
238 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
248 public function write_title($array_export_fields_label, $array_selected_sorted, $outputlangs, $array_types)
249 {
250 // phpcs:enable
251 global $conf;
252
253 // Create a format for the column headings
254 $this->workbook->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
255 $this->workbook->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
256 $selectlabel = array();
257
258 $this->col = 1;
259 if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
260 $this->col = 0;
261 }
262 foreach ($array_selected_sorted as $code => $value) {
263 $alias = $array_export_fields_label[$code];
264 //print "dd".$alias;
265 if (empty($alias)) {
266 dol_print_error('', 'Bad value for field with code='.$code.'. Try to redefine export.');
267 }
268 $typefield = isset($array_types[$code]) ? $array_types[$code] : '';
269
270 if (preg_match('/^Select:/i', $typefield) && $typefield = substr($typefield, 7)) {
271 $selectlabel[$code."_label"] = $alias."_label";
272 }
273 if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
274 $this->worksheet->write($this->row, $this->col, $outputlangs->transnoentities($alias), $formatheader);
275 } else {
276 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $outputlangs->transnoentities($alias));
277 if (!empty($array_types[$code]) && in_array($array_types[$code], array('Date', 'Numeric', 'TextAuto'))) { // Set autowidth for some types
278 $this->workbook->getActiveSheet()->getColumnDimension($this->column2Letter($this->col + 1))->setAutoSize(true);
279 }
280 }
281 $this->col++;
282 }
283 foreach ($selectlabel as $key => $value) {
284 if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
285 $this->worksheet->write($this->row, $this->col, $outputlangs->transnoentities($value), $formatheader);
286 } else {
287 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $outputlangs->transnoentities($value));
288 if (!empty($array_types[$code]) && in_array($array_types[$code], array('Date', 'Numeric', 'TextAuto'))) { // Set autowidth for some types
289 $this->workbook->getActiveSheet()->getColumnDimension($this->column2Letter($this->col + 1))->setAutoSize(true);
290 }
291 }
292 $this->col++;
293 }
294 $this->row++;
295 return 0;
296 }
297
298 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
308 public function write_record($array_selected_sorted, $objp, $outputlangs, $array_types)
309 {
310 // phpcs:enable
311 global $conf;
312
313 // Define first row
314 $this->col = 1;
315 if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
316 $this->col = 0;
317 }
318
319 $reg = array();
320 $selectlabelvalues = array();
321 foreach ($array_selected_sorted as $code => $value) {
322 if (strpos($code, ' as ') == 0) {
323 $alias = str_replace(array('.', '-', '(', ')'), '_', $code);
324 } else {
325 $alias = substr($code, strpos($code, ' as ') + 4);
326 }
327 if (empty($alias)) {
328 dol_print_error('', 'Bad value for field with code='.$code.'. Try to redefine export.');
329 }
330 $newvalue = $objp->$alias;
331
332 $newvalue = $this->excel_clean($newvalue);
333 $typefield = isset($array_types[$code]) ? $array_types[$code] : '';
334
335 if (preg_match('/^Select:/i', $typefield) && $typefield = substr($typefield, 7)) {
336 $array = jsonOrUnserialize($typefield);
337 if (is_array($array) && !empty($newvalue)) {
338 $array = $array['options'];
339 $selectlabelvalues[$code."_label"] = $array[$newvalue];
340 } else {
341 $selectlabelvalues[$code."_label"] = "";
342 }
343 }
344
345 // Traduction newvalue
346 if (preg_match('/^\‍((.*)\‍)$/i', $newvalue, $reg)) {
347 $newvalue = $outputlangs->transnoentities($reg[1]);
348 } else {
349 $newvalue = $outputlangs->convToOutputCharset($newvalue);
350 }
351
352 if (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i', $newvalue)) {
353 $newvalue = dol_stringtotime($newvalue);
354 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($newvalue));
355 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
356 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd');
357 } elseif (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]$/i', $newvalue)) {
358 $newvalue = dol_stringtotime($newvalue);
359 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($newvalue));
360 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
361 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
362 } else {
363 if ($typefield == 'Text' || $typefield == 'TextAuto') {
364 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, (string) $newvalue);
365 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
366 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('@');
367 $this->workbook->getActiveSheet()->getStyle($coord)->getAlignment()->setHorizontal(PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
368 } else {
369 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
370 }
371 }
372 $this->col++;
373 }
374 foreach ($selectlabelvalues as $key => $newvalue) {
375 if (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i', $newvalue)) {
376 $newvalue = dol_stringtotime($newvalue);
377 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($newvalue));
378 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
379 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd');
380 } elseif (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9]$/i', $newvalue)) {
381 $newvalue = dol_stringtotime($newvalue);
382 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($newvalue));
383 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
384 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
385 } else {
386 if ($typefield == 'Text' || $typefield == 'TextAuto') {
387 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, (string) $newvalue);
388 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
389 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('@');
390 $this->workbook->getActiveSheet()->getStyle($coord)->getAlignment()->setHorizontal(PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
391 } else {
392 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
393 }
394 }
395 $this->col++;
396 }
397 $this->row++;
398 return 0;
399 }
400
401
402 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
409 public function write_footer($outputlangs)
410 {
411 // phpcs:enable
412 return 0;
413 }
414
415
416 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
422 public function close_file()
423 {
424 // phpcs:enable
425 global $conf;
426
427 $objWriter = new Xlsx($this->workbook);
428 $objWriter->save($this->file);
429 $this->workbook->disconnectWorksheets();
430 unset($this->workbook);
431
432 return 1;
433 }
434
435
436 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
443 public function excel_clean($newvalue)
444 {
445 // phpcs:enable
446 // Rule Dolibarr: No HTML
447 $newvalue = dol_string_nohtmltag($newvalue);
448
449 return $newvalue;
450 }
451
452
459 public function column2Letter($c)
460 {
461
462 $c = intval($c);
463 if ($c <= 0) {
464 return '';
465 }
466
467 $letter = '';
468 while ($c != 0) {
469 $p = ($c - 1) % 26;
470 $c = intval(($c - $p) / 26);
471 $letter = chr(65 + $p).$letter;
472 }
473
474 return $letter;
475 }
476
485 public function setCellValue($val, $startCell, $endCell = '')
486 {
487 try {
488 $this->workbook->getActiveSheet()->setCellValue($startCell, $val);
489
490 if (!empty($endCell)) {
491 $cellRange = $startCell.':'.$endCell;
492 $this->workbook->getActiveSheet()->mergeCells($startCell.':'.$endCell);
493 } else {
494 $cellRange = $startCell;
495 }
496 if (!empty($this->styleArray)) {
497 $this->workbook->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->styleArray);
498 }
499 } catch (Exception $e) {
500 $this->error = $e->getMessage();
501 return -1;
502 }
503 return 1;
504 }
505
513 public function setBorderStyle($thickness, $color)
514 {
515 $this->styleArray['borders'] = array(
516 'outline' => array(
517 'borderStyle' => $thickness,
518 'color' => array('argb' => $color)
519 )
520 );
521 return 1;
522 }
523
531 public function setFontStyle($bold, $color)
532 {
533 $this->styleArray['font'] = array(
534 'color' => array('argb' => $color),
535 'bold' => $bold
536 );
537 return 1;
538 }
539
546 public function setAlignmentStyle($horizontal)
547 {
548 $this->styleArray['alignment'] = array('horizontal' => $horizontal);
549 return 1;
550 }
551
556 public function resetStyle()
557 {
558 $this->styleArray = array();
559 return 1;
560 }
561
570 public function setBlock($startCell, $TDatas = array(), $boldTitle = false)
571 {
572 try {
573 if (!empty($TDatas)) {
574 $startCell = $this->workbook->getActiveSheet()->getCell($startCell);
575 $startColumn = Coordinate::columnIndexFromString($startCell->getColumn());
576 $startRow = $startCell->getRow();
577 foreach ($TDatas as $column => $TRows) {
578 if ($boldTitle) {
579 $this->setFontStyle(true, $this->styleArray['font']['color']['argb']);
580 }
581 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
582 $this->setCellValue($column, $cell->getCoordinate());
583 $rowPos = $startRow;
584 if ($boldTitle) {
585 $this->setFontStyle(false, $this->styleArray['font']['color']['argb']);
586 }
587 foreach ($TRows as $row) {
588 $rowPos++;
589 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $rowPos);
590 $this->setCellValue($row, $cell->getCoordinate());
591 }
592 $startColumn++;
593 }
594 }
595 } catch (Exception $e) {
596 $this->error = $e->getMessage();
597 return -1;
598 }
599 return 1;
600 }
601
610 public function setBlock2Columns($startCell, $TDatas = array(), $boldTitle = false)
611 {
612 try {
613 if (!empty($TDatas)) {
614 $startCell = $this->workbook->getActiveSheet()->getCell($startCell);
615 $startColumn = Coordinate::columnIndexFromString($startCell->getColumn());
616 $startRow = $startCell->getRow();
617 foreach ($TDatas as $title => $val) {
618 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
619 if ($boldTitle) {
620 $this->setFontStyle(true, $this->styleArray['font']['color']['argb']);
621 }
622 $this->setCellValue($title, $cell->getCoordinate());
623 if ($boldTitle) {
624 $this->setFontStyle(false, $this->styleArray['font']['color']['argb']);
625 }
626 $cell2 = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn + 1, $startRow);
627 $this->setCellValue($val, $cell2->getCoordinate());
628 $startRow++;
629 }
630 }
631 } catch (Exception $e) {
632 $this->error = $e->getMessage();
633 return -1;
634 }
635 return 1;
636 }
637
645 public function enableAutosize($firstColumn, $lastColumn)
646 {
647 foreach (range($firstColumn, $lastColumn) as $columnID) {
648 $this->workbook->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
649 }
650 return 1;
651 }
652
662 public function setMergeCellValueByLength($val, $startCell, $length, $offset = 0)
663 {
664 try {
665 $startCell = $this->workbook->getActiveSheet()->getCell($startCell);
666 $startColumn = Coordinate::columnIndexFromString($startCell->getColumn());
667 if (!empty($offset)) {
668 $startColumn += $offset;
669 }
670
671 $startRow = $startCell->getRow();
672 $startCell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
673 $startCoordinate = $startCell->getCoordinate();
674 $this->setCellValue($val, $startCell->getCoordinate());
675
676 $endCell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn + ($length - 1), $startRow);
677 $endCoordinate = $endCell->getCoordinate();
678 $this->workbook->getActiveSheet()->mergeCells($startCoordinate.':'.$endCoordinate);
679 } catch (Exception $e) {
680 $this->error = $e->getMessage();
681 return -1;
682 }
683 return $endCoordinate;
684 }
685}
Class to build export files with Excel format.
write_record($array_selected_sorted, $objp, $outputlangs, $array_types)
Output record line into file.
setBlock2Columns($startCell, $TDatas=array(), $boldTitle=false)
Make a 2xN Tab in Sheet.
getDriverVersion()
getDriverVersion
setCellValue($val, $startCell, $endCell='')
Set cell value and automatically merge if we give an endcell.
setMergeCellValueByLength($val, $startCell, $length, $offset=0)
Set a value cell and merging it by giving a starting cell and a length.
close_file()
Close Excel file.
write_footer($outputlangs)
Write footer.
column2Letter($c)
Convert a column to letter (1->A, 0->B, 27->AA, ...)
getDriverExtension()
getDriverExtension
__construct($db)
Constructor.
setFontStyle($bold, $color)
Set font style.
setBlock($startCell, $TDatas=array(), $boldTitle=false)
Make a NxN Block in sheet.
open_file($file, $outputlangs)
Open output file.
write_title($array_export_fields_label, $array_selected_sorted, $outputlangs, $array_types)
Output title line into file.
enableAutosize($firstColumn, $lastColumn)
Enable auto sizing for column range.
setAlignmentStyle($horizontal)
Set alignment style (horizontal, left, right, ...)
write_header($outputlangs)
Write header.
excel_clean($newvalue)
Clean a cell to respect rules of Excel file cells.
setBorderStyle($thickness, $color)
Set border style.
Parent class for export modules.
dol_stringtotime($string, $gm=1)
Convert a string date into a GM Timestamps date Warning: YYYY-MM-DDTHH:MM:SS+02:00 (RFC3339) is not s...
Definition date.lib.php:409
dol_string_nohtmltag($stringtoclean, $removelinefeed=1, $pagecodeto='UTF-8', $strip_tags=0, $removedoublespaces=1)
Clean a string from all HTML tags and entities.
dol_print_error($db='', $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
jsonOrUnserialize($stringtodecode)
Decode an encode string.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.