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?

It works very well for me, your code is helpful. – justnajm May 26 '13 at 12:22 — justnajm, May 26 '13 at 12:22

score 5 · Answer 1 · answered Apr 26 '10 at 16:01

5

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.

answered Apr 26 '10 at 16:01

Marc B

356,200
43
426
500

With .NET you might as well just use the Excel COM object and work with that directly. PHPExcel is typically used on non-Windows platforms. – Marc B May 03 '10 at 17:30
Excel COM object is not similar to PHPExcel, it requires Excel to be installed on the machine. – Rosdi Kasim May 03 '10 at 21:33

score 1 · Answer 2 · answered Apr 26 '10 at 13:28

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.

score 1 · Answer 3 · answered Apr 26 '10 at 13:29

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.

score 0 · Answer 4 · answered Apr 26 '10 at 16:05

0

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.

answered Apr 26 '10 at 16:05

dnagirl

20,196
13
80
123

score 0 · Answer 5 · answered Apr 26 '10 at 16:12

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.

score 0 · Answer 6 · edited Jan 21 '11 at 08:41

0

Try PEAR's (PHP Extension and Application Repository) Spreadsheet_Excel_Writer. I use that to generate Excel files in the BIFF format.

edited Jan 21 '11 at 08:41

Peter Mortensen

30,738
21
105
131

answered May 03 '10 at 04:25

bumperbox

10,166
6
43
66

score 0 · Answer 7 · answered May 03 '10 at 04:33

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).

MySQL to Excel generation using PHP

7 Answers7

Linked