dolibarr  7.0.0-beta
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 {
35  var $id;
36  var $label;
37  var $extension;
38  var $version;
39 
40  var $label_lib;
41  var $version_lib;
42 
43  var $workbook; // Handle fichier
44  var $worksheet; // Handle onglet
45  var $row;
46  var $col;
47  var $file; // To save filename
48 
49 
55  function __construct($db)
56  {
57  global $conf, $langs;
58  $this->db = $db;
59 
60  $this->id='excel'; // Same value then xxx in file name export_xxx.modules.php
61  $this->label='Excel 95'; // Label of driver
62  $this->desc = $langs->trans('Excel95FormatDesc');
63  $this->extension='xls'; // Extension for generated file by this driver
64  $this->picto='mime/xls'; // Picto
65  $this->version='1.30'; // Driver version
66 
67  $this->disabled = (in_array(constant('PHPEXCEL_PATH'),array('disabled','disabled/'))?1:0); // A condition to disable module (used for native debian packages)
68 
69  if (empty($this->disabled))
70  {
71  // If driver use an external library, put its name here
72  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
73  {
74  require_once PHP_WRITEEXCEL_PATH.'class.writeexcel_workbookbig.inc.php';
75  require_once PHP_WRITEEXCEL_PATH.'class.writeexcel_worksheet.inc.php';
76  require_once PHP_WRITEEXCEL_PATH.'functions.writeexcel_utility.inc.php';
77  $this->label_lib='PhpWriteExcel';
78  $this->version_lib='unknown';
79  }
80  else
81  {
82  require_once PHPEXCEL_PATH.'PHPExcel.php';
83  require_once PHPEXCEL_PATH.'PHPExcel/Style/Alignment.php';
84  $this->label_lib='PhpExcel';
85  $this->version_lib='1.8.0'; // No way to get info from library
86  }
87  }
88 
89  $this->row=0;
90  }
91 
97  function getDriverId()
98  {
99  return $this->id;
100  }
101 
107  function getDriverLabel()
108  {
109  return $this->label;
110  }
111 
117  function getDriverDesc()
118  {
119  return $this->desc;
120  }
121 
128  {
129  return $this->extension;
130  }
131 
137  function getDriverVersion()
138  {
139  return $this->version;
140  }
141 
147  function getLibLabel()
148  {
149  return $this->label_lib;
150  }
151 
157  function getLibVersion()
158  {
159  return $this->version_lib;
160  }
161 
162 
170  function open_file($file,$outputlangs)
171  {
172  global $user,$conf,$langs;
173 
174  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
175  {
176  $outputlangs->charset_output='ISO-8859-1'; // Because Excel 5 format is ISO
177  }
178 
179  dol_syslog(get_class($this)."::open_file file=".$file);
180  $this->file=$file;
181 
182  $ret=1;
183 
184  $outputlangs->load("exports");
185  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
186  {
187  require_once PHP_WRITEEXCEL_PATH.'class.writeexcel_workbookbig.inc.php';
188  require_once PHP_WRITEEXCEL_PATH.'class.writeexcel_worksheet.inc.php';
189  require_once PHP_WRITEEXCEL_PATH.'functions.writeexcel_utility.inc.php';
190  $this->workbook = new writeexcel_workbookbig($file);
191  $this->workbook->set_tempdir($conf->export->dir_temp); // Set temporary directory
192  $this->workbook->set_sheetname($outputlangs->trans("Sheet"));
193  $this->worksheet = &$this->workbook->addworksheet();
194  }
195  else
196  {
197  require_once PHPEXCEL_PATH.'PHPExcel.php';
198  require_once PHPEXCEL_PATH.'PHPExcel/Style/Alignment.php';
199 
200  if ($this->id == 'excel2007')
201  {
202  if (! class_exists('ZipArchive')) // For Excel2007, PHPExcel need ZipArchive
203  {
204  $langs->load("errors");
205  $this->error=$langs->trans('ErrorPHPNeedModule','zip');
206  return -1;
207  }
208  }
209 
210  if (!empty($conf->global->MAIN_USE_FILECACHE_EXPORT_EXCEL_DIR)) {
211  $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
212  $cacheSettings = array (
213  'dir' => $conf->global->MAIN_USE_FILECACHE_EXPORT_EXCEL_DIR
214  );
215  PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
216  }
217 
218  $this->workbook = new PHPExcel();
219  $this->workbook->getProperties()->setCreator($user->getFullName($outputlangs).' - Dolibarr '.DOL_VERSION);
220  //$this->workbook->getProperties()->setLastModifiedBy('Dolibarr '.DOL_VERSION);
221  $this->workbook->getProperties()->setTitle($outputlangs->trans("Export").' - '.$file);
222  $this->workbook->getProperties()->setSubject($outputlangs->trans("Export").' - '.$file);
223  $this->workbook->getProperties()->setDescription($outputlangs->trans("Export").' - '.$file);
224 
225  $this->workbook->setActiveSheetIndex(0);
226  $this->workbook->getActiveSheet()->setTitle($outputlangs->trans("Sheet"));
227  $this->workbook->getActiveSheet()->getDefaultRowDimension()->setRowHeight(16);
228  }
229  return $ret;
230  }
231 
238  function write_header($outputlangs)
239  {
240  //$outputlangs->charset_output='ISO-8859-1'; // Because Excel 5 format is ISO
241 
242  return 0;
243  }
244 
245 
255  function write_title($array_export_fields_label,$array_selected_sorted,$outputlangs,$array_types)
256  {
257  global $conf;
258 
259  // Create a format for the column headings
260  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
261  {
262  $outputlangs->charset_output='ISO-8859-1'; // Because Excel 5 format is ISO
263 
264  $formatheader =$this->workbook->addformat();
265  $formatheader->set_bold();
266  $formatheader->set_color('blue');
267  //$formatheader->set_size(12);
268  //$formatheader->set_font("Courier New");
269  //$formatheader->set_align('center');
270  }
271  else
272  {
273  $this->workbook->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
274  $this->workbook->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
275  }
276 
277  $this->col=0;
278  foreach($array_selected_sorted as $code => $value)
279  {
280  $alias=$array_export_fields_label[$code];
281  //print "dd".$alias;
282  if (empty($alias)) dol_print_error('','Bad value for field with code='.$code.'. Try to redefine export.');
283  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
284  {
285  $this->worksheet->write($this->row, $this->col, $outputlangs->transnoentities($alias), $formatheader);
286  }
287  else
288  {
289  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row+1, $outputlangs->transnoentities($alias));
290  if (! empty($array_types[$code]) && in_array($array_types[$code],array('Date','Numeric','TextAuto'))) // Set autowidth for some types
291  {
292  $this->workbook->getActiveSheet()->getColumnDimension($this->column2Letter($this->col + 1))->setAutoSize(true);
293  }
294  }
295  $this->col++;
296  }
297  $this->row++;
298  return 0;
299  }
300 
310  function write_record($array_selected_sorted,$objp,$outputlangs,$array_types)
311  {
312  global $conf;
313 
314  // Create a format for the column headings
315  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
316  {
317  $outputlangs->charset_output='ISO-8859-1'; // Because Excel 5 format is ISO
318  }
319 
320  // Define first row
321  $this->col=0;
322 
323  foreach($array_selected_sorted as $code => $value)
324  {
325  if (strpos($code,' as ') == 0) $alias=str_replace(array('.','-','(',')'),'_',$code);
326  else $alias=substr($code, strpos($code, ' as ') + 4);
327  if (empty($alias)) dol_print_error('','Bad value for field with code='.$code.'. Try to redefine export.');
328  $newvalue=$objp->$alias;
329 
330  $newvalue=$this->excel_clean($newvalue);
331  $typefield=isset($array_types[$code])?$array_types[$code]:'';
332 
333  if (preg_match('/^Select:/i', $typefield, $reg) && $typefield = substr($typefield, 7))
334  {
335  $array = unserialize($typefield);
336  $array = $array['options'];
337  $newvalue = $array[$newvalue];
338  }
339 
340  // Traduction newvalue
341  if (preg_match('/^\((.*)\)$/i',$newvalue,$reg))
342  {
343  $newvalue=$outputlangs->transnoentities($reg[1]);
344  }
345  else
346  {
347  $newvalue=$outputlangs->convToOutputCharset($newvalue);
348  }
349 
350  if (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i',$newvalue))
351  {
352  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
353  {
354  $formatdate=$this->workbook->addformat();
355  $formatdate->set_num_format('yyyy-mm-dd');
356  //$formatdate->set_num_format(0x0f);
357  $arrayvalue=preg_split('/[.,]/',xl_parse_date($newvalue));
358  //print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
359  $newvalue=strval($arrayvalue[0]).'.'.strval($arrayvalue[1]); // $newvalue=strval(36892.521); directly does not work because . will be convert into , later
360  $this->worksheet->write($this->row, $this->col, $newvalue, PHPExcel_Shared_Date::PHPToExcel($formatdate));
361  }
362  else
363  {
364  $newvalue=dol_stringtotime($newvalue);
365  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row+1, PHPExcel_Shared_Date::PHPToExcel($newvalue));
366  $coord=$this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row+1)->getCoordinate();
367  $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd');
368  }
369  }
370  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))
371  {
372  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
373  {
374  $formatdatehour=$this->workbook->addformat();
375  $formatdatehour->set_num_format('yyyy-mm-dd hh:mm:ss');
376  //$formatdatehour->set_num_format(0x0f);
377  $arrayvalue=preg_split('/[.,]/',xl_parse_date($newvalue));
378  //print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
379  $newvalue=strval($arrayvalue[0]).'.'.strval($arrayvalue[1]); // $newvalue=strval(36892.521); directly does not work because . will be convert into , later
380  $this->worksheet->write($this->row, $this->col, $newvalue, $formatdatehour);
381  }
382  else
383  {
384  $newvalue=dol_stringtotime($newvalue);
385  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row+1, PHPExcel_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');
388  }
389  }
390  else
391  {
392  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
393  {
394  $this->worksheet->write($this->row, $this->col, $newvalue);
395  }
396  else
397  {
398  if ($typefield == 'Text' || $typefield == 'TextAuto')
399  {
400  //$this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row+1)->setValueExplicit($newvalue, PHPExcel_Cell_DataType::TYPE_STRING);
401  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row+1, (string) $newvalue);
402  $coord=$this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row+1)->getCoordinate();
403  $this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('@');
404  $this->workbook->getActiveSheet()->getStyle($coord)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
405  }
406  else
407  {
408  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row+1, $newvalue);
409  }
410  }
411  }
412  $this->col++;
413  }
414  $this->row++;
415  return 0;
416  }
417 
418 
425  function write_footer($outputlangs)
426  {
427  return 0;
428  }
429 
430 
436  function close_file()
437  {
438  global $conf;
439 
440  if (! empty($conf->global->MAIN_USE_PHP_WRITEEXCEL))
441  {
442  $this->workbook->close();
443  }
444  else
445  {
446  require_once PHPEXCEL_PATH.'PHPExcel/Writer/Excel5.php';
447  $objWriter = new PHPExcel_Writer_Excel5($this->workbook);
448  $objWriter->save($this->file);
449  $this->workbook->disconnectWorksheets();
450  unset($this->workbook);
451  }
452  return 1;
453  }
454 
455 
462  function excel_clean($newvalue)
463  {
464  // Rule Dolibarr: No HTML
465  $newvalue=dol_string_nohtmltag($newvalue);
466 
467  return $newvalue;
468  }
469 
470 
477  function column2Letter($c)
478  {
479 
480  $c = intval($c);
481  if ($c <= 0) return '';
482 
483  while ($c != 0)
484  {
485  $p = ($c - 1) % 26;
486  $c = intval(($c - $p) / 26);
487  $letter = chr(65 + $p) . $letter;
488  }
489 
490  return $letter;
491  }
492 }
493 
column2Letter($c)
Convert a column to letter (1->A, 0->B, 27->AA, ...)
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:286
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')
Clean a string from all HTML tags and entities.
open_file($file, $outputlangs)
Open output file.
close_file()
Close Excel file.