dolibarr  9.0.0
export_excel.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 <http://www.gnu.org/licenses/>.
17  */
18 
26 require_once DOL_DOCUMENT_ROOT.'/core/modules/export/modules_export.php';
27 require_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php';
28 
29 
34 {
38  public $id;
39 
43  public $label;
44 
45  public $extension;
46 
51  public $version = 'dolibarr';
52 
53  public $label_lib;
54 
55  public $version_lib;
56 
57  public $workbook; // Handle file
58 
59  public $worksheet; // Handle sheet
60 
61  public $row;
62 
63  public $col;
64 
65  public $file; // To save filename
66 
67 
73  function __construct($db)
74  {
75  global $conf, $langs;
76  $this->db = $db;
77 
78  $this->id='excel'; // Same value then xxx in file name export_xxx.modules.php
79  $this->label='Excel 95'; // Label of driver
80  $this->desc = $langs->trans('Excel95FormatDesc');
81  $this->extension='xls'; // Extension for generated file by this driver
82  $this->picto='mime/xls'; // Picto
83  $this->version='1.30'; // Driver version
84 
85  $this->disabled = (in_array(constant('PHPEXCEL_PATH'),array('disabled','disabled/'))?1:0); // A condition to disable module (used for native debian packages)
86 
87  if (empty($this->disabled))
88  {
89  // If driver use an external library, put its name here
90  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
91  {
92  require_once PHP_WRITEEXCEL_PATH.'class.writeexcel_workbookbig.inc.php';
93  require_once PHP_WRITEEXCEL_PATH.'class.writeexcel_worksheet.inc.php';
94  require_once PHP_WRITEEXCEL_PATH.'functions.writeexcel_utility.inc.php';
95  $this->label_lib='PhpWriteExcel';
96  $this->version_lib='unknown';
97  }
98  else
99  {
100  require_once PHPEXCEL_PATH.'PHPExcel.php';
101  require_once PHPEXCEL_PATH.'PHPExcel/Style/Alignment.php';
102  $this->label_lib='PhpExcel';
103  $this->version_lib='1.8.0'; // No way to get info from library
104  }
105  }
106 
107  $this->row=0;
108  }
109 
115  function getDriverId()
116  {
117  return $this->id;
118  }
119 
125  function getDriverLabel()
126  {
127  return $this->label;
128  }
129 
135  function getDriverDesc()
136  {
137  return $this->desc;
138  }
139 
146  {
147  return $this->extension;
148  }
149 
155  function getDriverVersion()
156  {
157  return $this->version;
158  }
159 
165  function getLibLabel()
166  {
167  return $this->label_lib;
168  }
169 
175  function getLibVersion()
176  {
177  return $this->version_lib;
178  }
179 
180 
181  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
189  function open_file($file,$outputlangs)
190  {
191  // phpcs:enable
192  global $user,$conf,$langs;
193 
194  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
195  {
196  $outputlangs->charset_output='ISO-8859-1'; // Because Excel 5 format is ISO
197  }
198 
199  dol_syslog(get_class($this)."::open_file file=".$file);
200  $this->file=$file;
201 
202  $ret=1;
203 
204  $outputlangs->load("exports");
205  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
206  {
207  require_once PHP_WRITEEXCEL_PATH.'class.writeexcel_workbookbig.inc.php';
208  require_once PHP_WRITEEXCEL_PATH.'class.writeexcel_worksheet.inc.php';
209  require_once PHP_WRITEEXCEL_PATH.'functions.writeexcel_utility.inc.php';
210  $this->workbook = new writeexcel_workbookbig($file);
211  $this->workbook->set_tempdir($conf->export->dir_temp); // Set temporary directory
212  $this->workbook->set_sheetname($outputlangs->trans("Sheet"));
213  $this->worksheet = &$this->workbook->addworksheet();
214  }
215  else
216  {
217  require_once PHPEXCEL_PATH.'PHPExcel.php';
218  require_once PHPEXCEL_PATH.'PHPExcel/Style/Alignment.php';
219 
220  if ($this->id == 'excel2007')
221  {
222  if (! class_exists('ZipArchive')) // For Excel2007, PHPExcel need ZipArchive
223  {
224  $langs->load("errors");
225  $this->error=$langs->trans('ErrorPHPNeedModule','zip');
226  return -1;
227  }
228  }
229 
230  if (!empty($conf->global->MAIN_USE_FILECACHE_EXPORT_EXCEL_DIR)) {
231  $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
232  $cacheSettings = array (
233  'dir' => $conf->global->MAIN_USE_FILECACHE_EXPORT_EXCEL_DIR
234  );
235  PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
236  }
237 
238  $this->workbook = new PHPExcel();
239  $this->workbook->getProperties()->setCreator($user->getFullName($outputlangs).' - '.DOL_APPLICATION_TITLE.' '.DOL_VERSION);
240  //$this->workbook->getProperties()->setLastModifiedBy('Dolibarr '.DOL_VERSION);
241  $this->workbook->getProperties()->setTitle(basename($file));
242  $this->workbook->getProperties()->setSubject(basename($file));
243  $this->workbook->getProperties()->setDescription(DOL_APPLICATION_TITLE.' '.DOL_VERSION);
244 
245  $this->workbook->setActiveSheetIndex(0);
246  $this->workbook->getActiveSheet()->setTitle($outputlangs->trans("Sheet"));
247  $this->workbook->getActiveSheet()->getDefaultRowDimension()->setRowHeight(16);
248  }
249  return $ret;
250  }
251 
252  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
259  function write_header($outputlangs)
260  {
261  // phpcs:enable
262  //$outputlangs->charset_output='ISO-8859-1'; // Because Excel 5 format is ISO
263 
264  return 0;
265  }
266 
267 
268  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
278  function write_title($array_export_fields_label,$array_selected_sorted,$outputlangs,$array_types)
279  {
280  // phpcs:enable
281  global $conf;
282 
283  // Create a format for the column headings
284  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
285  {
286  $outputlangs->charset_output='ISO-8859-1'; // Because Excel 5 format is ISO
287 
288  $formatheader =$this->workbook->addformat();
289  $formatheader->set_bold();
290  $formatheader->set_color('blue');
291  //$formatheader->set_size(12);
292  //$formatheader->set_font("Courier New");
293  //$formatheader->set_align('center');
294  }
295  else
296  {
297  $this->workbook->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
298  $this->workbook->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
299  }
300 
301  $this->col=0;
302  foreach($array_selected_sorted as $code => $value)
303  {
304  $alias=$array_export_fields_label[$code];
305  //print "dd".$alias;
306  if (empty($alias)) dol_print_error('','Bad value for field with code='.$code.'. Try to redefine export.');
307  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
308  {
309  $this->worksheet->write($this->row, $this->col, $outputlangs->transnoentities($alias), $formatheader);
310  }
311  else
312  {
313  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row+1, $outputlangs->transnoentities($alias));
314  if (! empty($array_types[$code]) && in_array($array_types[$code],array('Date','Numeric','TextAuto'))) // Set autowidth for some types
315  {
316  $this->workbook->getActiveSheet()->getColumnDimension($this->column2Letter($this->col + 1))->setAutoSize(true);
317  }
318  }
319  $this->col++;
320  }
321  $this->row++;
322  return 0;
323  }
324 
325  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
335  function write_record($array_selected_sorted,$objp,$outputlangs,$array_types)
336  {
337  // phpcs:enable
338  global $conf;
339 
340  // Create a format for the column headings
341  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
342  {
343  $outputlangs->charset_output='ISO-8859-1'; // Because Excel 5 format is ISO
344  }
345 
346  // Define first row
347  $this->col=0;
348 
349  foreach($array_selected_sorted as $code => $value)
350  {
351  if (strpos($code,' as ') == 0) $alias=str_replace(array('.','-','(',')'),'_',$code);
352  else $alias=substr($code, strpos($code, ' as ') + 4);
353  if (empty($alias)) dol_print_error('','Bad value for field with code='.$code.'. Try to redefine export.');
354  $newvalue=$objp->$alias;
355 
356  $newvalue=$this->excel_clean($newvalue);
357  $typefield=isset($array_types[$code])?$array_types[$code]:'';
358 
359  if (preg_match('/^Select:/i', $typefield, $reg) && $typefield = substr($typefield, 7))
360  {
361  $array = unserialize($typefield);
362  $array = $array['options'];
363  $newvalue = $array[$newvalue];
364  }
365 
366  // Traduction newvalue
367  if (preg_match('/^\((.*)\)$/i',$newvalue,$reg))
368  {
369  $newvalue=$outputlangs->transnoentities($reg[1]);
370  }
371  else
372  {
373  $newvalue=$outputlangs->convToOutputCharset($newvalue);
374  }
375 
376  if (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i',$newvalue))
377  {
378  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
379  {
380  $formatdate=$this->workbook->addformat();
381  $formatdate->set_num_format('yyyy-mm-dd');
382  //$formatdate->set_num_format(0x0f);
383  $arrayvalue=preg_split('/[.,]/',xl_parse_date($newvalue));
384  //print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
385  $newvalue=strval($arrayvalue[0]).'.'.strval($arrayvalue[1]); // $newvalue=strval(36892.521); directly does not work because . will be convert into , later
386  $this->worksheet->write($this->row, $this->col, $newvalue, PHPExcel_Shared_Date::PHPToExcel($formatdate));
387  }
388  else
389  {
390  $newvalue=dol_stringtotime($newvalue);
391  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row+1, PHPExcel_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');
394  }
395  }
396  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))
397  {
398  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
399  {
400  $formatdatehour=$this->workbook->addformat();
401  $formatdatehour->set_num_format('yyyy-mm-dd hh:mm:ss');
402  //$formatdatehour->set_num_format(0x0f);
403  $arrayvalue=preg_split('/[.,]/',xl_parse_date($newvalue));
404  //print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
405  $newvalue=strval($arrayvalue[0]).'.'.strval($arrayvalue[1]); // $newvalue=strval(36892.521); directly does not work because . will be convert into , later
406  $this->worksheet->write($this->row, $this->col, $newvalue, $formatdatehour);
407  }
408  else
409  {
410  $newvalue=dol_stringtotime($newvalue);
411  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row+1, PHPExcel_Shared_Date::PHPToExcel($newvalue));
412  $coord=$this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row+1)->getCoordinate();
413  $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
414  }
415  }
416  else
417  {
418  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
419  {
420  $this->worksheet->write($this->row, $this->col, $newvalue);
421  }
422  else
423  {
424  if ($typefield == 'Text' || $typefield == 'TextAuto')
425  {
426  //$this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row+1)->setValueExplicit($newvalue, PHPExcel_Cell_DataType::TYPE_STRING);
427  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row+1, (string) $newvalue);
428  $coord=$this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row+1)->getCoordinate();
429  $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('@');
430  $this->workbook->getActiveSheet()->getStyle($coord)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
431  }
432  else
433  {
434  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row+1, $newvalue);
435  }
436  }
437  }
438  $this->col++;
439  }
440  $this->row++;
441  return 0;
442  }
443 
444 
445  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
452  function write_footer($outputlangs)
453  {
454  // phpcs:enable
455  return 0;
456  }
457 
458 
459  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
465  function close_file()
466  {
467  // phpcs:enable
468  global $conf;
469 
470  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
471  {
472  $this->workbook->close();
473  }
474  else
475  {
476  require_once PHPEXCEL_PATH.'PHPExcel/Writer/Excel5.php';
477  $objWriter = new PHPExcel_Writer_Excel5($this->workbook);
478  $objWriter->save($this->file);
479  $this->workbook->disconnectWorksheets();
480  unset($this->workbook);
481  }
482  return 1;
483  }
484 
485 
486  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
493  function excel_clean($newvalue)
494  {
495  // phpcs:enable
496  // Rule Dolibarr: No HTML
497  $newvalue=dol_string_nohtmltag($newvalue);
498 
499  return $newvalue;
500  }
501 
502 
509  function column2Letter($c)
510  {
511 
512  $c = intval($c);
513  if ($c <= 0) return '';
514 
515  while ($c != 0)
516  {
517  $p = ($c - 1) % 26;
518  $c = intval(($c - $p) / 26);
519  $letter = chr(65 + $p) . $letter;
520  }
521 
522  return $letter;
523  }
524 }
column2Letter($c)
Convert a column to letter (1->A, 0->B, 27->AA, ...)
print $object label
hash of file content (md5_file(dol_osencode($destfull))
Definition: edit.php:153
Parent class for export modules.
write_header($outputlangs)
Write header.
write_title($array_export_fields_label, $array_selected_sorted, $outputlangs, $array_types)
Output title line into file.
getDriverDesc()
getDriverDesc
getDriverLabel()
getDriverLabel
write_record($array_selected_sorted, $objp, $outputlangs, $array_types)
Output record line into file.
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:295
dol_print_error($db='', $error='', $errors=null)
Affiche message erreur system avec toutes les informations pour faciliter le diagnostic et la remonte...
__construct($db)
Constructor.
getDriverVersion()
getDriverVersion
getDriverId()
getDriverId
excel_clean($newvalue)
Clean a cell to respect rules of Excel file cells.
write_footer($outputlangs)
Write footer.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
getLibVersion()
getLibVersion
Class to build export files with Excel format.
getLibLabel()
getLibLabel
getDriverExtension()
getDriverExtension
dol_string_nohtmltag($stringtoclean, $removelinefeed=1, $pagecodeto='UTF-8', $strip_tags=0)
Clean a string from all HTML tags and entities.
$version
Dolibarr version of the loaded document string.
open_file($file, $outputlangs)
Open output file.
close_file()
Close Excel file.