date after converting

Topics: User Forum
Jan 17, 2015 at 11:38 PM
Edited Jan 17, 2015 at 11:41 PM
Hi there,

i have a Little Problem with PHP Excel. I use folowing to convert a xls (xlsx) file to csv:
    $excel = @PHPExcel_IOFactory::load($source);
    $writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
    $writer->setDelimiter(";");
    $writer->setEnclosure('"');
    $writer->save($target);
    unset( $excel );
Thats works fine so far...

In my XLS File is a date like 04.07.1975 and the date Format is set to dd.mm.YYYY. Excel Shows me the Date correct. but after converting in the csv it Shows 07-04-75.

Thats a real Problem, because i dont know which year is mean. 75 can be 1975 or 2075 or 1875...

Why Shows Excel the date correct, but the csv Shows wrong?

I've tried so convert all fields to "text" with folowing:
 $inputFileType = PHPExcel_IOFactory::identify($source);
 $objReader = PHPExcel_IOFactory::createReader($inputFileType);
 $objPHPExcel = $objReader->load($source);   
  $objPHPExcel->getDefaultStyle()
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);  
But this doesnt help...

The stringoftime function doesnt help me too, because the year is obscure...

I dont know what i can do. The only Thing is to convert the whole sheet to text in excel. But when i have a sheet with postal codes like 04478 it changes after converting to 4478. What i try, i cant find a solution....

Some Ideas???

Thanks for your help....
Coordinator
Jan 18, 2015 at 12:08 AM
How are you checking this date conversion? Are you opening the CSV file in MS Excel, or in a text editor?
Jan 18, 2015 at 1:01 AM
I dont check date conversion. In the XML File it Shows the date correkt and i set it to dd.mm.yyyy in Excel.

It doesnt matter if i open the csv in text Editor or MS Excel - It Shows me the date Format mm-dd-yy after converting...
Jan 18, 2015 at 4:23 AM
Hmmm this seems like a bug or something

I saved the xlsx as xls and try it with php excelreader (which only can handle xls).

When i read the date with excelreader it Shows me mm/dd/yyyy

When i read the same fle with phpexcel it Shows me the mm-dd-yy

For Example: In Excel i set the date format to dd.mm.yyyy and save the value 18.04.2015

When i read the file with php excelreader it gives me 04/18/2015

When i read the same file with phpexel it gives me 04-18-15

OK both formats are not correct setted like in the Excel file, but 04/18/2015 is better then 04-18-15, because in excelreader i can read the year, which makes it easy to convert the date...

What can i do to fix this? Is it a bug?

Hope for your Help. Thank you!
Jan 18, 2015 at 4:38 AM
ahhh i found something interesting
    $excel = @PHPExcel_IOFactory::load($source);
    $excel->getActiveSheet()
    ->getStyle('A1:E99')
    ->getNumberFormat()
    ->setFormatCode(
        PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2
    );       
    $writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
    $writer->setDelimiter(";");
    $writer->setEnclosure('"');
    $writer->save($target);
    unset( $excel );
This works nearly - but getStyle('A1:E99') ist Problem. Dont know where file Begins (a1 i think) and where it Ends....

Can you help me?

Can i set the PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2 for the whole writer (createWriter)?
Jan 18, 2015 at 5:13 AM
OK i again (sorry)

i now have this 2 functions. First one gives me the first and the last field in the file

second one converts
function get_xls_startend ($source) {
require_once 'phpexcel/PHPExcel/IOFactory.php';
$inputFileType = PHPExcel_IOFactory::identify($source);
    
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load($source);
//$objWorksheet = $objPHPExcel->setActiveSheetIndex($dail_sheet);
    $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
    
    $highestRow=$objWorksheet->getHighestDataRow();
    $highestColumn=$objWorksheet->getHighestDataColumn();
    
    
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);  
    
    
$row = 0;
$lowestrow=1;
  for($row = $lowestrow; $row <= $highestRow; $row++)
      {
    for ($col = 0; $col <= $highestColumnIndex; ++$col)
      {
        if($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()!="NULL")
         {
          $lowestrow=$row;
          $col =$highestColumnIndex;
          $row = $highestRow;
         }
      
      
      }

      }
    unset( $objReader );  
    $resultarr = array();  
    $resultarr[] = "A".$lowestrow;
    $resultarr[] = $highestColumn.$highestRow;
    return $resultarr;
}    



