25 require_once DOL_DOCUMENT_ROOT.
'/core/modules/export/modules_export.php';
26 require_once DOL_DOCUMENT_ROOT.
'/core/lib/date.lib.php';
28 use PhpOffice\PhpSpreadsheet\Spreadsheet;
29 use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
30 use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
53 public $version =
'dolibarr';
82 $this->
id =
'excel2007';
83 $this->label =
'Excel 2007';
84 $this->desc = $langs->trans(
'Excel2007FormatDesc');
85 $this->extension =
'xlsx';
86 $this->picto =
'mime/xls';
87 $this->version =
'1.30';
88 $this->phpmin = array(7, 0);
92 if (empty($this->disabled)) {
93 require_once PHPEXCELNEW_PATH.
'Spreadsheet.php';
94 $this->label_lib =
'PhpSpreadSheet';
95 $this->version_lib =
'1.12.0';
138 return $this->extension;
148 return $this->version;
158 return $this->label_lib;
168 return $this->version_lib;
183 global $user, $conf, $langs;
185 if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
186 $outputlangs->charset_output =
'ISO-8859-1';
189 dol_syslog(get_class($this).
"::open_file file=".$file);
194 $outputlangs->load(
"exports");
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';
200 if ($this->
id ==
'excel2007') {
201 if (!class_exists(
'ZipArchive')) {
202 $langs->load(
"errors");
203 $this->error = $langs->trans(
'ErrorPHPNeedModule',
'zip');
208 $this->workbook =
new Spreadsheet();
209 $this->workbook->getProperties()->setCreator($user->getFullName($outputlangs).
' - '.DOL_APPLICATION_TITLE.
' '.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);
215 $this->workbook->setActiveSheetIndex(0);
216 $this->workbook->getActiveSheet()->setTitle($outputlangs->trans(
"Sheet"));
217 $this->workbook->getActiveSheet()->getDefaultRowDimension()->setRowHeight(16);
248 public function write_title($array_export_fields_label, $array_selected_sorted, $outputlangs, $array_types)
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();
259 if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
262 foreach ($array_selected_sorted as $code => $value) {
263 $alias = $array_export_fields_label[$code];
266 dol_print_error(
'',
'Bad value for field with code='.$code.
'. Try to redefine export.');
268 $typefield = isset($array_types[$code]) ? $array_types[$code] :
'';
270 if (preg_match(
'/^Select:/i', $typefield) && $typefield = substr($typefield, 7)) {
271 $selectlabel[$code.
"_label"] = $alias.
"_label";
273 if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
274 $this->worksheet->write($this->row, $this->col, $outputlangs->transnoentities($alias), $formatheader);
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'))) {
278 $this->workbook->getActiveSheet()->getColumnDimension($this->
column2Letter($this->col + 1))->setAutoSize(
true);
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);
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'))) {
289 $this->workbook->getActiveSheet()->getColumnDimension($this->
column2Letter($this->col + 1))->setAutoSize(
true);
308 public function write_record($array_selected_sorted, $objp, $outputlangs, $array_types)
315 if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
320 $selectlabelvalues = array();
321 foreach ($array_selected_sorted as $code => $value) {
322 if (strpos($code,
' as ') == 0) {
323 $alias = str_replace(array(
'.',
'-',
'(',
')'),
'_', $code);
325 $alias = substr($code, strpos($code,
' as ') + 4);
328 dol_print_error(
'',
'Bad value for field with code='.$code.
'. Try to redefine export.');
330 $newvalue = $objp->$alias;
333 $typefield = isset($array_types[$code]) ? $array_types[$code] :
'';
335 if (preg_match(
'/^Select:/i', $typefield) && $typefield = substr($typefield, 7)) {
337 if (is_array($array) && !empty($newvalue)) {
338 $array = $array[
'options'];
339 $selectlabelvalues[$code.
"_label"] = $array[$newvalue];
341 $selectlabelvalues[$code.
"_label"] =
"";
346 if (preg_match(
'/^\((.*)\)$/i', $newvalue, $reg)) {
347 $newvalue = $outputlangs->transnoentities($reg[1]);
349 $newvalue = $outputlangs->convToOutputCharset($newvalue);
352 if (preg_match(
'/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i', $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)) {
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');
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);
369 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
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)) {
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)) {
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');
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);
392 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
427 $objWriter =
new Xlsx($this->workbook);
428 $objWriter->save($this->file);
429 $this->workbook->disconnectWorksheets();
430 unset($this->workbook);
470 $c = intval(($c - $p) / 26);
471 $letter = chr(65 + $p).$letter;
488 $this->workbook->getActiveSheet()->setCellValue($startCell, $val);
490 if (!empty($endCell)) {
491 $cellRange = $startCell.
':'.$endCell;
492 $this->workbook->getActiveSheet()->mergeCells($startCell.
':'.$endCell);
494 $cellRange = $startCell;
496 if (!empty($this->styleArray)) {
497 $this->workbook->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->styleArray);
500 $this->error = $e->getMessage();
515 $this->styleArray[
'borders'] = array(
517 'borderStyle' => $thickness,
518 'color' => array(
'argb' => $color)
533 $this->styleArray[
'font'] = array(
534 'color' => array(
'argb' => $color),
548 $this->styleArray[
'alignment'] = array(
'horizontal' => $horizontal);
558 $this->styleArray = array();
570 public function setBlock($startCell, $TDatas = array(), $boldTitle =
false)
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) {
579 $this->
setFontStyle(
true, $this->styleArray[
'font'][
'color'][
'argb']);
581 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
585 $this->
setFontStyle(
false, $this->styleArray[
'font'][
'color'][
'argb']);
587 foreach ($TRows as $row) {
589 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $rowPos);
596 $this->error = $e->getMessage();
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);
620 $this->
setFontStyle(
true, $this->styleArray[
'font'][
'color'][
'argb']);
624 $this->
setFontStyle(
false, $this->styleArray[
'font'][
'color'][
'argb']);
626 $cell2 = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn + 1, $startRow);
632 $this->error = $e->getMessage();
647 foreach (range($firstColumn, $lastColumn) as $columnID) {
648 $this->workbook->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(
true);
665 $startCell = $this->workbook->getActiveSheet()->getCell($startCell);
666 $startColumn = Coordinate::columnIndexFromString($startCell->getColumn());
667 if (!empty($offset)) {
668 $startColumn += $offset;
671 $startRow = $startCell->getRow();
672 $startCell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
673 $startCoordinate = $startCell->getCoordinate();
676 $endCell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn + ($length - 1), $startRow);
677 $endCoordinate = $endCell->getCoordinate();
678 $this->workbook->getActiveSheet()->mergeCells($startCoordinate.
':'.$endCoordinate);
680 $this->error = $e->getMessage();
683 return $endCoordinate;