Leading zeros are truncated - even if declared as string

[dialogik]

I'm fetching some data from a MySQL table and put this into a sheet:

// Fetch data
$data = fetchData();

// Set values
$objPHPExcel->getActiveSheet()
    ->fromArray(
        $data,
        NULL,
        'A1'
);

There is one column that only contains numbers with a leading zero (actually no numbers but strings), but the leading zeros are truncated. Even if I explicitly declare values of that column as strings:

$objPHPExcel->getActiveSheet()->getStyle('C2:C16')
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

I have tried to place the type setting to string before the fromArray() statement in order to define the type before filling it with values but it still messes up my values. Do I have to consider something else or is this a bug?

[MarkBaker]

Numbers are stored as numbers in PHPExcel unless you use a custom value binder to enforce datatype of string for numeric values with leading zeroes, or you need to set cell values individually using the explicit function... only then will the value actually be stored as a string.

What you aren't doing is declaring the value as a string.... the setCellValueExplicit() allows you to declare the value as a string; setting a number format of Text simply tells Excel to display the number in the cell as a string, but at that points it's already a number, and as such has no leading zeroes.

This is actually documented behaviour, as described in the Developer Documentation (Setting a number with leading zeroes)

[MarkBaker]

See https://github.com/PHPOffice/PHPExcel/blob/develop/Documentation/markdown/Overview/07-Accessing-Cells.md#setting-a-number-with-leading-zeroes for details

[dialogik]

Ah thanks a lot, I was actually not aware of that. I thought the setFormatCode() would also set the data type. Thanks for helping me here!

[dialogik]

Is there a way to use setCellValueExplicit() on a range of existing cells instead of using it on one cell while setting the value? E.g.

$objPHPExcel->getActiveSheet()->setCellValueExplicit(
    'A1:A200', 
    NULL, // Don't set the value here, only apply setCellValueExplicit to existing values
    PHPExcel_Cell_DataType::TYPE_STRING
);

By the way: Applied like this, it throws error Cell coordinate can not be a range of cells.

[MarkBaker]

That's right, trying to set cell value on a range of cells will throw an exception... the setCellValue*() methods all work on one, and only one, cell. There is no short-cut to achieve what you want.... And nor can you retrospectively change the datatype of a cell and automatically convert the content to restore something that may have been lost (such as leading zeroes) when the cell value was first set.

However, it's pretty straightforward to define a cell binder that will recognise leading zeroes in a cell; and a recent change on the development branch did exactly this

[dialogik]

I see. Thus I updated to the latest version. Works like charm. Thanks!