198 global $user, $langs;
200 dol_syslog(get_class($this).
"::open_file file=".$file);
205 $outputlangs->load(
"exports");
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';
211 if ($this->
id ==
'excel2007') {
212 if (!class_exists(
'ZipArchive')) {
213 $langs->load(
"errors");
214 $this->error = $langs->trans(
'ErrorPHPNeedModule',
'zip');
219 $this->workbook =
new Spreadsheet();
220 $this->workbook->getProperties()->setCreator($user->getFullName($outputlangs).
' - '.DOL_APPLICATION_TITLE.
' '.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);
226 $this->workbook->setActiveSheetIndex(0);
227 $this->workbook->getActiveSheet()->setTitle($outputlangs->trans(
"Sheet"));
228 $this->workbook->getActiveSheet()->getDefaultRowDimension()->setRowHeight(16);
259 public function write_title($array_export_fields_label, $array_selected_sorted, $outputlangs, $array_types)
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();
270 foreach ($array_selected_sorted as $code => $value) {
271 $alias = $array_export_fields_label[$code];
274 dol_print_error(
null,
'Bad value for field with code='.$code.
'. Try to redefine export.');
276 $typefield = isset($array_types[$code]) ? $array_types[$code] :
'';
278 if (preg_match(
'/^Select:/i', $typefield) && $typefield = substr($typefield, 7)) {
279 $selectlabel[$code.
"_label"] = $alias.
"_label";
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'))) {
283 $this->workbook->getActiveSheet()->getColumnDimension($this->
column2Letter($this->col + 1))->setAutoSize(
true);
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'))) {
293 $this->workbook->getActiveSheet()->getColumnDimension($this->
column2Letter($this->col + 1))->setAutoSize(
true);
312 public function write_record($array_selected_sorted, $objp, $outputlangs, $array_types)
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(
null,
'Bad value for field with code='.$code.
'. Try to redefine export.');
331 $newvalue = !empty($objp->$alias) ? $objp->$alias :
'';
334 $typefield = isset($array_types[$code]) ? $array_types[$code] :
'';
336 if (preg_match(
'/^Select:/i', $typefield) && $typefield = substr($typefield, 7)) {
338 if (is_array($array) && !empty($newvalue)) {
339 $array = $array[
'options'];
340 $selectlabelvalues[$code.
"_label"] = $array[$newvalue];
342 $selectlabelvalues[$code.
"_label"] =
"";
347 if (preg_match(
'/^\((.*)\)$/i', $newvalue, $reg)) {
348 $newvalue = $outputlangs->transnoentities($reg[1]);
350 $newvalue = $outputlangs->convToOutputCharset($newvalue);
353 if (preg_match(
'/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i', $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)) {
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');
364 if ($typefield ==
'Text' || $typefield ==
'TextAuto') {
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);
373 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
380 foreach ($selectlabelvalues as $key => $newvalue) {
381 $code = preg_replace(
'/_label$/',
'', $key);
382 $typefield = isset($array_types[$code]) ? $array_types[$code] :
'';
384 if (preg_match(
'/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i', $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)) {
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');
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);
401 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
578 public function setBlock($startCell, $TDatas = array(), $boldTitle =
false)
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) {
587 $this->
setFontStyle(
true, $this->styleArray[
'font'][
'color'][
'argb']);
589 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
593 $this->
setFontStyle(
false, $this->styleArray[
'font'][
'color'][
'argb']);
595 foreach ($TRows as $row) {
597 $cell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $rowPos);
604 $this->error = $e->getMessage();
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);
628 $this->
setFontStyle(
true, $this->styleArray[
'font'][
'color'][
'argb']);
632 $this->
setFontStyle(
false, $this->styleArray[
'font'][
'color'][
'argb']);
634 $cell2 = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn + 1, $startRow);
640 $this->error = $e->getMessage();
673 $startCell = $this->workbook->getActiveSheet()->getCell($startCell);
674 $startColumn = Coordinate::columnIndexFromString($startCell->getColumn());
675 if (!empty($offset)) {
676 $startColumn += $offset;
679 $startRow = $startCell->getRow();
680 $startCell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn, $startRow);
681 $startCoordinate = $startCell->getCoordinate();
684 $endCell = $this->workbook->getActiveSheet()->getCellByColumnAndRow($startColumn + ($length - 1), $startRow);
685 $endCoordinate = $endCell->getCoordinate();
686 $this->workbook->getActiveSheet()->mergeCells($startCoordinate.
':'.$endCoordinate);
688 $this->error = $e->getMessage();
691 return $endCoordinate;