Exporting html (mysql populated) table to excel file

Question

I am trying to query a mysql database and display data in a table. That part is working. At the moment, it is set up for displaying the results within the certain date range.

I now want to take the table and make a button that allows you to export it to an Excel file. Before I added the option of choosing a date range, you were able to export to Excel, but now it seems that the second file does not know what table I am talking about. I tried using POST to send the values of the data and re-query on the other page.

When I click the button to export, the excel document that is downloaded is empty, (though it has a size). Any help please?

-----Query 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>

---------------Print to Excel File -- (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>";
?>

excel does not work with tables as far as I know. Simply create a CSV file and separate the data with comma's. You can open a csv file in excel. — Green Black, Mar 01 '13 at 22:11
@John actually it does. basic html like tables works fine in excel, you just get a message saying the file doesn't look like it is in the right format but click ok and it works. — Jonathan Kuhn, Mar 01 '13 at 22:21
excel will accept html tables as input, but you'd be better off using PHPExcel to generate a NATIVE excel file. — Marc B, Mar 01 '13 at 22:23

score 3 · Accepted Answer · answered Mar 01 '13 at 22:14

3

PHPexcel works great for exporting data to an actual Excel document.

It appears you are just generating an HTML table with your result.. which isn't exactly Excel format.

answered Mar 01 '13 at 22:14

Talvi Watia

1,070
1
14
28

score 1 · Answer 2 · edited May 23 '17 at 10:27

1

You should take the same code, and instead of printing <tr> lines with it, send it to fputcsv. Use the standard output as the file handle. You need to set proper html headers for the output to be sent as a csv, along the lines of this post: force file download.

edited May 23 '17 at 10:27

Community

1
1

answered Mar 01 '13 at 22:12

Seth Battin

2,811
22
36

score 1 · Answer 3 · answered Sep 29 '16 at 19:16

1

Take a look a this class. It is able to solve the problem.

https://github.com/GSTVAC/HtmlExcel

$xls = new HtmlExcel();
$xls->addSheet("Names", $names);
$xls->headers();
echo $xls->buildFile();

answered Sep 29 '16 at 19:16

Gus Costa

609
5
13

Exporting html (mysql populated) table to excel file

3 Answers3