Forum Moderators: coopster
I have the script working fine. But if a variable that does into a cell is only digits and has initial zeros, as in a zip-code such as "00123", then excel "assumes" it's a nmuber, and chops off those zeros. The data sent to excel all has quotes around it, and I just can't figure out how to avoid this other than to add a letter to that field, but a zip code of "x00123" just isn't the best-looking solution.
I've tried opening up the file directly via excel, using different file options, no go.
Anyone run into this before and figure out a workaround?
whenever I work with numbers that need leading zeros I use csv files and open them in a text editor. I have had some other problems with excel randomly formatting what it interprets as numbers. It really is a pain and I haven't yet found a work around for it, aside from not using excel. ;)
It certainly works if you're just typing straight into Excel - the apostophe is stripped and the cell will be treated as text format even if all characters are digits.
IM sure that your using something far more sophisticated.. but this is what im using and I have no probs...
<?php
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // always modified
header("Cache-Control: no-store, no-cache, must-revalidate"); // HTTP/1.1
header("Pragma: no-cache"); // HTTP/1.0
header("Content-Type: application/vnd.ms-excel");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
?>
Try using a leading apostrophe - eg, "'00005"
may i see your code you have for the 'force download'?
You bet:
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=".$filename.".xls");
header("Pragma: no-cache");
header("Expires: 0");echo $data;
"=\"$value\""when writing the value. For example,
="00002"It must be double quotes, single quotes don't work.
$item = '"' . $item . '"' . "\t";
So I'm afraid that's not the solution :(
I believe the formula is:
=TEXT($value;"00000")(perhaps "text" is "format" - i recall that from earlier excel versions)
I believe the formula is:=TEXT($value;"00000")
(perhaps "text" is "format" - i recall that from earlier excel versions)
I'm a bit confused with that one - isn't that Excel Basic? I'm writing to this "pseudo-file" using php, so I'm not sure how I could use =TEXT($value;"00000") or similar...
Does my example work at all? or do you have the same prob with it?
Thanks, Hanu, but yes, I'm already doing that:$item = '"' . $item . '"' . "\t";
No, you're not. There is no equals sign. It should be
$item = '="' . $item . '"' . "\t";
Sometimes reading posts properly helps, you know. I wouldn't have posted, if I hadn't tried it out.
You could write that to the cell in stead of the raw $value. It's an Excel formula that means that the cell will always have five numbers, even when the first ones are zeroes. It will show up in the spreadsheet as text formatted numbers, like this:
00001, 00012, 00123, 01234, 12345
Hanus method will do exactly the same (with the extra equals sign), only, with that method you have to put the leading zeroes inside the quotes. With the formula, you can omit the leading zeroes and they'll be inserted by the spreadsheet (This might not be good if you require that the leading zeroes exist physically in the cell - if you need that, you should make them part of $value vith both methods).
Many thanks, *both* of you for your help and patient explanations - I'll get to implementing that quickly! :)
="01234"
Thanks again everyone for your help; sorry I wasn't quicker at responding! :)
Also, don't worry about responding slowly. That wasn't my concern. It's just that I usually put a little effort into answering a question. Although my answer posts may be short, they are still valid. If they get ignored or not read properly I can sometimes get a bit grumpy. I'll buy you a beer sometime, ok?
and yep, a good beer sounds great :)
