php 使用 PHPExcel 导出到 xlsx

Using PHPExcel to export to xlsx

提问by Ankur

I am using PHPEXxcel to export an HTML Table generated using MYSQL and its like this.

我正在使用 PHPEXxcel 导出使用 MYSQL 生成的 HTML 表，就像这样。

<?php $query = "SELECT `Firstname`,`Lastname`,`Branch`,`Gender`,`Mobileno`, `Email`  
      FROM `student_details` WHERE Branch IN ('$branch') and `Year`='$year' 
         and Tenthresult > '$tenth' and 
      Twelthresult > '$twelth' and (CGPA > '$cgpa' || CGPA = '$cgpa')";  

$result = mysql_query($query);
confirm_query($result);
$objPHPExcel = new PHPExcel(); 
$objPHPExcel->setActiveSheetIndex(0); 

$rowCount = 1; 
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount,'Firstname');
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount,'Lastname');
$objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount,'Branch');
$objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount,'Gender');
$objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount,'Mobileno');
$objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount,'Email');

while($row = mysql_fetch_array($result)){ 
    $rowCount++;
    $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['0']);
    $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['1']);
    $objPHPExcel->getActiveSheet()->SetCellValue('C'.$rowCount, $row['2']);
    $objPHPExcel->getActiveSheet()->SetCellValue('D'.$rowCount, $row['3']);
    $objPHPExcel->getActiveSheet()->SetCellValue('E'.$rowCount, $row['4']);
    $objPHPExcel->getActiveSheet()->SetCellValue('F'.$rowCount, $row['5']);
} 

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
$objWriter->save('some_excel_file.xlsx'); 
?>

Its working but it saves the xlsx file in the root folder without showing to user any signs that its being downloaded. This code rund when i click a button.now, can i make it to be downloaded like we download a mail attachment and showing the user in the front end that its being downloaded along with the location.

它可以工作，但它将 xlsx 文件保存在根文件夹中，而不向用户显示任何正在下载的迹象。当我单击按钮时运行此代码。现在，我可以像下载邮件附件一样下载它，并在前端向用户显示其正在下载的位置。

I tried using

我尝试使用

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="01simple.xls"');
header('Cache-Control: max-age=0'); 

With this, i am getting what i wanted above but the xls file downloaded when opened shows the message 'The File you are trying to open 'filename' is in a different format than the specified extension.....etc.Do you want to open now?

有了这个，我得到了我上面想要的但是打开时下载的 xls 文件显示消息“您尝试打开的文件‘文件名’的格式与指定的扩展名不同......等你想要吗现在打开？

On opening it contains either the entire HTML Page or its simply blank... Can anybody help me..?

在打开它包含整个 HTML 页面或它只是空白...任何人都可以帮助我..？

回答by Mark Baker

Spreadsheets 101

电子表格 101

There are many different spreadsheet file formats, each with their own different filename extensions, and that can be sent to a web browser using different mime types. These are described in the PHPExcel documentation, and each has its own different Writer in PHPExcel. You're mismatching two different formats

有许多不同的电子表格文件格式，每种格式都有自己不同的文件扩展名，并且可以使用不同的 mime 类型发送到 Web 浏览器。这些在 PHPExcel 文档中都有描述，每个在 PHPExcel 中都有自己不同的 Writer。您不匹配两种不同的格式

BIFF Format

• Used by Microsoft Excel between versions 95 and 2003 File

• extension:xls

• PHPEXcel Writer:PHPExcel_Writer_Excel5

• Mime Type:application/vnd.ms-excel

OfficeOpenXML Format

• Used by Microsoft Excel since version 2007

• File extension:xlsx

• PHPEXcel Writer:PHPExcel_Writer_Excel2007

• Mime Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

BIFF 格式

• Microsoft Excel 在版本 95 和 2003 之间使用文件

• 扩展名：xls

• PHPEXcel Writer:PHPExcel_Writer_Excel5

• Mime 类型：application/vnd.ms-excel

OfficeOpenXML 格式

• 自 2007 版起由 Microsoft Excel 使用

• 文件扩展名：xlsx

• PHPEXcel 编写器：PHPExcel_Writer_Excel2007

• Mime 类型：application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Don't mix and match: if you do, then Excel will (and justifiably) complain. If you want a BIFF file, use PHPExcel's BIFF Writer (Excel5), a file extension of .xls, and the mime type listed above for BIFF Format. If you want an OfficeOpenXML file, then use PHPExcel's Excel2007 Writer, a file extension of .xlsx, and the mime type listed above for OfficeOpenXML.

不要混搭：如果你这样做了，Excel 会（并且有理由地）抱怨。如果您需要 BIFF 文件，请使用 PHPExcel 的 BIFF Writer (Excel5)、.xls 文件扩展名和上面列出的 BIFF 格式的 mime 类型。如果您需要 OfficeOpenXML 文件，请使用 PHPExcel 的 Excel2007 Writer，文件扩展名为 .xlsx，以及上面列出的 OfficeOpenXML 的 mime 类型。

EDIT

编辑

Note that the examples provided with the PHPExcel distribution include 01simple-download-xls.php and 01simple-download-xlsx.php to demonstrate exactly what you want

请注意，PHPExcel 发行版提供的示例包括 01simple-download-xls.php 和 01simple-download-xlsx.php 以准确演示您想要的内容

回答by Nanne

Just adding those headers only sends those headers. The rest of your code is still the same, so you're saving your xls to your root folder like you used to.

仅添加这些标头只会发送这些标头。其余代码仍然相同，因此您将像以前一样将 xls 保存到根文件夹中。

Sending the headers only makes the page you would normally see be send with xls headers. Something which is not what you want, and you're getting your HTML page but with the wrong headers.

发送标题只会使您通常看到的页面与 xls 标题一起发送。不是你想要的东西，你得到了你的 HTML 页面，但标题错误。

What you need to do is send those headers and then stream the xlsx.

您需要做的是发送这些标头，然后流式传输 xlsx。

Looking at a random thread (I know, bad idea, but this'll get you a headstart on what to do) here, you can find examples like this:

看着随机线程（我知道，坏主意，但是这会让你在做什么领域获得领先地位），在这里，你可以找到这样的例子：

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=$filename.xls");
header("Content-Transfer-Encoding: binary ");
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
$objWriter->setOffice2003Compatibility(true);
$objWriter->save('php://output');

回答by Zaid Bin Khalid

I do it with using below snippet.

我使用下面的代码片段来做到这一点。

// Redirect output to a client's web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="test_file.xlsx"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

回答by Yusuf Mawahid

try this ..

尝试这个 ..

header('Content-Type: application/vnd.ms-excel');
    $filename = "Reports".date("d-m-Y-His").".xls";
    header('Content-Disposition: attachment;filename='.$filename .' ');
    header('Cache-Control: max-age=0');
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');