How to export mysql table data to excel 2007

Question

I want to export data from mysql tables to excel sheet. I am using excel 2007. Once this code was working properly but today I am getting problem. Please guide me where I am doing wrong. I have huge data about 60,000 rows.

<?php
/* 
Export MySQL to Excel using PHP & HTML tables
Author: Vlatko Zdrale, http://blog.zemoon.com

Look but don't touch :)
*/
    include "mysql_connection.php";
    //$dbTable = 'info';            // table name
    $con=open_db_connection();


    $sql = "select info_id, name, category_list.category, company_name, company_address, company_phone, date from info, city_list, category_list where city=cid and info.category=id";
    $result = @mysql_query($sql)    or die("Couldn't execute query:<br>".mysql_error().'<br>'.mysql_errno());

    header('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');   //define header info for browser
    header('Content-Disposition:attachment; filename=information-'.date('Ymd').'.xlsx');
    header('Pragma: no-cache');
    header('Expires: 0');

    echo '<table><tr>';
    for ($i = 0; $i < mysql_num_fields($result); $i++)   // show column names as names of MySQL fields
        echo '<th>'.mysql_field_name($result, $i).'</th>';
    print('</tr>');

    while($row = mysql_fetch_row($result))
    {
        //set_time_limit(60); // you can enable this if you have lot of data
        $output = '<tr >';
        for($j=0; $j<mysql_num_fields($result); $j++)
        {
            if(!isset($row[$j]))
                $output .= '<td>&nbsp;</td>';
            else
                $output .= "<td>$row[$j]</td>";
        }
        print(trim($output))."</tr>\t\n";
    }
    echo('</table>');
?>

Its very important please guide me. Thanks in advance.

Duplicate of http://stackoverflow.com/questions/7706093/exporting-mysql-data-into-excel-csv-via-php ? — Jacob Tomlinson, Sep 21 '12 at 08:00
@Passerby its creating .xlsx file and asking to download but when i m opening the file after downloading its giving the error "cannot open file file format or extension is not valid" — Pawan Gupta, Sep 21 '12 at 09:14

score 1 · Accepted Answer · answered Sep 21 '12 at 09:39

You will get that message because the file isn't an OfficeOpenXML xlsx file, but a file containing HTML markup with a .xlsx extension. You're telling Excel that the file is one format by the extension when it's really another, and it let's you know that the content doesn't match the extension. As long as it can read the HTML markup cleanly, it should still load successfully, but will always issue this message.

The more recent versions of Excel are more fussy about this than earlier versions.

If you want to get rid of the message, then either you rename your .xlsx as a .html file so that the extension matches the content (you'll need to import into Excel then, or moodify file associations so that html files are opened using Excel); or give it a tab-separated value file with a csv extension (that can be opened by double click), though you can't add any formatting using this option; or give it a real OfficeOpenXML .xlsx file

Yea you are correct. but I want an OfficeOpenXML xlsx only how can i achieve that with the simple modification of above code ?? — Pawan Gupta, Sep 21 '12 at 09:56
You can't achieve it with a simple modification of your code, you'd need to use a library such as PHPExcel that actually creates real OfficeOpenXML file — Mark Baker, Sep 21 '12 at 11:30
Ohhk thanks but now I hv tired with code so i just connected excel sheet with mysql using odbc connecter. now there no need to export always when i data updated in mysql table again and again. :) — Pawan Gupta, Sep 21 '12 at 11:32

score 0 · Answer 2 · answered Sep 21 '12 at 08:01

0

First, you should put the output in a buffer variable and not echo or print all the way.

After that you should read your own comment -> //set_time_limit(60); // you can enable this if you have lot of data

answered Sep 21 '12 at 08:01

Fry_95

241
1
6

I m very new in php so don't know whw to put output in buffer variable will u pls give me a simple example for that – Pawan Gupta Sep 21 '12 at 09:26
Just put a `$output = '';` at the beginning and replace `echo` and `print` by `$output .=` then put a `print $output;` at the end. – Fry_95 Sep 21 '12 at 09:37

score 0 · Answer 3 · answered Sep 21 '12 at 08:02

0

First of all: tell us something about the error!

I recommend you to do one of the two following steps (or both).

set_time_limit(60); 
// increase this value to 300 (means the server doesn't timeout for 5 minutes)

ini_set('memory_limit','512M');
// increases the memory limit to 512 MB

answered Sep 21 '12 at 08:02

sics

1,298
1
11
24

thanks for ur advice but still i m getting same error. I hv added both of line u recommended but getting same error – Pawan Gupta Sep 21 '12 at 09:18

score 0 · Answer 4 · answered Sep 21 '12 at 08:03

0

Try to use "\t" between columns not rows, it makes the native offset/margin in Excel. Also better use for loop with curly-braces as a better standard to not confuse Yourself.

answered Sep 21 '12 at 08:03

Arthur Kushman

3,449
10
49
64

score 0 · Answer 5 · answered Sep 21 '12 at 09:33

From your comment I'll guess something is messing up the markup.

In everything between <th></th> and <tr></tr>, try to use htmlentities:

for ($i = 0; $i < mysql_num_fields($result); $i++)   // show column names as names of MySQL fields
    echo '<th>'.htmlentities(mysql_field_name($result, $i),ENT_COMPAT,"UTF-8").'</th>';

And

if(is_null($row[$j]))
     $output .= '<td>&nbsp;</td>';
else
     $output .= "<td>".htmlentities($row[$j],ENT_COMPAT,"UTF-8")."</td>";

Note that I'm assuming your encoding to be UTF-8.

How to export mysql table data to excel 2007

5 Answers5