将mysql结果导出到excel

Question

我找到了一个小脚本，可以将信息导出到 xls 文件，但我似乎无法让它工作。原始代码可以在这里找到： http://www .appservnetwork.com/modules.php?name=News&file=article&sid=8

这是我的代码：

// 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();

};

我回显每一行以确保数据正在传递，这是可以的，但对于某些人来说原因，xls 文件是这样吐出的：

公司 ID 公司名称 地址 1 0 2 2 0 0

等等，它似乎以某种方式传递了一个数字，但不是实际的信息..有人可以帮助我吗？或者向我指出一个像样的导出到 excel (xls) 的方法。:)

编辑：

如果有人能弄清楚如何将 xlsWriteLabel 设置为粗体，那就太棒了:)

Answer 1

如果实际信息是字符串，则这将不起作用。 Pack("d" 试图将其转换为 Double 而不是 String。

您需要创建一个 xlsWriteString 函数。

看起来这里有一个： 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;
}

Answer 2

我认为如果您的客户使用的是Office 2007，您最好查看一下Open Document。

php 那里有一个实现。 Excel 2007 查看器也是免费的。