CODEWITHSUNDEEP
phpphpexcel
Kiren S
Kiren S

Reputation: 3097

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

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 ...

Upvotes: 1

Views: 11617

Answers (2)

Mark Baker
Mark Baker

Reputation: 212422

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

Upvotes: 5

Thorsten
Thorsten

Reputation: 3122

I do it like this:

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

Upvotes: 7

Related Questions