Format Text in Excel File via PHP

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() );

Format cell file .xls made by php

I 've solved the problem

For solve my problem i had use the fuction ="value"

Example:

<?php
echo "<td>=\"$id1\"</td>";
?>

Or if you are not in php:

<td><?php echo "=\"$id\""; ?></td>

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

exporting php data into excel in text data type

A possible solution is to create a new value Binder method to override the PHPExcel method. (PHPExcel tries to guess the datatype we insert in the cells. So, following the advice of markBaker, I created this class to override this method:

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); 
        } 

        // if it is a string and starts with 0, the value will be converted into string 
        if (is_string($value) && $value[0] == '0') { 
            $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING); 
            return true; 
        } 
        return parent::bindValue($cell, $value); 
    } 
} 

this was a tip that markbaker recommended to me in this question.

---

Related Topics