/**
* Output record line into file
*
* @param array $array_selected_sorted Array with list of field to export
* @param resource $objp A record from a fetch with all fields from select
* @param Translate $outputlangs Object lang to translate values
* @param array $array_types Array with types of fields
* @return int <0 if KO, >0 if OK
*/
function write_record($array_selected_sorted, $objp, $outputlangs, $array_types)
{
global $conf;
// Create a format for the column headings
if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
$outputlangs->charset_output = 'ISO-8859-1';
// Because Excel 5 format is ISO
}
// Define first row
$this->col = 0;
foreach ($array_selected_sorted as $code => $value) {
if (strpos($code, ' as ') == 0) {
$alias = str_replace(array('.', '-'), '_', $code);
} else {
$alias = substr($code, strpos($code, ' as ') + 4);
}
if (empty($alias)) {
dol_print_error('', 'Bad value for field with code=' . $code . '. Try to redefine export.');
}
$newvalue = $objp->{$alias};
$newvalue = $this->excel_clean($newvalue);
$typefield = isset($array_types[$code]) ? $array_types[$code] : '';
// Traduction newvalue
if (preg_match('/^\\((.*)\\)$/i', $newvalue, $reg)) {
$newvalue = $outputlangs->transnoentities($reg[1]);
} else {
$newvalue = $outputlangs->convToOutputCharset($newvalue);
}
if (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i', $newvalue)) {
if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
$formatdate = $this->workbook->addformat();
$formatdate->set_num_format('yyyy-mm-dd');
//$formatdate->set_num_format(0x0f);
$arrayvalue = preg_split('/[.,]/', xl_parse_date($newvalue));
//print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
$newvalue = strval($arrayvalue[0]) . '.' . strval($arrayvalue[1]);
// $newvalue=strval(36892.521); directly does not work because . will be convert into , later
$this->worksheet->write($this->row, $this->col, $newvalue, PHPExcel_Shared_Date::PHPToExcel($formatdate));
} else {
$newvalue = dol_stringtotime($newvalue);
$this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, PHPExcel_Shared_Date::PHPToExcel($newvalue));
$coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
$this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd');
}
} 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)) {
if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
$formatdatehour = $this->workbook->addformat();
$formatdatehour->set_num_format('yyyy-mm-dd hh:mm:ss');
//$formatdatehour->set_num_format(0x0f);
$arrayvalue = preg_split('/[.,]/', xl_parse_date($newvalue));
//print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
$newvalue = strval($arrayvalue[0]) . '.' . strval($arrayvalue[1]);
// $newvalue=strval(36892.521); directly does not work because . will be convert into , later
$this->worksheet->write($this->row, $this->col, $newvalue, $formatdatehour);
} else {
$newvalue = dol_stringtotime($newvalue);
$this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, PHPExcel_Shared_Date::PHPToExcel($newvalue));
$coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
$this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
}
} else {
if (!empty($conf->global->MAIN_USE_PHP_WRITEEXCEL)) {
$this->worksheet->write($this->row, $this->col, $newvalue);
} else {
if ($typefield == 'Text' || $typefield == 'TextAuto') {
//$this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row+1)->setValueExplicit($newvalue, PHPExcel_Cell_DataType::TYPE_STRING);
$this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, (string) $newvalue);
$coord = $this->workbook->getActiveSheet()->getCellByColumnAndRow($this->col, $this->row + 1)->getCoordinate();
$this->workbook->getActiveSheet()->getStyle($coord)->getNumberFormat()->setFormatCode('@');
$this->workbook->getActiveSheet()->getStyle($coord)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
} else {
$this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($this->col, $this->row + 1, $newvalue);
}
}
}
$this->col++;
}
$this->row++;
return 0;
}
function xl_decode_date_US($date)
{
return xl_parse_date($date);
}
/**
* Output record line into file
* @param array_selected_sorted Array with list of field to export
* @param objp A record from a fetch with all fields from select
* @param outputlangs Object lang to translate values
*/
function write_record($array_selected_sorted,$objp,$outputlangs)
{
$outputlangs->charset_output='ISO-8859-1'; // Because Excel 5 format is ISO
$formatdate=$this->workbook->addformat();
$formatdate->set_num_format('yyyy-mm-dd');
//$formatdate->set_num_format(0x0f);
$formatdatehour=$this->workbook->addformat();
$formatdatehour->set_num_format('yyyy-mm-dd hh:mm:ss');
//$formatdatehour->set_num_format(0x0f);
$this->col=0;
foreach($array_selected_sorted as $code => $value)
{
$alias=str_replace(array('.','-'),'_',$code);
if (empty($alias)) dol_print_error('','Bad value for field with code='.$code.'. Try to redefine export.');
$newvalue=$objp->$alias;
$newvalue=$this->excel_clean($newvalue);
// Traduction newvalue
if (preg_match('/^\((.*)\)$/i',$newvalue,$reg))
{
$newvalue=$outputlangs->transnoentities($reg[1]);
}
else
{
$newvalue=$outputlangs->convToOutputCharset($newvalue);
}
if (preg_match('/^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/i',$newvalue))
{
$arrayvalue=preg_split('/[.,]/',xl_parse_date($newvalue));
//print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
$newvalue=strval($arrayvalue[0]).'.'.strval($arrayvalue[1]); // $newvalue=strval(36892.521); directly does not work because . will be convert into , later
$this->worksheet->write($this->row, $this->col, $newvalue, $formatdate);
}
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))
{
$arrayvalue=preg_split('/[.,]/',xl_parse_date($newvalue));
//print "x".$arrayvalue[0].'.'.strval($arrayvalue[1]).'<br>';
$newvalue=strval($arrayvalue[0]).'.'.strval($arrayvalue[1]); // $newvalue=strval(36892.521); directly does not work because . will be convert into , later
$this->worksheet->write($this->row, $this->col, $newvalue, $formatdatehour);
}
else
{
$this->worksheet->write($this->row, $this->col, $newvalue);
}
$this->col++;
}
$this->row++;
return 0;
}