Output & in MS-excel using PHP

#1
[eluser]Rey Philip Regis[/eluser]
Hi guys,

I need your help, I have problems on how to output the & sign in excel. The scenario is like this, we have a data like Master & Mind that is stored in one of the field in our database table, but when we tried to output that in excel using headers, the output will turn out to be like Master, the & Mind will not be outputted. Does anybody know how to out the complete sting in excel. Cause I want to outrput the whole word "Master & Mind".

Good day and advance thanks,

#2
[eluser]thePiet[/eluser]
When using headers, your strings should be urlencoded() I think. Allready tried that?

A string like

Code:
Hello, this is Master & Mind

Will then be converted to an URL/header-safe

Code:
Hello,+this+is+Mark+&+David.

EDIT: argh, URL encoded strings are not displayed well here Smile

Cheers

thePiet

#3
[eluser]Rey Philip Regis[/eluser]
Thanks for the reply man, but its not the answer that Im looking at. Cause I want the Master & Mind outputted in excel. What's happening is that when I tried to out that in Excel the output is not complete the output is only Master, the outputr should be Master & Mind same with the data in the database. You know how to do that?

Thanks in advance

#4
[eluser]thePiet[/eluser]
Hmm, then I don't understand your problem Smile

Can you paste some code or something?

#5
[eluser]Rey Philip Regis[/eluser]
example I have a table named test_table

test_table
id | description
1 | master & mind
2 | down to earth
3 | wonder woman
4 | clean the room

now, well outpt the data

$result = mysql_query("Select * from test_table");
.....
......

Then I'll output this in MS-Excel using headers. I created a function for this and I just need to pass the variable of the result set of the query I made above.

function displayToExcel($result)
{
ob_start("ob_gzhandler");
header("Content-Disposition: attachment; filename=campaign_excel.xls");

$i = 0;
while ($i < mysql_num_fields($result))
{
$field_name = mysql_field_name($result, $i);
$header .= $field_name."\t";
$i++;
}

while($row = mysql_fetch_row($result))
{
for($i = 0; $i < mysql_num_fields($result); $i++)
{
$data .= trim($row[$i])."\t";
}

$data .= "\n";
}
echo strtoupper($header)."\n".strtoupper(trim($data));
ob_end_flush();
}

But the problem is that, the output for the Master & Mind data in excel is just Master. The & Mind part is missing. Do you know how to solve this problem? I tried, replacing the & using str_replace with 'and' string, but it doesn't work and the output is still Master.

#6
[eluser]thePiet[/eluser]
Well, I haven't got any experience with this kind of exporting to Excel, I always echo a plain HTML table to some file and give it a .xls extension.

Something like

Code:
<table>
<tr>
<td>Hello</td>
<td>World</td>
</tr>
</table>

Saved as a .xls file is displayed perfectly in Excel.

But again, your problem is other characters than alphanumeric, because of the headers. A plain "&"-sign is simply recognized as a field name in headers.

Good luck,

thePiet

#7
[eluser]Herb[/eluser]
Q: Does $result return: id 1, description Master & Mind?

NO: The problem is not the export to excel; it would appear that the & is not being escaped.

YES: Check out how excel receives the ampersand. I believe it wants !amp to pass a literal ampersand.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.