Add array of objects to PHPExcel template

Question

I'm currently pulling an array of objects from my database with the intention of each object representing a row in a PHPExcel template. The data is as follows:

[
    {
        name: test,
        age: test,
        other_info: test,
        my_info: test
    },
    {
        name: test,
        age: test,
        other_info: test,
        my_info: test
    },
    {
        name: test,
        age: test,
        other_info: test,
        my_info: test
    },
]

The rows in my Excel sheet start at 17 - so the first object would be entered in row 17, the second object in the array would be entered in row 18 and so on. However, the columns I wish to add to are not all next to each other, so its difficult to loop. For example, the name field of the first object should be entered into C17, while the age field should be entered into F17 and the other_info field should be entered into J17 and my_info should be in L17. The second object would then be entered into the same columns but the next row and so on.

I have tried looping through the array but that doesn't allow me to skip the columns, only add to consecutive columns and rows;

foreach ($array as $rowArray) {
    $columnID = 'C';
    $rowID = 17;
    foreach ($rowArray as $columnValue) {
        $objPHPExcel->getActiveSheet()->setCellValue($columnID.$rowID, $columnValue);
        $rowID++;
    } 
}

Can anyone give me some guidance on this? Much appreciated!

Chris Chris · Accepted Answer · 2017-12-08T12:41:26

Try this one:

$json = '[
            {
                "name": "name1",
                "age":"age1",
                "other_info":"other_info1",
                "my_info":"my_info1"
            },
            {
                "name": "name2",
                "age": "age2",
                "other_info": "other_info2",
                "my_info": "my_info2"
            }
        ]';

$phpExcelObject = $this->get('phpexcel')->createPHPExcelObject();
$cells = ['name' => 'C', 'age' => 'F', 'other_info' => 'J', 'my_info' => 'L'];
$array = json_decode($json, true);

$rowID = 17;
foreach($array as $rowArray) {
    foreach ($rowArray as $index => $columnValue) {
        $col = $cells[$index].$rowID;
        $phpExcelObject
          ->getActiveSheet()
          ->setCellValue((string)$col, $columnValue);
    }
    $rowID++;
}

Add array of objects to PHPExcel template

1 Answers