php 将数据从php导出到excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11608160/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting data from php to excel
提问by PHP-Zend
I need to export data from php (data retrieved from mysql database) to excel. I'm using Zend Framework. I need to do some changes to my data before exporting to excel. Actually, what I really need is to generate a monthly cash book.
我需要将数据从 php（从 mysql 数据库中检索到的数据）导出到 excel。我正在使用 Zend 框架。在导出到 excel 之前，我需要对我的数据进行一些更改。实际上，我真正需要的是生成每月现金簿。
I have read a lot of documents, but ended up in a mess. I really can't understand how I should begin. Do I really need PEAR? Do I need to download any class library?Isn't there a simple way to do this?
我已经阅读了很多文件，但最终还是一团糟。我真的不明白我应该如何开始。我真的需要梨吗？我需要下载任何类库吗？有没有一种简单的方法可以做到这一点？
Thanks in advance
提前致谢
回答by Samuel Hapak
I would suggest you to use great PHPExcellibrary. It is really powerful, supports variety of formats, can do visual formatting and is easy to use.
我建议你使用很棒的PHPExcel图书馆。它真的很强大，支持多种格式，可以进行可视化格式化并且易于使用。
You can find more about it at their webpage:
http://phpexcel.codeplex.com/. (PHPExcelhas already moved at https://github.com/PHPOffice/PHPExcel)
你可以在他们的网页上找到更多关于它的信息：
http://phpexcel.codeplex.com/. （PHPExcel已移至https://github.com/PHPOffice/PHPExcel）
2019:
2019年：
PHPExcelhas now been superceded by PhpSpreadsheet GitHub.
PHPExcel现在已被PhpSpreadsheet GitHub取代
。
Example of usage:
用法示例：
$objPHPExcel = new PHPExcel();
/** You can set many properties */
$objPHPExcel->getProperties()->setCreator("My company")
->setLastModifiedBy("John Doe")
->setTitle("Annual report")
->setSubject("Sales")
->setDescription("Annual sales report by John Doe")
->setCategory("Finance");
/** Choose one of sheets */
$activeSheet = $objPHPExcel->setActiveSheetIndex(0);
/** Simply set value of cell */
$activeSheet->setCellValue("A1", 'plural');
You can do a lot more of course, reading excel files, setting visual styles, creating plots, expressions and lot more.
当然，您还可以做更多的事情，例如阅读 excel 文件、设置视觉样式、创建绘图、表达式等等。
回答by ytilanigiroon
I wrote a 94 line inventory variance report wherein I grab data from MySQL (6k+ records per table), create a multidimensional array from the MySQL data pulled from multiple tables, and then dump everything into a single string and generate an .xls thusly:
我写了一个 94 行的库存差异报告，其中我从 MySQL 中获取数据（每个表 6k+ 记录），从从多个表中提取的 MySQL 数据创建一个多维数组，然后将所有内容转储到一个字符串中并因此生成一个 .xls：
<?php
////////////////////////////////// BEGIN SETUP
$filename ="document_name.xls";
header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename='.$filename);
////////////////////////////////// END SETUP
////////////////////////////////// BEGIN GATHER
// Your MySQL queries, fopens, et cetera go here.
// Cells are delimited by \t
// \n is just like you might expect; new line/row below
// E.G:
$stuff="PART\tQTY\tVALUE\t\n";
$stuff=$stuff."01-001-0001\t37\t28.76\t\n";
$stuff=$stuff."01-001-0002\t6\t347.06\t\n";
$stuff=$stuff."01-001-0003\t12\t7.11\t\n";
////////////////////////////////// END GATHER
// The point is to get all of your data into one string and then:
////////////////////////////////// BEGIN DUMP
echo $stuff;
////////////////////////////////// END DUMP
?>
No extensions required.
不需要扩展。
The only caveats are that I've not yet figured out how to spit out an .xls without Excel telling me the data may be corrupt - and I haven't tried to do any formatting or anything more complex than simply "exporting" the data. The file/data is fine, of course, but it does generate a warning from Excel.
唯一需要注意的是，我还没有弄清楚如何在没有 Excel 告诉我数据可能已损坏的情况下吐出 .xls - 而且我没有尝试进行任何格式化或任何比简单地“导出”数据更复杂的事情. 当然，文件/数据很好，但它确实会从 Excel 中生成警告。
EDIT: I should note that the 'setup' and 'dump' are from the following: daniweb.com
编辑：我应该注意到“设置”和“转储”来自以下内容：daniweb.com
回答by Vinay
回答by Peon
With a bit of google you would have found this: http://www.the-art-of-web.com/php/dataexport/
用一点谷歌你会发现这个：http: //www.the-art-of-web.com/php/dataexport/
Preparing the data
准备数据
$data = array(
array( "firstname" => "Mary", "lastname" => "Johnson", "age" => 25 ),
array( "firstname" => "Amanda", "lastname" => "Miller", "age" => 18 ),
array( "firstname" => "James", "lastname" => "Brown", "age" => 31 ),
array( "firstname" => "Patricia", "lastname" => "Williams", "age" => 7 ),
array( "firstname" => "Michael", "lastname" => "Davis", "age" => 43 ),
array( "firstname" => "Sarah", "lastname" => "Miller", "age" => 24 ),
array( "firstname" => "Patrick", "lastname" => "Miller", "age" => 27 )
);
The first step is to output the data in a tab-delimited format (CSV can also be used but is slightly more complicated). To achieve this we use the following code:
第一步是以制表符分隔的格式输出数据（也可以使用CSV，但稍微复杂一些）。为了实现这一点，我们使用以下代码：
<?php
header("Content-Type: text/plain");
$flag = false;
foreach( $data as $row ) {
if( !$flag ) {
// display field/column names as first row
echo implode( "\t", array_keys( $row ) ) . "\r\n";
$flag = true;
}
echo implode( "\t", array_values( $row ) ) . "\r\n";
}
exit;
?>
We set the content type to text/plain so that the output can more easily be viewed in the browser. Otherwise, because there is no HTML formatting, the output would appear as a single line of text.
我们将内容类型设置为 text/plain，以便可以更轻松地在浏览器中查看输出。否则，由于没有 HTML 格式，输出将显示为单行文本。
The first line of output will be the column headings (in this case the field names are used). Values are separated with a tab \t and rows with a line break \n. The output should look something like the following:
输出的第一行将是列标题（在本例中使用字段名称）。值用制表符 \t 分隔，行用换行符 \n 分隔。输出应如下所示：
firstname lastname age
Mary Johnson 25
Amanda Miller 18
James Brown 31
Patricia Williams 7
Michael Davis 43
Sarah Miller 24
Patrick Miller 27
There's already a weakness in this code that may not be immediately obvious. What if one of the fields to be ouput already contains one or more tab characters, or worse, a newline? That's going to throw the whole process out as we rely on those characters to indicate column- and line-breaks.
这段代码中已经存在一个可能不是很明显的弱点。如果要输出的字段之一已经包含一个或多个制表符，或者更糟的是，包含换行符怎么办？由于我们依靠这些字符来指示列和换行符，这将把整个过程排除在外。
The solution is to 'escape' the tab characters. In this case we're going to replace tabs with a literal \t and line breaks with a literal \n so they don't affect the formatting:
解决方案是“转义”制表符。在这种情况下，我们将用文字 \t 替换制表符，用文字 \n 替换换行符，这样它们就不会影响格式：
<?php
function cleanData( &$str ) {
$str = preg_replace( "/\t/", "\t", $str );
$str = preg_replace("/\r?\n/", "\n", $str);
}
header("Content-Type: text/plain");
$flag = false;
foreach( $data as $row ) {
if( !$flag ) {
// display field/column names as first row
echo implode( "\t", array_keys( $row ) ) . "\r\n";
$flag = true;
}
array_walk( $row, 'cleanData' );
echo implode( "\t", array_values( $row ) ) . "\r\n";
}
exit;
?>
Now, before each row is echoed any tab characters are replaced "\t" so that our columns aren't broken up. Also any line breaks within the data are replaced with "\n".
现在，在回显每一行之前，任何制表符都被替换为“\t”，这样我们的列就不会被分解。此外，数据中的任何换行符都将替换为“\n”。
回答by zessx
You can use CSV to make a importable format for excel. This is the simpliest way to do it.
您可以使用 CSV 为 excel 制作可导入的格式。这是最简单的方法。
CSV looks like this :
CSV 看起来像这样：
"my first cellcontent", "my second cell content", "my third cell content"
"second line, first cell content", "etc", "etc
Each row represents an Excel row, you put cell content between double quotes, and separated by commas.
Be careful to \r\nif you've got some in your datas, it can break your CSV and create unwanted new lines.
每行代表一个 Excel 行，将单元格内容放在双引号之间，并用逗号分隔。请注意，\r\n如果您的数据中有一些，它可能会破坏您的 CSV 并创建不需要的新行。
