用 PHP 将 MySQL 数据导出到 Excel

Export MySQL data to Excel in PHP

提问by marc_s

I'm trying to get my MySQL data to Excel file, but I'm having problems with Excel cells. All my text goes to one cell, I would like to have each row value in separate Excel cell. Here is my code:

我正在尝试将我的 MySQL 数据转换为 Excel 文件，但我遇到了 Excel 单元格的问题。我的所有文本都转到一个单元格，我希望将每一行值放在单独的 Excel 单元格中。这是我的代码：

$queryexport = ("
SELECT username,password,fullname FROM ecustomer_users
WHERE fk_customer='".$fk_customer."'
");

$row = mysql_fetch_assoc($queryexport);

$result = mysql_query($queryexport);
$header = '';

for ($i = 0; $i < $count; $i++){
   $header .= mysql_field_name($result, $i)."\t";
   }

while($row = mysql_fetch_row($result)){
   $line = '';
   foreach($row as $value){
          if(!isset($value) || $value == ""){
                 $value = "\t";
          }else{
                 $value = str_replace('"', '""', $value);
                 $value = '"' . $value . '"' . "\t";
                 }
          $line .= $value;
          }
   $data .= trim($line)."\n";
   $data = str_replace("\r", "", $data);

if ($data == "") {
   $data = "\nno matching records found\n";
   }
}
header("Content-type: application/vnd.ms-excel; name='excel'");
header("Content-Disposition: attachment; filename=exportfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

// output data
echo $header."\n".$data;

mysql_close($conn);`

回答by John Peter

Just Try With The Following :

只需尝试以下操作：

PHP Part :

PHP部分：

<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server    
$DB_Username = "username"; //MySQL Username     
$DB_Password = "password";             //MySQL Password     
$DB_DBName = "databasename";         //MySQL Database Name  
$DB_TBLName = "tablename"; //MySQL Table Name   
$filename = "excelfilename";         //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/    
//create MySQL connection   
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database   
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());   
//execute query 
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());    
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");    
//end of printing column names  
//start while loop to get data
    while($row = mysql_fetch_row($result))
    {
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    }   
?>

I think this may help you to resolve your problem.

我认为这可以帮助您解决问题。

回答by Dileep kurahe

Try this code. It's definitly working.

试试这个代码。它绝对有效。

<?php
// Connection 

$conn=mysql_connect('localhost','root','');
$db=mysql_select_db('excel',$conn);

$filename = "Webinfopen.xls"; // File Name
// Download file
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel");
$user_query = mysql_query('select name,work from info');
// Write data to file
$flag = false;
while ($row = mysql_fetch_assoc($user_query)) {
    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";
}
?>

回答by RLytle

If you just want your query data dumped into excel I have to do this frequently and using an html table is a very simple method. I use mysqli for db queries and the following code for exports to excel:

如果您只想将查询数据转储到 excel 中，我必须经常这样做，而使用 html 表是一种非常简单的方法。我使用 mysqli 进行数据库查询，并使用以下代码导出到 excel：

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


echo '<table border="1">';
//make the column headers what you want in whatever order you want
echo '<tr><th>Field Name 1</th><th>Field Name 2</th><th>Field Name 3</th></tr>';
//loop the query data to the table in same order as the headers
while ($row = mysqli_fetch_assoc($result)){
    echo "<tr><td>".$row['field1']."</td><td>".$row['field2']."</td><td>".$row['field3']."</td></tr>";
}
echo '</table>';

回答by andrunix

PHPExcel is your friend. Very easy to use and works like a charm.

PHPExcel 是您的朋友。非常易于使用，就像一个魅力。

https://github.com/PHPOffice/PHPExcel

https://github.com/PHPOffice/PHPExcel

回答by A.A Noman

This is new version of php code

这是新版本的php代码

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "your_dbname";
//mysql and db connection

$con = new mysqli($servername, $username, $password, $dbname);

if ($con->connect_error) {  //error check
    die("Connection failed: " . $con->connect_error);
}
else
{

}


$DB_TBLName = "your_table_name"; 
$filename = "excelfilename";  //your_file_name
$file_ending = "xls";   //file_extention

header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.'.'.$file_ending");  
header("Pragma: no-cache"); 
header("Expires: 0");

$sep = "\t";

$sql="SELECT * FROM $DB_TBLName"; 
$resultt = $con->query($sql);
while ($property = mysqli_fetch_field($resultt)) { //fetch table field name
    echo $property->name."\t";
}

print("\n");    

while($row = mysqli_fetch_row($resultt))  //fetch_table_data
{
    $schema_insert = "";
    for($j=0; $j< mysqli_num_fields($resultt);$j++)
    {
        if(!isset($row[$j]))
            $schema_insert .= "NULL".$sep;
        elseif ($row[$j] != "")
            $schema_insert .= "$row[$j]".$sep;
        else
            $schema_insert .= "".$sep;
    }
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    $schema_insert .= "\t";
    print(trim($schema_insert));
    print "\n";
}

回答by Puzzled Boy

I think you should try with this API

我认为您应该尝试使用此 API

http://code.google.com/p/php-excel/source/browse/trunk/php-excel.class.php

http://code.google.com/p/php-excel/source/browse/trunk/php-excel.class.php

With This

有了这个

Create a quick export from a database table into Excel

Compile some statistical records with a few calculations and deliver
the result in an Excel worksheet

Gather the items off your (web-based) todo list, put them in a
worksheet and use it as a foundation for some more statistics
magic.**

回答by Jek Tv

Try this code:

试试这个代码：

<?php
    header("Content-type: application/vnd-ms-excel");

    header("Content-Disposition: attachment; filename=hasil-export.xls");

    include 'view-lap.php';
?>

回答by luvking

try this code

试试这个代码

data.php

数据.php

    <table border="1">
<tr>
    <th>NO.</th>
    <th>NAME</th>
    <th>Major</th>
</tr>
<?php
//connection to mysql
mysql_connect("localhost", "root", ""); //server , username , password
mysql_select_db("codelution");

//query get data
$sql = mysql_query("SELECT * FROM student ORDER BY id ASC");
$no = 1;
while($data = mysql_fetch_assoc($sql)){
    echo '
    <tr>
        <td>'.$no.'</td>
        <td>'.$data['name'].'</td>
        <td>'.$data['major'].'</td>
    </tr>
    ';
    $no++;
}
?>

code for excel file

excel文件的代码

export.php

导出.php

<?php
// The function header by sending raw excel
header("Content-type: application/vnd-ms-excel");
// Defines the name of the export file "codelution-export.xls"
header("Content-Disposition: attachment; filename=codelution-export.xls");
// Add data table
include 'data.php';
?>

if mysqli version

如果mysqli版本

$sql="SELECT * FROM user_details";
$result=mysqli_query($conn,$sql);
if(mysqli_num_rows($result) > 0)
{
    $no = 1;
            while($data = mysqli_fetch_assoc($result))
            {echo '
    <tr>
        <<td>'.$no.'</td>
        <td>'.$data['name'].'</td>
        <td>'.$data['major'].'</td>

    </tr>
    ';
    $no++;

http://codelution.com/development/web/easy-ways-to-export-data-from-mysql-to-excel-with-php/

http://codelution.com/development/web/easy-ways-to-export-data-from-mysql-to-excel-with-php/

回答by Trialsman

Posts by John Peter and Dileep kurahe helped me to develop what I consider as being a simpler and cleaner solution, just in case anyone else is still looking. (I am not showing any database code because I actually used a $_SESSION variable.)

John Peter 和 Dileep kurahe 的帖子帮助我开发了我认为更简单、更干净的解决方案，以防其他人仍在寻找。（我没有显示任何数据库代码，因为我实际上使用了 $_SESSION 变量。）

The above solutions invariably caused an error upon loading in Excel, about the extension not matching the formatting type. And some of these solutions create a spreadsheet with the data across the page in columns where it would be more traditional to have column headings and list the data down the rows. So here is my simple solution:

上述解决方案在 Excel 中加载时总是会导致错误，关于扩展名与格式类型不匹配。并且其中一些解决方案创建了一个电子表格，其中包含跨页面的数据在列中，在其中具有列标题并在行中列出数据更为传统。所以这是我的简单解决方案：

$filename = "webreport.csv";
header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename");  
header("Pragma: no-cache"); 
header("Expires: 0");
foreach($results as $x => $x_value){
    echo '"'.$x.'",' . '"'.$x_value.'"' . "\r\n";
}

1. Change to .csv (which Excel instantly updates to .xls and there is no error upon loading.)
2. Use the comma as delimiter.
3. Double quote the Key and Value to escape any commas in the data.
4. I also prepended column headers to $resultsso the spreadsheet looked even nicer.

1. 更改为 .csv（Excel 立即更新为 .xls，加载时没有错误。）
2. 使用逗号作为分隔符。
3. 双引号键和值以转义数据中的任何逗号。
4. 我还预先添加了列标题，$results因此电子表格看起来更好。

回答by Samir Lakhani

Try the Following Code Please. just only update two values.
1.your_database_name 2.table_name

请尝试以下代码。只更新两个值。
1.your_database_name 2.table_name

 <?php
    $host="localhost";
    $username="root";
    $password="";
    $dbname="your_database_name";
    $con = new mysqli($host, $username, $password,$dbname); 

        $sql_data="select * from table_name";
        $result_data=$con->query($sql_data);
        $results=array();
    filename = "Webinfopen.xls"; // File Name
    // Download file
    header("Content-Disposition: attachment; filename=\"$filename\"");
    header("Content-Type: application/vnd.ms-excel");

    $flag = false;
    while ($row = mysqli_fetch_assoc($result_data)) {
        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";
    }
    ?>