• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

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

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/vnd.ms-excel");
	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

1 Solution
You can't stop it doing that, it's just how numbers work on computers.
trg_dkAuthor Commented:
Ended up using ' in the cell with numbers I had to show as text.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now