Looping logic when adding new rows in PHPExcel

Question

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 1

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);
}