Hello guys!

I am working with an PHP page that will generate an excel file. The flow is all transaction made should be generated in an excel file. Also if there is another transation, the new information will be appended in the same excel file.

I have work on this and it generates an excel file, but it only shows in one cell of the excel file. Then when I try to append the new transaction made, it appends but the new information is appended in the same cell.(I used fopen for the option in writing to the file).

If anyone has an idea of how to place the information in different cells, would be appreciated. Below is the code I have taken from the internet and the output generated:

Page used to locate the directory where to place the generated excel file and calling a class to generate the excel file:

<?php

require_once "excel.php"; 
$filename = "theFile.xls";
?>
<p>   
</p>
<?php $export_file = "xlsfile: \\xampp\\htdocs\\".$filename; 
$fp = fopen($export_file, "w+"); 
if (!is_resource($fp))
{  
die("Cannot open $export_file"); 
}  
// typically this will be generated/read from a database table<br />   
$assoc = array(  
array("First name" => "Mattias", "IQ" => 250), 
array("First name" => "Tony", "IQ" => 100),
array("First name" => "Peter", "IQ" => 100),
array("First name" => "Edvard", "IQ" => 100));  ?> <br /> <?php
fwrite($fp, serialize($assoc));   
fclose($fp);

header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
header ("Cache-Control: no-cache, must-revalidate");   
header ("Pragma: no-cache");  
header ("Content-type: application/x-msexcel"); 
header ("Content-Disposition: attachment; filename=\"" . $filename . "\"" );  
header ("Content-Description: PHP/INTERBASE Generated Data" ); 
readfile($export_file);   
exit;  


?>

Class that is called to generate an excel file:

<?php
/**
 * MS-Excel stream handler
 * This class read/writes a data stream directly
 * from/to a Microsoft Excel spreadsheet
 * opened with the xlsfile:// protocol
 * This is used to export associative array data directly to MS-Excel
 * @requires    PHP 4 >= 4.3.2
 * @author      Ignatius Teo            <ignatius@act28.com>
 * @copyright   (C)2004 act28.com       <http://act28.com>
 * @version     0.3
 * @date        20 Jan 2005
 * $Id: excel.php,v 1.3 2005/01/20 09:58:58 Owner Exp $
 */
class xlsStream
{
    /* private */
    var $position = 0;          // stream pointer
    var $mode = "rb";           // default stream open mode
    var $xlsfilename = null;    // stream name
    var $fp = null;             // internal stream pointer to physical file
    var $buffer = null;         // internal write buffer
    var $endian = "unknown";	// little | unknown | big endian mode
	var $bin = array(
		"big" => "v",
		"little" => "s",
		"unknown" => "s",
	);

	/**
	 * detect server endian mode
	 * thanks to Charles Turner for picking this one up
	 * @access	private
	 * @params	void
	 * @returns	void
	 * @see		[url]http://www.phpdig.net/ref/rn45re877.html[/url]
	 */
	function _detect()
	{
		// A hex number that may represent 'abyz'
		$abyz = 0x6162797A;

		// Convert $abyz to a binary string containing 32 bits
		// Do the conversion the way that the system architecture wants to
		switch (pack ('L', $abyz))
		{
		    // Compare the value to the same value converted in a Little-Endian fashion
		    case pack ('V', $abyz):
		        $this->endian = "little";
		        break;

		    // Compare the value to the same value converted in a Big-Endian fashion
		    case pack ('N', $abyz):
		        $this->endian = "big";
		        break;

		    default:
		        $this->endian = "unknown";
		        break;
		}
	}

