create formatted excel spreadsheet with MySQL data and PHP using tables -

i working on exporting data mysql database excel spreadsheet. have found code doing before , works quite well. not format spreadsheet. first php/mysql site created , data didn't need pretty. current project requires formatted data.

yesterday, found article may have allowed me need do. idiot, didn't copy link down. can't find today.

here's code -

<?php      require_once("includes/connection.php");      require_once("includes/functions.php");           // set $closed 0 development     $closed = 0;      //create sting allow user see if s/he looking @ open or closed items     if ($closed) {         $filename = filename."_closed";      } else {         $filename = filename."_open";     }      // $data hold result     $data = '<table>';     // row header?     $th   = false;     // define separator character     $sep  = '\t';     // array keys     $thkey = array();     // in first row?      $firstrow = true;      // create query     $query  = "select ";     $query .=   "training_requirements.training, mechanism.mechname, location.locationname, impacted_employees.groupname, ";     $query .=   "training_requirements.datereceived, training_requirements.datestart, training_requirements.datedue, ";     $query .=   "requester.lastname, requester.firstname, impact.impactname, training_requirements.timeneeded, ";     $query .=   "priority.priority, training_requirements.notes ";     $query .= "from ";     $query .=   "training_requirements, impact, impacted_employees, location, mechanism, requester, priority ";     $query .= "where impact.impactid = training_requirements.impactid ";     $query .=   "and impacted_employees.groupid = training_requirements.impempid ";     $query .=   "and location.locationid = training_requirements.trainlocid ";     $query .=   "and mechanism.mechid = training_requirements.mechid ";     $query .=   "and requester.requesterid = training_requirements.requesterid ";     $query .=   "and priority.id = training_requirements.priority ";     $query .=   "and training_requirements.closed = $closed ";     $query .=   "and training_requirements.deleted = 0";      // run query     $result = executequery($connection, $query);      // process query     if (mysqli_num_rows($result) > 0) {         while ($resource = mysqli_fetch_assoc($result)) {             if (empty($thkey)) {                 foreach($resource $key => $value) {                     $thkey[] = $key;                 }             }             $data .= '<tr>';             ($i = 0; $i < count($resource); $i++) {                 if ($firstrow) {                     // create header                     ($j = 0; $j < count($resource); $j++) {                                          $data .= '<th>';                         $data .= $thkey[$j];                         $data .= '</th>';                     }                     $data .= '</tr><tr>';                     $firstrow = false;                 }                 $data .= '<td>';                 if (isset($resource[$thkey[$i]])) {                     $data .= $resource[$thkey[$i]];                  } else {                     $data .= '&nbsp;';                   }                 $data .= '</td>';             }             $data .= '</tr>';         }         $data .= '</table>';         echo $data;     }      //header('content-type: application/excel');     //header("content-disposition: attachment; filename={$filename}.xls");     //header("pragma: no-cache");     //header("expires: 0"); ?> 

the code continues on allow me display results in browser. proper looking table in firefox. when un-comment header statements @ bottom, blank excel file. no cells, no nothing.

using excel 2007(12.0.6715.5000) sp3 mso (12.0.6721.5000), php 5.4.24, , mysql 5.5.40. using plugins , libraries not option.

vern

your code fech data mysql , not make excel file .

here great class making execl file

with pure xml code without extension

<?php  /********************************/ /* code mr korosh raoufi     */ /* www.k2-4u.com                */ /********************************/   /**  * simple excel generating php5  *  * @package utilities  * @license http://www.opensource.org/licenses/mit-license.php  * @author oliver schwarz <oliver.schwarz@gmail.com>  * @version 1.0  */  /**  * generating excel documents on-the-fly php5  *   * uses excel xml-specification generate native  * xml document, readable/processable excel.  *   * @package utilities  * @subpackage excel  * @author oliver schwarz <oliver.schwarz@vaicon.de>  * @version 1.1  *   * @todo issue #4: internet explorer 7 not work given header  * @todo add option give out first line header (bold text)  * @todo add option give out last line footer (bold text)  * @todo add option write file  */ class excel_xml {      /**      * header (of document)      * @var string      */         private $header = "<?xml version=\"1.0\" encoding=\"%s\"?\>\n<workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/tr/rec-html40\">";          /**          * footer (of document)          * @var string          */         private $footer = "</workbook>";          /**          * lines output in excel document          * @var array          */         private $lines = array();          /**          * used encoding          * @var string          */         private $sencoding;          /**          * convert variable types          * @var boolean          */         private $bconverttypes;          /**          * worksheet title          * @var string          */         private $sworksheettitle;          /**          * constructor          *           * constructor allows setting of additional          * parameters library may configured          * one's needs.          *           * on converting types:          * when set true, library tries identify type of          * variable value , set field specification excel          * accordingly. careful article numbers or postcodes          * starting '0' (zero)!          *           * @param string $sencoding encoding used (defaults utf-8)          * @param boolean $bconverttypes convert variables field specification          * @param string $sworksheettitle title worksheet          */         public function __construct($sencoding = 'utf-8', $bconverttypes = false, $sworksheettitle = 'table1')         {                 $this->bconverttypes = $bconverttypes;             $this->setencoding($sencoding);             $this->setworksheettitle($sworksheettitle);         }          /**          * set encoding          * @param string encoding type set          */         public function setencoding($sencoding)         {             $this->sencoding = $sencoding;         }          /**          * set worksheet title          *           * strips out not allowed characters , trims          * title maximum length of 31.          *           * @param string $title title worksheet          */         public function setworksheettitle ($title)         {                 $title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);                 $title = substr ($title, 0, 31);                 $this->sworksheettitle = $title;         }          /**          * add row          *           * adds single row document. if set true, self::bconverttypes          * checks type of variable , returns specific field settings          * cell.          *           * @param array $array one-dimensional array row content          */         private function addrow ($array)         {             $cells = "";                 foreach ($array $k => $v):                         $type = 'string';                         if ($this->bconverttypes === true && is_numeric($v)):                                 $type = 'number';                         endif;                         $v = htmlentities($v, ent_compat, $this->sencoding);                         $cells .= "<cell><data ss:type=\"$type\">" . $v . "</data></cell>\n";                  endforeach;                 $this->lines[] = "<row>\n" . $cells . "</row>\n";         }          /**          * add array document          * @param array 2-dimensional array          */         public function addarray ($array)         {                 foreach ($array $k => $v)                         $this->addrow ($v);         }           /**          * generate excel file          * @param string $filename name of excel file generate (...xls)          */         public function generatexml ($filename = 'excel-export')         {                 // correct/validate filename                 $filename = preg_replace('/[^aa-zz0-9\_\-]/', '', $filename);                  // deliver header (as recommended in php manual)                 header("content-type: application/vnd.ms-excel; charset=" . $this->sencoding);                 header("content-disposition: inline; filename=\"" . $filename . ".xls\"");                  // print out document browser                 // need use stripslashes damn ">"                 echo stripslashes (sprintf($this->header, $this->sencoding));                 echo "\n<worksheet ss:name=\"" . $this->sworksheettitle . "\">\n<table>\n";                 foreach ($this->lines $line)                         echo $line;                  echo "</table>\n</worksheet>\n";                 echo $this->footer;         }  }  ?> 

usage:

$xls = new excel_xml('utf-8', false, 'title text');  $data = array(     1 => array ('column1 text', 'coloumn2 text'),     1 => array ('column1 text', 'coloumn2 text'),     1 => array ('column1 text', 'coloumn2 text'), );  $xls -> addarray($data); $xls -> generatexml('file name');  // print code browser use requred header download