dolibarr 20.0.2
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 * Copyright (C) 2024 William Mead <william.mead@manchenumerique.fr>
5 * Copyright (C) 2024 Frédéric France <frederic.france@free.fr>
6 *
7 * This program is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation; either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program. If not, see <https://www.gnu.org/licenses/>.
19 */
20
27require_once DOL_DOCUMENT_ROOT.'/core/modules/export/modules_export.php';
28require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
29
30use PhpOffice\PhpSpreadsheet\Spreadsheet;
31use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
32use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
33
38{
42 public $id;
43
47 public $label;
48
49 public $extension;
50
55 public $version = 'dolibarr';
56
57 public $label_lib;
58
59 public $version_lib;
60
62 public $workbook; // Handle file
63
64 public $worksheet; // Handle sheet
65
66 public $styleArray;
67
68 public $row;
69
70 public $col;
71
72 public $file; // To save filename
73
74
80 public function __construct($db)
81 {
82 global $langs;
83 $this->db = $db;
84
85 $this->id = 'excel2007'; // Same value then xxx in file name export_xxx.modules.php
86 $this->label = 'Excel 2007'; // Label of driver
87 $this->desc = $langs->trans('Excel2007FormatDesc');
88 $this->extension = 'xlsx'; // Extension for generated file by this driver
89 $this->picto = 'mime/xls'; // Picto
90 $this->version = '1.30'; // Driver version
91 $this->phpmin = array(7, 0); // Minimum version of PHP required by module
92
93 $this->disabled = 0;
94
95 if (empty($this->disabled)) {
96 require_once PHPEXCELNEW_PATH.'Spreadsheet.php';
97 $this->label_lib = 'PhpSpreadSheet';
98 $this->version_lib = '1.12.0'; // No way to get info from library
99 }
100
101 $this->row = 0;
102 }
103
109 public function getDriverId()
110 {
111 return $this->id;
112 }
113
119 public function getDriverLabel()
120 {
121 return $this->label;
122 }
123
129 public function getDriverLabelBis()
130 {
131 global $langs;
132 $langs->load("errors");
133 return $langs->trans("NumberOfLinesLimited");
134 }
135
141 public function getDriverDesc()
142 {
143 return $this->desc;
144 }
145
151 public function getDriverExtension()
152 {
153 return $this->extension;
154 }
155
161 public function getDriverVersion()
162 {
163 return $this->version;
164 }
165
171 public function getLibLabel()
172 {
173 return $this->label_lib;
174 }
175
181 public function getLibVersion()
182 {
183 return $this->version_lib;
184 }
185
186
187 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
195 public function open_file($file, $outputlangs)
196 {
197 // phpcs:enable
198 global $user, $langs;
199
200 dol_syslog(get_class($this)."::open_file file=".$file);
201 $this->file = $file;
202
203 $ret = 1;
204
205 $outputlangs->load("exports");
206
207 require_once DOL_DOCUMENT_ROOT.'/includes/phpoffice/phpspreadsheet/src/autoloader.php';
208 require_once DOL_DOCUMENT_ROOT.'/includes/Psr/autoloader.php';
209 require_once PHPEXCELNEW_PATH.'Spreadsheet.php';
210
211 if ($this->id == 'excel2007') {
212 if (!class_exists('ZipArchive')) { // For Excel2007, PHPSpreadSheet may need ZipArchive
213 $langs->load("errors");
214 $this->error = $langs->trans('ErrorPHPNeedModule', 'zip');
215 return -1;
216 }
217 }
218
219 $this->workbook = new Spreadsheet();
220 $this->workbook->getProperties()->setCreator($user->getFullName($outputlangs).' - '.DOL_APPLICATION_TITLE.' '.DOL_VERSION);
221 //$this->workbook->getProperties()->setLastModifiedBy('Dolibarr '.DOL_VERSION);
222 $this->workbook->getProperties()->setTitle(basename($file));
223 $this->workbook->getProperties()->setSubject(basename($file));
224 $this->workbook->getProperties()->setDescription(DOL_APPLICATION_TITLE.' '.DOL_VERSION);
225
226 $this->workbook->setActiveSheetIndex(0);
227 $this->workbook->getActiveSheet()->setTitle($outputlangs->trans("Sheet"));
228 $this->workbook->getActiveSheet()->getDefaultRowDimension()->setRowHeight(16);
229
230 return $ret;
231 }
232
233 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
240 public function write_header($outputlangs)
241 {
242 // phpcs:enable
243 //$outputlangs->charset_output='ISO-8859-1'; // Because Excel 5 format is ISO
244
245 return 0;
246 }
247
248
249 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
259 public function write_title($array_export_fields_label, $array_selected_sorted, $outputlangs, $array_types)
260 {
261 // phpcs:enable
262
263 // Create a format for the column headings
264 $this->workbook->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
265 $this->workbook->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
266 $selectlabel = array();
267
268 $this->col = 1;
269
270 foreach ($array_selected_sorted as $code => $value) {
271 $alias = $array_export_fields_label[$code];
272 //print "dd".$alias;
273 if (empty($alias)) {
274 dol_print_error(null, 'Bad value for field with code='.$code.'. Try to redefine export.');
275 }
276 $typefield = isset($array_types[$code]) ? $array_types[$code] : '';
277
278 if (preg_match('/^Select:/i', $typefield) && $typefield = substr($typefield, 7)) {
279 $selectlabel[$code."_label"] = $alias."_label";
280 }
281 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $outputlangs->transnoentities($alias));
282 if (!empty($array_types[$code]) && in_array($array_types[$code], array('Date', 'Numeric', 'TextAuto'))) { // Set autowidth for some types
283 $this->workbook->getActiveSheet()->getColumnDimension($this->column2Letter($this->col + 1))->setAutoSize(true);
284 }
285 $this->col++;
286 }
287
288 // Complete with some columns to add columns with the labels of columns of type Select, so we have more then the ID
289 foreach ($selectlabel as $key => $value) {
290 $code = preg_replace('/_label$/', '', $key);
291 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $outputlangs->transnoentities($value));
292 if (!empty($array_types[$code]) && in_array($array_types[$code], array('Date', 'Numeric', 'TextAuto'))) { // Set autowidth for some types
293 $this->workbook->getActiveSheet()->getColumnDimension($this->column2Letter($this->col + 1))->setAutoSize(true);
294 }
295 $this->col++;
296 }
297
298 $this->row++;
299 return 0;
300 }
301
302 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
312 public function write_record($array_selected_sorted, $objp, $outputlangs, $array_types)
313 {
314 // phpcs:enable
315
316 // Define first row
317 $this->col = 1;
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(null, 'Bad value for field with code='.$code.'. Try to redefine export.');
329 }
330
331 $newvalue = !empty($objp->$alias) ? $objp->$alias : '';
332
333 $newvalue = $this->excel_clean($newvalue);
334 $typefield = isset($array_types[$code]) ? $array_types[$code] : '';
335
336 if (preg_match('/^Select:/i', $typefield) && $typefield = substr($typefield, 7)) {
337 $array = jsonOrUnserialize($typefield);
338 if (is_array($array) && !empty($newvalue)) {
339 $array = $array['options'];
340 $selectlabelvalues[$code."_label"] = $array[$newvalue];
341 } else {
342 $selectlabelvalues[$code."_label"] = "";
343 }
344 }
345
346 // Traduction newvalue
347 if (preg_match('/^\‍((.*)\‍)$/i', $newvalue, $reg)) {
348 $newvalue = $outputlangs->transnoentities($reg[1]);
349 } else {
350 $newvalue = $outputlangs->convToOutputCharset($newvalue);
351 }
352
353 if (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i', $newvalue)) {
354 $newvalue = dol_stringtotime($newvalue);
355 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($newvalue));
356 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
357 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd');
358 } 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)) {
359 $newvalue = dol_stringtotime($newvalue);
360 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($newvalue));
361 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
362 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
363 } else {
364 if ($typefield == 'Text' || $typefield == 'TextAuto') {
365 // If $newvalue start with an equal sign we don't want it to be interpreted as a formula, so we add a '. Such transformation should be
366 // done by SetCellValueByColumnAndRow but it is not, so we do it ourself.
367 $newvalue = (dol_substr($newvalue, 0, 1) === '=' ? '\'' : '') . $newvalue;
368 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
369 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
370 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('@');
371 $this->workbook->getActiveSheet()->getStyle($coord)->getAlignment()->setHorizontal(PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
372 } else {
373 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
374 }
375 }
376 $this->col++;
377 }
378
379 // Complete with some columns to add columns with the labels of columns of type Select, so we have more then the ID
380 foreach ($selectlabelvalues as $key => $newvalue) {
381 $code = preg_replace('/_label$/', '', $key);
382 $typefield = isset($array_types[$code]) ? $array_types[$code] : '';
383
384 if (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i', $newvalue)) {
385 $newvalue = dol_stringtotime($newvalue);
386 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($newvalue));
387 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
388 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd');
389 } 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)) {
390 $newvalue = dol_stringtotime($newvalue);
391 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($newvalue));
392 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
393 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
394 } else {
395 if ($typefield == 'Text' || $typefield == 'TextAuto') {
396 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, (string) $newvalue);
397 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
398 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('@');
399 $this->workbook->getActiveSheet()->getStyle($coord)->getAlignment()->setHorizontal(PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
400 } else {
401 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
402 }
403 }
404 $this->col++;
405 }
406
407 $this->row++;
408 return 0;
409 }
410
411
412 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
419 public function write_footer($outputlangs)
420 {
421 // phpcs:enable
422 return 0;
423 }
424
425
426 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
432 public function close_file()
433 {
434 // phpcs:enable
435
436 $objWriter = new Xlsx($this->workbook);
437 $objWriter->save($this->file);
438 $this->workbook->disconnectWorksheets();
439 unset($this->workbook);
440
441 return 1;
442 }
443
444
445 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
452 public function excel_clean($newvalue)
453 {
454 // phpcs:enable
455 // Rule Dolibarr: No HTML
456 $newvalue = dol_string_nohtmltag($newvalue);
457
458 return $newvalue;
459 }
460
461
468 public function column2Letter($c)
469 {
470 $c = intval($c);
471 if ($c <= 0) {
472 return '';
473 }
474
475 $letter = '';
476 while ($c != 0) {
477 $p = ($c - 1) % 26;
478 $c = intval(($c - $p) / 26);
479 $letter = chr(65 + $p).$letter;
480 }
481
482 return $letter;
483 }
484
493 public function setCellValue($val, $startCell, $endCell = '')
494 {
495 try {
496 $this->workbook->getActiveSheet()->setCellValue($startCell, $val);
497
498 if (!empty($endCell)) {
499 $cellRange = $startCell.':'.$endCell;
500 $this->workbook->getActiveSheet()->mergeCells($startCell.':'.$endCell);
501 } else {
502 $cellRange = $startCell;
503 }
504 if (!empty($this->styleArray)) {
505 $this->workbook->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->styleArray);
506 }
507 } catch (Exception $e) {
508 $this->error = $e->getMessage();
509 return -1;
510 }
511 return 1;
512 }
513
521 public function setBorderStyle($thickness, $color)
522 {
523 $this->styleArray['borders'] = array(
524 'outline' => array(
525 'borderStyle' => $thickness,
526 'color' => array('argb' => $color)
527 )
528 );
529 return 1;
530 }
531
539 public function setFontStyle($bold, $color)
540 {
541 $this->styleArray['font'] = array(
542 'color' => array('argb' => $color),
543 'bold' => $bold
544 );
545 return 1;
546 }
547
554 public function setAlignmentStyle($horizontal)
555 {
556 $this->styleArray['alignment'] = array('horizontal' => $horizontal);
557 return 1;
558 }
559
564 public function resetStyle()
565 {
566 $this->styleArray = array();
567 return 1;
568 }
569
578 public function setBlock($startCell, $TDatas = array(), $boldTitle = false)
579 {
580 try {
581 if (!empty($TDatas)) {
582 $startCell = $this->workbook->getActiveSheet()->getCell($startCell);
583 $startColumn = Coordinate::columnIndexFromString($startCell->getColumn());
584 $startRow = $startCell->getRow();
585 foreach ($TDatas as $column => $TRows) {
586 if ($boldTitle) {
587 $this->setFontStyle(true, $this->styleArray['font']['color']['argb']);
588 }
589 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
590 $this->setCellValue($column, $cell->getCoordinate());
591 $rowPos = $startRow;
592 if ($boldTitle) {
593 $this->setFontStyle(false, $this->styleArray['font']['color']['argb']);
594 }
595 foreach ($TRows as $row) {
596 $rowPos++;
597 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $rowPos);
598 $this->setCellValue($row, $cell->getCoordinate());
599 }
600 $startColumn++;
601 }
602 }
603 } catch (Exception $e) {
604 $this->error = $e->getMessage();
605 return -1;
606 }
607 return 1;
608 }
609
618 public function setBlock2Columns($startCell, $TDatas = array(), $boldTitle = false)
619 {
620 try {
621 if (!empty($TDatas)) {
622 $startCell = $this->workbook->getActiveSheet()->getCell($startCell);
623 $startColumn = Coordinate::columnIndexFromString($startCell->getColumn());
624 $startRow = $startCell->getRow();
625 foreach ($TDatas as $title => $val) {
626 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
627 if ($boldTitle) {
628 $this->setFontStyle(true, $this->styleArray['font']['color']['argb']);
629 }
630 $this->setCellValue($title, $cell->getCoordinate());
631 if ($boldTitle) {
632 $this->setFontStyle(false, $this->styleArray['font']['color']['argb']);
633 }
634 $cell2 = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn + 1, $startRow);
635 $this->setCellValue($val, $cell2->getCoordinate());
636 $startRow++;
637 }
638 }
639 } catch (Exception $e) {
640 $this->error = $e->getMessage();
641 return -1;
642 }
643 return 1;
644 }
645
653 public function enableAutosize($firstColumn, $lastColumn)
654 {
655 foreach (range($firstColumn, $lastColumn) as $columnID) {
656 $this->workbook->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
657 }
658 return 1;
659 }
660
670 public function setMergeCellValueByLength($val, $startCell, $length, $offset = 0)
671 {
672 try {
673 $startCell = $this->workbook->getActiveSheet()->getCell($startCell);
674 $startColumn = Coordinate::columnIndexFromString($startCell->getColumn());
675 if (!empty($offset)) {
676 $startColumn += $offset;
677 }
678
679 $startRow = $startCell->getRow();
680 $startCell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
681 $startCoordinate = $startCell->getCoordinate();
682 $this->setCellValue($val, $startCell->getCoordinate());
683
684 $endCell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn + ($length - 1), $startRow);
685 $endCoordinate = $endCell->getCoordinate();
686 $this->workbook->getActiveSheet()->mergeCells($startCoordinate.':'.$endCoordinate);
687 } catch (Exception $e) {
688 $this->error = $e->getMessage();
689 return -1;
690 }
691 return $endCoordinate;
692 }
693}
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
getDriverLabelBis()
getDriverLabel
__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:427
dol_string_nohtmltag($stringtoclean, $removelinefeed=1, $pagecodeto='UTF-8', $strip_tags=0, $removedoublespaces=1)
Clean a string from all HTML tags and entities.
dol_substr($string, $start, $length=null, $stringencoding='', $trunconbytes=0)
Make a substring.
dol_print_error($db=null, $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.
a disabled