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
– Green Black, Commented Mar 1, 2013 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
– Jonathan Kuhn, Commented Mar 1, 2013 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
– Marc B, Commented Mar 1, 2013 at 22:23

Talvi Watia · Accepted Answer · 2013-03-01 22:14:41Z

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 1, 2013 at 22:14

Talvi Watia

1,0802 gold badges14 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:27:28Z

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, 2017 at 10:27

CommunityBot

11 silver badge

answered Mar 1, 2013 at 22:12

Seth Battin

2,86126 silver badges36 bronze badges

Comments

Gus Costa · Accepted Answer · 2016-09-29 19:16:31Z

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, 2016 at 19:16

Gus Costa

6295 silver badges14 bronze badges

Collectives™ on Stack Overflow

Exporting html (mysql populated) table to excel file

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related