PHPExcel - make column not drop leading zeros

Question

I'm currently running code to take a .csv file, which is created new each day and is using ; as delimiters, and export that into an excel spreadsheet. The code is as follows:

$xlFileName = $outDir."output_".date("n-j-Y").".xls";
require_once 'PHPExcel_1.8.0_doc/Classes/PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('CSV');

$objReader->setDelimiter(";");
//$objReader->setInputEncoding('UTF-16LE');

$objPHPExcel = $objReader->load($todayFileName);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($xlFileName);

This issue I'm having is that one column is zip codes, which can begin with 0. Is there any way that I can tell PHPExcel to not drop leading zeros in that particular column?

EDIT:

example csv contents:

Date;Zip Code;Data;Data;Data
10/12/2015;01234; Some Data ; More Data  ; More Data
10/12/2015;12345; Some Data ; More Data  ; More Data
10/12/2015;23456; Some Data ; More Data  ; More Data
10/12/2015;34567; Some Data ; More Data  ; More Data

Export for first zip code in spreadsheet is always 1234 instead of 01234

If there's a way to make the entire spreadsheet display exactly as they are entered, that works too. The .csv is formatted correctly. — drew kroft
I'd really like to know what is different for you that leading zeros are not lost with this code! I've tested this exact code (any many variations) on several different systems, and the result from all is that leading zeros are not transferred to the resulting spreadsheet. Even adjusting formatting to display the leading zeros in the spreadsheet does not help. The only quasi working solution I've found is to wrap the zip code in apostrophes in the csv, which, unfortunately, results in the zip code being wrapped in apostrophes in the resulting spreadsheet as well. — drew kroft
I used exact same code and getting the spreadsheet with zeros. Share your complete code and part of CSV file too — Subin Thomas
This code is all there is. It takes the .csv file and starts right there. Will add csv contents to my post. — drew kroft

drew kroft drew kroft · Accepted Answer · 2015-10-14T19:11:45

So, it seems like the problem had something to do with formatting it as text. I ran through cell by cell and manually added zeros and inserted that into the cell as a string. When I opened the excel file, the zero was missing still. The cells were formatted as text, and if I added a zero in Excel, it stuck around.

So, I took a totally different route. I have several files I have to convert daily, and the Zip Code column is not always the same, so I added some code to check where that is. Here is the code to set a format that is now working for me:

$worksheet = $objPHPExcel->getActiveSheet();
$zipColumn = "ZZ";
foreach ($worksheet->getRowIterator() as $row) {  
  $cellIterator = $row->getCellIterator();
  $cellIterator->setIterateOnlyExistingCells(false);
  foreach ($cellIterator as $cell) {
    if (!is_null($cell)) {
      if($cell->getColumn() == $zipColumn){
$objPHPExcel->getActiveSheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('[<=99999]00000;00000-0000');
      }
      if(strtolower( $cell->getValue() ) == "zip code"){
        $zipColumn = $cell->getColumn(); 
      }
    }
  }
}

Hope that helps if anybody else had a similar issue.

PHPExcel - make column not drop leading zeros

1 Answers