php PHPExcel：在客户端下载Excel文件

PHPExcel: Download the Excel file on the client side

提问by Alex

The problem was solved: For other users that may have this problem - notice the encoding of the PHP file. If you use PHPExcel it must be ANSII encoding and not UTF8, otherwise the EXCEL will be downloaded corruptly. The Headers that were added (answer 1) solved the problem after i changed the encoding of the file itself.

问题已解决：对于可能有此问题的其他用户 - 请注意 PHP 文件的编码。如果您使用 PHPExcel，它必须是 ANSII 编码而不是 UTF8，否则 EXCEL 将被损坏。在我更改文件本身的编码后，添加的标题（答案 1）解决了问题。

I am using PHPExcel in order to create an EXCEL from a table in MYSQL DB, so the user can download it to his computer.

我正在使用 PHPExcel 从 MYSQL DB 中的表创建 EXCEL，以便用户可以将其下载到他的计算机。

The code bellow creates a correct Excel file but the problem is that it is downloaded to my server. I read in PHPExcel manual that i need to add headers:

下面的代码创建了一个正确的 Excel 文件，但问题是它被下载到我的服务器上。我在 PHPExcel 手册中读到我需要添加标题：

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$name.'.xls"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
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, 'Excel5');
$objWriter->save('php://output');

But if i do that, the downloaded file is: 1. Has some jibrish inside 2. Says that Excel needs to fix it because the file is not good. The problem is that this file is saved as UTF8 and if i encode it as ANSI then it is working properly but of course it is a manual change and i need a working properly excel to reach the users.

但如果我这样做，下载的文件是： 1. 里面有一些乱七八糟的东西 2. 说 Excel 需要修复它，因为文件不好。问题是这个文件被保存为 UTF8，如果我将它编码为 ANSI，那么它可以正常工作，但当然这是一个手动更改，我需要一个正常工作的 excel 才能联系到用户。

What is the bug?

什么是错误？

My code that works (but download the file to the server):

我的代码有效（但将文件下载到服务器）：

<?php
include 'connection.php';
include 'checkUser.php';

//Getting all the needed information from the DB
$task_id=$_GET['id'];
$query2 = "SELECT * FROM projects WHERE ProjectID=$task_id";
$data2 = mysqli_query($con, $query2);
$row = mysqli_fetch_array($data2);
$project_type = $row['ProjectType'];
$project_name = $row['ProjectName'];

switch ($project_type){
                    case 2: $NumberOfRows=22;  $project = "slivedetails"; break;
                    case 3: $NumberOfRows=30;  $project = "plivedetails"; break;
                    default: $NumberOfRows=0; $project = "none";
                    }
//column names
if ($project="slivedetails"){
    $ColumnNames = mysqli_query($con,"SHOW COLUMNS FROM slivedetails") or die("mysql error"); 
    }
else if ($project="plivedetails"){
    $ColumnNames = mysqli_query($con, "SHOW COLUMNS FROM plivedetails") or die("mysql error"); 
    }

$query = "SELECT * FROM $project WHERE TaskID=$task_id";
$data = mysqli_query($con, $query);
$num_rows = mysqli_num_rows($data); 


/** Include PHPExcel */
require_once 'PHPExcel_1.7.9_doc/Classes/PHPExcel.php';
require_once 'PHPExcel_1.7.9_doc/Classes/PHPExcel/IOFactory.php';


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

// writer already created the first sheet for us, let's get it
$objSheet = $objPHPExcel->getActiveSheet();
// rename the sheet
$objSheet->setTitle('Task Results');

// let's bold and size the header font and write the header
// as you can see, we can specify a range of cells, like here: cells from A1 to A4
$objSheet->getStyle('A1:AD1')->getFont()->setBold(true)->setSize(12);

$char = 65;
// write header]
for ($i=1;$i<=$NumberOfRows;$i++){
    $col_name = mysqli_fetch_array($ColumnNames);
    $objSheet->getCell(chr($char).'1')->setValue($col_name['Field']);
    $char++;
}

// Now we need to get the data from the DB. While we have a row in the result:
$rowIterator=2; //our row number. We begin from 2 because the first one is the title.

while ($RowInfo = mysqli_fetch_array($data)){
//We will fill the information based on the amount of columns:
$char = 65; //we set the first char as column A
for ($i=0;$i<$NumberOfRows;$i++){
    $objSheet->getCell(chr($char).$rowIterator)->setValue($RowInfo[$i]);
    $char++;
}
$rowIterator++;
}

// autosize the columns
$char = 65;
for ($i=1;$i<=$NumberOfRows;$i++){
    $objSheet->getColumnDimension(chr($char))->setAutoSize(true);
    $char++;
}

// create the writer
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
$objWriter->save('results.xlsx');


?>

采纳答案by Placid

Remove the following inclusion:

删除以下包含：

require_once 'PHPExcel_1.7.9_doc/Classes/PHPExcel/IOFactory.php';

You declared the object twice. Remove one of them:

您声明了该对象两次。删除其中之一：

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

Insert the following headers just before creating the Writer:

在创建 Writer 之前插入以下标题：

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

Instead of the following (which actually saves the file in the server):

而不是以下（实际上将文件保存在服务器中）：

$objWriter->save('results.xlsx');

Insert the following (which will create the downloadable file):

插入以下内容（这将创建可下载文件）：

$objWriter->save("php://output");

This should solve the gibberish text. If you still get such text, insert the following code before the last line ($objWriter->save("php://output");):

这应该可以解决乱码。如果您仍然收到此类文本，请在最后一行 ( $objWriter->save("php://output");)之前插入以下代码：

ob_clean();

This worked for me. Hope it helps.

这对我有用。希望能帮助到你。

回答by Legionar

This should work, try to modify your code like this:

这应该有效，尝试像这样修改您的代码：

header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=my_excel_filename.xls");
header("Pragma: no-cache");
header("Expires: 0");

flush();

require_once 'PHPExcel.php';

$objPHPExcel = new PHPExcel();

// here fill data to your Excel sheet

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('php://output');

回答by Samir Dadash-zade

Alex, I had the same problem. I did all of them but again result was the same. I just changed $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

亚历克斯，我遇到了同样的问题。我做了所有这些，但结果还是一样。我刚改变 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

to

到

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

and changed all .xlsxto .xls.

并全部更改.xlsx为.xls.

Hope It will help.

希望它会有所帮助。