correct dates using getOldCalculatedValue() with setLoadSheetsOnly()

Topics: Developer Forum, User Forum
Feb 1, 2013 at 3:50 PM
Hello,

I am trying to load specific worksheets from an xlsx workbook that has many formulas and references.

Basically, this is the code I'm using.
$filename = "/home/rob/Desktop/Testing_012213 XLSX.xlsx";
$objReader = PHPExcel_IOFactory::createReader("Excel2007");
$objReader->setLoadSheetsOnly( array("Mini Transfer") );
$objPHPExcel = $objReader->load($filename);

$objWorksheet = $objPHPExcel->getSheetByName("Mini Transfer");

$cell = $objWorksheet->getCellByColumnAndRow(3, 23);

if(PHPExcel_Shared_Date::isDateTime($cell)){
    echo date('m/d/Y', PHPExcel_Shared_Date::ExcelToPHP($cell->getOldCalculatedValue())) . "\n";
    echo PHPExcel_Style_NumberFormat::toFormattedString($cell->getOldCalculatedValue(), "MM/DD/YYYY") . "\n";
}else{
    echo $cell->getOldCalculatedValue() . "\n";
}
By using the getOldCalculatedValue() function, I can determine the referenced values without loading every worksheet.

The problem I'm having is with calculating dates. When reading in a date of '2/11/2013' from an Excel2007, the above code prints out
02/06/2013
02/07/2013
I can try the same thing using an Excel 2003 xls file. Reading in the date 2/1/2013 gives me a return value of
08/27/2012
08/28/2012
Reading in all other calculated values apart from dates works great.

Am I reading in the date values or formatting them incorrectly?
Feb 5, 2013 at 6:13 PM
I did more tests and found that dates are not pulled from the workbook properly if the excel sheet is saved using 2013 and is a protected sheet.

Excel 2007 protected sheet (good)
41296 : 01/22/2013 Should be 1/22/2013
41320 : 02/15/2013 Should be 2/15/2013

Excel 2013 protected sheet (bad)
41296 : 01/22/2013 Should be 1/22/2013
41312 : 02/07/2013 Should be 2/15/2013 I confirmed here that the date number is '41320', but phpexcel is pulling back '41312'.

Excel 2013 unprotected sheet (good)
41306 : 02/01/2013 Should be 2/1/2013
41320 : 02/15/2013 Should be 2/15/2013

Has anybody else had problems date types in Excel 2013 protected worksheets?
Feb 5, 2013 at 9:40 PM
I found my problem, nevermind about this question :)

The cell I was using had a formula of 'Today+10 days'. I was expecting to see one date, but the date saved by the other person some days ago was correct.