create formatted excel spreadsheet with MySQL data and PHP using tables

Question

I am working on exporting data from a MySQL database into an Excel spreadsheet. I have found code for doing this before and it works quite well. It does not format the spreadsheet. It was also the first PHP/MySQL site I created and the data didn't need to be pretty. The current project requires formatted data.

Yesterday, I found an article that may have allowed me to do what I need to do. Like an idiot, I didn't copy the link down. I can't find it today.

Here's my code -

<?php 
    require_once("includes/connection.php"); 
    require_once("includes/functions.php");     

    // set $closed to 0 for development
    $closed = 0;

    //create a sting to allow the user to see if s/he is looking at open or closed items
    if ($closed) {
        $filename = FILENAME."_closed"; 
    } else {
        $filename = FILENAME."_open";
    }

    // $data will hold the result
    $data = '<table>';
    // is the row a header?
    $th   = FALSE;
    // define the separator character
    $sep  = '\t';
    // array for keys
    $thKey = array();
    // are we in the first row? 
    $firstRow = TRUE;

    // create the 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 the query
    $result = executeQuery($connection, $query);

    // process the query
    if (mysqli_num_rows($result) > 0) {
        while ($resource = mysqli_fetch_assoc($result)) {
            if (empty($thKey)) {
                foreach($resource as $key => $value) {
                    $thKey[] = $key;
                }
            }
            $data .= '<tr>';
            for ($i = 0; $i < count($resource); $i++) {
                if ($firstRow) {
                    // create the header
                    for ($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 to allow me to display the results in a browser. I get a proper looking table in Firefox. When I un-comment the header statements at the bottom, I get a blank Excel file. No cells, no nothing.

Using Excel 2007(12.0.6715.5000) SP3 MSO (12.0.6721.5000), PHP 5.4.24, and MySQL 5.5.40. Using plugins and libraries is not an option.

Vern

Answer 1

your code is just fech data from MySQL and not make any excel file .

here you are a great class for making execl file

with pure XML code without any extension

<?php

/********************************/
/* Code By Mr Korosh Raoufi     */
/* WwW.k2-4u.CoM                */
/********************************/


/**
 * Simple excel generating from 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 from PHP5
 * 
 * Uses the excel XML-specification to generate a native
 * XML document, readable/processable by excel.
 * 
 * @package Utilities
 * @subpackage Excel
 * @author Oliver Schwarz <oliver.schwarz@vaicon.de>
 * @version 1.1
 * 
 * @todo Issue #4: Internet Explorer 7 does not work well with the given header
 * @todo Add option to give out first line as header (bold text)
 * @todo Add option to give out last line as footer (bold text)
 * @todo Add option to write to 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 to output in the 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
         * 
         * The constructor allows the setting of some additional
         * parameters so that the library may be configured to
         * one's needs.
         * 
         * On converting types:
         * When set to true, the library tries to identify the type of
         * the variable value and set the field specification for Excel
         * accordingly. Be careful with article numbers or postcodes
         * starting with a '0' (zero)!
         * 
         * @param string $sEncoding Encoding to be used (defaults to UTF-8)
         * @param boolean $bConvertTypes Convert variables to field specification
         * @param string $sWorksheetTitle Title for the 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 to set
         */
        public function setEncoding($sEncoding)
        {
            $this->sEncoding = $sEncoding;
        }

        /**
         * Set worksheet title
         * 
         * Strips out not allowed characters and trims the
         * title to a maximum length of 31.
         * 
         * @param string $title Title for worksheet
         */
        public function setWorksheetTitle ($title)
        {
                $title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
                $title = substr ($title, 0, 31);
                $this->sWorksheetTitle = $title;
        }

        /**
         * Add row
         * 
         * Adds a single row to the document. If set to true, self::bConvertTypes
         * checks the type of variable and returns the specific field settings
         * for the cell.
         * 
         * @param array $array One-dimensional array with row content
         */
        private function addRow ($array)
        {
            $cells = "";
                foreach ($array as $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 an array to the document
         * @param array 2-dimensional array
         */
        public function addArray ($array)
        {
                foreach ($array as $k => $v)
                        $this->addRow ($v);
        }


        /**
         * Generate the excel file
         * @param string $filename Name of excel file to 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 to the browser
                // need to use stripslashes for the damn ">"
                echo stripslashes (sprintf($this->header, $this->sEncoding));
                echo "\n<Worksheet ss:Name=\"" . $this->sWorksheetTitle . "\">\n<Table>\n";
                foreach ($this->lines as $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');  // its print code to browser use requred header to download