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') {
366 $newvalue = (
dol_substr($newvalue, 0, 1) ===
'=' ?
'\'' :
'') . $newvalue;
367 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
368 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
369 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode(
'@');
370 $this->workbook->getActiveSheet()->getStyle($coord)->getAlignment()->setHorizontal(PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
372 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
377 foreach ($selectlabelvalues as $key => $newvalue) {
378 if (preg_match(
'/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i', $newvalue)) {
380 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($newvalue));
381 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
382 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode(
'yyyy-mm-dd');
383 } 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)) {
385 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($newvalue));
386 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
387 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode(
'yyyy-mm-dd h:mm:ss');
389 if ($typefield ==
'Text' || $typefield ==
'TextAuto') {
390 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, (
string) $newvalue);
391 $coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
392 $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode(
'@');
393 $this->workbook->getActiveSheet()->getStyle($coord)->getAlignment()->setHorizontal(PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
395 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
430 $objWriter =
new Xlsx($this->workbook);
431 $objWriter->save($this->file);
432 $this->workbook->disconnectWorksheets();
433 unset($this->workbook);
473 $c = intval(($c - $p) / 26);
474 $letter = chr(65 + $p).$letter;
491 $this->workbook->getActiveSheet()->setCellValue($startCell, $val);
493 if (!empty($endCell)) {
494 $cellRange = $startCell.
':'.$endCell;
495 $this->workbook->getActiveSheet()->mergeCells($startCell.
':'.$endCell);
497 $cellRange = $startCell;
499 if (!empty($this->styleArray)) {
500 $this->workbook->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->styleArray);
503 $this->error = $e->getMessage();
518 $this->styleArray[
'borders'] = array(
520 'borderStyle' => $thickness,
521 'color' => array(
'argb' => $color)
536 $this->styleArray[
'font'] = array(
537 'color' => array(
'argb' => $color),
551 $this->styleArray[
'alignment'] = array(
'horizontal' => $horizontal);
561 $this->styleArray = array();
573 public function setBlock($startCell, $TDatas = array(), $boldTitle =
false)
576 if (!empty($TDatas)) {
577 $startCell = $this->workbook->getActiveSheet()->getCell($startCell);
578 $startColumn = Coordinate::columnIndexFromString($startCell->getColumn());
579 $startRow = $startCell->getRow();
580 foreach ($TDatas as $column => $TRows) {
582 $this->
setFontStyle(
true, $this->styleArray[
'font'][
'color'][
'argb']);
584 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
588 $this->
setFontStyle(
false, $this->styleArray[
'font'][
'color'][
'argb']);
590 foreach ($TRows as $row) {
592 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $rowPos);
599 $this->error = $e->getMessage();
616 if (!empty($TDatas)) {
617 $startCell = $this->workbook->getActiveSheet()->getCell($startCell);
618 $startColumn = Coordinate::columnIndexFromString($startCell->getColumn());
619 $startRow = $startCell->getRow();
620 foreach ($TDatas as $title => $val) {
621 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
623 $this->
setFontStyle(
true, $this->styleArray[
'font'][
'color'][
'argb']);
627 $this->
setFontStyle(
false, $this->styleArray[
'font'][
'color'][
'argb']);
629 $cell2 = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn + 1, $startRow);
635 $this->error = $e->getMessage();
650 foreach (range($firstColumn, $lastColumn) as $columnID) {
651 $this->workbook->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(
true);
668 $startCell = $this->workbook->getActiveSheet()->getCell($startCell);
669 $startColumn = Coordinate::columnIndexFromString($startCell->getColumn());
670 if (!empty($offset)) {
671 $startColumn += $offset;
674 $startRow = $startCell->getRow();
675 $startCell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
676 $startCoordinate = $startCell->getCoordinate();
679 $endCell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn + ($length - 1), $startRow);
680 $endCoordinate = $endCell->getCoordinate();
681 $this->workbook->getActiveSheet()->mergeCells($startCoordinate.
':'.$endCoordinate);
683 $this->error = $e->getMessage();
686 return $endCoordinate;