php 从 MySQL 创建 Excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17131743/
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
Creating Excel file from MySQL
提问by Dipak Saraf
I am trying to generate an XLS file from a table in a MySQL DB, but the Excel file is not properly formatted & given error when the Excel file generated "The file which you are trying to open is in different format than one specified". When the file is opened the data is not properly formatted.
我正在尝试从 MySQL 数据库中的表生成一个 XLS 文件，但是当 Excel 文件生成“您尝试打开的文件与指定的格式不同”时，Excel 文件格式不正确并出现错误。打开文件时，数据格式不正确。
Any ideas what I am missing?
任何想法我缺少什么？
<?php
$host = 'XXXXXXX';
$dbname = 'XXXXXXXX';
$username = 'XXXXXXXX';
$password = 'XXXXXXXX';
function xlsBOF() {
echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}
function xlsEOF() {
echo pack("ss", 0x0A, 0x00);
return;
}
function xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo $Value;
return;
}
function xlsWriteNumber($Row, $Col, $Value) {
echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo pack("d", $Value);
return;
}
try {
$conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
echo "Connected to $dbname at $host successfully.";
$conn = null;
} catch (PDOException $pe) {
die("Could not connect to the database $dbname :" . $pe->getMessage());
}
$q = "SELECT * FROM tablename";
$qr = mysql_query( $q ) or die( mysql_error() );
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=export_".$dbtable.".xls ");
header("Content-Transfer-Encoding: binary ");
xlsBOF();
$col = 0;
$row = 0;
$first = true;
while( $qrow = mysql_fetch_assoc( $qr ) )
{
if( $first )
{
foreach( $qrow as $k => $v )
{
xlsWriteLabel( $row, $col, strtoupper( ereg_replace( "_" , " " , $k ) ) );
$col++;
}
$col = 0;
$row++;
$first = false;
}
// go through the data
foreach( $qrow as $k => $v )
{
// write it out
xlsWriteLabel( $row, $col, $v );
$col++;
}
// reset col and goto next row
$col = 0;
$row++;
}
xlsEOF();
exit();
采纳答案by Marcel Wasko
I'm not sure about .xls but for outputting a MySQL result as a CSV table the fputcsv function does it without much fuss:
我不确定 .xls 但是为了将 MySQL 结果输出为 CSV 表，fputcsv 函数可以毫不费力地完成它：
// Clear any previous output
ob_end_clean();
// I assume you already have your $result
$num_fields = mysql_num_fields($result);
// Fetch MySQL result headers
$headers = array();
$headers[] = "[Row]";
for ($i = 0; $i < $num_fields; $i++) {
$headers[] = strtoupper(mysql_field_name($result , $i));
}
// Filename with current date
$current_date = date("y/m/d");
$filename = "MyFileName" . $current_date . ".csv";
// Open php output stream and write headers
$fp = fopen('php://output', 'w');
if ($fp && $result) {
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename='.$filename);
header('Pragma: no-cache');
header('Expires: 0');
echo "Title of Your CSV File\n\n";
// Write mysql headers to csv
fputcsv($fp, $headers);
$row_tally = 0;
// Write mysql rows to csv
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$row_tally = $row_tally + 1;
echo $row_tally.",";
fputcsv($fp, array_values($row));
}
die;
}
回答by janhsh
use http://phpexcel.codeplex.com/
使用 http://phpexcel.codeplex.com/
C'est la meilleur solution pour générer un fichier excel Vous pouvez même créer plusieurs feuilles dans le fichier et formater les cellules (couleur, police, bordure, ...)
C'est la meilleur solution Pour générer un fichier excel Vous pouvez même créer plusieurs feuilles dans le fichier et formater les cellules (couleur, police, bordure, ...)
Google translate:
谷歌翻译：
This is the best solution to generate an excel file You can even create multiple sheets in the file and format the cells (color, font, border, ...)
这是生成 excel 文件的最佳解决方案您甚至可以在文件中创建多个工作表并设置单元格格式（颜色、字体、边框等）
回答by rahul patel
<?php
//download.php page code
//THIS PROGRAM WILL FETCH THE RESULT OF SQL QUERY AND WILL DOWNLOAD IT. (IF YOU HAVE ANY QUERY CONTACT:[email protected])
//include the database file connection
include_once('database.php');
//will work if the link is set in the indx.php page
if(isset($_GET['name']))
{
$name=$_GET['name']; //to rename the file
header('Content-Disposition: attachment; filename='.$name.'.xls');
header('Cache-Control: no-cache, no-store, must-revalidate, post-check=0, pre-check=0');
header('Pragma: no-cache');
header('Content-Type: application/x-msexcel; charset=windows-1251; format=attachment;');
$msg="";
$var="";
//write your query
$sql="select * from tablename";
$res = mysql_query($sql);
$numcolumn = mysql_num_fields($res); //will fetch number of field in table
$msg="<table><tr><td>Sl No</td>";
for ( $i = 0; $i < $numcolumn; $i++ ) {
$msg.="<td>";
$msg.= mysql_field_name($res, $i); //will store column name of the table to msg variable
$msg.="</td>";
}
$msg.="</tr>";
$i=0;
$count=1; //used to print sl.no
while($row=mysql_fetch_array($res)) //fetch all the row as array
{
$msg.="<tr><td>".$count."</td>";
for($i=0;$i< $numcolumn;$i++)
{
$var=$row[$i]; //will store all the values of row
$msg.="<td>".$var."</td>";
}
$count=$count+1;
$msg.="</tr>";
}
$msg.="</table>";
echo $msg; //will print the content in the exel page
}
?>
<?php
//index.php page
$name="any file name";
echo "<a href='download.php?name=".$name."'>Click to download</a>"; //link to download file
?>
回答by Abid Ali
Here is simple Excel file generation function, very fast and exactly .xls file.
这是简单的 Excel 文件生成功能，非常快速且准确地生成 .xls 文件。
$filename = "sample_php_excel.xls";
$data = array(
array("User Name" => "Abid Ali", "Q1" => "055", "Q2" => "067", "Q3" => 32045, "Q4" => 39043),
array("User Name" => "Sajid Ali", "Q1" => "080", "Q2" => "677", "Q3" => 32025, "Q4" => 34010),
array("User Name" => "Wajid Ali", "Q1" => "067", "Q2" => "075", "Q3" => 95404, "Q4" => 102055),
);
to_xls($data, $filename);
function to_xls($data, $filename){
$fp = fopen($filename, "w+");
$str = pack(str_repeat("s", 6), 0x809, 0x8, 0x0, 0x10, 0x0, 0x0); // s | v
fwrite($fp, $str);
if (is_array($data) && !empty($data)){
$row = 0;
foreach (array_values($data) as $_data){
if (is_array($_data) && !empty($_data)){
if ($row == 0){
foreach (array_keys($_data) as $col => $val){
_xlsWriteCell($row, $col, $val, $fp);
}
$row++;
}
foreach (array_values($_data) as $col => $val){
_xlsWriteCell($row, $col, $val, $fp);
}
$row++;
}
}
}
$str = pack(str_repeat("s", 2), 0x0A, 0x00);
fwrite($fp, $str);
fclose($fp);
}
function _xlsWriteCell($row, $col, $val, $fp){
if (is_float($val) || is_int($val)){
$str = pack(str_repeat("s", 5), 0x203, 14, $row, $col, 0x0);
$str .= pack("d", $val);
} else {
$l = strlen($val);
$str = pack(str_repeat("s", 6), 0x204, 8 + $l, $row, $col, 0x0, $l);
$str .= $val;
}
fwrite($fp, $str);
}
回答by Manikandan
<?php
ob_end_clean();
$num_fields = mysql_num_fields($result);
$headers = array();
$headers[] = "[Row]";
for ($i = 0; $i < $num_fields; $i++)
$headers[] = strtoupper(mysql_field_name($result , $i));
$current_date = date("y/m/d");
$filename = "MyFileName" . $current_date . ".csv";
$fp = fopen('php://output', 'w');
if ($fp && $result) {
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename='.$filename);
header('Pragma: no-cache');
header('Expires: 0');
echo "Title of Your CSV File\n\n";
fputcsv($fp, $headers);
$row_tally = 0;
// Write mysql rows to csv
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$row_tally = $row_tally + 1;
echo $row_tally.",";
fputcsv($fp, array_values($row));
}
die;
}
?>
