Trouble with PHPexcel removing leading zeros

Question

I have the code below but when I run my CSV though the import process my leading zeros are gone. For example, I have a field with a number such as "0010" but after it comes from the below code the number is "10". Anybody have a suggestion?

	$objPHPExcel = new PHPExcel();
	
	function ci_import($inputFileName){
		
			//echo "calling....";exit;
			
		try {
			$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
			$objReader = PHPExcel_IOFactory::createReader($inputFileType);
			$objPHPExcel = $objReader->load($inputFileName);
		} catch (Exception $e) {
			die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) 
			. '": ' . $e->getMessage());
		}
		
		$sheets = count($objPHPExcel->getAllSheets());
		//echo $sheets;
		//echo "<pre>";
		$arr=array();
		foreach($objPHPExcel->getAllSheets() as $sheet){
			$title = $sheet->getTitle();
			$arr[$title]=array();
			$rows= array();
			// fetch the data
			foreach ($sheet->getRowIterator() as $row) 
			{
				$cols= array();
				$cellIterator = $row->getCellIterator();
				$cellIterator->setIterateOnlyExistingCells(false); // This loops all cells,
				foreach ($cellIterator as $cell)
				{
					$cols[]=$cell->getValue();
				}
				$rows[] = $cols;
			}
			$arr[$title]=$rows;
			
			
					}
		
		return $arr;
		print_r( $arr);
		
	}

Mark Baker Mark Baker · Accepted Answer · 2017-03-14T19:08:30

Numbers don't have leading zeroes; but PHPExcel's CSV Reader will recognise that the value 0010 is numeric and convert it to a number 10, which is perfectly correct, and (just for reference) is exactly what the MS Excel CSV Reader does.

If you want to treat this value as a string, or to format it as a 4-digit number with leading zeroes, then you want to create a custom binder that specifies this as a rule for importing the value.

class PHPExcel_Cell_MyValueBinder extends PHPExcel_Cell_DefaultValueBinder
    implements PHPExcel_Cell_IValueBinder 
{ 
    public function bindValue(PHPExcel_Cell $cell, $value = null) 
    { 
        // sanitize UTF-8 strings 
        if (is_string($value)) { 
            $value = PHPExcel_Shared_String::SanitizeUTF8($value); 
        } 

        // Implement your own override logic 
        if (is_string($value) && $value[0] == '0') { 
            // Here, we're just enforcing that the value should be treated
            //   as a string, but we could convert it to a numeric and apply
            //   a format mask to the cell instead
            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); 
            return true; 
        } 

        // Not bound yet? Use default value parent... 
        return parent::bindValue($cell, $value); 
    } 
}

To avoid any problems with the autoloader, create this in the /Classes/PHPExcel/Cell directory. Otherwise, give the class your own non-PHPExcel name, and ensure that it's loaded independently.

Then, before loading the file, indicate that your custom binder should be used:

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_MyValueBinder() );

Trouble with PHPexcel removing leading zeros

1 Answers