CODEWITHSUNDEEP
phpphpexcel
Gilberto Albino
Gilberto Albino

Reputation: 2745

How to make PHPEXcel add other fields from database

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!

Upvotes: 0

Views: 271

Answers (1)

Mark Baker
Mark Baker

Reputation: 212402

$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

Upvotes: 2

Related Questions