I'm converting various xls and xlsx spreadsheets into csv files with PHPExcel. The original spreadsheet has dates in various formats but I need them to be consistent format in the csv file.
Here are the pertinent parts of the conversion code.
$filePath = "fromFile.xlsx";
$csvPath = "toFile.csv";
PHPExcel_Settings::setZipClass(PHPExcel_Settings::PCLZIP);
$reader = PHPExcel_IOFactory::load( $filePath );
$writer = PHPExcel_IOFactory::createWriter( $reader, 'CSV' );
$writer->setDelimiter(",");
$writer->setEnclosure('"');
$writer->setSheetIndex(0);
$writer->save( $csvPath );
I'm seeing dates that are formatted like mm/dd/yyyy in the original spreadsheet being converted to mm-dd-yy. I paged around the documentation and the code looking for where that format is being set but I wasn't able to find a clear answer.
My test file is using excel's default Short Date format of *3/14/2012. I consistently get "12-20-18" for a date that reads as '12/20/2018' in the excel file.
EDIT:
Proposed duplicate of question answers how to set a format on a specific cell. In my case the formats are already set, but they are not working properly.
I've discovered that the issue is specific to the "*mm/dd/yyyy" format. For some reason, this particular format gets changed to mm-dd-yy when written to the csv file.
Excel's Format Cells dialog says "Date formats that begin with an asterisk respond to changes in regional date and time settings that are specified for the operating system."
Is there a more elegant solution than cell by cell search for date fields and overwriting their format?