Maintaining date formatting

Sep 22, 2010 at 4:32 AM

In short I am trying to copy a cell with a date value from one .xls to another. Problem I am having is date formatting is not being maintained, instead I get a number like 40422. I found a way around it by explicitely defining the format, but this could change. Ideally I'd like to use the source formatting.

Here is what I am doing now:

$objReader = PHPExcel_IOFactory::createReaderForFile($inputfile);
$objReader->setReadDataOnly(false);
$objPHPExcelInput = $objReader->load($inputfile);
$activeSheetInput=$objPHPExcelInput->getActiveSheet();

...

//Get the date value from the input .xls
$val = ($activeSheetInput->getCell($inputCell));
$data = $val->getvalue();//returns the value in the cell

if (PHPExcel_Shared_Date::isDateTime($val))
{
   //Write the data from the input file to the output .xls
   $activeSheetOutput->setCellValue($outputCell,$data);

   //Fix date formatting as it is not being maintained from input getValue
   $activeSheetOutput->getStyle($outputCell)->getNumberFormat()->setFormatCode('d-mmm-yy');
}

 

Thanks

Coordinator
Sep 22, 2010 at 8:35 AM

The only way that PHPExcel (or even Excel itself) can differentiate between a number and a date is the format mask

$data = $val->getvalue();
$activeSheetOutput->setCellValue($outputCell,$data);

only copies the cell value (40422), not the format mask.

You also need to copy the formatting mask as well

$data = $val->getvalue();
if (PHPExcel_Shared_Date::isDateTime($val))
{
   $format = $val->getParent()->getStyle($val->getCoordinate())->getNumberFormat();
   $activeSheetOutput->setCellValue($outputCell,$data);
   $activeSheetOutput->getStyle($outputCell)->getNumberFormat()->setFormatCode($format);
}

 

Sep 23, 2010 at 4:41 PM

Thanks for the explanation!

The code you supplied got me on the right track, but was missing one small piece:

$format = $val->getParent()->getStyle($val->getCoordinate())->getNumberFormat();

needs to be:

$format = $val->getParent()->getStyle($val->getCoordinate())->getNumberFormat()->getFormatCode();

Thanks!

Coordinator
Sep 23, 2010 at 4:43 PM
master44 wrote:

The code you supplied got me on the right track, but was missing one small piece:

 <blush>

Glad you got it working anyway