Download Data from HTML table to excel

Question

Hi,

I have a table, constructed in PHP that displays results from a mysql table.

Like so:

$sql = mysql_query("SELECT * FROM survey");

            echo "<table border='1'>
            <tr>
            <th>Question 1</th>
            <th>Question 2</th>
            <th>Question 3</th>
            <th>Question 4</th>
            <th>Question 5</th>
            <th>Question 6</th>
            <th>Question 7</th>
            <th>Question 8</th>
            <th>Question 9</th>
            <th>Question 10</th>
            <th>Question 11</th>
            <th>Question 12</th>
            <th>Question 13</th>
            <th>Question 14</th>
            <th>eMail</th>
            </tr>";

            while ($row = mysql_fetch_array($sql))
            {
                echo "<tr>";
                echo "<td>" . $row['Question1'] . "</td>";
                echo "<td>" . $row['Question2'] . "</td>";
                echo "<td>" . $row['Question3'] . "</td>";
                echo "<td>" . $row['Question4'] . "</td>";
                echo "<td>" . $row['Question5'] . "</td>";
                echo "<td>" . $row['Question6'] . "</td>";
                echo "<td>" . $row['Question7'] . "</td>";
                echo "<td>" . $row['Question8'] . "</td>";
                echo "<td>" . $row['Question9'] . "</td>";
                echo "<td>" . $row['Question10'] . "</td>";
                echo "<td>" . $row['Question11'] . "</td>";
                echo "<td>" . $row['Question12'] . "</td>";
                echo "<td>" . $row['Question13'] . "</td>";
                echo "<td>" . $row['Question14'] . "</td>";
                echo "<td>" . $row['eMail'] . "</td>";              
            }
            echo "</table>";

What I want to do is output this table into an excel file, without any source code, or table tags. Is there a way I can do this? I've been overwhelmed with information on the internet and not quite sure what I need.

If possible I'd prefer to do this with just PHP, and without any additional libraries.

Answer 1

You could use csv (comma separated values) which can be opened by excel:

while ($row = mysql_fetch_array($sql))
{
  echo '"'. $row['Question1'] . '";';
  echo '"'. $row['Question2'] . '";';
  echo '"'. $row['Question3'] . '";';
  echo '"'. $row['Question4'] . '";';
  echo '"'. $row['Question5'] . '";';
  echo '"'. $row['Question6'] . '";';
  echo '"'. $row['Question7'] . '";';
  echo '"'. $row['Question8'] . '";';
  echo '"'. $row['Question9'] . '";';
  echo '"'. $row['Question10'] . '";';
  echo '"'. $row['Question11'] . '";';
  echo '"'. $row['Question12'] . '";';
  echo '"'. $row['Question13'] . '";';
  echo '"'. $row['Question14'] . '";';
  echo '"'. $row['eMail'] . '"\n';         
}

Answer 2

Well outputting Excel native format would be quite complex.. thats why there are libraries for it. CSV on the other hand is easy and Excel can read that.

$filePath = sys_get_temp_dir() . '/test.csv';
$file = fopen($filePath, '-w');

while (false !== ($row = mysql_fetch_array($sql)))
{
  fputcsv($file, $row);
}

// rewind both pointers
mysql_data_seek($sql, 0);
fseek($file, 0);

// add headers
$rowForHeaders = mysql_fetch_array($sql);
fputcsv($file, array_keys($rowforHeaders));

fclose($file);


// to send the file via the http response
header('Content-type: application/vnd.ms-excel');
header('Content-disposition: attachment; filename="mytable.csv"');
header('Content-length: '.filesize($filePath));
print file_get_contents($filePath);
exit;

Answer 3

Do you want to download an Excel file using your PHP script (ie- the PHP outputs a .xls file) or do you want it to produced HTML output that you can copy/paste into an Excel file without losing formatting?

If the former, look into this nifty tool

If you just want it to produce HTML that you can copy/paste into Excel, so long as your table is properly formatted (and from your source code, it looks like you need to add a </tr> at the end of your while loop) then you should just be able to drag and drop it

Alternatively, Excel should be smart enough to recognize HTML tables so long as there is no <html> tag encapsulating it. this article explains the process, but essentially you can send an Excel file (as per the first "nifty tool" suggestion) with just:

header("Content-type: application/vnd.ms-excel");
echo "
<table>
    ...
</table>";