export mysql result to excel..

Question

Hi all, I found a little script that will export information to an xls file, but I can't seem to get it working. The original code is found here: http://www.appservnetwork.com/modules.php?name=News&file=article&sid=8

And here's my code:

// Query Database
$query = 'SELECT * FROM #__db_clients WHERE published = '1' AND companyid IN (1,2) AND country = 'Africa' ORDER BY state ASC';
$db->setQuery($query);
$rows = $db->loadObjectList();

if ($rows) {

 function xlsBOF() { 
  echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
  return; 
 };
 function xlsEOF() { 
  echo pack("ss", 0x0A, 0x00); 
  return; 
 };
 function xlsWriteNumber($Row, $Col, $Value) { 
  echo pack("sssss", 0x203, 14, $Row, $Col, 0x0); 
  echo pack("d", $Value); 
  return; 
 };
 function xlsWriteLabel($Row, $Col, $Value ) { 
  $L = strlen($Value); 
  echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L); 
  echo $Value; 
 return; 
 };

 // Send Header
 header("Pragma: public");
 header("Expires: 0");
 header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
 header("Content-Type: application/force-download");
 header("Content-Type: application/octet-stream");
 header("Content-Type: application/download");;
 header("Content-Disposition: attachment;filename=export.xls ");
 header("Content-Transfer-Encoding: binary ");

 // XLS Data Cell
 xlsBOF();
 xlsWriteLabel(1,0,"Company ID");
 xlsWriteLabel(1,1,"Company Name");
 xlsWriteLabel(1,2,"Address");
 xlsWriteLabel(1,3,"Address 2");
 xlsWriteLabel(1,4,"Suburb");
 xlsWriteLabel(1,5,"City");
 xlsWriteLabel(1,6,"State");
 xlsWriteLabel(1,7,"Post Code");
 xlsWriteLabel(1,8,"Country");
 xlsWriteLabel(1,9,"Date");
 xlsWriteLabel(1,10,"Phone Business");
 xlsWriteLabel(1,11,"Phone Direct");
 xlsWriteLabel(1,12,"Fax");
 xlsWriteLabel(1,13,"Phone Mobile");
 xlsWriteLabel(1,14,"Phone Personal");
 xlsWriteLabel(1,15,"Discount");
 xlsWriteLabel(1,16,"Title");
 xlsWriteLabel(1,17,"Name");
 xlsWriteLabel(1,18,"Surname");
 xlsWriteLabel(1,19,"Position");
 xlsWriteLabel(1,20,"Email");
 xlsWriteLabel(1,21,"Contact");
 xlsWriteLabel(1,22,"Introduced");
 xlsWriteLabel(1,23,"Comments");
 xlsWriteLabel(1,24,"Type");
 xlsWriteLabel(1,25,"Status");
 xlsWriteLabel(1,26,"Rating");
 xlsWriteLabel(1,27,"Credit Terms");
 xlsWriteLabel(1,28,"Classifications");
 $xlsRow = 2;

 $i = 0;
 foreach ($rows as $item) {

  $companyid   = $rows[$i]->companyid;
  $company   = $rows[$i]->company;
  $address   = $rows[$i]->address;
  $address2   = $rows[$i]->address2;
  $suburb   = $rows[$i]->suburb;
  $city    = $rows[$i]->city;
  $state    = $rows[$i]->state;
  $pcode    = $rows[$i]->pcode;
  $country   = $rows[$i]->country;
  $date    = $rows[$i]->date;
  $phone_b   = $rows[$i]->phone_b;
  $phone_d   = $rows[$i]->phone_d;
  $phone_f   = $rows[$i]->phone_f;
  $phone_m   = $rows[$i]->phone_m;
  $phone_p   = $rows[$i]->phone_p;
  $discount   = $rows[$i]->discount;
  $title    = $rows[$i]->title;
  $name    = $rows[$i]->name;
  $surname   = $rows[$i]->surname;
  $position   = $rows[$i]->position;
  $email    = $rows[$i]->email;
  $contact   = $rows[$i]->contact;
  $introduced  = $rows[$i]->introduced;
  $comments   = $rows[$i]->comments;
  $type    = $rows[$i]->type;
  $status   = $rows[$i]->status;
  $rating   = $rows[$i]->rating;
  $cterms   = $rows[$i]->cterms;
  $classifactions = $rows[$i]->classifactions;

  xlsWriteNumber($xlsRow,0,"$companyid");
  xlsWriteNumber($xlsRow,1,"$company");
  xlsWriteNumber($xlsRow,2,"$address");
  xlsWriteNumber($xlsRow,3,"$address2");
  xlsWriteNumber($xlsRow,4,"$suburb");
  xlsWriteNumber($xlsRow,5,"$city");
  xlsWriteNumber($xlsRow,6,"$state");
  xlsWriteNumber($xlsRow,7,"$pcode");
  xlsWriteNumber($xlsRow,8,"$country");
  xlsWriteNumber($xlsRow,9,"$date");
  xlsWriteNumber($xlsRow,10,"$phone_b");
  xlsWriteNumber($xlsRow,11,"$phone_d");
  xlsWriteNumber($xlsRow,12,"$phone_f");
  xlsWriteNumber($xlsRow,13,"$phone_m");
  xlsWriteNumber($xlsRow,14,"$phone_p");
  xlsWriteNumber($xlsRow,15,"$discount");
  xlsWriteNumber($xlsRow,16,"$title");
  xlsWriteNumber($xlsRow,17,"$name");
  xlsWriteNumber($xlsRow,18,"$surname");
  xlsWriteNumber($xlsRow,19,"$position");
  xlsWriteNumber($xlsRow,20,"$email");
  xlsWriteNumber($xlsRow,21,"$contact");
  xlsWriteNumber($xlsRow,22,"$introduced");
  xlsWriteNumber($xlsRow,23,"$comments");
  xlsWriteNumber($xlsRow,24,"$type");
  xlsWriteNumber($xlsRow,25,"$rating");
  xlsWriteNumber($xlsRow,26,"$status");
  xlsWriteNumber($xlsRow,27,"$cterms");
  xlsWriteNumber($xlsRow,28,"$classifactions");

 $xlsRow++;
 $i++;
 };

 xlsEOF();
 exit();

};

I echoed out each row to make sure data is being passed through, which is ok, but for some reason, the xls file is spitting out like this:

Company ID Company Name Address 1 0 2 2 0 0

And so on, it seems to be somehow passing through a number, but not the actual information.. Can anyone help me out? Or point me to a decent export to excel (xls).. :)

EDIT:

If anyone can figure out how to set xlsWriteLabel to be bold, that would be fantastic :)

Answer 1

I think if you client are using Office 2007, you have better to look into the Open Document.

there is an implementation in php there. Also the Excel 2007 viewer is free so.

Answer 2

If the actual information is strings, this won't work. Pack("d" is trying to cast it as a Double instead of a String.

You need to create a xlsWriteString function.

Looks like there's one here: http://hunter.forumotion.com/forum-f9/topic-t98.htm

function xlsWriteString( $Row , $Col , $Value )
{
$L = strlen( $Value );
echo pack( "ssssss" , 0x204 , 8 + $L , $Row , $Col , 0x0 , $L );
echo $Value;
return;
}