Reading Numbers as Text Format With Phpexcel

Reading numbers as text format with PHPExcel

Formatting using a number format affects the way a number is displayed, not the way it is stored.

You'll have to store the numbers explicitly as strings, so you can't use fromArray().
Use setCellValueExplicit() or setCellValueExplicitByColumnAndRow() instead, passing a $pDataType argument of PHPExcel_Cell_DataType::TYPE_STRING.

EDIT

Note that you can also set styles for a range of cells, so there's no need to add the overhead of the for loop:

$range = 'A'.$row.':'.$latestBLColumn.$row;
$objPHPExcel->getActiveSheet()
    ->getStyle($range)
    ->getNumberFormat()
    ->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );

EDIT #2 Using a cell binder

Create a customised cell value binder:

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') { 
            $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 using your fromArray() call, tell PHPExcel to use your value binder instead of the default binder:

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_MyValueBinder() );

Explicitly set a cell’s datatype as text for number values

I've not tested it, but possibly setting the cell as "quotePrefix" may prevent MS Excel from trying to convert the datatype when editing it with MS Excel

$objPHPExcel->getActiveSheet()
    ->getStyle('A2:O128')
    ->setQuotePrefix(true);

Excel2007 only

Trying to convert output from text to numeric in PhPExcel

Set the value as a straight number, and use a format mask to display it as currency

$objPHPExcel->getActiveSheet()
    ->setCellValue('D'.$excel_row, $aGenericAmenity['price']);
$objPHPExcel->getActiveSheet()
    ->getStyle('D'.$excel_row)
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);

There's plenty examples showing how to do this, and it's described in the documentation as well

converting text in to number format in phpexcel

Unless you use setCellValueExplicit(), then PHPExcel uses a value binder to identify the datatype that you are setting in the cell.

By default, this is PHPExcel_Cell_DefaultValueBinder, which performs a very simplistic check on whether the PHP datatype is Null or Boolean, or a numeric value or a string. A value such as 37.7% is a PHP string, so will be treated as a string.

There is also an advanced value binder (PHPExcel_Cell_AdvancedValueBinder) that can perform much more sophisticated checks, and is capable of identifying strings containing basic date formats and converting them to an Excel serialized datetime, and setting a number format mask accordingly. It can also detect currency values, fractions, and (most importantly) percentages.

If you pass a string value like 37.7% through the advanced value binder, it will divide the numeric part by 100, and set the number format mask to a percentage mask, in exactly the same way as the MS Excel GUI.

To enable the advanced value binder, call

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );

before instantiating your PHPExcel object.

You can find an example of advanced binder usage in /Examples/29advancedvaluebinder.php

Detecting Number Format Of Excel Cells With PHPExcel

$objPHPExcel->getActiveSheet()
    ->getStyle('A1');

Will return the Style object for cell A1 in the current active worksheet. You can then look at the properties of the style object to identify the elements of style that you're interested in.

If it's specifically the number format, then

$objPHPExcel->getActiveSheet()
    ->getStyle('A1')
    ->getNumberFormat();

Will return the number format mask object, which has a getFormatCode() method to return the format mask as a string

EDIT

If you specifically want to test if a cell contains a date format mask or not, then there's a special built-in function specifically written for this purpose:

if(PHPExcel_Shared_Date::isDateTime($objWorksheet->getCellByColumnAndRow($col, $row))) {
    echo 'Cell contains a date or time';
}

---

Related Topics