将特定的列从Mysql表导出到Excel [英] Exporting specific columns from Mysql table to Excel

问题描述

我正在尝试从表中导出一些特定的列，而不是全部使用自定义标题，但是当我从服务器下载它时，它表示该文件已损坏，无法打开。我使用以下代码

  //导出到excel的功能。 
函数xlsBOF（）{$​​ b $ b echo pack（ssssss，0x809,0x8,0x0,0x10,0x0,0x0）; 
返回; 
} 
函数xlsEOF（）{$​​ b $ b echo pack（ss，0x0A，0x00）; 
返回; 
} 
函数xlsWriteNumber（$ Row，$ Col，$ Value）{
 echo pack（sssss，0x203,14，$ Row，$ Col，0x0）; 
 echo pack（d，$ Value）; 
返回; 
} 
 
 
函数xlsWriteLabel（$ Row，$ Col，$ Value）{
 $ L = strlen（$ Value）; 
 echo pack（ssssss，0x204,8 + $ L，$ Row，$ Col，0x0，$ L）; 
 echo $ Value; 
返回; 
} 
 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 = orderlist.xls）; 
 header（Content-Transfer-Encoding：binary）; 
 
 xlsBOF（）; 
 xlsWriteLabel（0,5，LIST OF CLOSED LEADS）; 
 
 //设置列标签。 （在第3行）
 xlsWriteLabel（2,0查询号）; 
 xlsWriteLabel（2,1，POSTED DATE）; 
 xlsWriteLabel（2,2，AGENT NAME）; 
 xlsWriteLabel（2,3，STATUS）; 
 xlsWriteLabel（2,4，客户名称）; 
 xlsWriteLabel（2,5，UNIT NUMBER）; 
 xlsWriteLabel（2,6，PROPERTY TYPE）; 
 xlsWriteLabel（2,7，AREA）; 
 xlsWriteLabel（2,8，COMMISSION RECEIVED）; 
 xlsWriteLabel（2,9，BREAK UP DETAIL）; 
 xlsWriteLabel（2,10，DESCRIPTION）; 
 xlsWriteLabel（2,11，ADMIN COMMENT）; 
 
 $ xlsRow = 4; 
 
 while（$ row = mysql_fetch_array（$ result））{
 
 $ agent_query =从pf_agents中选择名称，其中ID =。$ row ['agent_id']; 
 $ agent_result = mysql_query（$ agent_query）; 
 $ agent_row = mysql_fetch_array（$ agent_result）; 
 $ agent_name = $ agent_row ['Name']; 
 
 $ prop_query =从pf_property_types中选择标题，其中ID =。$ row ['prop_type']; 
 $ prop_result = mysql_query（$ prop_query）; 
 $ prop_row = mysql_fetch_array（$ prop_result）; 
 $ prop_name = $ prop_row ['Title']; 
 
 $ area_query =从pf_master_dev中选择名称，其中ID =。$ row ['Area']; 
 $ area_result = mysql_query（$ area_query）; 
 $ area_row = mysql_fetch_array（$ area_result）; 
 $ area_name = $ area_row ['Name']; 
 
 $ break_up = strip_word_html（$ row ['reason_closed']）; 
 $ comment = strip_word_html（$ row ['comment']）; 
 $ admin_comment = strip_word_html（$ row ['comment_admin']）; 
 $ date_closed = date（'d-m-Y'，strtotime（$ row ['updated_date']））; 
 
 xlsWriteLabel（$ xlsRow，0，$ row ['ID']）; 
 xlsWriteLabel（$ xlsRow，1，$ date_closed）; 
 xlsWriteLabel（$ xlsRow，2，$ agent_name）; 
 xlsWriteLabel（$ xlsRow，3，$ row ['status']）; 
 xlsWriteLabel（$ xlsRow，4，$ row ['name']）; 
 xlsWriteLabel（$ xlsRow，5，$ row ['closed_unit']）; 
 xlsWriteLabel（$ xlsRow，6，$ prop_name）; 
 xlsWriteLabel（$ xlsRow，7，$ area_name）; 
 xlsWriteLabel（$ xlsRow，8，$ row ['closed_amt']）; 
 xlsWriteLabel（$ xlsRow，9，$ break_up）; 
 xlsWriteLabel（$ xlsRow，10，$ comment）; 
 xlsWriteLabel（$ xlsRow，11，$ admin_comment）; 
 
 $ xlsRow ++; 
} 
 xlsEOF（）; 
  

