wal_toor
asked on
MySql export to XLS (by php) problem
Hellow all,
I have this script for exporting a mysql query to excel. This script is downloaden from: http://www.fundisom.com/phparadise/php/databases/mySQL_to_excel
One of the pieces of data that are exporter are id numbers. These are random numbers of 10 digits. But when I make an export all the id numbers starting with a zero are stripped of this zero. So numbers like '9876569033' remain like this, but numers like '0345606541' become '345606541' in the exported xls file.
I have this script for exporting a mysql query to excel. This script is downloaden from: http://www.fundisom.com/phparadise/php/databases/mySQL_to_excel
One of the pieces of data that are exporter are id numbers. These are random numbers of 10 digits. But when I make an export all the id numbers starting with a zero are stripped of this zero. So numbers like '9876569033' remain like this, but numers like '0345606541' become '345606541' in the exported xls file.
// piece of code that exports as xls
}else if(isset($_POST['filetype']) && ($_POST['filetype'] == 'xls')){
/* FORMATTING FOR EXCEL DOCUMENTS ('.xls') */
//create title with timestamp:
if ($Use_Title == 1)
{
echo("$title\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names
//start while loop to get data
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
//following fix suggested by Josue (thanks, Josue!)
//this corrects output in excel when table fields contain \n or \r
//these two characters are now replaced with a space
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
}
}else ...
ASKER
Hi ycTIN,
I have tried this, and works when directly working in excel, but the mysql export shows '0345606541 and will not be automatically converted.
greetz,
walter
I have tried this, and works when directly working in excel, but the mysql export shows '0345606541 and will not be automatically converted.
greetz,
walter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the ' is not required in this solution
echo "<td x:str=\"$value\">";
ASKER
Yes, this works. I have implemented this in the existing script and works perfectly. Thanks a lot!
greetw,
walter
greetw,
walter
ASKER
Works great! Thankz
just need add the ' into the value front
'0345606541
you can try the different in the MS Excel
when you type 0345606541 and enter, excel will auto convert to 345606541
type '0345606541, excel will show 0345606541