Jerielle Jerielle - 5 months ago 68
PHP Question

Looping logic when adding new rows in PHPExcel

I have a problem about adding new rows using PHPExcel. In my data I have a multidimensional array. And I don't know how can I add a new rows below the newly inserted row. Here's the sample data of my array I removes some of the details to make it short:

Array
(
[0] => Array
(
[particular_name] => HELLO WORLD
[child_label] => Array
(
[items] => Array
(
[0] => Array
(
[particular] => Sample Particular Name1.1
[unit_label] => sqm
[unit_price] => 100

)

)

)

[note] => SAMLPE NOTE 12
)

[1] => Array
(
[particular_name] => TEST ME
[child_label] => Array
(
[items] => Array
(
[0] => Array
(
[particular] => Sample Particular 2.1
[unit_label] => sqm
[unit_price] => 1000

)

[1] => Array
(
[particular] => Sample Particular Name2.2
[unit_label] => lot
[unit_price] => 2000

)

)

)

[note] => SAMLPE NOTE 22
)

)


And in the excel I can display the particular_name in the excel. Now I want to add the child_label below it. But I don't how.

Here's some of my code:

$baseRow = 14;

$style_header = array(
'font' => array(
'bold' => true
)
);


foreach($content_list['items'] as $r => $dataRow) {

$row = $baseRow + $r;
$h_counter = $r + 1;

$objPHPExcel->getActiveSheet()->insertNewRowBefore($row,1);

$objPHPExcel->getActiveSheet()->setCellValue('A'.$row, $h_counter);

$objPHPExcel->getActiveSheet()->setCellValue('B'.$row, $dataRow['particular_name']); //OK NO ISSUE
$objPHPExcel->getActiveSheet()->getStyle('B'.$row)->applyFromArray($style_header);

/* HERE IS THE ISSUE, I CREATE A LOOP TO DISPLAY THE 2ND ARRAY BUT IT ONLY GETS THE LAST ARRAY AND INSTEAD OF DISPLAYING IT TO THE
BOTTOM OF THE PARTICULAR_NAME IT SHOWS AT THE TOP

foreach($dataRow['child_label']['items'] as $key => $value) {

$child_row = $row + $key;
$child_counter = $child_row + 1;

$objPHPExcel->getActiveSheet()->insertNewRowBefore($child_row,1);

$objPHPExcel->getActiveSheet()->setCellValue('A'.$child_row, $row . "." . $child_row);

$objPHPExcel->getActiveSheet()->setCellValue('B'.$child_row, $value['particular']);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$child_row, $value['unit']);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$child_row, $value['unit_label']);
$objPHPExcel->getActiveSheet()->setCellValue('H'.$child_row, $value['unit_price']);
$objPHPExcel->getActiveSheet()->setCellValue('I'.$child_row, "=F" . $child_row . "* H" . $child_row);

}

$objPHPExcel->getActiveSheet()->removeRow($row-1,1);
*/
}

$objPHPExcel->getActiveSheet()->removeRow($baseRow-1,1);

Answer

You are handling the row indexes incorrectly in your loops and that results your code to write several times to same rows. You need a generic row counter to get the row numbers correctly in place, i.e. something like this (I removed the removeRow calls as I didn't quite understand what you were trying to achieve with those):

$rowCounter = $baseRow;

foreach($data as $r => $dataRow) 
{
    //$row = $baseRow + $rowCounter;
    //$h_counter = $r + 1;

    $objPHPExcel->getActiveSheet()->insertNewRowBefore($rowCounter,1);

    $objPHPExcel->getActiveSheet()->setCellValue('A'.$rowCounter, $rowCounter);

    $objPHPExcel->getActiveSheet()->setCellValue('B'.$rowCounter, $dataRow['particular_name']); //OK NO ISSUE
    $objPHPExcel->getActiveSheet()->getStyle('B'.$rowCounter)->applyFromArray($style_header);

    $rowCounter++;

    foreach($dataRow['child_label']['items'] as $key => $value) 
    {
        //$child_row = $row + $key;
        //$child_counter = $child_row + 1;

        $objPHPExcel->getActiveSheet()->insertNewRowBefore($rowCounter,1);

        $objPHPExcel->getActiveSheet()->setCellValue('A'.$rowCounter, $rowCounter . "." . $rowCounter);

        $objPHPExcel->getActiveSheet()->setCellValue('B'.$rowCounter, $value['particular']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$rowCounter, $value['unit']);
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$rowCounter, $value['unit_label']);
        $objPHPExcel->getActiveSheet()->setCellValue('H'.$rowCounter, $value['unit_price']);
        $objPHPExcel->getActiveSheet()->setCellValue('I'.$rowCounter, "=F" . $rowCounter . "* H" . $rowCounter);
        $rowCounter++;
    }

    //$objPHPExcel->getActiveSheet()->removeRow($row-1,1);
}