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.

Solution 1

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

Solution 2

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