1

I am creating an excel sheet using PHPExcel. As we need good optimization, I am avoiding the looping in its maximum. But I am not able to optimize the below code anymore.

$activeSheet->fromArray($excel_rows, null, 'A1');
$activeSheet->getStyle('A2:K'.$index)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
$activeSheet->getStyle('L2:L'.$index)->getNumberFormat()->setFormatCode( '@' );
$activeSheet->getStyle('U2:W'.$index)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
$in = 2;
foreach($micr_pin as $value){
    $activeSheet->getCell('H' . $in)->setValueExplicit($value['pin'], PHPExcel_Cell_DataType::TYPE_STRING);
    $activeSheet->getCell('M' . $in)->setValueExplicit($value['microchip'], PHPExcel_Cell_DataType::TYPE_STRING);
    $in++;
}

Here the value of pin and microchip can contain preceding zeros. So I need to keep them in excel. Till now I am able to do so by adding them in the loop and give datatype for each cell.

Is there a better way to do this(Can I give some custom formatting?)? The pin is a fixed length of 5. But microchip can be varied in length.

Arun
  • 3,640
  • 7
  • 44
  • 87

1 Answers1

0

Not 100% on this now post-edit, so please let me know if it works. You should be able to use setFormatCode to preserve some data context, and then style by range:

$pins_length = count($micr_pin);

// For pin, as we know it's 5 characters already, we can just explicity set that to 5

$activeSheet->getStyle('H1:H' . $pins_length)
    ->getNumberFormat()
    ->setFormatCode('00000');

// For microchip we need to introduce a bit of variability

$activeSheet->getStyle('M1:M' . $pins_length)
    ->getNumberFormat()
    ->setFormatCode(str_repeat('0', max(array_map('strlen', array_column($micr_pin, 'microchip')))));
Scoots
  • 3,048
  • 2
  • 21
  • 33