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

10,614

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');
Share:
10,614
Kiren S
Author by

Kiren S

10+ years of experience in web industry. Microservices, NodeJS, PHP Laravel Framework Developer, Full Stack. Hands on Hybrid mobile app development using Titanium Alloy framework. Master Degree in Computer Application and Bachelor Degree in Electronics.

Updated on November 23, 2022

Comments

  • Kiren S
    Kiren S less than a minute

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