Save array into xls file

Question

I wish to save my 2 dimensional array into an .xls file. If use this code, the data is saved without leading zeroes:

$arr = array(
    array("01", "02", "03"),
    array("001", "02", "03"),
    array("00001", "02", "03"),
);


// include PHPExcel library

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->fromArray($arr);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save("file.xls");

If use this instead, 01 is saved as 00001:

......
$objPHPExcel->getActiveSheet()->fromArray($arr);
$objPHPExcel->getActiveSheet()->getStyle('A1:C3')->getNumberFormat()->setFormatCode("00000");
.....

How can I solve this problem? How can I save the original data from my array into an .xls file correctly?

Would it be viable to strlen() the element and then use str_repeat() to set the format? — MDEV
– MDEV, Commented Jul 26, 2013 at 13:57
@ SmokeyPHP --- You mean that make this in cycle? for each element to array? — Oto Shavadze
– Oto Shavadze, Commented Jul 26, 2013 at 14:03

Elias Van Ootegem · Accepted Answer · 2013-07-26 14:12:05Z

Try setting the cells to the text format instead:

$objPHPExcel->getActiveSheet()->getStyle('A1:C3')->getNumberFormat()->setFormatCode('@');

or loop through the array, setting the format according to the string's length (->setFormatCode(str_repeat('0', strlen($arrElem));, but that would be as silly as looping over the array, and explicitly setting each value:

$objPHPExcel->->getCell('A1')->setValueExplicit($arr[0][0], PHPExcel_Cell_DataType::TYPE_STRING);

There might be a case for the blunt approach here, too:

$objPHPExcel->getDefaultStyle()
            ->getNumberFormat()
            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

This should set the default format to txt.

What smokey (and I suggested) was using a number format for each iteration.

$col = range('A','C');
$cell = '';
foreach($arr as $colIndex => $vals)
{
    $cell = $col[$colIndex];//the first array will be $col[0] === A
    foreach($vals as $row => $val)
    {
        $cell .= $row +1;//A.(0+1) -->A1
        $objPHPExcel->getCell($cell)
                     ->getNumberFormat()
                     ->setFormatCode(str_repeat('0', strlen($val)));
                     //for 001, this is str_repeat('0',3) or '000'
    }
}

This formats the cells accordingly, to add the correct amount of leading zeroes. If all else fails, this, is a fairly good way of setting the correct formats

Try setting the cells to the text format --- Please tell how to make this?
@OTARIKI: Well, my first snippet: setFormatCode('@') should do that, but you can also set the default style to text format (added the code for that in edit)
@OTARIKI: added code for the manual approach, too (setting the number format cell by cell, according to the string in $arr

Collectives™ on Stack Overflow

Save array into xls file

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related