logo标签列表

将HTML（使用MySQL填充）表格导出为Excel文件

phphtmlmysqlexport-to-excel
3

我正在尝试查询一个mysql数据库并在表格中显示数据，这部分工作已经完成。目前，它被设置为在特定日期范围内显示结果。

现在我想把表格做成一个按钮，让你可以将它导出到Excel文件中。在添加选择日期范围选项之前，您可以将其导出到Excel，但现在似乎第二个文件不知道我在谈论哪个表格。我尝试使用POST发送数据的值并在其他页面上重新查询。

当我点击导出按钮时，下载的Excel文档是空的（尽管它有一个大小）。请帮忙解决一下问题吗？

-----查询mysql---------

 <html><head><title>New Production Rejections</title></head></html>
    <?php 

    include("config.php");
    //get serial from submitted data
        //$serial = $_POST['sNumber'];
        //if the submitted data is empty
        $serial = $_POST['entryDate'];
    $dateEnd = $_POST['entryDate2'];
        //parse the serial from the link in tracker
    ?>
        <form method="post" action="<?php echo "queryNewProdRejections.php?"?>">
        Search between dates: (Format: YYYY-MM-DD)<input type='text' size='20' maxlength='20' name='entryDate'> - <input type='text' size='20' maxlength='20' name='entryDate2'>
        <input type="submit" value="Search Date Range"><br/></form>

    <?php


    //query based on approved date that is nothing, repaired date that is nothing, 
    //tech is a real tech, location that is not Revite (RVP), action was to replace, 
    //and the status is not (declined or skipped).

    $query = "SELECT *
    FROM `rma`
    WHERE `origin` NOT LIKE 'Field_failure'
    AND `origin` NOT LIKE 'DOA_at_Customer'
    AND `origin` NOT LIKE 'Sweden_Fail_VI'
    AND `entry` > '$serial' AND `entry` < '$dateEnd'";
    $data = mysql_query($query) or die(mysql_error());

    //Create a table with the array of data from repairs, based on the previous query

    echo "<table border='1'><tr><th>RMA</th><th>Product</th><th>Serial</th><th>Origin</th><th>Return To</th><th>Credit Num</th><th>Order</th><th>Entry Date</th><th>Tech</th><th>Traking Num</th></tr>";

    while($row = mysql_fetch_array($data)){ 
        print "<tr><td>".$row['intrma']."</td><td>".$row['product']."</td><td>".$row['serial']."</td><td>".$row['origin']."</td><td>".$row['retto']."</td><td>".$row['creditnum']."</td><td>".$row['ordernum']."</td><td>".$row['entry']."</td><td>".$row['tech']."</td><td>".$row['tracknum']."</td></tr>";
    }
    print "</table>";

    ?>
    <html>
    <form method="post" action="saveQueryToExcel.php">
    <input type='hidden' name='ent_1' value="<?php echo $_POST['entryDate']; ?>">
    <input type='hidden' name='ent_2' value="<?php echo $_POST['entryDate2']; ?>">
      <input type="submit" value="Save to Excel">
    </form>
    </html>

---------------打印到Excel文件 -- (saveQueryToExcel.php)

<html><head><title>New Production Rejections</title></head></html>

<?php
error_reporting(0);

$dateBeg=$_POST['ent_1'];
$dateEnd=$_POST['ent_2'];

//Connect to the database, repairs in maprdweb
include("config.php");

//query based on approved date that is nothing, repaired date that is nothing, 
//tech is a real tech, location that is not Revite (RVP), action was to replace, 
//and the status is not (declined or skipped).

$query = "SELECT *
FROM `rma`
WHERE `origin` NOT LIKE 'Field_failure'
AND `origin` NOT LIKE 'DOA_at_Customer'
AND `origin` NOT LIKE 'Sweden_Fail_VI'
AND `entry` > '$dateBeg' AND `entry` < '$dateEnd'";
$data = mysql_query($query) or die(mysql_error());

//Create a table with the array of data from repairs, based on the previous query
header('Content-type: application/vnd.ms-excel');

echo "<table border='1'><tr><th>RMA</th><th>Product</th><th>Serial</th><th>Origin</th><th>Return To</th><th>Credit Num</th><th>Order</th><th>Entry Date</th><th>Tech</th><th>Traking Num</th></tr>";

while($row = mysql_fetch_array($data)){ 
    print "<tr><td>".$row['intrma']."</td><td>".$row['product']."</td><td>".$row['serial']."</td><td>".$row['origin']."</td><td>".$row['retto']."</td><td>".$row['creditnum']."</td><td>".$row['ordernum']."</td><td>".$row['entry']."</td><td>".$row['tech']."</td><td>".$row['tracknum']."</td></tr>";
}
print "</table>";
?>
1
你应该告诉我们，你的问题是什么。 - Oliver
谢谢，我漏掉了那一部分。 - Emeria
1
据我所知，Excel 不能直接处理表格。只需创建一个 CSV 文件，并用逗号分隔数据即可。您可以在 Excel 中打开 CSV 文件。 - Green Black
@John，实际上是可以的。像表格这样的基本HTML在Excel中可以正常工作，你只需要收到一个消息，说文件似乎不是正确的格式，但点击“确定”就可以了。 - Jonathan Kuhn
Excel可以接受HTML表格作为输入，但最好使用PHPExcel生成本地的Excel文件。 - Marc B
3个回答
3

PHPexcel非常适合将数据导出到实际的Excel文档中。

看起来你只是在生成一个HTML表格来展示结果，这并不完全符合Excel格式。

1
你应该使用相同的代码，而不是打印<tr>行，将其发送到 fputcsv。使用标准输出作为文件句柄。您需要设置适当的HTML头以将输出作为CSV发送，类似于此帖子：force file download。
1

看看这个类。它能够解决问题。

https://github.com/GSTVAC/HtmlExcel

$xls = new HtmlExcel();
$xls->addSheet("Names", $names);
$xls->headers();
echo $xls->buildFile();
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接