HTML Table with Excel export option

Topics: Developer Forum, User Forum
Nov 2, 2011 at 2:49 PM
Edited Nov 2, 2011 at 2:50 PM

Hi All!

I honestly spend last 2h looking for some solution. Im using php function (Code 1) in order to display query results.

Im trying to ad link/Icon - "Save as Excel File" on every dynamically generated page.

I found code (Code 2) but using RAW output from SQL is not perfect solution,

I would like to process data with php and generate static HTML table and add option to save this table as Excel file if needed.

Thank you in advance for any help.

Code 1

function list() {
 include("dbset.php"); //start SQL connection
 $q = mysql_query("SELECT ") or die ('SQL Error !<br>'.$q.'<br>'.mysql_error());
 echo "<table id=\"staff-table\" width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";
 echo "<colgroup>
 <col class=\"col1\" />
 <col class=\"col2\" />
 <col class=\"col3\" />
 <col class=\"col4\" />
 <col class=\"col5\" />
 <col class=\"col6\" />
 echo "<tr><td colspan=\"6\" scope=\"col\"><span class=\"bold\">Training Name: </span>";
 echo "</tD></tr>";
 echo "<tr><td colspan=\"6\" scope=\"col\"><span class=\"bold\">Training Description: </span>";
 echo "</tD></tr>";
 echo "<tr><td colspan=\"6\" scope=\"col\"><span class=\"bold\">Number of unique entries: </span>";
 echo "</tD></tr>";
 echo "<tr>";
 echo "<th scope=\"col\" width=\"10%\"><span class=\"class2\">1</span></th>";
 echo "<th scope=\"col\" width=\"18%\"><span class=\"class2\">2</span></th>";
 echo "<th scope=\"col\" width=\"18%\"><span class=\"class2\">3</span></th>";
 echo "<th scope=\"col\" width=\"12%\"><span class=\"class2\">4</span></th>";
 echo "<th scope=\"col\" width=\"20%\"><span class=\"class2\">5</span></th>";
 echo "<th scope=\"col\" width=\"22%\"><span class=\"class2\">6</span></th>";
 echo "</tr>";
 $t = 1; // colors changer for rows
 while ($a = mysql_fetch_array($q)) {
 echo "<tr class=\"d$t\">";
 echo "<td>".$a['']."</td>";
 echo "<td>".$a['']."</td>";
 echo "<td>".$a['']."</td>";
 echo "<td>".$a['']."</td>";
 echo "<td>".$a['']."</td>";
 echo "<td>".$a['']."</td>";
 echo "</tr>";
 $t != 0 ? $t = 0 : $t = 1;
 echo "<tr><td colspan=\"6\" scope=\"col\"><span class=\"bold\">Number of unique entries: </span>";
 echo "</td></tr>";
 echo "</table>";
 include ("dbend.php"); // end SQL connection
// --------------------------------------------------------> End


Code 2

require_once 'PHPExcel.php';
//require_once 'PHPExcel/IOFactory.php';
// $sql = "Select * from T12594_RELOAD";
$sql = $_SESSION['sql'];
$result = mysql_query("$sql");
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("***-LTD UK")
        ->setLastModifiedBy("***-LTD UK")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("Office 2007 openxml php")
        ->setCategory("DB ");
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Name of Sheet 1');
// datadunp
$rowIterator = 0;
while($row = mysql_fetch_assoc($result)) {
   $col = 0;
    foreach($row as $key=>$value) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $rowIterator, $value);
// Redirect output to a client?s web browser (Excel5)
$today = date('Y-m-d');
header('Content-Type: application/; charset=utf-8');
header('Content-Disposition: attachment;filename="test.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');