...

View Full Version : exporting mysql table to xls file



rakasv
06-24-2010, 10:39 PM
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:

<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
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:



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), 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");


excel-ext.php:


<?php
function createExcel($filename, $arrydata) {
$excelfile = "xlsfile://".$filename;
$fp = fopen($excelfile, "wb");
if (!is_resource($fp)) {
die("Error al crear $excelfile");
}
fwrite($fp, serialize($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!!

Fou-Lu
06-24-2010, 11:16 PM
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.

rakasv
06-24-2010, 11:27 PM
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 :p).

Fou-Lu
06-24-2010, 11:43 PM
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:


printf("Filename: %s; parse_url: <pre>%s</pre>", $this->xlsfilename, print_r($url, true));


DIRECTLY AFTER:


$this->xlsfilename = '/' . $url['host'] . $url['path'];


And post those results please.

rakasv
06-24-2010, 11:50 PM
these are the results

Filename: /excel-mysql.xls; parse_url:

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

Fou-Lu
06-25-2010, 12:02 AM
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:


createExcel("excel-mysql.xls", $data);

to this:


createExcel(dirname(__FILE__) . '/excel-mysql.xls', $data);


And post back if that works.

rakasv
06-25-2010, 12:19 AM
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!!

Fou-Lu
06-25-2010, 12:29 AM
Lame.
Change that back to the way it was and inside the class stream_open, change this:


$this->xlsfilename = '/' . $url['host'] . $url['path'];

to


$this->xlsfilename = './' . $url['host'] . $url['path'];


Shouldn't need to do that, but what can you do.

rakasv
06-25-2010, 12:35 AM
well, actually i don't know exactly why i shoud'n do that... :D but if it works it's fine for me... i'll let you know

rakasv
06-25-2010, 12:40 AM
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

:(

Fou-Lu
06-25-2010, 12:46 AM
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:


foreach (glob('*.xls') AS $file)
{
printf("File: %s; is readable: %d, is writable: %d<br />\n", $file, is_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:


File: excel-mysql.xls, is readable: 1, is writable: 1

If you don't see that entry, ensure that excel-mysql.xls has been uploaded.

rakasv
06-25-2010, 01:11 AM
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

Fou-Lu
06-25-2010, 01:25 AM
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.

rakasv
06-25-2010, 01:31 AM
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 :D



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum