Exporting specific columns from Mysql table to Excel

Question

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

@Mark I have added the xlsEOF() in my file already, I am showing the first part of my code only — kakaajee, May 02 '11 at 12:02
No. If you want to output an Excel file, it should read `application/vnd.ms-excel` and nothing else. — Marcel Korpel, May 02 '11 at 12:06
@kakaajee - only one content type (the last) will actually be sent to the browser, so using all three is unnecessary; and you should actually be sending a content type of application/vnd.ms-excel — Mark Baker, May 02 '11 at 12:07
@kakaajee - Assuming you copied your method from this link http://www.appservnetwork.com/modules.php?name=News&file=article&sid=8 have you put the xlsEOF() in the correct place; and what version of MS Excel are you using to open the generated file? MS Excel 2010 is a bit more fussy about the file being a true BIFF file (as per the MS specification). — Mark Baker, May 02 '11 at 12:10
@kakaajee - Try opening your Excel file in a text editor, and see if there are any PHP/SQL error messages visible. — Mark Baker, May 02 '11 at 14:15
Also, if you learned how to use SQL JOINs, you could retrieve all this data in your initial SQL query, rather than retrieving agent, type and area individually in every single loop — Mark Baker, May 02 '11 at 14:34

score 0 · Answer 1 · answered May 02 '11 at 11:14

0

Try this PEAR extension to create XLS files: Spreadsheet_Excel_Writer. As minimum, it well-documented.

answered May 02 '11 at 11:14

OZ_

11,972
6
45
66

score 0 · Accepted Answer · edited May 23 '17 at 12:12

0

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

edited May 23 '17 at 12:12

Community

1
1

answered May 02 '11 at 11:23

Mark Baker

199,760
28
325
373

Yes, these can be used, but I have to get data from multiple tables and showing under the specific custom columns, like in my code I have mentioned the column names and then in the same order the data in wile loop. s there some simpler solution for this? – kakaajee May 02 '11 at 12:28
It's a pretty simple solution... it all comes down to your SQL query. I doubt if you'll find anything simpler than a single include and about 20 lines of code. – Mark Baker May 02 '11 at 12:30
Mark you are right about that, but the reason is, I am not able to find find any example in which multiple column headings are used. If you can point out or give any code, that will be greathelp. – kakaajee May 02 '11 at 13:03
@kakaajee - So is your problem with the SQL query? or with writing column headings to Excel? – Mark Baker May 02 '11 at 13:15
@Mark, no my problem is not with Query . May be you have not read my problem description closely "export some specific columns from a table not all with custom headings," . I need every column with custom heading, and I want to have the control of every result set I get,for I am getting data from multiple table. thanks – kakaajee May 02 '11 at 13:31
I'm reading your original post word by word, and having great difficulty understanding your problem. You have a select query that retrieves the values you want to export to Excel, you have a set of custom headings that you want to use. But I can't understand what your problem actually is. You should write your custom headings to the appropriate cells in row 3, then loop through the resultset for your database query writing the returned values to the appropriate cells in row 4 onwards. That gives you absolute control... so I'm at a loss to understand what else you need – Mark Baker May 02 '11 at 13:40

Exporting specific columns from Mysql table to Excel

2 Answers2

Linked

Related