phpexcel set data type on range of cells

Question

With PHPExcel, I'm using:

$workSheet->fromArray($array);

To set my data. However, one column needs to be set as string (a number with a leading zero, the leading zero gets cut off if not set to string). If I wasn't using the fromArray method, I could set the data type using PHPExcel_Worksheet::setCellValueExplicit().

However, I want to do this using a range of columns. Ideally something like this:

$worksheet->getCell('A1:A50')->setDataType(PHPExcel_Cell_DataType::TYPE_STRING);

But looking at the code, getCell won't allow a range, only a single cell. How can I do this?

Answer 1

There is currently no method in PHPExcel that will allow you to set the data type for a range of cells, only for an individual cell.

There are a couple of options... the harder would be to write a custom Cell Value Binder that automatically sets the data type for those cells to string when the cell value is set by fromArray().

What you could do instead (and is far easier), is to leave the datatype as a number, and the value as a numeric, but to set a number format mask which tells Excel to display the numbers with leading zeroes.

$objPHPExcel->getActiveSheet()->getStyle('L3:N2048')
                              ->getNumberFormat()->setFormatCode('0000');