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

Question

I want to explicitly set a cell’s datatype as a text for number values.

I am fetching data from my MySQL database and exporting to excel file using PHPExcel Library. I have large about 20 digit long numbers which I want to export to excel cell(each cell of the entire column will have different numbers).

I have found that you can do this by code mentioned PHPExcel Documentation 4.6.7.

$i=0;
foreach($dd as $d) {
    $objPHPExcel
        ->setActiveSheetIndex(0)
       ->getcell('A'.$i)
       ->setValueExplicit($d['serial_no'], PHPExcel_Cell_DataType::TYPE_STRING);
    $i++;
}

$d['serial_no']-different serials coming from database e.g."9876543211234567890".

I have implemented this code its working fine. But when I open that excel sheet and try to modify that cell then click on anywhere else it again gets converted to number format(client's requirement: it should remain as text).

I want to stay that cell as text even if I edit in it.

possible duplicate of [PHPExcel - set cell type before writing a value in it](http://stackoverflow.com/questions/17143236/phpexcel-set-cell-type-before-writing-a-value-in-it) — Siim Kallari, Jun 27 '15 at 07:32
thanks for reply, solved by answer mentioned in @SiimKallari comment, i think i should have declare type before writing a value in cell. Also thank for great library. — mokNathal, Jun 27 '15 at 09:12

score 6 · Accepted Answer · answered Jul 01 '15 at 13:19

6

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

answered Jul 01 '15 at 13:19

Mark Baker

209,507
32
346
385

do I have to add this with `setValueExplicit()` – mokNathal Jul 02 '15 at 05:06
Use the `setValueExplicit()` to set the cell's value as string (the default) then set the cell style as quotePrefix true – Mark Baker Jul 02 '15 at 06:59

score 5 · Answer 2 · answered Aug 22 '16 at 07:17

5

$objPHPExcel->getActiveSheet()
->getStyle('A'.$i)
->getNumberFormat()
->setFormatCode(
    PHPExcel_Style_NumberFormat::FORMAT_TEXT
);

answered Aug 22 '16 at 07:17

Onesinus Saut

314
5
18

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

2 Answers2

Linked

Related