Phpexcel file: string data type is parsed as numbers after creating the file

Question

I am using php excel for creating excel files. I am populating my excel file from php array values that is fetched from MySQL database.I have string data type in first column. But after creating the excel file, string like 0987 are converted into 987 and automatically aligned to right of the cells. I really don't want this default feature of excel file. How do I over come this problem. Any hint/solutions... Help me ...

Solution 1

I do it like this:

$objPHPExcel->getActiveSheet()->setCellValue('A1', '="0987"')

Solution 2

Either:

// Set the value explicitly as a string
$objPHPExcel->getActiveSheet()
    ->setCellValueExplicit(
        'A1', 
        '0987', 
        PHPExcel_Cell_DataType::TYPE_STRING
    );

or

// Set the value as a number formatted with leading zeroes
$objPHPExcel->getActiveSheet()
    ->setCellValue('A3', 987);
$objPHPExcel->getActiveSheet()
    ->getStyle('A3')
    ->getNumberFormat()
    ->setFormatCode('0000');

Note that in the first case I'm calling the setCellValueExplicit() method, not the setCellValue() method.

If you're populating blocks of data in one step using the fromArray() method, then the latter approach is probably easier, especially as you can set the style for a whole block of cells in one step once you've populated all the data.

$objPHPExcel->getActiveSheet()
    ->getStyle('A3:A123')
    ->getNumberFormat()
    ->setFormatCode('0000');