CODEWITHSUNDEEP
phpexcelphpexcel
drew kroft
drew kroft

Reputation: 916

PHPExcel - make column not drop leading zeros

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

Upvotes: 0

Views: 3083

Answers (1)

drew kroft
drew kroft

Reputation: 916

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.

Upvotes: 2

Related Questions