php - 使用 PHPExcel 从 MYSQL 生成 excel 文件

Question

我正在使用 PHPExcel 在最终用户单击“生成 excel 文件”按钮后生成 excel 文件。此按钮应为客户端打开一个 excel 文件，并显示数据。到目前为止我有这段代码，但目前它只打开一个没有 excel 文件的空白页面。谁能告诉我我做错了什么？我猜我没有正确连接到数据库..

<?php 
$dbhost= "mysql"; //your MySQL Server 
$dbuser = "survey"; //your MySQL User Name 
$dbpass = "password"; //your MySQL Password 
$dbname = "database"; 
//your MySQL Database Name of which database to use this 
$tablename = "questions"; //your MySQL Table Name which one you have to create excel   file 
// your mysql query here , we can edit this for your requirement 
$sql = "Select * from $table "; 
//create  code for connecting to mysql 
$Connect = @mysql_connect($dbhost, $dbuser, $dbpass) 
 or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); 
 //select database 
 $Db = @mysql_select_db($dbname, $Connect) 
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); 
//execute query 
$result = @mysql_query($sql,$Connect) 
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno()); 

 error_reporting(E_ALL);

 require_once '../Classes/PHPExcel.php';
 $objPHPExcel = new PHPExcel();

 // Set the active Excel worksheet to sheet 0 

$objPHPExcel->setActiveSheetIndex(0);  

// Initialise the Excel row number 

$rowCount = 1;  

// Redirect output to a client’s web browser (Excel5) 
header('Content-Type: application/vnd.ms-excel'); 
header('Content-Disposition: attachment;filename="results.xls"'); 
header('Cache-Control: max-age=0'); 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
$objWriter->save('php://output');

exit;

Answer 1

我知道这已经发布很久了..但它可能对某人有帮助。

您没有为工作表分配任何值。所以在select查询后给出如下代码

while($row=mysqli_fetch_array($result))
              {
               $objWorksheet->fromArray($data, ' ', $ii);
                }

整个代码是这样的:

<?php 
$dbhost= "mysql"; //your MySQL Server 
$dbuser = "survey"; //your MySQL User Name 
$dbpass = "password"; //your MySQL Password 
$dbname = "database"; 
//your MySQL Database Name of which database to use this 
$tablename = "questions"; //your MySQL Table Name which one you have to create excel   file 
// your mysql query here , we can edit this for your requirement 
$sql = "Select * from $table "; 
//create  code for connecting to mysql 
$Connect = @mysql_connect($dbhost, $dbuser, $dbpass) 
 or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno()); 
 //select database 
 $Db = @mysql_select_db($dbname, $Connect) 
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno()); 
//execute query 


 error_reporting(E_ALL);

 require_once '../Classes/PHPExcel.php';
 $objPHPExcel = new PHPExcel();



  // Set the active Excel worksheet to sheet 0 

    $objWorksheet =$objPHPExcel->getActiveSheet(); 
//title for worksheet 
$objWorksheet->setTitle('Data');
//header values
$objWorksheet->setCellValue('A1', 'Sl.');
$objWorksheet->setCellValue('B1', 'Username');
$i=2;


$result = @mysql_query($sql,$Connect);


    while($row=mysqli_fetch_array($result))
              {
                $data= array($row[0],$row[1],$row[2],$row[3]);
                $ii="A".$i;
//fill data
                $objWorksheet->fromArray($data, ' ', $ii);
                 $i++;


              }

// Redirect output to a client’s web browser (Excel5) 
header('Content-Type: application/vnd.ms-excel'); 
header('Content-Disposition: attachment;filename="results.xls"'); 
header('Cache-Control: max-age=0'); 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 
$objWriter->save('php://output');

exit;
?>