有什么办法可以解决它，还是其他解决方案，我可以为其使用。 p>

谢谢

解决方案

有很多可以写入Excel的PHP库文件。尝试我自己的 PHPExcel 或响应这个SO问题

编辑

有一个例子，使用PHPExcel将数据从MySQL写入Excel文件 here

I was trying to export some specific columns from a table not all with custom headings, but when I download it from server, it says the file is corrupt and cannot be opened. I am using the following code

// Functions for export to excel.
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;
}
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=orderlist.xls ");
header("Content-Transfer-Encoding: binary ");

xlsBOF();
xlsWriteLabel(0,5,"LIST OF CLOSED LEADS.");

// Make column labels. (at line 3)
xlsWriteLabel(2,0,"INQUIRY NO."); 
xlsWriteLabel(2,1,"POSTED DATE");
xlsWriteLabel(2,2,"AGENT NAME");
xlsWriteLabel(2,3,"STATUS");
xlsWriteLabel(2,4,"CLIENT NAME");
xlsWriteLabel(2,5,"UNIT NUMBER");
xlsWriteLabel(2,6,"PROPERTY TYPE");
xlsWriteLabel(2,7,"AREA");
xlsWriteLabel(2,8,"COMMISSION RECEIVED");
xlsWriteLabel(2,9,"BREAK UP DETAIL");
xlsWriteLabel(2,10,"DESCRIPTION");
xlsWriteLabel(2,11,"ADMIN COMMENT");

$xlsRow = 4;

while($row=mysql_fetch_array($result)){

   $agent_query = "select Name from pf_agents where ID =".$row['agent_id'];
   $agent_result=mysql_query("$agent_query");
   $agent_row=mysql_fetch_array($agent_result);
   $agent_name = $agent_row['Name'];

   $prop_query = "select Title from pf_property_types where ID =".$row['prop_type'];
   $prop_result = mysql_query("$prop_query");
   $prop_row = mysql_fetch_array($prop_result);
   $prop_name = $prop_row['Title'];

   $area_query = "select Name from pf_master_dev where ID =".$row['Area'];
   $area_result = mysql_query("$area_query");
   $area_row = mysql_fetch_array($area_result);
   $area_name = $area_row['Name'];

   $break_up = strip_word_html($row['reason_closed']);
   $comment = strip_word_html($row['comment']);
   $admin_comment = strip_word_html($row['comment_admin']);
   $date_closed = date('d-m-Y', strtotime($row['updated_date']));

   xlsWriteLabel($xlsRow,0,$row['ID']);
   xlsWriteLabel($xlsRow,1,$date_closed);
   xlsWriteLabel($xlsRow,2,$agent_name);
   xlsWriteLabel($xlsRow,3,$row['status']);
   xlsWriteLabel($xlsRow,4,$row['name']);
   xlsWriteLabel($xlsRow,5,$row['closed_unit']);
   xlsWriteLabel($xlsRow,6,$prop_name);
   xlsWriteLabel($xlsRow,7,$area_name);
   xlsWriteLabel($xlsRow,8,$row['closed_amt']);
   xlsWriteLabel($xlsRow,9,$break_up);
   xlsWriteLabel($xlsRow,10,$comment);
   xlsWriteLabel($xlsRow,11,$admin_comment);

   $xlsRow++;
}
xlsEOF();

Is there any way I can fix it, or some other solution I can user for it.

Thanks

解决方案

There are plenty of PHP libraries that can write to Excel files. Try my own PHPExcel or one of the libraries listed in response to this SO question

EDIT

There's an example of writing data from MySQL to an Excel file using PHPExcel here

这篇关于将特定的列从Mysql表导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！