Exporting to Excel

  • tommya
  • Graduate
  • Graduate
  • tommya
  • Posts: 221
  • Loc: United Kingdom

Post 3+ Months Ago

Hi,

I've got the code below to export a list to excel, works fine, except that Excel is stripping the leading zero's from the abdialNumber column, which should be represented as 000, 001, 002 etc....
I dont know how to get it to display it as a string instead of a number. I know the leading ' can be put in, but if I try to do this, excel is displaying it when it usually hides it

Any ideas

Code: [ Select ]

<?php
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=current_abdial_list.xls");
header("Pragma: no-cache");
header("Expires: 0");
include("includes/php/dblib.inc");
?>



<?php
$sql = "SELECT contactName, abdialNumber FROM abdials ORDER BY abdialNumber";        
$export = mysql_query($sql);
$fields = mysql_num_fields($export);
$data = "";
$header = "";
?>

<?php
for ($i = 0; $i < $fields; $i++) {     
  $header .= mysql_field_name($export, $i) . "\t";
}
?>

<?php

while($row = mysql_fetch_row($export)) {
  $line = '';
  foreach($row as $value) {                      
    if ((!isset($value)) OR ($value == "")) {
      $value = "\t";
    } else {
      $value = str_replace('"', '""', $value);
      $value = '"' . $value . '"' . "\t";
    }
    $line .= $value;
  }
  $data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);

?>



<?php

if ($data == "") {
  $data = "\n(0) Records Found!\n";            
}

?>

<?php
print "Due to the current problems with exporting number beginning with a zero, \n";
print "please note that all numbers are actually 3 digit numbers. i.e 7 = 007 and 43 = 043 etc...\n\n\n";
print "$header\n$data";
?>
  1. <?php
  2. header("Content-type: application/octet-stream");
  3. header("Content-Disposition: attachment; filename=current_abdial_list.xls");
  4. header("Pragma: no-cache");
  5. header("Expires: 0");
  6. include("includes/php/dblib.inc");
  7. ?>
  8. <?php
  9. $sql = "SELECT contactName, abdialNumber FROM abdials ORDER BY abdialNumber";        
  10. $export = mysql_query($sql);
  11. $fields = mysql_num_fields($export);
  12. $data = "";
  13. $header = "";
  14. ?>
  15. <?php
  16. for ($i = 0; $i < $fields; $i++) {     
  17.   $header .= mysql_field_name($export, $i) . "\t";
  18. }
  19. ?>
  20. <?php
  21. while($row = mysql_fetch_row($export)) {
  22.   $line = '';
  23.   foreach($row as $value) {                      
  24.     if ((!isset($value)) OR ($value == "")) {
  25.       $value = "\t";
  26.     } else {
  27.       $value = str_replace('"', '""', $value);
  28.       $value = '"' . $value . '"' . "\t";
  29.     }
  30.     $line .= $value;
  31.   }
  32.   $data .= trim($line)."\n";
  33. }
  34. $data = str_replace("\r","",$data);
  35. ?>
  36. <?php
  37. if ($data == "") {
  38.   $data = "\n(0) Records Found!\n";            
  39. }
  40. ?>
  41. <?php
  42. print "Due to the current problems with exporting number beginning with a zero, \n";
  43. print "please note that all numbers are actually 3 digit numbers. i.e 7 = 007 and 43 = 043 etc...\n\n\n";
  44. print "$header\n$data";
  45. ?>
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

do you need the leading zeros?
  • tommya
  • Graduate
  • Graduate
  • tommya
  • Posts: 221
  • Loc: United Kingdom

Post 3+ Months Ago

not particularly, its more for convenience.
if they're not there, you always get some idiot who wants them in "cos he's confused" :x

judging by your reply, its probably more hassle than its worth no doubt?

is it possible to format it during the export or doesit have to be formatted manually after export?
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

I just got a funny feeling that you will have to set something like that inside your program prefs (Excel) when opening a delimited file.

Sorry I don't know how to do this - tried a couple things but came off second best
  • tommya
  • Graduate
  • Graduate
  • tommya
  • Posts: 221
  • Loc: United Kingdom

Post 3+ Months Ago

no problem mate, will soldier on regardless. :roll:
cheers again

Post Information

  • Total Posts in this topic: 5 posts
  • Users browsing this forum: No registered users and 105 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.