Have Geek Brain

Generating MySQL for Excel using PHP

<?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 above code is used to create an Excel spreadsheet from a MySQL database, but we are getting the following error:

The file you are trying to open, 'users.xls', is in a different file extension. Make sure the file is intact and secure before opening the file. Do you want to open the file now?

What is the problem and how do we fix it?

+2


a source to share


7 replies


You are not really creating an Excel file. You generate what makes up the .csv file using tabs as separator.



To create a "real" Excel file, use PHPExcel .

+5


a source


The problem is that when Excel sees a file ending in .xls, it expects the file to conform to its BIFF standard. Your code doesn't create 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.



+1


a source


It seems that you are creating a tab-delimited file and asserting in your headers that this is the same format as an Excel spreadsheet. This is not an Excel format. And the spreadsheet downloader lets you know that you are misleading people - maybe just yourself.

You should probably look into an export format such as a CSV file; converting from tab delimited to comma delimited won't be difficult.

+1


a source


If you're 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 upload part. Set the Content-type header to text / plain. Excel knows how to open this.

0


a source


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've seen people try this before and it only works with certain versions of Office Excel. It does not work on other platforms with other spreadsheet programs or every version of Excel. Use a standard file format, or at least create a proper OLE2 Compound Document that contains the XLS data in the way that Excel expects if you are going to send the file to the browser as this type.

0


a source


Try PEAR (PHP Extensions and Applications Repository) Spreadsheet_Excel_Writer . I am using this to generate Excel files in BIFF format.

0


a source


Your program is looking for a standard .xls

document that is completely different from what you are outputting. Your program outputs a tab-delimited sheet, which is a file .txt

. Try switching your program from tabs to commas as separators and save as .csv

(comma separated value).

0


a source






More articles: