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);
}</div>
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() );