How to set datatype in Excel, when importing a HTML table

Posted on 2009-04-22
Last Modified: 2012-05-06
Hi Experts :-)

I'm using PHP to make som output that will open in Excel. (see code)

My problem is that some cell's conatin a long integer, that Excel then decides to convert, eg. '100175181000500000' is converted to '1,00175E+17'

How can I prevent this using the HTML -> Excel routine ?
header("Content-Type: application/");

	header("Expires: 0");

	header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

	header("content-disposition: attachment;filename=trg_scannerdata_". $id ."_". $date .".xls");


	echo "<table width=100% ><tr><td>Terminal ". $scanner['term'] ."</td><td>Bil ". $scanner['bil'] ."</td><td>Chauff&oslash;r ". $scanner['chauf'] ."</td></tr></table>";

	echo "<table border=1>";

	echo "<tr bgcolor='#cccccc'><td width=10></td><td><b>Fragtbrev nr.</b></td><td><b>Tid</b></td><td><b>Modtaget</b></td><td><b>Leveret</b></td></tr>";



		echo "\n\r <!-- ". $data[$i]['sql'] ." -->\n\r";

		$tmp = "<tr><td>". ($i + 1) ."</td><td>". $data[$i]['kode'] ."</td><td>". $data[$i]['tid'] ."</td>";

		$tmp .= $data[$i]['modtaget'] == 'Ja' ? "<td bgcolor='green'>". $data[$i]['modtaget'] ."</td>" : "<td bgcolor='red'>". $data[$i]['modtaget'] ."</td>" ;

		$tmp .= $data[$i]['leveret'] == 'Ja' ? "<td bgcolor='green'>". $data[$i]['leveret'] ."</td></tr>" : "<td bgcolor='red'>". $data[$i]['leveret'] ."</td></tr>" ;

		echo $tmp;


	echo "</table>";

Open in new window

Question by:trg_dk
    LVL 2

    Expert Comment

    You can't stop it doing that, it's just how numbers work on computers.
    LVL 2

    Accepted Solution

    Ended up using ' in the cell with numbers I had to show as text.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    As this topic comes over and over again in different forms, I've finally decided to write a short (yea, right...) article / tutorial about pagination with PHP with MySQL database. There are dozens of these kind of tutorials, I know - I wanted to mak…
    Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now