function xls2csv($source,$target) {
    require_once 'phpexcel/PHPExcel/IOFactory.php';
    $inputFileType = PHPExcel_IOFactory::identify($source);
    
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    
    list($start,$end) = get_xls_startend($source);
    
    $startend = $start.":".$end;

    
    if($inputFileType != "HTML") {
    $excel = @PHPExcel_IOFactory::load($source);
    $excel->getActiveSheet()
    ->getStyle($startend)
    ->getNumberFormat()
    ->setFormatCode(
        PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2
    );       
    $writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
    $writer->setDelimiter(";");
    $writer->setEnclosure('"');
    $writer->save($target);
    unset( $excel );
    }
}
But now all integer fields will be converted to a date....Better way where a Default date Format for the csv writer...is this possible?
Coordinator
Jan 18, 2015 at 10:56 AM
No, it isn't a bug.

Your problem is
$objReader->setReadDataOnly(true);
You're telling PHPExcel to read only the raw data for cells, not the cell formatting, not the number format masks, etc.

This means that PHPExcel can't tell whether a cell contains a number that should be formatted as a "date", or simply a number; PHPExcel will realise that the value loaded from an xlsx file should be a date, but not what format it should be displayed as (because you've told it to ignore that information), so it uses a default format.
Jan 18, 2015 at 4:27 PM
OK i have the setReadDataOnly(true) out. So my 2 Functions:
function get_xls_startend ($source) {
require_once 'phpexcel/PHPExcel/IOFactory.php';
$inputFileType = PHPExcel_IOFactory::identify($source);
    
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    //$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load($source);
//$objWorksheet = $objPHPExcel->setActiveSheetIndex($dail_sheet);
    $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
    
    $highestRow=$objWorksheet->getHighestDataRow();
    $highestColumn=$objWorksheet->getHighestDataColumn();
    
    
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);  
    
    
$row = 0;
$lowestrow=1;
  for($row = $lowestrow; $row <= $highestRow; $row++)
      {
    for ($col = 0; $col <= $highestColumnIndex; ++$col)
      {
        if($objWorksheet->getCellByColumnAndRow($col, $row)->getValue()!="NULL")
         {
          $lowestrow=$row;
          $col =$highestColumnIndex;
          $row = $highestRow;
         }
      
      
      }

      }
    unset( $objReader );  
    $resultarr = array();  
    $resultarr[] = "A".$lowestrow;
    $resultarr[] = $highestColumn.$highestRow;
    return $resultarr;
}    



function xls2csv($source,$target) {
    require_once 'phpexcel/PHPExcel/IOFactory.php';
    $inputFileType = PHPExcel_IOFactory::identify($source);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    
    list($start,$end) = get_xls_startend($source);
    
    $startend = $start.":".$end;

    
    if($inputFileType != "HTML") {
    $excel = @PHPExcel_IOFactory::load($source);
    $excel->getActiveSheet()
    ->getStyle($startend)
    ->getNumberFormat()
    ->setFormatCode(
        PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2
    );       
    $writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
    $writer->setDelimiter(";");
    $writer->setEnclosure('"');
    $writer->save($target);
    unset( $excel );
    }
}
This changes nothing and brings still the same Problem that all numbers convertet to date, because the setReadDataOnly(true); was only set in the first function, that tells me only the first and the last cell of the php file. The convert function is the second function and there is no setReadDataOnly(true); set...

The Problem is, that the first function gives me ALL cells of the whole sheet and i set ist to FORMAT_DATE_YYYYMMDD2. I cant set only the date cells to date Format, because the user gives me the xls and i dont know which cells contains date informations....

What is my fault in this case?

I've read that php Excel can detect if a cell is a date. is it posible to detect it in the first function and set only this cells to FORMAT_DATE_YYYYMMDD2?