PhpSpreadsheet Populate cells using a loop

Question

I am using PHP library PhpSpreadsheet and would like to populate a spreadsheet (xlsx) using data from MySQL table using a loop to iterate through the cells and rows, something similar to this:

.------------------------.
|ID first_name last_name |
|1  John       Smith     |
|2  John       Doe       |
`------------------------`

My table will have the first row as header (bold text) and the rows below will be the data from MySQL.

Here is the script I wrote for this purpose:

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$spreadsheet->getActiveSheet()->getStyle('A1:C1')->getFont()->setBold( true );

$header_row_array = ['ID', 'First Name', 'Last Name'];
$spreadsheet->getActiveSheet()->fromArray( $header_row_array, NULL, 'A1' );

global $wpdb;
$query = 'SELECT * FROM custom_table WHERE DATE( ts ) BETWEEN SUBDATE( NOW(), 1) and NOW()';
$rows = $wpdb->get_results( $query, OBJECT );

foreach( $rows as $row ) {
    for( $i = 2; $i <= count( $rows ) + 1; $i++ ) {
        foreach( range( 'A', 'C' ) as $v ) {
            switch( $v ) {
                case 'A': {
                    $value = $row->id;
                    break;
                }
                case 'B': {
                    $value = $row->first_name;
                    break;
                }
                case 'C': {
                    $value = $row->last_name;
                    break;
                }
            }
            $spreadsheet->getActiveSheet()->setCellValue( $v . $i, $value );
        }
    }
    //echo '<pre>';var_dump( $row );echo '</pre>';
}

$writer = new Xlsx( $spreadsheet );
$writer->save( 'test.xlsx' );

I also think these loops are a crude way to solve this, if you have any idea on improvements please share!

The result I am getting is:
The same row data in every row, as if the outer loop doesn't really loop through the items.

Please advise.
Thanks

Answer 1

I know it's a little late, but I want to share my workaround for this same problem. I start where you are already fetched data from database:

$header_row_array = ['ID', 'First Name', 'Last Name'];
$spreadsheet->getActiveSheet()->fromArray( $header_row_array);

Since it's the first row you insert on the spreadsheet, it's not neccesary pass all the arguments to fromArray(), just the array containing the headers of each colum.

Next thing to do is iterate through $rows. With a fromArray() and one foreach statement you could get this, just adding a counter (not the most elegant solution, but practical).

$count = 2;
foreach($rows as $row){
    $spreadsheet->getActiveSheet()->fromArray(array($row->id, $row->first_name, $row->last_name), null, 'A'.$count);
    $count++;
}

Like you did when inserted the header row, I just pass the cell of the first colum (A) in the current row (remember $count starts at 2), the method puts the regarding data in the consecuent cells of that same row. The counter starts on 2 because A1 is already used by the header row, so it starts at A2 and sums up from there.

I hope it helps!

Answer 2

The problem came from your loops.

$rows = [
 ['id'=> 1, 'first_name'=> 'John', 'last_name'=> 'Smith'],
 ['id'=> 2, 'first_name'=> 'Jane', 'last_name'=> 'Doe'],
];

foreach( $rows as $row ) {
    for( $i = 2; $i <= count( $rows ) + 1; $i++ ) {
        foreach( range( 'A', 'C' ) as $v ) {
            switch( $v ) {
                case 'A': {
                    // $value = $row->id;
                    $value = $row['id'];
                    break;
                }
                case 'B': {
                    // $value = $row->first_name;
                    $value = $row['first_name'];
                    break;
                }
                case 'C': {
                    // $value = $row->last_name;
                    $value = $row['last_name'];
                    break;
                }
            }
            print $v.$i.' : '. $value . "\n";
        }
        print '--------' . "\n";
    }
}

return

A2 : 1
B2 : John
C2 : Smith
--------
A3 : 1
B3 : John
C3 : Smith
--------
A2 : 2
B2 : Jane
C2 : Doe
--------
A3 : 2
B3 : Jane
C3 : Doe
--------

Edit

For not thinking here is the solution

$i = 2;
foreach( $rows as $row ) {
    foreach( range( 'A', 'C' ) as $v ) {
        switch( $v ) {
            case 'A': {
                $value = $row->id;
                break;
            }
            case 'B': {
                $value = $row->first_name;
                break;
            }
            case 'C': {
                $value = $row->last_name;
                break;
            }
        }
        print $v.$i.' : '. $value . "\n";
    }
    $i++;
}

output

A2 : 1
B2 : John
C2 : Smith
A3 : 2
B3 : Jane
C3 : Doe