inserting a numeric string in a cell converts to number

May 25, 2010 at 10:42 AM

Hello,

I'm trying to insert a numeric string ("012") in a cell using the following code, but the leading zero is removed in the excel file.

The excel cell format is set to "Text".

How may I avoid this problem?

$objPHPExcel = PHPExcel_IOFactory::load("models/report1.xls");
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $cntrows, "012");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
$objWriter->save("elaborazioni/test.xls");
Thank you

May 25, 2010 at 10:53 AM

The following code has worked:

 

$objPHPExcel = PHPExcel_IOFactory::load("models/report1.xls");
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $cntrows, "012");
$objPHPExcel->getActiveSheet()->getCellByColumnAndRow(0, $cntrows)->setValueExplicit('012', PHPExcel_Cell_DataType::TYPE_STRING);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
$objWriter->save("elaborazioni/test.xls");

Is there a way to achieve this using setCellValueByColumnAndRow directly?

Thank you

 

Coordinator
May 26, 2010 at 7:45 AM
softmasters wrote:

Is there a way to achieve this using setCellValueByColumnAndRow directly?

We deliberately provide the two separate pairs of methods for this purpose.

$objPHPExcel->getActiveSheet()->setCellValue('B3',123);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,3',123);
$objPHPExcel->getActiveSheet()->setCellValueExplicit('B4','0123',PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->setCellValueExplicitByColumnAndRow(2,3','0123',PHPExcel_Cell_DataType::TYPE_STRING);

However, you can also make use of Cell Value Binders. Take a look at the AdvancedValueBinder.php in /Classes/PHPExcel/Cell and the sample code in /Tests/29advancedvaluebinder.php to see how this works