[FIXED] How to make PHPEXcel add other fields from database

Issue

I've tried to create an spreedsheet with PHPExcel class, from phpexcel.codeplex.com, but I got a situation and can't figure out what's going wrong.

I have 3 fields from database to be insert in the spreedsheet 'name,'price', 'stock'.

The problem is that I only get the product name, which is the very first field in the table.

This is the code I am handling:

$sql = "SELECT name, price, stock FROM products";

$objPHPExcel = new PHPExcel();

$res = $con->query( $sql );

// First row
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow( 'name', 1, 'Name' );
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow( 'price', 1, 'Price' );
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow( 'stock', 1, 'Stock' );

// Other rows
$i = 2;
while( $row = $res->fetch( PDO::FETCH_ASSOC ) ) {
    foreach( $row as $col => $data ) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow( $col, $i, $data );
    }
    $i++;
}

// Redirect output to a client web browser (Excel5)
header( 'Content-Type: application/vnd.ms-excel' );
header( 'Content-Disposition: attachment;filename="report.xls"' );
header( 'Cache-Control: max-age=0' );

$objWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel, 'Excel5' );
$objWriter->save( 'php://output' );
exit;

I can't see what I am missing...

Thanks in advance!

Solution

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow( 'name', 1, 'Name' ); 

The column argument should be a numeric value: 0 = column A, 1 = column B, etc. You're using a string, which will by type juggled to a numeric 0, so everything is being written to the first (0) column, overwriting whatever's already there.... I'm surprised you're not simply getting the value of the last (stock) column

Answered By - Mark Baker

Answer Checked By - Gilberto Lyons (PHPFixing Admin)