从数据库导出时出现PHPexcel库错误

Question

I am using the PHPexcel Library for my reporting. It functions well when I'm using static values but, when I try to export my data in MySQL database to PHPexcel,

it always gives me this error

Warning: Cannot modify header information - headers already sent 

I've tried ob_start() function but the moment i opened my downloaded excel, it is empty

this is my PHPexcel code:

<?php

include("conn.php");

$query ="Select * from info";
$result = mysql_query($query);



/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
ini_set('include_path', ini_get('include_path').';../Classes/');

/** PHPExcel */
include 'PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';

// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object
";
$objPHPExcel = new PHPExcel();




// Add some data
$row1=1;
$row2=1;
$row3=1;

echo date('H:i:s') . " Add some data
";
$objPHPExcel->setActiveSheetIndex(0);


while($row = mysql_fetch_array($result))
{
$let1 = "A".$row1."";
$let2 = "B".$row2."";
$let3 = "C".$row3."";

$objPHPExcel->getActiveSheet()->SetCellValue($let1, $row['id']);
$objPHPExcel->getActiveSheet()->SetCellValue($let2, $row['name']);
$objPHPExcel->getActiveSheet()->SetCellValue($let3, $row['age']);

$row1++;
$row2++;
$row3++;
}

$objPHPExcel->getActiveSheet()->getStyle("A1:C1")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A1:C1")->getFont()->setSize(20);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:C1');

$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(
    array(
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'FF0000')
        )
    )
);


header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename=\"filename.xlsx\"");
header("Cache-Control: max-age=0");

exit();

?>

It works perfect dealing with static values but when I tried to dynamically add values, it messes up. Please help, thank u in advance

Answer 1

Looking at your code, your header() calls are saying that the content of the page should be treated as a spreadsheet but you do not create any output from your spreadsheet object. Remove all output (your echo calls) as it's not possible to have it, move your header calls to the top of the file and echo out the raw output from your Excel class.

Code below. I've moved the headers, stripped some junk and added what the PHPExcel manual suggests is the way to write to STDOUT.

<?php

header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header("Content-Disposition: attachment;filename=\"filename.xlsx\"");
header("Cache-Control: max-age=0");

/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
ini_set('include_path', ini_get('include_path').';../Classes/');

/** PHPExcel */
include 'PHPExcel.php';

/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';

include("conn.php");

$query ="Select * from info";
$result = mysql_query($query);

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Add some data
$row1=1;
$row2=1;
$row3=1;

$objPHPExcel->setActiveSheetIndex(0);

while($row = mysql_fetch_array($result))
{
    $let1 = "A".$row1."";
    $let2 = "B".$row2."";
    $let3 = "C".$row3."";

    $objPHPExcel->getActiveSheet()->SetCellValue($let1, $row['id']);
    $objPHPExcel->getActiveSheet()->SetCellValue($let2, $row['name']);
    $objPHPExcel->getActiveSheet()->SetCellValue($let3, $row['age']);

    $row1++;
    $row2++;
    $row3++;
}

$objPHPExcel->getActiveSheet()->getStyle("A1:C1")->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle("A1:C1")->getFont()->setSize(20);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:C1');

$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(
    array(
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'FF0000')
        )
    )
);

// Untested... pulled from the manual as the way to write with PHPExcel
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');                                                                                                           
$objWriter->save('php://output');

?>