PHPExcel:在客户端下载Excel文件 [英] PHPExcel: Download the Excel file on the client side

问题描述

问题已解决:对于其他可能有此问题的用户-请注意PHP文件的编码.如果使用PHPExcel，则必须是ANSII编码，而不是UTF8，否则EXCEL将被错误地下载.我更改了文件本身的编码后，添加的标题(答案1)解决了该问题.

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.

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

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.

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

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:

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');

但是，如果我这样做，则下载的文件为: 1.里面有些杂物 2.说Excel需要修复它，因为文件不好. 问题在于该文件另存为UTF8，如果我将其编码为ANSI，则它可以正常工作，但是当然，这是手动更改，因此我需要一个能够正常工作的Excel才能吸引用户.

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.

什么是错误?

我的代码有效(但是将文件下载到服务器上):

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');


?>

推荐答案

删除以下内容:

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();

在创建Writer之前插入以下标头:

Insert the following headers just before creating the 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");

这应该解决乱码.如果仍然收到这样的文本，请在最后一行($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");):

ob_clean();

这对我有用.希望对您有所帮助.

This worked for me. Hope it helps.

这篇关于PHPExcel:在客户端下载Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！