    /**
     * called by fopen() to the stream
     * @param   (string)    $path           file path
     * @param   (string)    $mode           stream open mode
     * @param   (int)       $options        stream options (STREAM_USE_PATH |
     *                                      STREAM_REPORT_ERRORS)
     * @param   (string)    $opened_path    stream opened path
     */
    function stream_open($path, $mode, $options, &$opened_path)
    {
        $url = parse_url($path);
        $this->xlsfilename = '/' . $url['host'] . $url['path'];
        $this->position = 0;
        $this->mode = $mode;

		$this->_detect();	// detect endian mode

        //@TODO: test for invalid mode and trigger error if required

        // open underlying resource
        $this->fp = @fopen($this->xlsfilename, $this->mode);
        if (is_resource($this->fp))
        {
            // empty the buffer
            $this->buffer = "";

            if (preg_match("/^w|x/", $this->mode))
            {
                // write an Excel stream header
                $str = pack(str_repeat($this->bin[$this->endian], 6), 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
                fwrite($this->fp, $str);
                $opened_path = $this->xlsfilename;
                $this->position = strlen($str);
            }
        }
        return is_resource($this->fp);
    }

    /**
     * read the underlying stream resource (automatically called by fread/fgets)
     * @todo    modify this to convert an excel stream to an array
     * @param   (int)       $byte_count     number of bytes to read (in 8192 byte blocks)
     */
    function stream_read($byte_count)
    {
        if (is_resource($this->fp) && !feof($this->fp))
        {
            $data .= fread($this->fp, $byte_count);
            $this->position = strlen($data);
        }
        return $data;
    }

    /**
     * called automatically by an fwrite() to the stream
     * @param   (string)    $data           serialized array data string
     *                                      representing a tabular worksheet
     */
    function stream_write($data)
    {
        // buffer the data
        $this->buffer .= $data;
        $bufsize = strlen($data);
        return $bufsize;
    }

    /**
     * pseudo write function to manipulate the data
     * stream before writing it
     * modify this to suit your data array
     * @access  private
     * @param   (array)     $data           associative array representing
     *                                      a tabular worksheet
     */
    function _xls_stream_write($data)
    {
        if (is_array($data) && !empty($data))
        {
            $row = 0;
            foreach (array_values($data) as $_data)
            {
                if (is_array($_data) && !empty($_data))
                {
                    if ($row == 0)
                    {
                        // write the column headers
                        foreach (array_keys($_data) as $col => $val)
                        {
                            // next line intentionally commented out
                            // since we don't want a warning about the
                            // extra bytes written
                            // $size += $this->write($row, $col, $val);
                            $this->_xlsWriteCell($row, $col, $val);
                        }
                        $row++;
                    }

                    foreach (array_values($_data) as $col => $val)
                    {
                        $size += $this->_xlsWriteCell($row, $col, $val);
                    }
                    $row++;
                }
            }
        }
        return $size;
    }

    /**
     * Excel worksheet cell insertion
     * (single-worksheet supported only)
     * @access  private
     * @param   (int)       $row            worksheet row number (0...65536)
     * @param   (int)       $col            worksheet column number (0..255)
     * @param   (mixed)     $val            worksheet row number
     */
    function _xlsWriteCell($row, $col, $val)
    {
        if (is_float($val) || is_int($val))
        {
            // doubles, floats, integers
            $str  = pack(str_repeat($this->bin[$this->endian], 5), 0x203, 14, $row, $col, 0x0);
            $str .= pack("d", $val);
        }
        else
        {
            // everything else is treated as a string
            $l    = strlen($val);
            $str  = pack(str_repeat($this->bin[$this->endian], 6), 0x204, 8 + $l, $row, $col, 0x0, $l);
            $str .= $val;
        }
        fwrite($this->fp, $str);
        $this->position += strlen($str);
        return strlen($str);
    }

    /**
     * called by an fclose() on the stream
     */
    function stream_close()
    {
        if (preg_match("/^w|x/", $this->mode))
        {
            // flush the buffer
            $bufsize = $this->_xls_stream_write(unserialize($this->buffer));

            // ...and empty it
            $this->buffer = null;

            // write the xls EOF
            $str = pack(str_repeat($this->bin[$this->endian], 2), 0x0A, 0x00);
            $this->position += strlen($str);
            fwrite($this->fp, $str);
        }

        // ...and close the internal stream
        return fclose($this->fp);
    }

    function stream_eof()
    {
        $eof = true;
        if (is_resource($this->fp))
        {
            $eof = feof($this->fp);
        }
        return $eof;
    }
}

stream_wrapper_register("xlsfile", "xlsStream")
    or die("Failed to register protocol: xlsfile");
?>

Output that is shown in one cell only:

a:4:{i:0;a:2:{s:10:"First name";s:7:"Mattias";s:2:"IQ";i:250;}i:1;a:2:{s:10:"First name";s:4:"Tony";s:2:"IQ";i:100;}i:2;a:2:{s:10:"First name";s:5:"Peter";s:2:"IQ";i:100;}i:3;a:2:{s:10:"First name";s:6:"Edvard";s:2:"IQ";i:100;}}


please, please help... Thanks a lot

Excel is basically tab delimited. Basically, in between columns add a "\t" to insert a tab to let excel know that the first column is finished and the second column should start. To start a new row, add a "\n" to force excel to put the next data in a separate row.

I tried the tab, its works but the unnecessary characters are still showing as well as the \n is not working. The unnecessary characters are those in the output such as :, ;, {, } etc.
this value should be removed. please help...

Output:
a:4:{i:0;a:2:{s:10:"First name";s:7:"Mattias";s:2:"IQ";i:250;}i:1;a:2:{s:10:"First name";s:4:"Tony";s:2:"IQ";i:100;}i:2;a:2:{s:10:"First name";s:5:"Peter";s:2:"IQ";i:100;}i:3;a:2:{s:10:"First name";s:6:"Edvard";s:2:"IQ";i:100;}}

Below is the block of code I have modified for the \n and \t suggestion:

$assoc = array(  
array("First name\t" => "Mattias\t", "IQ\t" => 250 + "\n"), 
array("First name\t" => "Tony\t", "IQ\t" => 100 + "\n"),
array("First name" => "Peter", "IQ" => 100),
array("First name" => "Edvard", "IQ" => 100));  ?> <br /> <?php

help please.. thanks...

Hello Guys!

Please help on this if you have an idea.

Thanks..

You could use a regular expression to evaluate the output to get rid of :, ;, {, }

<?php
$string='a:4:{i:0;a:2:{s:10:"First name";s:7:"Mattias";s:2:"IQ";i:250;}i:1;a:2:{s:10:"First name";s:4:"Tony";s:2:"IQ";i:100;}i:2;a:2:{s:10:"First name";s:5:"Peter";s:2:"IQ";i:100;}i:3;a:2:{s:10:"First name";s:6:"Edvard";s:2:"IQ";i:100;}} ';
preg_match_all ("/[^0-9^a-z^_^A-Z^.:]/", $string, $matches);
	// Loop through the matches with foreach
	foreach ($matches[0] as $value) {
           $string = str_replace($value, "", $string);
	}
echo $string;
?>

This will return

a:4:i:0a:2:s:10:Firstnames:7:Mattiass:2:IQi:250i:1a:2:s:10:Firstnames:4:Tonys:2:IQi:100i:2a:
2:s:10:Firstnames:5:Peters:2:IQi:100i:3a:2:s:10:Firstnames:6:Edvards:2:IQi:100
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.