Phpexcel Write Long Number in Cell

phpExcel write long number in cell

$objPHPExcel->getActiveSheet()->setCellValueExplicit('A1', '1234567890', PHPExcel_Cell_DataType::TYPE_STRING);

Export PHPExcel write long number cell - code igniter

PHP Excel's setCellValueExplicit requires an alphanumeric coordinate: A1, C27, etc. Basically, you need to give the cell coordinates in one value.

Since you apparently write on the first column only, try this:

$coordinate = 'A' . $excel_row;

$object->getActiveSheet()->setCellValueExplicit(
    $coordinate, 
    $row->member,
    PHPExcel_Cell_DataType::TYPE_STRING
);

---

Note:
PHP Excel is deprecated, you should try PHP Spreadsheet. It's developed by the same vendor and it has a similar API.

How to put large numbers(more than 15 digits) in an excel file using PHPExcel?

I found that final solution works. I mean this one:

$objPHPExcel->getActiveSheet()
            ->getCell('A1')
            ->setValueExplicit($cellDate, PHPExcel_Cell_DataType::TYPE_STRING);

But, I was watching my final excel file in LibreOffice Calc and it shows a single quote. When I load my final excel file in Microsoft Office, there were no additional character.

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

---

Related Topics