export data to excel

peppeto

Hi
I have a php script to export data from mysql to excel with

header("Content-Type: application/vnd.ms-excel");

$sql = "SELECT * FROM table ";
$result = mysql_query($sql);
$numRows = mysql_num_rows($result);

for ($row = 1;$row <= $numRows; $row++){
$rowArray = mysql_fetch_row($result);

$ar1 = $rowArray[1];
$var2 = $rowArray[2];

echo $var1."<br>";
echo $var2."<br>";

}
Those values are as int(4) in mysql
The problem is when I open the excel the values are not integer he shows them as standard value and I ca't export them to SPSS
because he works with integers
Is there a way to force the variables to be exported from mysql to excel as integer only
Thanx in advance
Peppe

PHPGuru2

I don't know if this works but you can try casting the data:

$new_value = (int)$old_value;

$new_value is an interger now. I justdon't know what happens if $old_value's type is other than integer and is forced to be integer. Let me know.

tekky

grab the data with PHP, dump it to a TXT comma delimited, have EXCEL load the comma delimited file to the table....

PHP auto casts variables... it could care less what it was before the comparison or function... it just trys to make it work... I believe the general output of a string cast to INT is 0 or some conglamorate value of the ASCII values....

peppeto

I tryed with (int)$oldvalue; nothing hapens he just displais standard I have to try another aproach
Thank you for replay

PHPGuru2

Are you sure that the data in your database is a type of integer?

Check if the data is an integer value or not.

if(!is_numeric($value))
{
echo ($value ." is not a numeric.");
}

you can also use gettype() function. See the PHP Manual (Go to PHP.net and search for "gettype")

ahundiak

You might need a bigger hammer. This pear package will do the trick.
http://pear.php.net/package/Spreadsheet_Excel_Writer

fandelem

http://us2.php.net/manual/en/function.intval.php

might be of some use....

maxpup979

I have also run into a similar problem (I think I used the package ahundiak recommended to fix it)--near as I can tell, unless you can tell Excel exactly what format type to import something as (using one of those packages), it makes the decision for you. So you can have math equations (10-9-8) turned into dates, and so on. So use one of the packages that lets you force a format, and you should be fine

mikerh9

Originally posted by ahundiak
You might need a bigger hammer. This pear package will do the trick.
http://pear.php.net/package/Spreadsheet_Excel_Writer

I looked on the PEAR and installed with PHP.

Can you give a short example ? how it can save file in excel format?

ahundiak

I suppose I could post an example but the pear documentation already has several excellent ones.

LordShryku

PEAR bugs me. I dunno why, it just does. I use this class from phpclasses. You can specify a number by using write_number()