php mysql生成excel文件,使用PHP的MySQL到Excel生成

// DB Connection here

mysql_connect("localhost","root","");

mysql_select_db("hitnrunf_db");

$select = "SELECT * FROM jos_users ";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )

{

$header .= mysql_field_name( $export , $i ) . "\t";

}

while( $row = mysql_fetch_row( $export ) )

{

$line = '';

foreach( $row as $value )

{

if ( ( !isset( $value ) ) || ( $value == "" ) )

{

$value = "\t";

}

else

{

$value = str_replace( '"' , '""' , $value );

$value = '"' . $value . '"' . "\t";

}

$line .= $value;

}

$data .= trim( $line ) . "\n";

}

$data = str_replace( "\r" , "" , $data );

if ( $data == "" )

{

$data = "\n(0) Records Found!\n";

}

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

header("Content-Disposition: attachment; filename=your_desired_name.xls");

header("Pragma: no-cache");

header("Expires: 0");

print "$header\n$data";

?>

The code above is used for generating an Excel spreadsheet from a MySQL database,

but we are getting following error:

The file you are trying to open, 'users.xls', is in a

different format than specified by the file extension.

Verify that the file is not corrupted and is from a trusted

source before opening the file. Do you want to open the file

now?

What is the problem and how do we fix it?

解决方案

You're not really generating an Excel file. You're generating what amounts to a .csv file using tabs as the seperator.

To generate a 'real' Excel file, use PHPExcel.