exporting mysql db to excel

sarmenhb · 05-29-2008, 04:08 AM

i tried this script below but when i run it this is the error i keep receiving, how can i fix it?? thanks

ERROR

Code:

Warning: Cannot modify header information - headers already sent by (output started at localhost/status/exportxls.php:1) in localhost/status/exportxls.php on line 44

Warning: Cannot modify header information - headers already sent by (output started at localhost/status/exportxls.php:1) in localhost/status/exportxls.php on line 45

Warning: Cannot modify header information - headers already sent by (output started at localhost/status/exportxls.php:1) in localhost/status/exportxls.php on line 46

Warning: Cannot modify header information - headers already sent by (output started at localhost/status/exportxls.php:1) in localhost/status/exportxls.php on line 47

Warning: Cannot modify header information - headers already sent by (output started at localhost/status/exportxls.php:1) in localhost/status/exportxls.php on line 48

Warning: Cannot modify header information - headers already sent by (output started at localhost/status/exportxls.php:1) in localhost/status/exportxls.php on line 49

Warning: Cannot modify header information - headers already sent by (output started at localhost/status/exportxls.php:1) in localhost/status/exportxls.php on line 50

Warning: Cannot modify header information - headers already sent by (output started at localhost/status/exportxls.php:1) in localhost/status/exportxls.php on line 51
���������������Domain Listing�������domain������client���������� �date_prom������status������devsite ������ready������exist_customization����� �new_custom�� ����comments�� ����version������priority������status_desc�� ����customization_yn�����������������
���������
���������������� ������No�������������none������none�� ������� ����� ������10��������� �������������������������heritage��������
������
������� ������No ������no�������������manager module *�� ���"�get someone to add the 300 patches�� ����� ������10��������� ������������������������ �CPA Actuaries���������������

CODE

Code:

<?php

include("config.php");
$result=mysql_query("select * from tbl_clone order by id");




#=============================================
#	Create Function for XLS#=============================================

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 Data
#=============================================



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=status.xls"); 
header("Content-Transfer-Encoding: binary");




xlsBOF();

#=============================================
# Begin Export
# NOTE:  FIRST: ROW   || SECOND: COLUMN
#=============================================

/*

	=========== HEADER DATA ===============
	
*/

xlsWriteLabel(0,0,"Domain Listing");

// Make column labels. (at line 3)
xlsWriteLabel(2,0,"domain");
xlsWriteLabel(2,1,"client");
xlsWriteLabel(2,2,"payrol");
xlsWriteLabel(2,3,"date_prom");
xlsWriteLabel(2,4,"status");
xlsWriteLabel(2,5,"devsite");
xlsWriteLabel(2,6,"ready");
xlsWriteLabel(2,7,"exist_customization");
xlsWriteLabel(2,8,"new_custom");
xlsWriteLabel(2,9,"comments");
xlsWriteLabel(2,10,"version");
xlsWriteLabel(2,11,"priority");
xlsWriteLabel(2,12,"status_desc");
xlsWriteLabel(2,13,"customization_yn");


#=============================================
#	LOOP and spit out info from db
#=============================================



$xlsRow = 14;
while($row=mysql_fetch_array($result)){

xlsWriteNumber($xlsRow,0,$row['domain']);
xlsWriteLabel($xlsRow,1,$row['client']);
xlsWriteLabel($xlsRow,2,$row['payrol']);
xlsWriteLabel($xlsRow,3,$row['date_prom']);
xlsWriteLabel($xlsRow,4,$row['status']);
xlsWriteLabel($xlsRow,5,$row['devsite']);
xlsWriteLabel($xlsRow,6,$row['ready']);
xlsWriteLabel($xlsRow,7,$row['exist_customization']);
xlsWriteLabel($xlsRow,8,$row['new_custom']);
xlsWriteLabel($xlsRow,9,$row['comments']);
xlsWriteLabel($xlsRow,10,$row['version']);
xlsWriteLabel($xlsRow,11,$row['priority']);
xlsWriteLabel($xlsRow,12,$row['status_desc']);
xlsWriteLabel($xlsRow,13,$row['customization_yn']);
$xlsRow++;

            } 
            
xlsEOF();
exit();
mysql_close();

?>

ps: i'm using office 2007 to open the file if that makes a diff.

Orc · 05-29-2008, 04:36 AM

That means there have been headers already sent, do an output control, using flush(); try that

Jim · 05-29-2008, 09:13 AM

Make sure you don't ouput (eg: echo, or even just whitespace) anything before calling the Excel functions. This error means that the options (headers) of the page have already send while your Excel class tries to add/change a header. After the headers are send, you can't edit/add headers.

You can fix this by simply calling ob_start(); at the top of your script, not a very clean way to fix it though...

Orc · 05-29-2008, 09:16 AM

Quote:

Originally Posted by Jim

Make sure you don't ouput (eg: echo, or even just whitespace) anything before calling the Excel functions. This error means that the options (headers) of the page have already send while your Excel class tries to add/change a header. After the headers are send, you can't edit/add headers.

You can fix this by simply calling ob_start(); at the top of your script, not a very clean way to fix it though...

Well the flush() does it pretty much xD should I have put the ob_start in my post? :O

Jim · 05-29-2008, 09:22 AM

Flush() does the opposite of ob_start() so it would only give more errors right?

delayedinsanity · 05-29-2008, 01:45 PM

It's probably a better fix to determine what's being output before the headers are sent. There's no reason why those headers can't be moved to the top of the script, before the function declarations and even before the included file (which could be the culprit as well as this script itself). Send your headers, include your file, run your mysql query, insert code here, then declare the functions, but that's just how I'd go about it. Use ob_start() for what it was intended, but not to fix a mistake.

-m

Salathe · 05-29-2008, 01:49 PM

Make sure there is nothing before the opening <?php in the file you posted above, and absolutely nothing can be output from the config file either, so check for whitespace at the top and tail of that file as well.

sketchMedia · 05-29-2008, 02:14 PM

just a tip about trailing white space, if you omit the closing php tag '?>' you dont run the risk of the script having white space at the tail of the file and still works (well depending on how your code is structured).

sarmenhb · 06-01-2008, 06:07 PM

i tried this peice of code at home and it worked perfectly. the server i use at work is having this problem. something was installed wrong because this doesnt work and the header function doesnt work.. ill have to find out.