Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    New Coder
    Join Date
    May 2010
    Location
    Mexico
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts

    exporting mysql table to xls file

    hi! here's the thing, i have some code for export mysql tables to xls files that works perfectly in my local server, but when i move it to the internet server ir doesn't work at all... i can't find out what could be wrong..

    these are the files:
    the button:
    Code:
    <form action="mysql.php" method="post" name="form1" target="_self" id="form1">
      <input type="submit" name="Exporta" id="Exporta" value="Exporta" />
    </form>
    the database connection:

    PHP Code:
    <?php 
          
    require_once("excel.php");
          require_once(
    "excel-ext.php");

          
    // Consultamos los datos desde MySQL

          
    $conEmp mysql_connect("localhost""user""pass");
          
    mysql_select_db("database"$conEmp);
          
    $queEmp "SELECT * FROM table";
          
    $resEmp mysql_query($queEmp$conEmp) or die(mysql_error());
          
    $totEmp mysql_num_rows($resEmp);
          
    // Creamos el array con los datos
          
    while($datatmp mysql_fetch_assoc($resEmp)) {
              
    $data[] = $datatmp;
          }
          
    // Generamos el Excel 
          
    createExcel("excel-mysql.xls"$data);
            exit; 
    ?>
    excel.php:

    PHP Code:
    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        http://www.phpdig.net/ref/rn45re877.html
         */
        
    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), 0x8090x80x00x100x00x0);
                    
    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), 0x20314$row$col0x0);
                
    $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$l$row$col0x0$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), 0x0A0x00);
                
    $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"); 
    excel-ext.php:
    PHP Code:
    <?php
    function createExcel($filename$arrydata) {
        
    $excelfile "xlsfile://".$filename;
        
    $fp fopen($excelfile"wb");  
        if (!
    is_resource($fp)) {  
            die(
    "Error al crear $excelfile");  
        }  
        
    fwrite($fpserialize($arrydata));  
        
    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 "\"" );
        
    readfile($excelfile);  
    }
    ?>
    i keep receiving this error: Warning: fopen(xlsfile://excel-mysql.xls) [function.fopen]: failed to open stream: "xlsStream::stream_open" call failed in /home/u75334/domains/adconsulting.mx/public_html/adacosta/pruebas_php/excel-ext.php on line 4
    Error al crear xlsfile://excel-mysql.xls

    someone? please? thanks!!
    Lo bueno del cine es que durante dos horas los problemas son de otros.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    xlsfile has been mapped to the xlsStream wrapper class. That should work; there is nothing in this class (from a quick scan over) that is system dependant, it relies soley on the packing of binary data. Thats good.
    Has this excel-mysql.xls file been placed into the same directory as the executing script? This will be the same directory as where mysql.php is located, which according to your includes is the same directory as the excel-ext.php and excel.php files.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    New Coder
    Join Date
    May 2010
    Location
    Mexico
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts
    yes! they are located in the same directory

    thanks for your help, i'll check it out with my hosting provider (that's what i should do right?? i can't understand english very well ).
    Lo bueno del cine es que durante dos horas los problemas son de otros.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    I doubt your hosting provider will give you any help with this. It doesn't appear to be a configuration problem as of this point.
    Within the stream_open method, add this:
    PHP Code:
    printf("Filename: %s; parse_url: <pre>%s</pre>"$this->xlsfilenameprint_r($urltrue)); 
    DIRECTLY AFTER:
    PHP Code:
    $this->xlsfilename '/' $url['host'] . $url['path']; 
    And post those results please.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    New Coder
    Join Date
    May 2010
    Location
    Mexico
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts
    these are the results

    Filename: /excel-mysql.xls; parse_url:

    Array
    (
    [scheme] => xlsfile
    [host] => excel-mysql.xls
    )
    Lo bueno del cine es que durante dos horas los problemas son de otros.

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    And theres your problem.
    There is no /excel-mysql.xls file in your root directory.
    In your second code block there with all the database connection stuffs, change this:
    PHP Code:
    createExcel("excel-mysql.xls"$data); 
    to this:
    PHP Code:
    createExcel(dirname(__FILE__) . '/excel-mysql.xls'$data); 
    And post back if that works.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #7
    New Coder
    Join Date
    May 2010
    Location
    Mexico
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks for your help

    i made the changes and i get this

    Warning: parse_url(xlsfile:///home/u75334/domains/adconsulting.mx/public_html/adacosta/pruebas_php/exportar/excel-mysql.xls) [function.parse-url]: Unable to parse URL in /home/u75334/domains/adconsulting.mx/public_html/adacosta/pruebas_php/exportar/excel.php on line 73
    Filename: /; parse_url:


    Warning: fopen(xlsfile:///home/u75334/domains/adconsulting.mx/public_html/adacosta/pruebas_php/exportar/excel-mysql.xls) [function.fopen]: failed to open stream: "xlsStream::stream_open" call failed in /home/u75334/domains/adconsulting.mx/public_html/adacosta/pruebas_php/exportar/excel-ext.php on line 4
    Error al crear xlsfile:///home/u75334/domains/adconsulting.mx/public_html/adacosta/pruebas_php/exportar/excel-mysql.xls

    at least its a different error!!
    Lo bueno del cine es que durante dos horas los problemas son de otros.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Lame.
    Change that back to the way it was and inside the class stream_open, change this:
    PHP Code:
    $this->xlsfilename '/' $url['host'] . $url['path']; 
    to
    PHP Code:
    $this->xlsfilename './' $url['host'] . $url['path']; 
    Shouldn't need to do that, but what can you do.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #9
    New Coder
    Join Date
    May 2010
    Location
    Mexico
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts
    well, actually i don't know exactly why i shoud'n do that... but if it works it's fine for me... i'll let you know
    Lo bueno del cine es que durante dos horas los problemas son de otros.

  • #10
    New Coder
    Join Date
    May 2010
    Location
    Mexico
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts
    uhhhm ... this is the result:

    Filename: ./excel-mysql.xls; parse_url:

    Array
    (
    [scheme] => xlsfile
    [host] => excel-mysql.xls
    )


    Warning: fopen(xlsfile://excel-mysql.xls) [function.fopen]: failed to open stream: "xlsStream::stream_open" call failed in /home/u75334/domains/adconsulting.mx/public_html/adacosta/pruebas_php/exportar/excel-ext.php on line 4
    Error al crear xlsfile://excel-mysql.xls

    Lo bueno del cine es que durante dos horas los problemas son de otros.

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    You are 100% positive that this file exists: /home/u75334/domains/adconsulting.mx/public_html/adacosta/pruebas_php/exportar/excel-mysql.xls
    ?

    You can check this by adding this to your mysql script:
    PHP Code:
    foreach (glob('*.xls') AS $file)
    {
        
    printf("File: %s; is readable: %d, is writable: %d<br />\n"$fileis_readable($file), is_writable($file));

    It should show up on your screen when you attach to that script.
    That must have at least one entry:
    PHP Code:
    Fileexcel-mysql.xlsis readable1is writable
    If you don't see that entry, ensure that excel-mysql.xls has been uploaded.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #12
    New Coder
    Join Date
    May 2010
    Location
    Mexico
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts
    yes, positive... i wrote the code and it returns well the same error:

    Filename: /excel-mysql.xls; parse_url:

    Array
    (
    [scheme] => xlsfile
    [host] => excel-mysql.xls
    )


    Warning: fopen(xlsfile://excel-mysql.xls) [function.fopen]: failed to open stream: "xlsStream::stream_open" call failed in /home/u75334/domains/adconsulting.mx/public_html/adacosta/pruebas_php/exportar/excel-ext.php on line 4
    Error al crear xlsfile://excel-mysql.xls

    maybe its because of the library?? i don't want to be foolish but before i post the question i asked if the MS-Excel Stream Handler existed (in my hosting plan)and thet told me that they don't have that cause its a linux plan... could be that possible?

    thanks thanks thanks again
    Lo bueno del cine es que durante dos horas los problemas son de otros.

  • #13
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    If that has the glob in there, then that result doesnt show that the file exists, or any .xls file.
    The stream handler is irrelevant, it is a custom stream wrapper. It won't matter that you're on linux.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #14
    New Coder
    Join Date
    May 2010
    Location
    Mexico
    Posts
    62
    Thanks
    1
    Thanked 0 Times in 0 Posts
    well i'll keep trying tomorrow i'v been working with this for like 10 hours so i may be tired gotta run its rainnig A LOT here!!!!

    Thanks a lot i really appreciate your help
    Lo bueno del cine es que durante dos horas los problemas son de otros.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •