mysql导出列到excel,将特定的列从Mysql表导出到Excel

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