将mysql结果导出到excel [英] export mysql result to excel

问题描述

我找到了一个小脚本，该脚本可以将信息导出到xls文件，但似乎无法正常工作.原始代码位于此处: http://www .appservnetwork.com/modules.php?name = News& file = article& sid = 8

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

这是我的代码:

// 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文件会像这样吐出来:

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:

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

Company ID Company Name Address 1 0 2 2 0 0

以此类推，似乎通过某种方式传递了一个数字，但没有传递实际的信息.有人可以帮助我吗?或指出我要导出到excel(xls)..:)

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).. :)

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

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

推荐答案

如果实际信息是字符串，则此方法将无效. Pack("d"试图将其强制转换为Double而不是String.

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

您需要创建一个xlsWriteString函数.

You need to create a xlsWriteString function.

这里好像有一个: 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;
}

这篇关于将mysql结果导出到excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！