Issue with date formatting.

Topics: User Forum
Jul 11, 2013 at 7:51 PM
Edited Jul 11, 2013 at 7:51 PM
Currently, I'm making a program for my job that uses PHPexcel to basically take all of the data from an excel sheet, make a new excel sheet and format it to text, and transfer all of the data from the old excel sheet.

My php has to be able to take in the values from the old excel sheet and parse them correctly, especially dates/numbers/etc.

Everything is working fine except for the dates. For some reason when I get the formatted value of the cell; it does not match the date format in the first sheet. Specifically a value like this: 12/31/2099 . For some reason, it reads the formatcode incorrectly as mm-dd-yy and outputs 12-31-99. This is extremely frustrating and I've scoured half of the internet for an answer. Help please; I'm not sure what the issue is! Code:
<?php
$spreadSheet=$_FILES["filebrowser"]["tmp_name"];
/** PHPExcel_IOFactory */
require_once 'Classes/PHPExcel/IOFactory.php';
require_once 'Classes/PHPExcel/Shared/Date.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
//Create temporary sheet from uploaded file data
$objPHPExcel = $objReader->load($spreadSheet);
$sheet = $objPHPExcel->getActiveSheet();
$sheet->setTitle("Original Data");
$data=$sheet->getCell("D9")->getFormattedValue();

var_dump($data);
exit;?>
D9 is a cell with DATE format and a value of 12/31/2099. This code prints 12-31-99...
I also cannot explicitly define the format string, this is because the user can enter different types of formats mm-dd-yyyy, mm/dd/yyyy, m-d-Y, etc. I really need some help here, so any at all is very much appreciated.
Coordinator
Jul 12, 2013 at 12:12 AM
Without seeing an example, I can't explain what's happening. Is it possible to upload a sample file that demonstrates this behaviour: the date format mask for those fields should be a straight text value, making it difficult for me to understand why it's being changed in any way.
Jul 12, 2013 at 1:58 PM
Edited Jul 12, 2013 at 1:58 PM
Jul 15, 2013 at 3:15 PM
The dates in your example file use an adaptable format.
This type of format is written to the file in US version. When you open the file in Excel, the application takes into account the format defined in the control panel and tailors.
If you want to reproduce this operation directly in php, I think you should implement what Excel do : make a transcript between the US and the desired local format (there are two formats date and two time formats).
Jul 15, 2013 at 4:34 PM
Edited Jul 15, 2013 at 7:01 PM
Is there a simple way around this? What bothers me is that it obviously parses it correctly if the date is entered in a TEXT format. But when you specify DATE in the number format dropdown, it breaks it. Again; I can't necessarily specify a format. It needs to be parsed correctly and give leverage to the user.
Jul 16, 2013 at 7:31 AM
When you loop the cells, tests whether it is a date/time format (with PHPExcel_Shared_Date::isDateTime()).
If so, reads the format ($sheet->getStyle ('A1')->getNumberFormat()->getFormatCode()), compares it to the adaptable formats, if there is one of these formats, computes the value in imposing the formatting:
[...]
case 'mm-dd-yy':
$FormattedValue = PHPExcel_Style_NumberFormat:toFormattedString ($Cell->getValue(), PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
break;
[...]
Replaces the format of output by the one desired, using constants or by explicitly passing a string.
If this is not an adaptable format, use getFormattedValue().
I do not have in mind other adaptable formats. To know them, created a little workbook with values of test using these formats (there is a "*" in front) and then look for the chain used (directly in the file or using PHPExcel).
Jul 17, 2013 at 3:37 PM
I can't replace the date format though. That leverages the programmer rather than the user. It's critical that the format stay in the format that they specify.
Jul 19, 2013 at 11:19 AM
No. I never suggested that the programmer imposes his views.

I suggested you to reproduce the behavior of Excel so that the textual representation is that the user expect.