[Resolved] Export mySQL to Excel question

[skeetley]

I have the code below that exports my mysql data to excel. The two porblems I have are that the data starts after five blank rows in excel. Is there a way to change this?
Also, I have a date column in my data and excel shows this as ###### is there anyway to have it automatically format this column as a date?

<?php 
header("Content-type: application/octet-stream"); 
header("Content-Disposition: attachment; filename=".$Date_Valid."_noon_actuals.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
define(db_host, "localhost"); 
define(db_user, "user"); 
define(db_pass, "pass"); 
define(db_link, mysql_connect(db_host,db_user,db_pass)); 
define(db_name, "ops_db"); 
mysql_select_db(db_name); 
?> 
<?php 
$select = "SELECT * FROM Actual_Noon";                 

$export = mysql_query($select); 
$fields = mysql_num_fields($export); 
?> 
<?php 
for ($i = 0; $i < $fields; $i++) { 
    $header .= mysql_field_name($export, $i) . "\t"; 
} 
?> 
<?php 
while($row = mysql_fetch_row($export)) { 
    $line = ''; 
    foreach($row as $value) {                                             

        if ((!isset($value)) OR ($value == "")) { 
            $value = "\t"; 
        } else { 
            $value = str_replace('"', '""', $value); 
            $value = '"' . $value . '"' . "\t"; 
        } 
        $line .= $value; 
    } 
    $data .= trim($line)."\n"; 
} 
$data = str_replace("\r","",$data); 
?> 
<?php 
if ($data == "") { 
    $data = "\n(0) Records Found!\n";                         

} 
?> 
<?php 
print "$header\n$data"; 
?> 

[jpmoriarty]

don't know why the blank lines, but the ##### is probably because the column width is too small and it can't display the date properly - just widen the column and it will appear

[web_lacky]

I have an application that does something similar-- one item that differs in mine is the following line:

header("Content-type: application/octet-stream");

I have:

header("Content-Type: application/vnd.ms-excel");

See if that helps.

[skeetley]

Thanks for the suggestions.

For the first, yes the column width was too small. Sorry i thought it was a formatting issue.

For the second, I tired changing the header but it didn't make any noticable difference.

Any other ideas?

[web_lacky]

Here's the code that I use...doesn't appear to have the problem you mentioned. Try it out, see if it works for you. 🙂

<?php

//MySQL Connection Info:
$DB_Server = "localhost";    	//your MySQL Server 
$DB_Username = "user"; 			//your MySQL User Name 
$DB_Password = "password";    			//your MySQL Password 
$DB_DBName = "DB";    			//your MySQL Database Name 
$DB_TBLName = "tablename";    			//your MySQL Table Name 

//DEFINE SQL QUERY:
$sql = "Select * from $DB_TBLName";

/*
**Optional: print out title to top of Excel file with Timestamp
**for when file was generated:
**set $Use_Titel = 1 to generate title, 0 not to use title
*/
$Use_Title = 1;
//define date for title: EDIT this to create the time-format you need
$now_date = date('m-d-Y H:i');
//define title for .doc or .xls file: EDIT this if you want
$title = "This is the title: $now_date";

//define file name
$file_name = "filename";
/*

Leave the connection info below as it is:
just edit the above.

(Editing of code past this point recommended only for advanced users.)
*/
//create MySQL connection
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
    or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
    or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect)
    or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());

$file_type = "vnd.ms-excel";
$file_ending = "xls";

//header info for browser: determines file type ('.xls')
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=$file_name.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");

/*    Start of Formatting for Excel    */
/*    FORMATTING FOR EXCEL DOCUMENTS ('.xls')   */

//create title with timestamp:
if ($Use_Title == 1){
echo("$title\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character

//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names

//start while loop to get data
/*
note: the following while-loop was taken from phpMyAdmin 2.1.0.
--from the file "lib.inc.php".
*/
    while($row = mysql_fetch_row($result))
    {
        //set_time_limit(60); // HaRa
        $schema_insert = "";
        for($j=0; $j<mysql_num_fields($result);$j++)
        {
            if(!isset($row[$j]))
                $schema_insert .= "NULL".$sep;
            elseif ($row[$j] != "")
                $schema_insert .= "$row[$j]".$sep;
            else
                $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
		//following fix suggested by Josue (thanks, Josue!)
		//this corrects output in excel when table fields contain \n or \r
		//these two characters are now replaced with a space
		$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
    }
?>

[skeetley]

web_lacky,
Many thanks, that code works perfect.

[web_lacky]

great, glad I could help. 🙂