PHP MySQLi to formatted xlsx worksheet as columns

Question

Looking at programs like PHP Excel and the newer PHPSpreadsheet, I'm hoping to send results from my mysqli query to Excel in a formatted workbook. However, rather than having the data come into rows, I need each record to display in columns.

STANDARD:

           Name      Age       Sex
 Row1      Tom       30        Male
 Row2      Dick      35        Male
 Row3      Harriett  29        Female

WHAT I NEED

           Row1      Row2      Row3
 Name      Tom       Dick      Harriet
 Age       30        35        29
 Sex       Male      Male      Female

Anyone familiar with how I can achieve this either using PHPSpreadsheet of another method?

Answer 1

PHPSpreadsheet has a setCellValueByColumnAndRow method that makes this kind of manipulation easy.

$column = 2            
foreach($records as $record){
   $spreadsheet->getActiveSheet()
        ->setCellValueByColumnAndRow($column, 2, $record->name)
        ->setCellValueByColumnAndRow($column, 3, $record->age)
        ->setCellValueByColumnAndRow($column, 4, $record->sex);
 $column++;
   //$row++;  original mistake 
}