mysql to excel generation using php

Question

<?php

// 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?

Answer 1

The problem is that when Excel sees a file ending in .xls, it expects the file to conform to its BIFF standard. Your code does not produce a BIFF-compliant file. If you want, I can provide you with PHP functions that produce BIFF-compliant files, but it's too long to post here.

Answer 2

You seem to be generating a file with tab-separated fields, and are claiming in your headers that this is the same format as a binary Excel spreadsheet uses. It isn't the format Excel uses. And the program loading the spreadsheet is letting you know that you are misleading people - possibly just yourself.

You should probably look into using an export format such as a CSV file; converting from tab-delimited to comma-separated is not going to be hard.

Answer 3

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.

Answer 4

If you're just going to create a tab delimited file, why not skip PHP and let MySQL do it for you with SELECT INTO OUTFILE?

PHP can handle the download part. Set the Content-type header to text/plain. Excel knows how to open that.

Answer 5

Modify the code to use pure CSV, and then return the file to the browser as a filename ending in .csv and content type text/csv.

I have seen people try to do this before, and it only works with certain specific versions of Office Excel. It does not work on other platforms with other spreadsheet programs or with every version of Excel. Use a standard file format, or at least generate a proper OLE2 Compound Document file that contains XLS data in the manner which Excel expects if you're going to send the file to the browser as that file type.

Answer 6

try pear spreadsheet writer, i use that to generate excel files in biff format

http://pear.php.net/package/Spreadsheet_Excel_Writer/redirected

Answer 7

Your program is looking for a standard .xls document, which is entirely different than what you are outputting. Your program outputs a tab delimited sheet, which is a .txt file. Try switching your program from tabs to commas as delimiters and saving as a .csv (comma separated value).